前端之家

  1. 首页
  2. 问答

如何在SQL（postgres）中按年份查找与最大资源相关的项目ID？

• 问答

我正在尝试运行一个看起来很简单的SQL查询，但是我不知道我应该使用哪种机制来实现这一点。 我有下表：

| id | playcount | release_date |
| -- | --------- | ------------ |
| 1  | 47712     | 2019-12-27   |
| 2  | 626778    | 2017-07-23   |
| 3  | 827091    | 2019-09-12   |
| 4  | 66419     | 2015-09-05   |
| 5  | 58627     | 2016-09-12   |
| 6  | 60272     | 2017-09-06   |
| 7  | 1762582   | 2017-10-07   |
| 8  | 11280     | 2016-10-12   |
| 9  | 30890     | 2019-10-29   |
| 10 | 715173    | 2019-07-02   |

我想找出哪个ID每年拥有最多playcount个。 在此示例中，2019年的playcount最大值为3。

我正在寻找以下输出：

playcount_table
| year | playcount | id |
| ---- | --------- | -- |
| 2019 | 827091    | 3  |
| 2017 | 1762582   | 7  |
| 2016 | 58627     | 5  |
| 2015 | 66419     | 4  |

通过以下查询，我成功获得了按年计的最大播放次数：

select to_char(date_trunc('year',TO_DATE(p_table.release_date,'YYYY-MM-DD')),'YYYY-MM-DD') as year,max(p_table.playcount) as playcounts
from playcount_table as p_table
group by year;

但是我无法检索与此最大值id相关的playcount。

您能帮我吗？

先谢谢您

qiao799 回答：如何在SQL（postgres）中按年份查找与最大资源相关的项目ID？

使用ROW_NUMBER：

WITH cte AS (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY EXTRACT(year FROM release_date)
                                 ORDER BY playcount DESC) rn
    FROM playcount_table
)

SELECT
    EXTRACT(year FROM release_date) year
    playcount,id
FROM cte
WHERE
    rn = 1
ORDER BY
    EXTRACT(year FROM release_date) DESC;

如果给定年份可能有两个或更多记录并列为最大播放次数，而您想报告这两个记录，则将上面的ROW_NUMBER替换为RANK。

我们也许还可以在这里使用DISTINCT ON 

SELECT DISTINCT ON (EXTRACT(year FROM release_date))
    EXTRACT(year FROM release_date),playcount,id
FROM playcount_table
ORDER BY
    EXTRACT(year FROM release_date),playcount DESC;
datetimegreatest-n-per-groupgroup-bypostgresqlsql
本文链接：https://www.f2er.com/3168230.html

大家都在问