如何在BigQuery中实现WTD计算及周度聚合差值统计

以下实现默认原表名为daily_user_data，周度统计默认以周一为每周起始日，如果你的业务规则以周日为周起始，调整日期截断函数的偏移参数即可。

核心实现逻辑 #

• 周粒度聚合：先给每条日度数据映射对应周的标识字段calendar_week（取当周第一天的日期值），再按周分组计算当周user_id合计值
• 相邻周差值计算：用窗口函数错位取上一周的合计值，和本周值做差得到周环比变动
• WTD（周迄今）计算：不需要等整周结束，取当周起始日到指定统计截止日的累计值即可

可直接运行的SQL代码 #

-- 1. 先完成周度基础聚合
WITH weekly_base AS (
    SELECT
        -- 按你用的SQL引擎选对应周起始日期计算逻辑：
        -- PostgreSQL/Redshift: DATE_TRUNC('week', calendar_date)::DATE
        -- MySQL: DATE_SUB(calendar_date, INTERVAL WEEKDAY(calendar_date) DAY)
        -- Hive/SparkSQL: date_sub(calendar_date, pmod(datediff(calendar_date, '1900-01-01'), 7))
        -- BigQuery: DATE_TRUNC(calendar_date, WEEK(MONDAY))
        DATE_TRUNC('week', calendar_date)::DATE AS calendar_week,
        -- 如果是统计活跃用户数，把SUM换成COUNT(DISTINCT user_id)
        SUM(user_id) AS weekly_user_sum
    FROM daily_user_data
    GROUP BY calendar_week
),
-- 2. 计算相邻周差值
weekly_with_diff AS (
    SELECT
        calendar_week,
        weekly_user_sum,
        -- 第一周没有上一周数据，差值默认返回NULL
        weekly_user_sum - LAG(weekly_user_sum, 1) OVER (ORDER BY calendar_week) AS wow_diff
    FROM weekly_base
)
-- 输出周度聚合结果
SELECT * FROM weekly_with_diff ORDER BY calendar_week;

WTD指标单独计算逻辑 #

如果需要动态取任意统计日的周迄今累计值，用下面的代码即可，只需要替换统计日期参数：

-- 替换成你要统计的截止日期即可
WITH param AS (SELECT '2024-06-18'::DATE AS stat_date),
daily_tag AS (
    SELECT
        calendar_date,
        user_id,
        DATE_TRUNC('week', calendar_date)::DATE AS calendar_week
    FROM daily_user_data, param
    -- 只取截止统计日之前、且和统计日同属一周的数据
    WHERE calendar_date BETWEEN DATE_TRUNC('week', (SELECT stat_date FROM param))::DATE 
        AND (SELECT stat_date FROM param)
)
SELECT
    calendar_week,
    (SELECT stat_date FROM param) AS stat_date,
    SUM(user_id) AS wtd_user_sum
FROM daily_tag
GROUP BY calendar_week, stat_date;

注意点 #

如果业务侧周度定义是周日为一周第一天，所有DATE_TRUNC相关的周截断逻辑要对应调整成周日起始的参数，避免周度数据对齐出错。
如果user_id字段是字符串类型的用户唯一标识，所有SUM(user_id)的逻辑都要替换成COUNT(DISTINCT user_id)，否则会报类型错误或者统计值完全失真。

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