SQL优化：统计各设备组下拥有多设备组的用户数量实现方案

按device_grp分组，统计每个设备组中拥有超过1个不同设备组的用户数量。

示例说明 #

• android分组下，user_id为1和3的用户拥有大于1个不同设备组
• iOS分组下，user_id为2、3、4的用户拥有大于1个不同设备组
• fireTV分组下，user_id为1、2的用户拥有大于1个不同设备组

原始数据表 #

*----------------------*
|user_id | device_grp  |
*----------------------*
|    1   |    android  |
|    1   |    fireTV   |  
|    2   |    iOS      |
|    2   |    fireTV   |
|    3   |    android  |
|    3   |    iOS      |
|    4   |    web-play |
|    4   |    iOS      |
|    5   |    android  |
*----------------------*

预期结果 #

*--------------------------------------*
| device_group | users >1 device_grp   |
*--------------------------------------*
|   android    |    2                  |
|   iOS        |    3                  |
|   fireTV     |    2                  |
|   web-play   |    1                  |
*--------------------------------------*

更优实现方案 #

方案1：CTE关联写法（可读性优先） #

先筛选出拥有多设备组的用户，再关联原始表按设备组统计数量，逻辑清晰易维护，适合小数据量场景：

WITH multi_device_users AS (
    -- 先找出所有拥有超过1个设备组的用户
    SELECT user_id
    FROM your_table
    GROUP BY user_id
    HAVING COUNT(DISTINCT device_grp) > 1
)
SELECT 
    t.device_grp AS device_group,
    COUNT(DISTINCT t.user_id) AS [users >1 device_grp]
FROM your_table t
INNER JOIN multi_device_users m ON t.user_id = m.user_id
GROUP BY t.device_grp

方案2：窗口函数写法（性能优先） #

仅需一次全表扫描，无需表关联，性能更高，适合百万级以上大数据量场景：

SELECT 
    device_grp AS device_group,
    COUNT(DISTINCT user_id) AS [users >1 device_grp]
FROM (
    SELECT 
        user_id,
        device_grp,
        -- 窗口函数统计每个用户对应的设备组数量
        COUNT(DISTINCT device_grp) OVER(PARTITION BY user_id) AS grp_total
    FROM your_table
) t
WHERE grp_total > 1
GROUP BY device_grp

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