GROUP BY后如何为每个ward保留COUNT值最高的唯一记录

你的初始查询已经完成了按Ward和犯罪类型的统计，但要筛选每个Ward的最高值，核心是给每个Ward内的统计结果按犯罪次数排序，再取排名第一的记录。这里提供两种适用于BigQuery的方案：

方案一：使用QUALIFY子句（推荐，更简洁） #

BigQuery支持QUALIFY子句，可以直接在分组统计后过滤窗口函数的结果，无需嵌套子查询：

SELECT ward, primary_type, COUNT(primary_type) as amt_of_crimes
FROM `bigquery-public-data.chicago_crime.crime`
WHERE ward IS NOT NULL AND year = 2022
GROUP BY ward, primary_type
QUALIFY ROW_NUMBER() OVER (PARTITION BY ward ORDER BY amt_of_crimes DESC) = 1
ORDER BY ward ASC;

说明： #

• PARTITION BY ward：按Ward分组，每个Ward单独处理排序逻辑
• ORDER BY amt_of_crimes DESC：在每个Ward内，按犯罪次数从高到低排序
• ROW_NUMBER() = 1：取每个Ward内排名第一的记录
• 如果需要保留并列最高的犯罪类型（比如同一个Ward有两种犯罪类型次数相同且都是最高），把ROW_NUMBER()换成RANK()即可

方案二：使用子查询+窗口函数 #

如果习惯用子查询的写法，也可以先统计出所有分组结果，再在外层筛选排名第一的记录：

SELECT ward, primary_type, amt_of_crimes
FROM (
    SELECT 
        ward, 
        primary_type, 
        COUNT(primary_type) as amt_of_crimes,
        ROW_NUMBER() OVER (PARTITION BY ward ORDER BY COUNT(primary_type) DESC) as rn
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE ward IS NOT NULL AND year = 2022
    GROUP BY ward, primary_type
)
WHERE rn = 1
ORDER BY ward ASC;

关键点： #

• 内层子查询先完成分组统计，并给每个Ward内的记录添加排名rn
• 外层筛选rn=1的记录，确保每个Ward仅出现一次
• 同样，替换ROW_NUMBER()为RANK()可处理并列最高的场景

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