如何基于current_date实现BigQuery按周动态统计记录（周为周一至周日）

改造完成的BigQuery周统计SQL #

SELECT
  DATE_TRUNC(o.date, WEEK(MONDAY)) AS week_start_date,
  DATE_ADD(DATE_TRUNC(o.date, WEEK(MONDAY)), INTERVAL 6 DAY) AS week_end_date,
  COUNT(DISTINCT customer_id) AS WAU
FROM xyz.orders o
WHERE
  -- 动态匹配当前周（周一至周日）的时间范围
  o.date >= DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY))
  AND o.date < DATE_ADD(DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)), INTERVAL 7 DAY)
  -- 保留原有过滤规则
  AND o.refund = 0
  AND o.parent_order_id IS NULL
  AND o.status = 1
GROUP BY week_start_date, week_end_date

核心逻辑说明 #

• 使用BigQuery内置的DATE_TRUNC函数，通过WEEK(MONDAY)参数指定周的起始日为周一，自动对齐你要求的「周一至周日」的周统计区间规则
• 时间过滤采用左闭右开写法，兼容o.date为DATE、DATETIME、TIMESTAMP三种类型的场景，不会出现边界值统计遗漏或重复的问题
• 如果你需要统计历史所有周的WAU，仅需删除WHERE子句中的周范围过滤条件即可，SQL会自动按周分组统计所有符合条件的记录
• 验证示例：当CURRENT_DATE()为2021-09-18时，上述SQL的统计区间自动匹配为2021-09-13到2021-09-19，和你给出的示例完全一致

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