BigQuery多国家开放端口主机计数SQL实现求助

我来帮你搞定这个BigQuery的问题，先梳理下你遇到的几个核心错误，再给你正确的方案和成本建议：

先看你原SQL的问题 #

1. WHERE子句写法错误：你用了WHERE location.country_code LIKE 'AT', 'DE', 'CH'，这不符合SQL语法。因为是精确匹配两位国家代码，应该用IN ('AT', 'DE', 'CH')；如果是模糊匹配才用LIKE，这里完全没必要。
2. 字段引用错误：外层查询里写了location.country_code，但你的子查询已经把location.country_code作为单独字段返回了，外层应该直接用country_code，这就是报错Unrecognized name: location的原因——外层查询里根本没有location这个对象。
3. 窗口函数与GROUP BY混用逻辑混乱：你试图用窗口函数count(ports) OVER (PARTITION BY location.country_code)再配合GROUP BY ports，这会导致聚合逻辑冲突，因为你要的是每个国家每个端口的统计，直接分组聚合就够了，不需要窗口函数。

正确的多国家统计SQL #

如果你想一次性获取奥地利、德国、瑞士三个国家每个开放端口对应的主机数量，用这个SQL就可以：

#standardsql
SELECT 
  location.country_code AS country,
  ports,
  COUNT(DISTINCT ip) AS host_count  -- 统计有该端口的独立主机数
FROM `censys-io.ipv4_public.20171231` i,
UNNEST(i.ports) AS ports  -- 用UNNEST展开端口数组，比旧写法更清晰
WHERE location.country_code IN ('AT', 'DE', 'CH')
GROUP BY country, ports
ORDER BY country, ports

这里有个细节要注意：

• 如果你要的是端口出现的总次数（比如一个主机开了3个端口就算3次），就把COUNT(DISTINCT ip)改成COUNT(ports)，和你原来单个国家的逻辑一致；
• 我用了UNNEST(i.ports) AS ports来展开端口数组，这是BigQuery更标准的写法，比i, i.ports可读性更强，功能是一样的。

关于成本的问题 #

BigQuery的成本是按扫描的字节数计算的：

• 一次性查询三个国家，扫描的是这三个国家的所有数据行；
• 逐个查询的话，三次扫描的总字节数和一次基本相同（因为三个国家的行加起来就是一次扫描的范围）。

所以成本上差别不大，但一次性查询更高效，能减少多次提交查询的开销，还能直接拿到统一格式的结果。如果你还是担心，可以用--dry_run模式估算扫描字节数，对比两种方案的成本。

内容的提问来源于stack exchange，提问作者dr. gruselglatz