如何从PostgreSQL中的聚合计数中获取top name

Question

我有3个表

1. 用户
2. 硬币历史
3. 硬币

用户表数据看起来像

id | email 
------------------
1  | [e-mail] 

硬币历史

id | from_user_id | coin_id 
----------------------
1  |    1    | 2
1  |    1    | 2   
1  |    1    | 3 

硬币

id | name 
------------
2 | Lira
3 | Bitcoin

我的预期输出

Email         | total_given   | top_coin_name 
============================================
[e-mail]  | 3             | lira 

在写下以下查询后，我可以通过电子邮件获得总计数

SELECT
users.email
from_user_id,
count (coin_id)
FROM
coin_histories
INNER JOIN users 
ON coin_histories.from_user_id = users.id
GROUP BY
from_user_id,
users.email
ORDER BY 
count desc

如何获取用户给出的顶级硬币名称？

Answer 1

您可以在一条语句中计算给定的硬币总数和每个用户的最高数量：

select from_user_id, 
coin_id,
sum(count(*)) over (partition by from_user_id) as total_given,
dense_rank() over (partition by from_user_id order by count(*) desc) as rnk
from coin_histories
group by from_user_id, coin_id 

这首先计算每个用户提供的硬币数量和硬币id。窗口函数(over (...)(在group by之后进行评估。因此，sum()为我们提供了每个用户的总行数，dense_rank()评估了每个给定硬币的排名，以便我们可以选择最高的一个。

对于您的示例数据，上面的查询返回以下内容：

from_user_id | coin_id | total_given | rnk
-------------+---------+-------------+----
1 |       2 |           3 |   1
1 |       3 |           3 |   2

这个查询可以针对用户和硬币表加入，以获得您想要的：

select u.id, u.email, tp.total_given, c.name 
from users u
join (
select from_user_id, 
coin_id,
sum(count(*)) over (partition by from_user_id) as total_given,
dense_rank() over (partition by from_user_id order by count(*) desc) as rnk
from coin_histories
group by from_user_id, coin_id 
) tp on tp.from_user_id = u.id and tp.rnk = 1
join coins c on c.id = tp.coin_id
;       

在线示例