如何在BigQuery SQL的WHERE子句中筛选双日期区间并分时段统计？

我来帮你调整SQL语句，实现同时统计2020年8月和10月的新增确诊、新增检测数，并分别计算两个月份的阳性检测率。

修改后的完整SQL语句 #

SELECT 
  subregion1_code, 
  subregion1_name, 
  -- 统计8月核心指标
  SUM(CASE WHEN date BETWEEN '2020-08-01' AND '2020-08-31' THEN new_confirmed ELSE 0 END) AS august_confirmed,
  SUM(CASE WHEN date BETWEEN '2020-08-01' AND '2020-08-31' THEN new_tested ELSE 0 END) AS august_tested,
  SAFE_DIVIDE(
    SUM(CASE WHEN date BETWEEN '2020-08-01' AND '2020-08-31' THEN new_confirmed ELSE 0 END),
    SUM(CASE WHEN date BETWEEN '2020-08-01' AND '2020-08-31' THEN new_tested ELSE 0 END)
  ) AS august_positive_rate,
  -- 统计10月核心指标
  SUM(CASE WHEN date BETWEEN '2020-10-01' AND '2020-10-31' THEN new_confirmed ELSE 0 END) AS october_confirmed,
  SUM(CASE WHEN date BETWEEN '2020-10-01' AND '2020-10-31' THEN new_tested ELSE 0 END) AS october_tested,
  SAFE_DIVIDE(
    SUM(CASE WHEN date BETWEEN '2020-10-01' AND '2020-10-31' THEN new_confirmed ELSE 0 END),
    SUM(CASE WHEN date BETWEEN '2020-10-01' AND '2020-10-31' THEN new_tested ELSE 0 END)
  ) AS october_positive_rate
FROM `bigquery-public-data.covid19_open_data.covid19_open_data` AS B
WHERE 
  country_code = "US" 
  AND aggregation_level = 1 
  AND (date BETWEEN '2020-08-01' AND '2020-08-31' OR date BETWEEN '2020-10-01' AND '2020-10-31')
GROUP BY subregion1_code, subregion1_name 
ORDER BY october_positive_rate DESC;

关键修改说明 #

• 分月统计的核心：条件聚合
  原语句中直接用SUM(new_confirmed)无法区分月份，这里用CASE WHEN判断每条记录的日期所属区间，仅对对应月份的数值求和，确保august_*字段只统计8月数据，october_*字段只统计10月数据。

• 优化WHERE条件
  把原来单一的10月范围，改成同时包含8月和10月的逻辑，这样查询只会扫描需要的日期数据，提升效率。

• 安全计算阳性率
  使用BigQuery的SAFE_DIVIDE函数替代直接除法，避免当某个地区当月检测数为0时触发除以0的错误，返回NULL而非报错。

• 分月计算阳性率
  原语句的阳性率是基于10月整体数据的，现在改为分别计算两个月份的阳性率，更符合你对比两个月份疫情数据的需求。如果需要整体的阳性率，也可以在SELECT里额外添加对应的计算字段。

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