为何Google BigQuery中使用EXTRACT MONTH和GROUP BY查询结果不一致？

当在Google BigQuery中使用EXTRACT(MONTH)和GROUP BY按月份统计骑行数据时，出现了异常现象：扩大查询的月份范围后，前期月份的计数会莫名增加。具体案例如下：

案例1：查询2022年1月数据 #

SELECT COUNT(start_station_name) AS num_rides, EXTRACT(MONTH FROM start_date) AS month
FROM `bigquery-public-data.london_bicycles.cycle_hire`
WHERE start_station_name='Hyde Park Corner, Hyde Park' AND start_date >= '2022-01-01' and start_date <= '2022-01-31'
GROUP BY month
ORDER BY month;

结果：得到1月的骑行计数。

案例2：查询2022年1-2月数据 #

SELECT COUNT(start_station_name) AS num_rides, EXTRACT(MONTH FROM start_date) AS month
FROM `bigquery-public-data.london_bicycles.cycle_hire`
WHERE start_station_name='Hyde Park Corner, Hyde Park' AND start_date >= '2022-01-01' and start_date <= '2022-02-28'
GROUP BY month
ORDER BY month;

结果：此时1月的骑行计数比第一次单独查询1月时的结果多。

案例3：查询2022年1-3月数据 #

SELECT COUNT(start_station_name) AS num_rides, EXTRACT(MONTH FROM start_date) AS month
FROM `bigquery-public-data.london_bicycles.cycle_hire`
WHERE start_station_name='Hyde Park Corner, Hyde Park' AND start_date >= '2022-01-01' and start_date <= '2022-03-31'
GROUP BY month
ORDER BY month;

结果：此时2月的骑行计数比第二次查询1-2月时的结果多。

问题原因 #

核心问题出在日期过滤条件的写法上：
start_date <= '2022-01-31'这种写法只会匹配到2022-01-31 00:00:00及之前的记录，但start_date是带时间戳的字段（比如包含2022-01-31 14:30:00这样的具体时间），这些带时间的1月日期记录会被直接排除。

当你扩大查询范围到2月时，之前被排除的2022-01-31 00:00:00之后的1月记录，会被start_date <= '2022-02-28'的条件包含进来，而EXTRACT(MONTH)会把这些记录归到1月分组，导致1月计数增加。同理，查询范围扩大到3月时，2022-02-28 00:00:00之后的2月记录会被包含，使得2月计数上升。

正确写法 #

使用半开区间过滤日期，确保包含目标月份的所有时间戳记录：

• 查询1月数据：

SELECT COUNT(start_station_name) AS num_rides, EXTRACT(MONTH FROM start_date) AS month
FROM `bigquery-public-data.london_bicycles.cycle_hire`
WHERE start_station_name='Hyde Park Corner, Hyde Park' 
  AND start_date >= '2022-01-01' 
  AND start_date < '2022-02-01' -- 不包含2月1日0点，即包含1月所有时间
GROUP BY month
ORDER BY month;

• 查询1-2月数据：

SELECT COUNT(start_station_name) AS num_rides, EXTRACT(MONTH FROM start_date) AS month
FROM `bigquery-public-data.london_bicycles.cycle_hire`
WHERE start_station_name='Hyde Park Corner, Hyde Park' 
  AND start_date >= '2022-01-01' 
  AND start_date < '2022-03-01'
GROUP BY month
ORDER BY month;

内容的提问来源于stack exchange，提问作者salmiah-ls