BigQuery SQL如何统计周内占比最高的所属月份？

需求说明 #

针对以周一为起始日的周日期范围（例如2023年10月30日至11月5日），需要新增字段返回该周内日期数量最多的月份（示例中10月仅2天，11月有5天，故返回11月）。示例输出表格如下：

实现方案 #

已自行完成需求实现，对应的BigQuery SQL语句如下：

SELECT
  MIN(Week_Beginning) AS wb_date,
  (SELECT EXTRACT(MONTH FROM date) AS most_common_month
   FROM UNNEST(GENERATE_DATE_ARRAY(Week_Beginning, DATE_ADD(Week_Beginning, INTERVAL 6 DAY), INTERVAL 1 DAY)) AS date
   GROUP BY most_common_month
   ORDER BY COUNT(*) DESC
   LIMIT 1) AS dominant_month
FROM
  `table_name`
WHERE Week_Beginning BETWEEN '2023-01-01' AND '2024-12-31'
GROUP BY
  Week_Beginning
ORDER BY Week_Beginning asc

语句逻辑说明 #

• 通过GENERATE_DATE_ARRAY生成从周一起始日到该周周日的完整日期序列
• 将日期数组展开后，按月份分组并统计每个月份的日期数量
• 按日期数量降序排序，取第一条结果即为该周占比最高的月份
• 最后按周一起始日分组聚合，输出每个周对应的目标月份

内容的提问来源于stack exchange，提问作者Roxan Mae Mendoza