postgres:获取每个组中前n个出现的值 [英] postgres: get top n occurrences of a value within each group

问题描述

我有一个像这样的简单表:

I have a simple table like this:

user    letter
--------------
1       A
1       A
1       B
1       B
1       B
1       C

2       A
2       B
2       B
2       C
2       C
2       C

我想获得每个用户的前2个出现的字母"，

I want to get the top 2 occurrences of 'letter' per user, like so

user    letter  rank(within user group)
--------------------
1       B       1
1       A       2

2       C       1
2       B       2

甚至更好:折叠成列

user    1st-most-occurrence  2nd-most-occurrence
1       B                   A
2       C                   B

如何在postgres中完成此操作?

How can I accomplish this in postgres?

推荐答案

with cte as (
    select 
        t.user_id, t.letter,
        row_number() over(partition by t.user_id order by count(*) desc) as row_num
    from Table1 as t
    group by t.user_id, t.letter
)
select
    c.user_id,
    max(case when c.row_num = 1 then c.letter end) as "1st-most-occurance",
    max(case when c.row_num = 2 then c.letter end) as "2st-most-occurance"
from cte as c
where c.row_num <= 2
group by c.user_id

=> sql小提琴演示

这篇关于postgres:获取每个组中前n个出现的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！