如何使用GDELT选择在其中映射国家名称提及网络的国家/地区？

Question

我正在访问BigQuery上的GDELT全球新闻数据库。我有一些SQL代码，它已经生成了共同提到的国家的邻接列表。这个结果是所有全球新闻的总和。我的问题是，我想将结果限制在源自一个国家的新闻故事上，这样我就可以比较两个或更多国家的共同提到的国家的网络。

以下是查询输出：

https://docs.google.com/spreadsheets/d/1BBxUC9SRFAeBEo5AyX4EYgtK92VMmcEK9DLxte2Xhhk/edit?usp=sharing

仅供参考，以下是输出的网络图：

https://drive.google.com/file/d/101h4_kgbfOBGAakqPR-x6dVvObxKwvI6/view?usp=sharing

SELECT c.Source SourceCountryCode, d.country Source, c.Target TargetCountryCode, e.country Target, Count RawCount, "Undirected" Type, RATIO_TO_REPORT(Count) OVER () Weight FROM (
SELECT a.name Source, b.name Target, COUNT(*) as Count
FROM (FLATTEN(
SELECT GKGRECORDID, UNIQUE(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[1- 
5]#.*?#(.*?)#')) name
FROM [gdelt-bq:gdeltv2.gkg] 
WHERE DATE > 20190101000000 and DATE < 20190605000000 AND V2Locations like '%United States%United States%' and (V2Themes like '%CYBER_ATTACK%' or DocumentIdentifier like '%cyber_attack%') ignore case
,name)) a
JOIN EACH (
SELECT GKGRECORDID, UNIQUE(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[1-5]#.*?#(.*?)#')) name
FROM [gdelt-bq:gdeltv2.gkg] 
WHERE DATE > 20190101000000 and DATE < 20190605000000 AND V2Locations like '%United States%United States%' and (V2Themes like '%CYBER_ATTACK%' or DocumentIdentifier like '%cyber_attack%') ignore case
) b
ON a.GKGRECORDID=b.GKGRECORDID
WHERE a.name<b.name
GROUP EACH BY 1,2
ORDER BY 3 DESC
LIMIT 1000
) c
JOIN EACH (
select fips, country from [gdelt-bq:extra.countryinfo]) d
ON c.Source = d.fips
JOIN EACH (
select fips, country from [gdelt-bq:extra.countryinfo]) e
ON c.Target = e.fips
order by Count Desc

Answer 1

只需将下面添加到查询的底部(但显然在ORDER BY之上)

WHERE c.Source = 'US'   

此外，可以重写最后两个连接，以减少冗长

JOIN EACH [gdelt-bq:extra.countryinfo] d 
ON c.Source = d.fips
JOIN EACH [gdelt-bq:extra.countryinfo] e
ON c.Target = e.fips