GDELT:计算特定主题的出现次数 [英] GDELT: count occurence of specific themes

问题描述

我正在尝试计算术语比特币"的使用频率.发生在GDELT数据库的主题"列中，然后按日期对计数进行分组.这是我到目前为止的内容:

I am trying to count how often the term "BITCOIN" occurs in the Themes column of the GDELT database, and then group the counts by date. Here is what I have so far:

SELECT DATE, SPLIT(RTRIM(Themes,';'),';') themes 

FROM `gdelt-bq.gdeltv2.gkg_partitioned` WHERE _PARTITIONTIME >= "2020-11-01 00:00:00" AND _PARTITIONTIME < "2020-11-07 00:00:00" 
#and (Themes like "%BITCOIN%")
#or (AllNames like "%bitcoin%" or AllNames like "%BITCOIN%")

and length(Themes) > 1
) select count(theme) cnt from nested, UNNEST(themes) as theme WHERE theme like "%BITCOIN%"

group by DATE

这是正确的方法吗?谢谢！

Is this the correct approach? Thanks!

推荐答案

下面是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardsql
select date(_partitiontime) date, count(theme) occurences
from `gdelt-bq.gdeltv2.gkg_partitioned`, unnest(split(themes,';')) as theme 
where _partitiontime >= "2020-11-01 00:00:00" and _partitiontime < "2020-11-07 00:00:00" 
and lower(theme) like "%bitcoin%"
group by date
-- order by date  

有输出

这篇关于GDELT:计算特定主题的出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！