Блог

У меня есть родственник, у которого есть маршруты авиакомпаний и аэропорты, через которые проходят эти рейсы по таким маршрутам. Я пытаюсь определить, какие маршруты проходят через одни и те же аэропорты.

Я сократил отношение до таблицы, которой должно быть возможно манипулировать, чтобы создать желаемый результат:

 SELECT route_id, airport_id
FROM routes_airports
WHERE stops = false
ORDER BY route_id, airport_id;
  

Я хотел бы сопоставлять маршруты друг с другом, когда они имеют одинаковые значения для airport_id в своих записях в таблице, включая маршруты, которые демонстрируют это свойство.

Так, например, маршруты 5 и 7 пропускают аэропорты 10,15,20, поэтому их следует сопоставлять вместе, но не с маршрутом 10, который пропускает только аэропорты 10 и 20.

  route_id | skipped_airport_id
---------- ------------
        1 |         76
        2 |         21*
        2 |         22*
        4 |         42
        5 |         21*
        5 |         22*
        7 |         15
        7 |         16
        7 |         17
        7 |         18
        7 |         46
        9 |         26
       11 |         19
       14 |         45*
       14 |         46*
       14 |         47*
       15 |         45*
       15 |         46*
       15 |         47*
       17 |         78
       20 |         20
  

Я бы хотел, чтобы приведенные выше данные примера привели к таблице только с теми маршрутами, которые имеют совпадение, как показано ниже.

  route_id 
----------
        2 
        5
       14
       15
  

Вы можете сделать это, объединив все пропущенные аэропорты в массив, а затем найти те маршруты, где эти массивы совпадают:

 with skipped as (
  select route_id, array_agg(skipped_airport_id order by skipped_airport_id) skipped_airports
  from routes_airports
  where stops = false
  group by route_id
) 
select s1.*
from skipped s1
where exists (select * 
              from skipped s2
              where s1.route_id <> s2.route_id
                and s1.skipped_airports = s2.skipped_airports);
  

Это возвращает:

 route_id | skipped_airports
--------- -----------------
       2 | {21,22}         
       5 | {21,22}         
      14 | {45,46,47}      
      15 | {45,46,47}      
  

Онлайн-пример:https://rextester.com/MJPJ90714

Попробуйте что-то вроде этого:

 SELECT route_id, STRING_AGG(airport_id, ',') AS airports
FROM routes_airports
WHERE stops = FALSE
GROUP BY route_id
ORDER BY 2
  

Это соберет airport_id s в один столбец и ORDER BY этот столбец.

 WITH
  skips AS
(
  SELECT route_id, STRING_AGG(airport_id, ',' ORDER BY airport_id) AS airport_ids
  FROM routes_airports
  WHERE stops = false
  GROUP BY route_id
)
SELECT airport_ids, STRING_AGG(route_id, ',' ORDER BY route_id) AS route_ids
FROM skips
GROUP BY airport_ids
HAVING COUNT(*) > 1