三列共现计数：去重及避免反向重复的SQL查询优化需求

要让A-B和B-A这类反向组合被视为同一组统计，核心是把每一对家族组合统一成固定顺序，再合并计数。这里分两种场景调整你的SQL：

场景1：统计所有列对的共现次数（含同一行内多列对的重复计数） #

如果需要统计每列对的出现次数（比如同一行里A-B出现两次就算两次），可以用LEAST和GREATEST函数统一组合顺序，再求和：

output_one = sqldf("""
SELECT 
    LEAST(family, related_family) AS family,
    GREATEST(family, related_family) AS related_family,
    SUM(cooccurence) AS co-occurrence
FROM (
    SELECT Family1 AS family, Family2 AS related_family, COUNT(*) AS cooccurence 
    FROM ds_neighbours 
    WHERE Family1 != Family2
    GROUP BY Family1, Family2
    UNION ALL
    SELECT Family2 AS family, Family3 AS related_family, COUNT(*) AS cooccurence 
    FROM ds_neighbours 
    WHERE Family2 != Family3
    GROUP BY Family2, Family3
    UNION ALL
    SELECT Family1 AS family, Family3 AS related_family, COUNT(*) AS cooccurence 
    FROM ds_neighbours 
    WHERE Family1 != Family3
    GROUP BY Family1, Family3
) AS temp
-- 保留原过滤条件的话可以解开注释
-- WHERE cooccurence > 25
GROUP BY LEAST(family, related_family), GREATEST(family, related_family)
ORDER BY co-occurrence DESC
""")

修改说明：

• 把UNION换成UNION ALL，避免自动去重丢失计数
• 用LEAST取字典序更小的家族作为family，GREATEST取更大的作为related_family，强制统一组合顺序
• 按统一后的组合分组求和，合并反向组合的计数

场景2：统计行级共现次数（同一行内同一组合只算一次） #

如果你的需求是两个家族只要在同一行出现就算一次（和示例输出匹配），需要先对每行的组合去重，再统计：

output_one = sqldf("""
SELECT 
    LEAST(family, related_family) AS family,
    GREATEST(family, related_family) AS related_family,
    COUNT(*) AS co-occurrence
FROM (
    -- 提取每行中所有不同的家族对，去重
    SELECT DISTINCT
        Family1 AS family,
        Family2 AS related_family
    FROM ds_neighbours
    WHERE Family1 != Family2
    
    UNION ALL
    
    SELECT DISTINCT
        Family2 AS family,
        Family3 AS related_family
    FROM ds_neighbours
    WHERE Family2 != Family3
    
    UNION ALL
    
    SELECT DISTINCT
        Family1 AS family,
        Family3 AS related_family
    FROM ds_neighbours
    WHERE Family1 != Family3
) AS temp
GROUP BY LEAST(family, related_family), GREATEST(family, related_family)
ORDER BY co-occurrence DESC
""")

修改说明：

• 每个子查询加DISTINCT，确保同一行内的相同组合只保留一次
• 同样用LEAST和GREATEST统一组合顺序，最后按组合统计行数，得到行级共现次数

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