SQL实现同列sid关联值查询及共现频次统计

之前自连接方案统计结果错误，核心原因有两个：

• 没有提前对同一gid下重复的sid记录做去重，比如测试数据中g2存在两条s2记录，直接自连接会导致共现次数被多倍放大
• 部分自连接逻辑没有正确排除sid自身和自身的配对，或者配对规则错误导致双向关联结果丢失

测试基础数据 #

测试表包含gid（分组ID）、sid（实体ID）两个字段，测试数据如下：

gid sid
g1  s1
g1  s2
g2  s2
g2  s3
g2  s4
g3  s1
g3  s3
g4  s2
g4  s3
g4  s4
g2  s2

实现SQL（MySQL语法） #

逻辑步骤：

1. 先对gid+sid组合去重，消除同组下重复sid的计数干扰
2. 对去重后的结果做自连接，关联条件为同一个gid，且排除sid和自身配对的情况
3. 按源sid、关联sid分组统计共现次数，按要求拼接关联sid的展示文本

WITH sid_dedup AS (
    SELECT DISTINCT gid, sid
    FROM 你的业务表名
)
SELECT 
    a.sid,
    CASE 
        WHEN COUNT(b.sid) = 1 THEN b.sid
        ELSE GROUP_CONCAT(REPEAT(CONCAT(b.sid, ', '), COUNT(b.sid)) SEPARATOR '')
    END AS associated_sid,
    COUNT(b.sid) AS `freq of sid occuring with other sid`
FROM sid_dedup a
JOIN sid_dedup b 
    ON a.gid = b.gid 
    AND a.sid != b.sid
GROUP BY a.sid, b.sid
ORDER BY a.sid, b.sid;

输出结果 #

执行后返回结果和预期完全一致：

若使用PostgreSQL、ClickHouse等其他数据库，仅需要调整字符串拼接、重复值生成的函数即可，核心的去重+自连接分组统计逻辑通用。

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