多列匹配（顺序无关）行查询：统计匹配动物组合的user_id数量

需要统计两类不同user_id的数量：

• 系统中的总不同用户数
• 存在其他用户与其拥有完全相同的3种动物（动物列的顺序不影响匹配）的用户数

示例数据：

期望输出统计结果：

可以通过SQL语句实现该统计需求，核心思路是先为每个用户生成一个不考虑动物顺序的唯一标识，再基于这个标识统计用户分组情况：

WITH user_animal_sets AS (
    SELECT 
        user_id,
        -- 将三个动物列排序后拼接，消除顺序差异
        CONCAT_WS(',', 
            LEAST(Col1, Col2, Col3),
            -- 取中间值
            CASE 
                WHEN Col1 NOT IN (LEAST(Col1, Col2, Col3), GREATEST(Col1, Col2, Col3)) THEN Col1
                WHEN Col2 NOT IN (LEAST(Col1, Col2, Col3), GREATEST(Col1, Col2, Col3)) THEN Col2
                ELSE Col3 
            END,
            GREATEST(Col1, Col2, Col3)
        ) AS normalized_animals
    FROM your_table
),
group_user_counts AS (
    SELECT 
        normalized_animals,
        COUNT(DISTINCT user_id) AS user_count
    FROM user_animal_sets
    GROUP BY normalized_animals
)
SELECT 
    (SELECT COUNT(DISTINCT user_id) FROM your_table) AS distinct_users,
    COUNT(DISTINCT u.user_id) AS distinct_users_with_a_match
FROM user_animal_sets u
JOIN group_user_counts g ON u.normalized_animals = g.normalized_animals
WHERE g.user_count >= 2;

逻辑说明 #

1. user_animal_sets：对每个用户的三个动物列进行排序拼接，生成统一格式的动物集合标识，比如用户11和13都会得到bird,cat,dog，这样就消除了列顺序的影响。
2. group_user_counts：统计每个动物集合对应的用户数量，判断哪些集合存在多个用户。
3. 最终统计：计算总不同用户数，同时筛选出属于用户数≥2的集合中的所有用户，统计这类用户的数量。

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