如何用SQL分组查询出现频率最高的字段值？

我需要查询每个物业（pid）对应的出现频率最高的Response值，期望每个pid返回一行结果，包含物业基本信息及该物业中出现次数最多的Response值。

我尝试了以下SQL，但它只能返回一条全局出现次数最高的记录，无法满足每个pid单独统计的需求：

select pid, propName, Address, City, Response, count(Response) as cr
from Prop
group by 
        pid,
        propName,
        Address,
        City,
        Response
order by
      cr
limit 1;

期望输出示例：

pid propName    Address City    Response
1   p1          addr1   c1      2
2   p2          addr2   c2      3

其中2是pid=1的物业中出现频率最高的Response值，3是pid=2的物业中出现频率最高的Response值。

可以利用窗口函数对每个pid分组内的Response出现次数进行排名，再筛选出排名第一的记录，以下提供两种常用方法：

方法1：仅取单条最高频记录（适用于无并列或无需保留并列的场景） #

使用ROW_NUMBER()为每个pid分组内的记录按出现次数降序编号，编号为1的就是该组出现次数最多的Response：

WITH ranked_responses AS (
    SELECT 
        pid, 
        propName, 
        Address, 
        City, 
        Response,
        ROW_NUMBER() OVER (PARTITION BY pid ORDER BY COUNT(Response) DESC) AS rn
    FROM Prop
    GROUP BY pid, propName, Address, City, Response
)
SELECT pid, propName, Address, City, Response
FROM ranked_responses
WHERE rn = 1;

方法2：保留所有并列最高的记录（适用于存在多个Response出现次数相同且均为最高的场景） #

使用RANK()替代ROW_NUMBER()，这样出现次数相同的记录会获得相同的排名，所有排名为1的记录都会被返回：

WITH ranked_responses AS (
    SELECT 
        pid, 
        propName, 
        Address, 
        City, 
        Response,
        RANK() OVER (PARTITION BY pid ORDER BY COUNT(Response) DESC) AS rn
    FROM Prop
    GROUP BY pid, propName, Address, City, Response
)
SELECT pid, propName, Address, City, Response
FROM ranked_responses
WHERE rn = 1;

关键逻辑说明 #

• PARTITION BY pid：将数据按pid拆分，确保每个物业的统计独立进行
• ORDER BY COUNT(Response) DESC：按Response的出现次数从高到低排序，保证次数最多的记录排在最前
• 公共表表达式（CTE）ranked_responses先完成分组统计与排名，再通过WHERE rn = 1筛选出目标记录

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