Postgres共现计数表按col1分组取TopN最大计数的正确SQL查询方法

核心逻辑说明 #

问题本质是共现记录为单向存储，同一个共现对(A,B)仅存储一次，需要先将单向记录展开为双向，才能保证每个对象作为统计主体时，能拿到所有关联对象的共现计数。

最优SQL实现 #

不需要提前手动拼接数据，直接在查询中用UNION ALL生成双向共现记录，配合窗口函数取TOP N即可：

WITH bidirectional_cooccurrence AS (
    -- 生成双向共现映射，将原单向记录的两列互换后合并
    SELECT col1 AS main_obj, col2 AS related_obj, count FROM 你的表名
    UNION ALL
    SELECT col2 AS main_obj, col1 AS related_obj, count FROM 你的表名
),
ranked_result AS (
    -- 按主体分组，按共现次数降序排名
    SELECT
        main_obj,
        related_obj,
        count,
        ROW_NUMBER() OVER (PARTITION BY main_obj ORDER BY count DESC) AS rank_num
    FROM bidirectional_cooccurrence
)
-- 取TOP N，此处示例取TOP 1，修改为10即可满足实际需求
SELECT main_obj AS col1, related_obj AS col2, count
FROM ranked_result
WHERE rank_num <= 1;

如果需要保留相同计数的并列排名，可以将ROW_NUMBER()替换为RANK()或DENSE_RANK()函数。

性能优化建议 #

• 若为临时查询，上述写法已经是最优实现，Postgres对UNION ALL和窗口函数的优化足够成熟，只要原表col1、col2上建有索引，查询效率有保障。
• 若为高频查询场景，可以预先创建物化视图存储双向共现记录，避免每次查询重复计算UNION ALL逻辑，查询时直接从物化视图取数排名即可。
• 逻辑层面不存在完全跳过双向展开的方案，因为原表仅存储单向关联，必须显式/隐式生成双向记录才能拿到完整的关联计数。

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