如何获取GROUP BY后各(week,year,device_id)分组中my_number计数最大的行？

表结构 #

PostgreSQL表public.tmch的结构如下：

CREATE TABLE IF NOT EXISTS public.tmch (
    id bigserial NOT NULL,
    year integer,
    week integer,
    my_number integer,
    device_id bigint,
    CONSTRAINT tmch_pkey PRIMARY KEY (id)
);

示例数据 #

1716446 2024    37  13  2
1716447 2024    37  13  2
1716448 2024    37  0   3
1716449 2024    37  11  4
1716450 2024    37  12  4
1716451 2024    37  0   6
1716452 2024    37  0   6
1716453 2024    37  0   6
1716454 2024    37  1   6
1716455 2024    37  1   6
1716456 2024    37  9   7

现有统计查询 #

已使用以下SQL统计每个(week, year, device_id)分组中my_number的出现次数：

select count(my_number) c, my_number, device_id, year, week from tmch
group by my_number, device_id, year, week
order by device_id asc, c desc

基于更大样本的查询结果：

6   16  2   2024    37
4   17  2   2024    37
4   15  2   2024    37
2   13  2   2024    37
1   2   2   2024    37
8   15  3   2024    37
6   16  3   2024    37
5   14  3   2024    37
3   20  3   2024    37  
3   18  3   2024    37
3   19  3   2024    37
1   8   3   2024    37

需求 #

仅获取每个(week, year, device_id)分组中my_number计数最高的行，期望结果：

6   16  2   2024    37  -- my_number=16在device_id=2、2024年第37周出现6次，为该分组最高
8   15  3   2024    37  -- my_number=15在device_id=3、2024年第37周出现8次，为该分组最高

解决方案 #

使用row_number()窗口函数，核心是按year, week, device_id分区，再按计数c降序排序，取排序后行号为1的记录：

WITH counted AS (
    SELECT 
        count(my_number) c, 
        my_number, 
        device_id, 
        year, 
        week,
        row_number() OVER (PARTITION BY year, week, device_id ORDER BY count(my_number) DESC) AS rn
    FROM tmch
    GROUP BY my_number, device_id, year, week
)
SELECT c, my_number, device_id, year, week
FROM counted
WHERE rn = 1
ORDER BY device_id;

补充说明 #

如果同一个分组中存在多个my_number计数相同且均为最高的情况，上述SQL只会返回其中一条。若要保留所有并列最高的记录，将row_number()替换为rank()即可：

WITH counted AS (
    SELECT 
        count(my_number) c, 
        my_number, 
        device_id, 
        year, 
        week,
        rank() OVER (PARTITION BY year, week, device_id ORDER BY count(my_number) DESC) AS rn
    FROM tmch
    GROUP BY my_number, device_id, year, week
)
SELECT c, my_number, device_id, year, week
FROM counted
WHERE rn = 1
ORDER BY device_id;

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