Bigquery/SQL中的分区和排序

Question

假设我有一个查询，提取所有国家、所有城市和每个城市的人口。

我希望它只拉出最大的城市对于每个国家。因此，每个国家只有一个城市(最大的城市)。

取"MAX()"的最佳方法是什么?每个国家的城市?

我在BQ写SQL

Answer 1

使用下面的简单方法

select country, 
array_agg(struct(city, population) order by population desc limit 1)[offset(0)].*
from your_table
group by country

Answer 2

您还可以使用分析窗口函数和限定子句:

SELECT  country,city,population
FROM your_table
QUALIFY RANK() OVER (PARTITION BY country ORDER BY population DESC) = 1

请注意，在不太可能的情况下，一个国家的两个最大的城市拥有相同的人口，您将在此查询的结果集中获得两行。如果希望避免这种情况，可以使用ROW_NUMBER函数，在平局的情况下，该函数只选择一条记录。您还可以在窗口函数的order by子句中添加一个tie-break。