求助：数字银行用户按登录设备类型分组统计的SQL实现

核心思路 #

先对每个用户的登录设备类型做归类去重，再根据用户覆盖的设备类别数量划分分组，最后统计各分组的唯一用户数。以下提供CTE和子查询两种实现方式，适配不同SQL环境。

假设前提 #

假设你的基础表your_base_table中：

• USER_ID为用户唯一标识
• SESSION字段可直接区分设备类型（如存储phone/tablet/desktop），或可通过字符串匹配提取设备类别

方式一：使用CTE（推荐，可读性更强） #

WITH user_device_categories AS (
    -- 步骤1：统一设备类别并去重每个用户的设备类型
    SELECT 
        USER_ID,
        CASE 
            WHEN SESSION IN ('phone', 'tablet') THEN 'mobile'
            WHEN SESSION = 'desktop' THEN 'desktop'
            ELSE 'unknown' -- 处理异常设备类型
        END AS device_category
    FROM your_base_table
    GROUP BY USER_ID, device_category
),
user_group_mapping AS (
    -- 步骤2：为每个用户划分所属分组
    SELECT 
        USER_ID,
        CASE 
            WHEN COUNT(DISTINCT device_category) = 1 AND MAX(device_category) = 'mobile' THEN '仅通过phone/tablet登录'
            WHEN COUNT(DISTINCT device_category) = 1 AND MAX(device_category) = 'desktop' THEN '仅通过desktop登录'
            WHEN COUNT(DISTINCT device_category) >= 2 THEN '跨多设备（phone/tablet+desktop）登录'
            ELSE '未知设备类型用户'
        END AS user_group
    FROM user_device_categories
    GROUP BY USER_ID
)
-- 步骤3：统计各分组的唯一用户数
SELECT 
    user_group AS 用户分组,
    COUNT(DISTINCT USER_ID) AS 唯一用户计数
FROM user_group_mapping
GROUP BY user_group
ORDER BY 
    CASE user_group
        WHEN '仅通过phone/tablet登录' THEN 1
        WHEN '仅通过desktop登录' THEN 2
        WHEN '跨多设备（phone/tablet+desktop）登录' THEN 3
        ELSE 4
    END;

方式二：使用子查询 #

如果你的SQL环境不支持CTE，可改用嵌套子查询实现：

SELECT 
    user_group AS 用户分组,
    COUNT(DISTINCT USER_ID) AS 唯一用户计数
FROM (
    SELECT 
        USER_ID,
        CASE 
            WHEN COUNT(DISTINCT device_category) = 1 AND MAX(device_category) = 'mobile' THEN '仅通过phone/tablet登录'
            WHEN COUNT(DISTINCT device_category) = 1 AND MAX(device_category) = 'desktop' THEN '仅通过desktop登录'
            WHEN COUNT(DISTINCT device_category) >= 2 THEN '跨多设备（phone/tablet+desktop）登录'
            ELSE '未知设备类型用户'
        END AS user_group
    FROM (
        SELECT 
            USER_ID,
            CASE 
                WHEN SESSION IN ('phone', 'tablet') THEN 'mobile'
                WHEN SESSION = 'desktop' THEN 'desktop'
                ELSE 'unknown'
            END AS device_category
        FROM your_base_table
        GROUP BY USER_ID, device_category
    ) device_type_subquery
    GROUP BY USER_ID
) user_group_subquery
GROUP BY user_group
ORDER BY 
    CASE user_group
        WHEN '仅通过phone/tablet登录' THEN 1
        WHEN '仅通过desktop登录' THEN 2
        WHEN '跨多设备（phone/tablet+desktop）登录' THEN 3
        ELSE 4
    END;

适配复杂SESSION字段的处理 #

如果SESSION存储的是User-Agent字符串（如浏览器标识），可通过正则匹配提取设备类型，示例：

-- 替换CTE或子查询中的device_category判断逻辑
CASE
    WHEN REGEXP_LIKE(SESSION, 'iPhone|Android|iPad|Tablet|Mobile') THEN 'mobile'
    WHEN REGEXP_LIKE(SESSION, 'Windows|Macintosh|Linux|Desktop') THEN 'desktop'
    ELSE 'unknown'
END AS device_category

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