postgres:获取每个组中前n个出现的值
问题描述：
我有一个像这样的简单表:
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