Presto 如何编写按两列剔除反向重复行的SQL查询

原有方案的问题根源 #

使用 NOT EXISTS、自连接实现时出现两行反向配对记录被全部删除的现象，本质是筛选逻辑存在缺陷：规则设置为「只要存在反向匹配的行就排除当前行」，导致两个互为反向的行互相满足排除条件，最终被同时过滤。

核心实现逻辑 #

针对colA、colB构成的无序配对特性，为每一组配对生成统一的无序列组标识——无论两个值的存储顺序如何，只要值的组合完全一致就归入同一分组，最后每个分组内仅保留任意一行即可。 #

可直接运行的Presto查询 #

方案1：窗口函数分组去重（大表场景推荐，性能最优） #

通过生成统一组标识后用窗口函数给组内条目编号，仅保留编号为1的记录，完全规避全删问题：

WITH paired_base AS (
    SELECT
        colA,
        colB,
        colC,
        -- 生成统一组key：取字典序较小的值放在前面，较大的值放在后面，同组key完全一致
        concat(least(colA, colB), '||', greatest(colA, colB)) AS group_key
    FROM your_business_table
),
ranked_record AS (
    SELECT
        *,
        -- 组内随机排序编号，排序规则可根据业务需求调整，不影响去重效果
        row_number() OVER (
            PARTITION BY group_key
            ORDER BY rand()
        ) AS rn
    FROM paired_base
)
SELECT colA, colB, colC
FROM ranked_record
WHERE rn = 1;

适配说明：如果存在colA = colB的特殊场景，上述逻辑可正常处理——此时least与greatest返回值完全一致，生成的组key仅对应单行记录，不会出现误删，也不会与其他行构成反向配对。

方案2：自连接过滤（无窗口函数场景的备选实现） #

调整自连接的过滤规则，避免两行全删：无反向配对的行直接保留，存在反向配对的行仅保留字典序更靠前的单条记录：

SELECT t1.colA, t1.colB, t1.colC
FROM your_business_table t1
LEFT JOIN your_business_table t2
    ON t1.colA = t2.colB
    AND t1.colB = t2.colA
WHERE t2.colA IS NULL
   OR t1.colA < t1.colB;

逻辑说明：没有反向配对的行无法关联到t2的记录，会直接进入结果集；存在反向配对的两行，仅colA字典序小于colB的行满足保留条件，另一行会被过滤，不会出现双向全删的问题。

输出效果验证 #

针对提供的示例数据（(AA,BB,CC)、(BB,AA,CC)、(DD,EE,FF)、(AA,DD,GG)），最终查询输出为：

• (AA,BB,CC)
• (DD,EE,FF)
• (AA,DD,GG)
  与业务预期完全匹配。

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