BigQuery查询GDELT数据库时count统计事件与提及数结果一致问题

核心问题 #

你使用了错误的聚合函数，count()的作用是统计指定字段非空的行数，而非对字段的数值进行累加。

原因说明 #

• GDELT的events_partitioned表中，每一行对应1条独立的事件记录，NumMentions字段存储的是当前这条事件对应的总提及次数。
• 你写的count(NumMentions)仅统计分组内NumMentions不为空的行数，只要该字段没有空值，统计结果就会和统计总行数的count(*)完全一致，和你预期的总提及数逻辑不符。

修复方案 #

将count(NumMentions)替换为sum(NumMentions)，对分组内所有事件的提及次数做累加，就能得到你需要的总提及数，修正后的查询代码如下：

SELECT
  YEAR,
  ACTOR1COUNTRYCODE as Country1,
  Actor2CountryCode as Country2,
  SUM(NumMentions) as Mentions,
  COUNT(*) as EventCount
FROM `gdelt-bq.full.events_partitioned`
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2012-01-01') AND TIMESTAMP('2013-12-31')
  AND ACTOR2COUNTRYCODE='CHN'
GROUP BY YEAR,Country1,Country2
ORDER BY YEAR,Country1,Country2

如果需要避免NumMentions空值对求和结果的影响，可以把求和逻辑调整为SUM(IFNULL(NumMentions, 0))。

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