在BigQuery中如何获取每月首日起始的周度数据并完成求和计算

核心实现逻辑 #

我们的目标是生成不跨月份的周分组，完全匹配你的两个规则：每月第一周从1日启动、月末周不包含下月日期，核心逻辑是直接按当月日期每7天为一组做自定义周划分，无需调用默认周函数避免跨月问题。

具体实现代码 #

首先先把原始的字符串格式日期转为BigQuery标准DATE类型，再做周分组聚合，完整查询如下：

WITH formatted_data AS (
  -- 先把原始字符串日期转为标准DATE格式
  SELECT
    PARSE_DATE("%e %b %Y", TRIM(date)) AS order_date,
    sales
  FROM `你的原始表名`
),
week_grouped AS (
  SELECT
    order_date,
    sales,
    -- 取当月第一天作为月份标识
    DATE_TRUNC(order_date, MONTH) AS month_start,
    -- 自定义当月周数：每7天为1周，不足7天的月末部分单独成周
    CEIL(EXTRACT(DAY FROM order_date)/7) AS custom_week_of_month
  FROM formatted_data
)
-- 按自定义周分组聚合销售数据
SELECT
  FORMAT_DATE("%Y年%m月", month_start) AS 所属月份,
  CONCAT("第", custom_week_of_month, "周") AS 当月周次,
  -- 计算当前周的开始日期
  DATE_ADD(month_start, INTERVAL (custom_week_of_month - 1)*7 DAY) AS 周开始日期,
  -- 计算当前周的结束日期，取周理论结束日和当月最后一天的较小值避免跨月
  LEAST(
    DATE_ADD(month_start, INTERVAL custom_week_of_month * 7 - 1 DAY),
    LAST_DAY(month_start)
  ) AS 周结束日期,
  SUM(sales) AS 周销售总额
FROM week_grouped
GROUP BY 所属月份, 当月周次, 周开始日期, 周结束日期
ORDER BY 周开始日期

效果验证 #

以你提供的样例数据为例，输出结果会符合以下预期：

• 2021年10月第1周：2021-10-01 至 2021-10-07，销售总额为对应7天数据求和
• 2021年10月第5周：2021-10-29 至 2021-10-31，不会包含11月的日期
• 2021年11月第1周：2021-11-01 至 2021-11-07，从当月1日开始计算

该方案兼容多月份数据批量处理，完全适配定时任务的自动化跑数需求。

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