大查询:根据嵌套字段中的计数进行筛选

Question

我正在尝试查找嵌套字段中有 5 次或更多次提及"BE"或"比利时"的记录。 以下查询不会产生任何结果：

#standardSQL
SELECT
GKGRECORDID
FROM `gdelt-bq.gdeltv2.gkg_partitioned`
where _PARTITIONTIME BETWEEN TIMESTAMP('2019-10-09') AND TIMESTAMP('2019-10-09')
and (V2Themes LIKE "%WB_%GROWTH%")
group by GKGRECORDID
having count(V2Locations LIKE "%BE%" OR V2Locations LIKE "%Belgium%")>5

我会感谢任何想法。

Answer 1

如果我正确理解您的数据，您正在尝试计算每个V2Themes记录中"BE"或"Belgium"的出现次数。因此，在下面的示例中，计数应为 4？

1#俄罗斯#RS#RS##

60#100#RS#2475;1#委内瑞拉#VE#VE##8#-66#VE#471;1#委内瑞拉#VE#VE##8#-66#VE#1435;1#委内瑞拉#VE#VE##8#-66#VE#1521;1#委内瑞拉#VE#VE##8#-66#VE#2409;1#俄语#RS#RS##60#100#RS#2440;4#布鲁塞尔， 布鲁塞尔首都， 比利时#BE#BE11#5850#50.8333#4.33333#-1955538#673;4#布鲁塞尔， 布鲁塞尔首都， 比利时#BE#BE11#5850#50.8333#4.33333#-1955538#2342;4#基多， 皮钦查，

如果这是正确的，一种可能的解决方法是此处解释的解决方法。将此解决方案转化为您的需求(计算单词而不是字符(，我建议使用 SPLIT 方法使用给定的分隔符划分字符串，并计算其元素(包含和不包含您要搜索的字符串(。这将是您问题的一种解决方案：

#standardSQL
SELECT
GKGRECORDID,
(ARRAY_LENGTH(SPLIT(V2Locations, '#')) - ARRAY_LENGTH(SPLIT(REPLACE(V2Locations, '#BE', ''), "#"))) + (ARRAY_LENGTH(SPLIT(V2Locations, '#')) - ARRAY_LENGTH(SPLIT(REPLACE(V2Locations, '#Belgium', ''), "#"))) as bel_num,
V2Locations
FROM `gdelt-bq.gdeltv2.gkg_partitioned`
where _PARTITIONTIME BETWEEN TIMESTAMP('2019-10-09') AND TIMESTAMP('2019-10-09')
and (V2Themes LIKE "%WB_%GROWTH%")
group by GKGRECORDID, V2Locations
having bel_num<5

Answer 2

这里有几点：

而不是

COUNT(V2Locations LIKE "%BE%" OR V2Locations LIKE "%Belgium%")>5   

你应该使用

COUNTIF(V2Locations LIKE "%BE%" OR V2Locations LIKE "%Belgium%")>5    

即使使用上述修复 - 您仍然无法获得预期的结果，因为您只针对一个分区，并且在该分区中具有相同GKGRECORDID的最大行数仅为2，因此显然无法输出具有超过5内容的GKGRECORDID