bigquery和gdelt数据库:如何统计事件数量和提及次数

Question

是count(*)代码中的事件编号。但在我的数据中，它们和count(NumMentions) as Mentions是一样的，事实上，提到的可能比事件更多。我的代码出了什么问题

SELECT
YEAR,
ACTOR1COUNTRYCODE as Country1,
Actor2CountryCode as Country2,
count(NumMentions) as Mentions,
COUNT(*),
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

Answer 1

听起来你只需要把你的COUNT(NumMentions)改成SUM(NumMentions)。COUNT()将计算记录数，但SUM()实际上将对整数求和。

SELECT
  YEAR,
  ACTOR1COUNTRYCODE as Country1,
  Actor2CountryCode as Country2,
  SUM(NumMentions) as Mentions,
  COUNT(*)
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