如何用SQL高效统计3列中交互的唯一用户数量？

问题背景 #

需要统计每个项目（ItemID）在State1、State2、State3三个状态流转环节中操作的唯一用户数量，替代原有繁琐的CASE语句实现。

推荐方案1：通用型行转列统计（兼容多数SQL数据库） #

这种方式通过将列数据转为行，直接用COUNT(DISTINCT)统计唯一值，简洁易维护，后续新增状态列时只需扩展子查询即可。

MySQL/PostgreSQL版本：

SELECT
    ItemID,
    (SELECT COUNT(DISTINCT user_val) 
     FROM (VALUES (State1), (State2), (State3)) AS temp(user_val)) AS UserCount
FROM your_table;

SQL Server版本：

SELECT
    t.ItemID,
    COUNT(DISTINCT temp.user_val) AS UserCount
FROM your_table t
CROSS APPLY (VALUES (t.State1), (t.State2), (t.State3)) AS temp(user_val)
GROUP BY t.ItemID;

推荐方案2：数学计算法（适合支持基础函数的数据库） #

通过计算重复项的数量推导唯一用户数，适合不想用子查询的场景，但扩展性较差（新增状态列需大幅修改逻辑）：

SELECT
    ItemID,
    3 - 
    (CASE WHEN State1 = State2 THEN 1 ELSE 0 END) -
    (CASE WHEN State1 = State3 THEN 1 ELSE 0 END) -
    (CASE WHEN State2 = State3 THEN 1 ELSE 0 END) +
    (CASE WHEN State1 = State2 AND State1 = State3 THEN 1 ELSE 0 END) AS UserCount
FROM your_table;

推荐方案3：PostgreSQL专属数组函数 #

利用PostgreSQL的数组和unnest函数快速展开列数据并统计：

SELECT
    ItemID,
    COUNT(DISTINCT unnest(array[State1, State2, State3])) AS UserCount
FROM your_table
GROUP BY ItemID;

方案对比 #

• 方案1是最优选择：逻辑清晰、扩展性强，几乎兼容所有主流SQL数据库，后续新增State4/State5等字段时，仅需在VALUES列表中添加对应列即可。
• 方案2仅适合固定3个状态列的场景，灵活性不足。
• 方案3仅适用于PostgreSQL环境。

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