BigQuery如何实现按2个月时间窗口分组聚合统计数据

核心逻辑 #

将每个月份归属到对应双月周期的起始奇数月（1月、3月、5月……），按这个起始月+市场分组聚合营收即可匹配要求的统计规则。

修改后SQL代码 #

WITH agg AS (
  SELECT
    -- 生成要求格式的双月标识字符串
    CONCAT(
      EXTRACT(YEAR FROM CAST(date AS DATE)),
      '年',
      IF(
        MOD(EXTRACT(MONTH FROM CAST(date AS DATE)), 2) = 1,
        EXTRACT(MONTH FROM CAST(date AS DATE)),
        EXTRACT(MONTH FROM CAST(date AS DATE)) - 1
      ),
      '月'
    ) AS Date,
    market AS Market,
    SUM(Rev) AS Rev
  FROM `data_table`
  GROUP BY 1, 2
  -- 可选排序，输出顺序和示例一致
  ORDER BY PARSE_DATE('%Y年%m月', Date), Market
)
SELECT * FROM agg

补充说明 #

如果需要保留日期类型的双月标识方便后续计算，可以替换Date字段的计算逻辑为以下写法，得到双月第一天的DATE类型值（比如2021年2月的记录会返回2021-01-01）：

DATE_TRUNC(CAST(date AS DATE), MONTH) 
- INTERVAL MOD(EXTRACT(MONTH FROM CAST(date AS DATE)) - 1, 2) MONTH 
AS bi_month_start

该逻辑对全年所有月份都适用：12月会自动归属到11月起始的双月周期，统计11、12月的营收总和。

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