如何用SQL生成多系统用户重叠情况的组合矩阵？

需求与数据说明 #

有5个独立系统，已从各系统用户日志表中提取出唯一用户ID，需要统计跨系统的用户重叠情况，输出包含各系统存在标识（1表示用户存在，0表示不存在）及对应去重用户数的矩阵。

数据示例 #

sysA
-----
user1
user2
user3

sysB
-----
user2
user3
user4
user5

sysC
-----
user5

期望输出 #

sysA sysB sysC sysD sysE count_distinct(userkey)
1    0    0    0    0    1
1    1    0    0    0    2
1    0    1    0    0    0
...

已尝试的方法及问题 #

• 尝试过Oracle特有的GROUP BY CUBE，未得到预期结果
• 尝试过多表全外连接的SQL（如下），但结果仅包含与sysA相关的用户组合，且执行效率较低：

SELECT sysA_flag, sysB_flag, sysC_flag, sysD_flag, sysE_flag, COUNT(*)
FROM (
  SELECT DISTINCT userId, 1 sysA_flag
  FROM sysA_input_table
) sysA
FULL OUTER JOIN (
  SELECT DISTINCT userId, 1 sysB_flag
  FROM sysB_input_table
) sysB
ON sysA.userId = sysB.userId
FULL OUTER JOIN (
  SELECT DISTINCT userId, 1 sysC_flag
  FROM sysC_input_table
) sysC
ON sysA.userId = sysC.userId
FULL OUTER JOIN (
  SELECT DISTINCT userId, 1 sysD_flag
  FROM sysD_input_table
) sysD
ON sysA.userId = sysD.userId
FULL OUTER JOIN (
  SELECT DISTINCT userId, 1 sysE_flag
  FROM sysE_input_table
) sysE
ON sysA.userId = sysE.userId

GROUP BY (sysA_flag, sysB_flag, sysC_flag, sysD_flag, sysE_flag)

正确实现方案 #

核心思路是先纵向合并所有系统的用户数据，生成每个用户的完整系统存在标识，再按标识组合统计用户数。

完整SQL代码 #

WITH all_user_systems AS (
  -- 合并所有系统的用户ID及所属系统标记
  SELECT userId, 1 AS sysA_flag, NULL AS sysB_flag, NULL AS sysC_flag, NULL AS sysD_flag, NULL AS sysE_flag FROM sysA_input_table
  UNION ALL
  SELECT userId, NULL, 1, NULL, NULL, NULL FROM sysB_input_table
  UNION ALL
  SELECT userId, NULL, NULL, 1, NULL, NULL FROM sysC_input_table
  UNION ALL
  SELECT userId, NULL, NULL, NULL, 1, NULL FROM sysD_input_table
  UNION ALL
  SELECT userId, NULL, NULL, NULL, NULL, 1 FROM sysE_input_table
),
user_system_flags AS (
  -- 生成每个用户的完整系统存在标识（1/0）
  SELECT
    userId,
    NVL(MAX(sysA_flag), 0) AS sysA_flag,
    NVL(MAX(sysB_flag), 0) AS sysB_flag,
    NVL(MAX(sysC_flag), 0) AS sysC_flag,
    NVL(MAX(sysD_flag), 0) AS sysD_flag,
    NVL(MAX(sysE_flag), 0) AS sysE_flag
  FROM all_user_systems
  GROUP BY userId
)
-- 按标识组合统计去重用户数
SELECT
  sysA_flag,
  sysB_flag,
  sysC_flag,
  sysD_flag,
  sysE_flag,
  COUNT(DISTINCT userId) AS count_distinct_userkey
FROM user_system_flags
GROUP BY sysA_flag, sysB_flag, sysC_flag, sysD_flag, sysE_flag
ORDER BY sysA_flag DESC, sysB_flag DESC, sysC_flag DESC, sysD_flag DESC, sysE_flag DESC;

方案说明 #

1. 合并用户数据：通过UNION ALL将各系统的用户ID合并，同时标记用户所属的系统（对应系统设为1，其余为NULL），这一步高效且能覆盖所有用户。
2. 生成系统标识：按userId分组，用MAX()聚合每个系统的标记（存在则保留1，不存在则为NULL），再通过NVL()将NULL转为0，得到每个用户的完整系统存在标识。
3. 统计组合数：按各系统标识分组，统计每组的去重用户数，得到所有可能的用户重叠组合。

扩展：使用GROUP BY CUBE #

如果需要包含各类小计（比如所有sysA用户数、所有同时在sysA和sysB的用户数等），可将最后一步的GROUP BY改为GROUP BY CUBE：

SELECT
  sysA_flag,
  sysB_flag,
  sysC_flag,
  sysD_flag,
  sysE_flag,
  COUNT(DISTINCT userId) AS count_distinct_userkey
FROM user_system_flags
GROUP BY CUBE(sysA_flag, sysB_flag, sysC_flag, sysD_flag, sysE_flag)
ORDER BY sysA_flag DESC, sysB_flag DESC, sysC_flag DESC, sysD_flag DESC, sysE_flag DESC;

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