如何在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;
本文链接:https://www.f2er.com/3168230.html

大家都在问