如何用SQL筛选航班数据中的唯一与非唯一往返组合？

现有航班数据 #

dep, arr
uk, usa
usa, uk
china,aus
aus,china
brazil,uk

筛选需求 #

• 唯一组合：出发地与目的地的往返记录均存在的航线，结果示例：

dep, arr
uk, usa
usa, uk
china,aus
aus,china

• 非唯一组合：仅存在单向记录的航线，结果示例：

brazil,uk

现有查询代码 #

已尝试以下窗口函数查询，但不确定如何精准筛选非唯一组合：

Select          
           dep, arr,  count(*) over (partition by least(dep, arr), greatest(dep, arr) order by dep) rnk
From flights

解决方案 #

你的思路方向是对的——用least()和greatest()把往返航线归为同一分组，接下来可基于分组计数直接筛选两类数据：

1. 筛选唯一组合（往返均存在） #

先计算每个航线组的总记录数，筛选出总记录数为2的组即可：

SELECT dep, arr
FROM (
    SELECT 
        dep, arr,
        COUNT(*) OVER (PARTITION BY LEAST(dep, arr), GREATEST(dep, arr)) AS group_count
    FROM flights
) t
WHERE group_count = 2;

2. 筛选非唯一组合（仅单向存在） #

同理，筛选总记录数为1的组：

SELECT dep, arr
FROM (
    SELECT 
        dep, arr,
        COUNT(*) OVER (PARTITION BY LEAST(dep, arr), GREATEST(dep, arr)) AS group_count
    FROM flights
) t
WHERE group_count = 1;

替代写法（GROUP BY子查询关联） #

如果偏好不用窗口函数，也可以先分组统计航线组数量，再关联原表筛选：

-- 唯一组合
SELECT f.dep, f.arr
FROM flights f
JOIN (
    SELECT LEAST(dep, arr) AS a, GREATEST(dep, arr) AS b
    FROM flights
    GROUP BY a, b
    HAVING COUNT(*) = 2
) g ON (f.dep = g.a AND f.arr = g.b) OR (f.dep = g.b AND f.arr = g.a);

-- 非唯一组合
SELECT f.dep, f.arr
FROM flights f
JOIN (
    SELECT LEAST(dep, arr) AS a, GREATEST(dep, arr) AS b
    FROM flights
    GROUP BY a, b
    HAVING COUNT(*) = 1
) g ON (f.dep = g.a AND f.arr = g.b) OR (f.dep = g.b AND f.arr = g.a);

内容的提问来源于stack exchange，提问作者morgan