BigQuery/SQL中如何查询每个国家人口最多的城市

直接用MAX()的常见问题 #

很多人第一反应会用GROUP BY 国家 + MAX(城市人口)的写法，但这种写法存在明显缺陷：你可以通过聚合拿到每个国家的最大人口数值，但无法直接关联到这个最大人口对应的城市名称、城市维度的其他属性，必须额外做一次自关联才能匹配到对应城市记录，不仅写法冗余，大数据量下性能损耗也很明显。

最优方案：QUALIFY子句搭配窗口函数 #

BigQuery原生支持QUALIFY过滤子句，是实现分组TopN取数的最优选择，不需要嵌套子查询、不需要自连接，查询性能和代码可维护性都是最好的。
假设你当前查询的数据源包含country（国家字段）、city_name（城市名称字段）、population（城市人口字段），最终代码如下：

SELECT
  country,
  city_name,
  population
  -- 这里可以直接加你需要返回的其他城市维度字段，不需要额外处理
FROM `你的项目名.你的数据集名.存储城市人口的表名`
-- 按国家分区，按人口降序排序，仅保留每个国家排名第一的城市
QUALIFY RANK() OVER (PARTITION BY country ORDER BY population DESC) = 1

方案说明 #

• 函数选择逻辑：优先用RANK()而非ROW_NUMBER()，如果同一个国家存在多个城市人口并列第一的情况，RANK()会把所有并列的最大城市全部返回，不会意外丢数据；如果业务明确要求每个国家只返回1条城市记录、就算并列也随机取一条，替换成ROW_NUMBER()即可，两者性能差异可以忽略。
• 性能优势：QUALIFY是BigQuery查询优化器深度适配的语法，比传统的「窗口函数算排名+外层子查询WHERE过滤排名=1」的写法少一层嵌套，执行时会直接在数据扫描阶段完成分区排序和过滤，不需要生成中间结果表；和MAX()自关联的写法相比，仅需要一次表扫描，数据量越大性能优势越明显。
• 扩展性强：后续如果需求调整为取每个国家人口Top3的城市，只需要把=1改成<=3即可，不需要调整整体查询结构。

基于MAX()的实现写法（仅做对比参考） #

注意：以下写法仅做逻辑对比参考，生产环境大数据量下不推荐使用，性能比QUALIFY方案差30%以上。

WITH country_max_pop AS (
  SELECT
    country,
    MAX(population) AS max_city_pop
  FROM `你的项目名.你的数据集名.存储城市人口的表名`
  GROUP BY country
)
SELECT
  t.country,
  t.city_name,
  t.population
FROM `你的项目名.你的数据集名.存储城市人口的表名` t
JOIN country_max_pop m
  ON t.country = m.country
  AND t.population = m.max_city_pop

内容的提问来源于stack exchange，提问作者williamn