如何在Google BigQuery中按周数计算各区域不同周次的计数差值
BigQuery 跨周差值统计查询写法
你可以使用条件聚合直接将两周的统计值放到同一行后计算差值,正确查询语句如下:
SELECT Admin_Level_2_district, COUNTIF(WeekNumber = '18') AS count_Week_18, COUNTIF(WeekNumber = '19') AS count_Week_19, COUNTIF(WeekNumber = '19') - COUNTIF(WeekNumber = '18') AS Difference FROM `interim-data.casedata.Interim EpiData` WHERE WeekNumber IN ('18', '19') GROUP BY Admin_Level_2_district
逻辑说明
- 原语句按「地区+周数」分组,所以每个地区会输出两行分别对应两周的统计值;调整后仅按地区分组,通过
COUNTIF按周条件分别统计对应周的Epid_ID数量 - 过滤逻辑从
HAVING改为WHERE,提前过滤仅保留18、19两周的数据,查询效率更高 - 因为Epid_ID是每行唯一标识,直接计数即可得到准确的每周数据量,差值直接通过两周统计值相减得到
内容的提问来源于stack exchange,提问作者khushbu
相关产品推荐
相关产品推荐

