如何用单条Presto查询计算月度、周度、日度独立活跃用户数

可以通过CTE（公共表表达式）先统一处理基础数据，再分别计算不同粒度的活跃用户数，最后关联结果并计算所需比率。以下是适配Presto语法的完整查询：

WITH user_activity AS (
    -- 统一处理原始数据，提取各粒度日期及维度字段
    SELECT
        CAST(date(date) AS DATE) AS dau_date,
        -- 计算周起始日期（与原WAU查询逻辑一致）
        CAST(date_parse(CAST(year_of_week(date(date)) AS varchar) || ':' || CAST(week(date(date)) AS varchar), '%x:%v') AS date) AS wau_date,
        -- 计算月起始日期（与原MAU查询逻辑一致）
        CAST(CONCAT(CAST(year(date(date)) AS varchar), '', CAST(month(date(date)) AS varchar), '', '01') AS date) AS mau_date,
        mobile_app_os,
        CASE country WHEN 'DE' THEN 'Germany' ELSE 'International' END AS country,
        app_user_id
    FROM your_source_table -- 替换为你的原始数据表名
),
dau_stats AS (
    -- 计算日度活跃用户
    SELECT
        dau_date AS date,
        country,
        mobile_app_os,
        COUNT(DISTINCT app_user_id) AS DAU
    FROM user_activity
    GROUP BY dau_date, country, mobile_app_os
),
wau_stats AS (
    -- 计算周度活跃用户
    SELECT
        wau_date AS date,
        country,
        mobile_app_os,
        COUNT(DISTINCT app_user_id) AS WAU
    FROM user_activity
    GROUP BY wau_date, country, mobile_app_os
),
mau_stats AS (
    -- 计算月度活跃用户
    SELECT
        mau_date AS date,
        country,
        mobile_app_os,
        COUNT(DISTINCT app_user_id) AS MAU
    FROM user_activity
    GROUP BY mau_date, country, mobile_app_os
)
-- 关联所有统计结果，计算比率
SELECT
    COALESCE(d.date, w.date, m.date) AS date,
    COALESCE(d.country, w.country, m.country) AS country,
    COALESCE(d.mobile_app_os, w.mobile_app_os, m.mobile_app_os) AS mobile_app_os,
    d.DAU,
    w.WAU,
    m.MAU,
    -- 计算比率，避免除以0的情况
    CASE WHEN m.MAU > 0 THEN ROUND(d.DAU / m.MAU, 4) ELSE NULL END AS DAU_MAU_RATIO,
    CASE WHEN m.MAU > 0 THEN ROUND(w.WAU / m.MAU, 4) ELSE NULL END AS WAU_MAU_RATIO,
    CASE WHEN w.WAU > 0 THEN ROUND(d.DAU / w.WAU, 4) ELSE NULL END AS DAU_WAU_RATIO
FROM dau_stats d
-- 关联日与周统计：日期属于对应周范围
FULL OUTER JOIN wau_stats w 
    ON d.country = w.country 
    AND d.mobile_app_os = w.mobile_app_os 
    AND d.date >= w.date 
    AND d.date < DATE_ADD(w.date, INTERVAL 1 WEEK)
-- 关联日/周与月统计：日期属于对应月范围
FULL OUTER JOIN mau_stats m 
    ON COALESCE(d.country, w.country) = m.country 
    AND COALESCE(d.mobile_app_os, w.mobile_app_os) = m.mobile_app_os 
    AND COALESCE(d.date, w.date) >= m.date 
    AND COALESCE(d.date, w.date) < DATE_ADD(m.date, INTERVAL 1 MONTH)
ORDER BY date, country, mobile_app_os;

关键说明： #

1. 统一维度处理：在user_activity CTE中一次性处理日期转换、country映射，避免重复代码。
2. 分粒度统计：三个统计CTE严格遵循你原有的DAU/WAU/MAU计算逻辑，确保结果一致。
3. 关联逻辑：用全外连接保证所有日期粒度的记录都被保留，通过日期范围匹配日-周、日-月的归属关系。
4. 比率计算：添加CASE判断避免除以0的错误，用ROUND控制精度。

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