基于BigQuery的GDELT抗议事件计数结果偏高，求优化查询获单事件数

你的查询结果远高于ACLED数据的核心原因是：GDELT的events_partitioned表中每条记录是对单个事件的一次媒体提及，而非独立事件本身。ACLED是人工编码的单个事件数据集，而GDELT会把同一事件的每一次媒体报道/提及都生成一条记录，直接COUNT(*)会把所有提及数都算进去，自然远高于实际事件数量。

要获取单个事件的计数，必须基于GDELT的唯一事件标识字段GLOBALEVENTID去重，优化后的查询有两种方式：

方式1：使用COUNT(DISTINCT)直接去重 #

SELECT COUNT(DISTINCT GLOBALEVENTID) 
FROM gdelt-bq.gdeltv2.events_partitioned 
WHERE 
  _PARTITIONTIME >= TIMESTAMP('2022-09-01') 
  AND _PARTITIONTIME <= TIMESTAMP('2022-09-30') 
  AND EventRootCode = '14' 
  AND ActionGeo_CountryCode = 'IR';

方式2：先分组再计数（适合同时查看事件其他属性） #

SELECT GLOBALEVENTID, COUNT(*) AS mention_count
FROM gdelt-bq.gdeltv2.events_partitioned 
WHERE 
  _PARTITIONTIME >= TIMESTAMP('2022-09-01') 
  AND _PARTITIONTIME <= TIMESTAMP('2022-09-30') 
  AND EventRootCode = '14' 
  AND ActionGeo_CountryCode = 'IR'
GROUP BY GLOBALEVENTID;
-- 此时结果的行数就是独立事件数量，也可以嵌套查询统计总数：
-- SELECT COUNT(*) FROM (上面的分组查询) AS sub;

额外优化建议 #

如果要更精准匹配事件的实际发生时间，建议用SQLDATE字段（格式为YYYYMMDD）替代_PARTITIONTIME，因为_PARTITIONTIME是数据被GDELT入库的时间，可能和事件实际发生时间有延迟：

SELECT COUNT(DISTINCT GLOBALEVENTID) 
FROM gdelt-bq.gdeltv2.events_partitioned 
WHERE 
  SQLDATE BETWEEN 20220901 AND 20220930
  AND EventRootCode = '14' 
  AND ActionGeo_CountryCode = 'IR';

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