sql - 如何制定 SQL 查询以识别表中的匹配集

Question

我有一个关系，有航线和这些航类在这些航线上经过的机场。我正在尝试确定哪些路线会跳过相同的机场。

我已经将关系缩减到一个表中，应该可以对其进行操作以创建我想要的结果:

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 和 20 的路线 10 匹配。

 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

Answer 1

您可以通过将所有跳过的机场聚合到一个数组中，然后找出那些数组相同的路线来做到这一点:

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