如何制定SQL查询以识别表中的匹配集[英] How to formulate a SQL query to identify sets of matches across a table

本文是小编为大家收集整理的关于如何制定SQL查询以识别表中的匹配集的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

本文来自:IT宝库(https://www.itbaoku.cn)

我的关系具有航空公司路线和这些航班上的机场.我正在尝试确定在同一机场上跳过哪些路线.

我将关系缩减为应该可以操纵以创建我所需结果的表格:

:

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

当他们在桌子上的条目中具有相同的机场_id值时,我想彼此匹配路线,同时包括展示此属性的路线.

所以,例如,第5和7号路线都跳过机场10,15,20,因此应将它们匹配,但不要与10号公路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

本文地址:https://www.itbaoku.cn/post/2648289.html

问题描述

I have a relation that has airline routes and the airports that these flights go over on such routes. I'm trying to identify what routes skip over the same airports.

I have whittled down the relation into a table that should be possible to manipulate to create my desired result:

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

I would like to match routes to one another when they have the same values for airport_id across their entries in the table while including the routes that exhibit this property.

So, for example, routes 5 and 7 both skip over airports 10,15,20, so they should be matched together, but not with say, route 10 which only skips over airports 10 and 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

I would like the above example data to result in a table with just the routes that have a match such as below.

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

推荐答案

You can do this by aggregating all skipped airports into an array and then find out those routes where those arrays are the same:

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);

This returns:

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

Online example: https://rextester.com/MJPJ90714

其他推荐答案

Try something like this:

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

This will collect the airport_ids into a single column, and ORDER BY that column.

其他推荐答案

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