多SQL表用户访问占比计算：仅移动端、网页端及双端用户占比求解

我来给你梳理下怎么正确计算仅移动端、仅网页端以及双端用户的占比，思路其实很清晰，咱们一步步来：

核心逻辑 #

先明确每个用户的访问渠道类型（仅移动端、仅网页端、双端都访问），再统计各类用户的数量，最后除以总用户数得到占比，确保三者之和为1。

方法一：合并渠道记录分类（适合存在多次访问记录的场景） #

这种方法先整合两个表的访问数据，去重后得到每个用户的渠道列表，再进行分类统计：

WITH user_channels AS (
    -- 合并两个表的用户ID与对应渠道
    SELECT user_id, 'mobile' AS channel FROM mobile
    UNION ALL
    SELECT user_id, 'web' AS channel FROM web
),
distinct_user_channels AS (
    -- 去重，保证每个用户每个渠道只记录一次
    SELECT DISTINCT user_id, channel FROM user_channels
),
user_category AS (
    -- 按用户分组，判断所属渠道类别
    SELECT 
        user_id,
        CASE 
            WHEN COUNT(DISTINCT channel) = 2 THEN 'both'
            WHEN MAX(channel) = 'mobile' THEN 'only_mobile'
            ELSE 'only_web'
        END AS category
    FROM distinct_user_channels
    GROUP BY user_id
),
total_users AS (
    -- 统计总用户数，用于计算占比
    SELECT COUNT(*) AS total FROM user_category
)
-- 输出各类用户数及占比
SELECT 
    category,
    COUNT(*) AS user_count,
    ROUND(COUNT(*)::FLOAT / total, 4) AS percentage
FROM user_category, total_users
GROUP BY category, total
ORDER BY category;

方法二：关联表判断存在性（更直观简洁） #

这种方法先获取所有唯一用户，再通过左连接判断用户是否在移动端/网页端有访问记录，从而完成分类：

WITH all_users AS (
    -- 获取所有访问过的唯一用户ID
    SELECT user_id FROM mobile
    UNION
    SELECT user_id FROM web
),
user_category AS (
    -- 左连接两个表，判断用户的渠道类型
    SELECT 
        au.user_id,
        CASE
            WHEN m.user_id IS NOT NULL AND w.user_id IS NOT NULL THEN 'both'
            WHEN m.user_id IS NOT NULL THEN 'only_mobile'
            ELSE 'only_web'
        END AS category
    FROM all_users au
    LEFT JOIN mobile m ON au.user_id = m.user_id
    LEFT JOIN web w ON au.user_id = w.user_id
),
total_users AS (
    -- 统计总用户数
    SELECT COUNT(*) AS total FROM user_category
)
-- 输出各类用户数及占比
SELECT 
    category,
    COUNT(*) AS user_count,
    ROUND(COUNT(*)::FLOAT / total, 4) AS percentage
FROM user_category, total_users
GROUP BY category, total
ORDER BY category;

用你的测试数据验证 #

运行上面的SQL后，结果会和预期一致：

• only_mobile：2个用户（ID1、2），占比0.4
• only_web：2个用户（ID4、15），占比0.4
• both：1个用户（ID3），占比0.2
  三者占比之和正好为1。

小细节提示 #

• 优先用UNION ALL代替UNION，前者性能更好，因为不需要自动去重，我们可以手动控制去重逻辑；
• 不同数据库的浮点数转换语法略有差异，比如MySQL可以用COUNT(*) / total * 1.0，PostgreSQL用COUNT(*)::FLOAT，你可以根据自己的数据库调整。

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