如何使用google bigquery for GDELT gfg为指定的单词添加特定的国家/地区计数搜索

Question

我正在使用GDELT和Google Bigquery。我想搜索一个特定的单词，例如“经济”和“失业”，用于特定的国家，例如加拿大。示例代码如下所示。我将ToLinkURL like '%canada%‘添加到WHERE。但是，它不会给我带来任何数据。有没有更好的方法来做这件事？

SELECT
  SUBSTR(CAST(DATE AS STRING), 0, 8) DAY,
  COUNT(1) COUNT
FROM
  `gdelt-bq.gdeltv2.gfg_partitioned`
WHERE
  
  LOWER(LinkText) LIKE '%economy%'
   
  AND (LOWER(LinkText) LIKE '%uncertainty%' AND _PARTITIONTIME >= "2019-09-04" AND _PARTITIONTIME < "2019-09-07") AND ToLinkURL like '%canada%'
    
GROUP BY
  DAY 
ORDER BY
 DAY DESC

Answer 1

下面是针对BigQuery标准SQL的说明

#standardSQL
SELECT
  SUBSTR(CAST(DATE AS STRING), 0, 8) DAY,
  COUNT(1) COUNT
FROM `gdelt-bq.gdeltv2.gfg_partitioned`
WHERE LOWER(LinkText) LIKE '%economy%'
  AND LOWER(LinkText) LIKE '%unemployment%' 
  AND _PARTITIONTIME >= "2019-09-04" AND _PARTITIONTIME < "2019-09-07"
  AND ToLinkURL LIKE '%canada%'
GROUP BY DAY 
ORDER BY DAY DESC   

并返回

Row DAY         COUNT   
1   20190906    12

同时，我觉得下面的内容与你想要的更相关(这取决于你真正想要寻找的东西是否存在于同一条目或其中任何一个条目中)

#standardSQL
SELECT
  SUBSTR(CAST(DATE AS STRING), 0, 8) DAY,
  COUNT(1) COUNT
FROM `gdelt-bq.gdeltv2.gfg_partitioned`
WHERE (LOWER(LinkText) LIKE '%economy%'
  OR LOWER(LinkText) LIKE '%unemployment%')
  AND _PARTITIONTIME >= "2019-09-04" AND _PARTITIONTIME < "2019-09-07"
  AND ToLinkURL LIKE '%canada%'
GROUP BY DAY 
ORDER BY DAY DESC 

在这种情况下，输出为

Row DAY         COUNT    
1   20190906    831  
2   20190905    718  
3   20190904    815