本文是小编为大家收集整理的关于如何制定SQL查询以识别表中的匹配集的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
我的关系具有航空公司路线和这些航班上的机场.我正在尝试确定在同一机场上跳过哪些路线.
我将关系缩减为应该可以操纵以创建我所需结果的表格:
: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
问题描述
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
更多相关问答
联系站长