如何按每个用户显示最常订购的产品?

dkqlctbz  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(255)

假设我们有一张table

User   Product  Frequency
A       P1         5
A       P2         4
A       P3         3
B       P1         3
B       P2         4
C       P1         2
C       P2         1

我希望结果显示为:

User   Product  Frequency
A       P1        5
B       P2        4
C       P1        2

有人能帮我吗?

vql8enpb

vql8enpb1#

你可以用 row_number 按每个用户的频率分配等级。如果您使用的是SQLServer、MySQL8.0、oracle、postgresql,那么下面的解决方案应该可以使用。
你可以用 rank() 也。

select
  user,
  product,
  frequency
from
(
  select
    *,
    row_number() over (partition by user order by frequency desc) as rn
  from yourTable
) subq
where rn = 1
unftdfkk

unftdfkk2#

可采用的简单方法是:

Select user, product, frequency from
(select t.*,  max(t.frequency) OVER (PARTITION BY t.user) max_frq
from tab t) where frequency = max_frq;

另一种方法是按行编号:

Select usr, product, freq from(
Select t.*, row_number() OVER (Partition by usr order by freq desc) rn
from tab t)
where rn = 1;

rank():

Select usr, product, freq from (
Select t.*, rank() OVER (Partition by usr order by freq desc) rn
from tab t
)where rn = 1;

密级():

Select usr, product, freq from(
Select t.*, dense_rank() OVER (Partition by usr order by freq desc) rn
from tab t
)where rn = 1;

相关问题