如何用SQL基于每日点击数据计算DAU、MAU及SF？

指标回顾 #

• DAU（日活跃用户）：单日使用产品1次及以上的用户数，每个用户仅计1次。
• MAU（滚动月活跃用户）：当日往前30天内（含当日）使用产品1次及以上的用户数，日期范围随每日滚动。
• SF（粘性系数）：DAU/MAU*100%，保留两位小数，MAU为0时SF设为0避免除零错误。

高效SQL语句（PostgreSQL 16.0） #

WITH daily_active AS (
    -- 预聚合：去重得到每个产品每日的活跃用户
    SELECT DISTINCT
        product,
        date,
        user_id
    FROM clicks
),
user_mau_contribution AS (
    -- 为每个活跃用户生成其贡献的MAU日期范围（当日至之后29天）
    SELECT
        product,
        user_id,
        generate_series(
            date,
            date + INTERVAL '29 days',
            INTERVAL '1 day'
        )::DATE AS mau_calendar_date
    FROM daily_active
)
SELECT
    da.product,
    da.date,
    COUNT(DISTINCT da.user_id) AS dau,
    COUNT(DISTINCT umc.user_id) AS mau,
    -- 处理MAU为0的边界情况
    CASE 
        WHEN COUNT(DISTINCT umc.user_id) = 0 THEN 0.00
        ELSE ROUND(COUNT(DISTINCT da.user_id)::FLOAT / COUNT(DISTINCT umc.user_id) * 100, 2) 
    END AS sf
FROM daily_active da
LEFT JOIN user_mau_contribution umc
    ON da.product = umc.product
    AND da.date = umc.mau_calendar_date
GROUP BY da.product, da.date
ORDER BY da.product, da.date;

性能优化说明 #

1. 预聚合去重：daily_active CTE先对原始clicks表的重复记录（同一用户同一产品同一日期的多条点击）去重，大幅减少后续计算的数据量。
2. 生成贡献日期范围：通过generate_series一次性为每个活跃用户生成其能计入MAU的所有日期，避免了对每个日期重复扫描过去30天的历史数据，这是提升大表查询效率的核心。
3. 索引优化建议：在clicks表上创建复合索引，加速预聚合查询：

CREATE INDEX idx_clicks_product_date_user ON clicks (product, date, user_id);

关键逻辑说明 #

• 滚动30天MAU的计算：每个用户在某一天活跃后，会被计入当天及之后29天的MAU统计中，正好覆盖连续30天的窗口。
• 除零错误处理：通过CASE语句判断MAU为0的情况，避免计算SF时出现报错。

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