如何修改Google BigQuery查询以按月份/年份分组统计请求数据

原查询按单日和来源统计请求量，现调整为按**月份（或年份）**和来源分组，同时输出"月份缩写-年份后两位"格式的日期（如Jun-24）。

修改后的SQL查询语句 #

SELECT
  FORMAT_DATE("%b-%y", DATE(TIMESTAMP_SECONDS(CAST(CAST(JSON_VALUE(DATA, '$.createdAt') AS INT64) / 1000 AS INT64)))) AS date,
  JSON_VALUE(DATA, '$.source') AS source,
  COUNT(*) AS source_request_count
FROM `my_big_query_table`
-- 可选：筛选特定年月，比如2024年1月
-- WHERE EXTRACT(YEAR FROM DATE(TIMESTAMP_SECONDS(CAST(CAST(JSON_VALUE(DATA, '$.createdAt') AS INT64) / 1000 AS INT64)))) = 2024
--   AND EXTRACT(MONTH FROM DATE(TIMESTAMP_SECONDS(CAST(CAST(JSON_VALUE(DATA, '$.createdAt') AS INT64) / 1000 AS INT64)))) = 1
GROUP BY
  date, source
ORDER BY PARSE_DATE("%b-%y", date) DESC

关键调整说明 #

1. 日期格式转换：用FORMAT_DATE("%b-%y", ...)将标准日期转换为"月份缩写-年份后两位"格式，%b对应英文月份缩写，%y对应年份后两位。
2. 分组逻辑：直接按格式化后的date字段和source分组，确保同一月份的请求被聚合。
3. 排序优化：排序时用PARSE_DATE("%b-%y", date)将字符串转回日期类型，避免字符串排序导致的顺序错乱。
4. 特定年月筛选：若仅需某一年/月数据，取消注释WHERE子句并调整对应年份、月份即可。

期望输出示例 #

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