如何用SQL按variant分区计算每日ID占比？

核心思路 #

先统计每日每个operator对应的去重用户数，再计算当日同variant下的总去重用户数，最后通过除法得到占比。

完整SQL代码（基础版） #

假设输入数据表名为user_activity，包含字段：date（日期）、operator（运营方/操作标识）、variant（变体分组）、user_id（用户ID）：

WITH daily_operator_users AS (
    -- 第一步：计算每日每个operator的去重用户数
    SELECT
        date,
        variant,
        operator,
        COUNT(DISTINCT user_id) AS operator_distinct_users
    FROM user_activity
    GROUP BY date, variant, operator
),
daily_variant_total AS (
    -- 第二步：计算每日每个variant的总去重用户数
    SELECT
        date,
        variant,
        COUNT(DISTINCT user_id) AS variant_total_users
    FROM user_activity
    GROUP BY date, variant
)
-- 第三步：关联数据并计算占比
SELECT
    do.date,
    do.variant,
    do.operator,
    do.operator_distinct_users,
    dv.variant_total_users,
    -- 保留两位小数，可按需调整精度
    ROUND(do.operator_distinct_users * 1.0 / dv.variant_total_users, 2) AS operator_ratio
FROM daily_operator_users do
JOIN daily_variant_total dv
    ON do.date = dv.date
    AND do.variant = dv.variant
ORDER BY do.date, do.variant, do.operator;

简化版（窗口函数实现） #

如果你的SQL环境支持窗口函数（如MySQL 8.0+、PostgreSQL、BigQuery等），可以用更高效的写法，避免两次扫描数据表：

SELECT
    date,
    variant,
    operator,
    COUNT(DISTINCT user_id) AS operator_distinct_users,
    -- 窗口函数计算当日同variant的总去重用户数
    COUNT(DISTINCT user_id) OVER (PARTITION BY date, variant) AS variant_total_users,
    ROUND(COUNT(DISTINCT user_id) * 1.0 / COUNT(DISTINCT user_id) OVER (PARTITION BY date, variant), 2) AS operator_ratio
FROM user_activity
GROUP BY date, variant, operator
ORDER BY date, variant, operator;

注意事项 #

• 乘以1.0是为了避免整数除法问题：SQL中整数相除会默认取整（例如3/5结果为0），乘以1.0后转为浮点数运算，得到正确的小数结果
• 可根据业务需求调整ROUND函数的小数位数，或者直接去掉ROUND保留原始精度
• 若存在variant当日无用户数据的场景，可将JOIN替换为LEFT JOIN，并通过COALESCE函数处理空值（例如COALESCE(dv.variant_total_users, 0)）

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