BigQuery按日期年份/月份过滤数据失效问题解决方案

BigQuery 原生不支持部分传统RDBMS中提供的year()、month()这类快捷日期提取函数，同时原语句存在三处可导致执行失败或效果异常的问题：

• WHERE子句中使用了BigQuery不存在的year()函数，无法被SQL解析器识别执行
• SELECT子句最后一个计算字段末尾多了冗余逗号，会直接触发语法报错
• 原写法中将提取的年份值和字符串类型的'2021'做比较，存在隐式类型转换问题，会影响查询性能甚至出现匹配错误

基础修正版（和原有逻辑完全一致） #

统一使用BigQuery标准SQL支持的EXTRACT()函数做日期部分提取，注意提取出的年、月值为整数类型，匹配时直接写数值不要加字符串引号，同时去掉多余的冗余逗号：

SELECT 
  EXTRACT(YEAR FROM date) as Affected_year,
  EXTRACT(MONTH FROM date) as Affected_Month, 
  (SUM(deaths_white)/SUM(cases_white))*100 AS White_Death_Percent, 
  (SUM(deaths_black)/SUM(cases_black)) *100 AS Black_Death_Percent, 
  (SUM(deaths_asian)/SUM(cases_asian)) *100 AS Asian_Death_Percent, 
  (SUM(deaths_latinx)/SUM(cases_latinx)) *100 AS Latin_Death_Percent
FROM `bigquery-public-data.covid19_tracking.covid_racial_data_tracker` 
WHERE EXTRACT(YEAR FROM date) = 2021
GROUP BY Affected_year, Affected_Month
ORDER BY Affected_year, Affected_Month;

性能优化版（推荐大表场景使用） #

如果是按整年、整月做过滤，更推荐直接写日期区间匹配，不要在日期字段上套用函数，这样可以触发BigQuery的分区裁剪机制，大幅减少扫描的数据量，降低查询成本、提升运行速度。比如过滤2021年全年数据可以把WHERE子句改成：

WHERE date >= '2021-01-01' AND date < '2022-01-01'

如果需要同时过滤年+月，比如过滤2021年3月的数据，对应写法为：

WHERE date >= '2021-03-01' AND date < '2021-04-01'

后续如果需要做其他日期维度的提取，EXTRACT()函数通用写法为EXTRACT(时间单位 FROM 日期/时间字段)，支持的常用单位包括DAY、WEEK、QUARTER等，可以直接替换使用。

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