查找每个id的最常见值

Question

我试过了，但输出不是我需要的期望输出。这个代码每年只给我不同的值：

select distinct  (year) year, most_frequent_value from (
SELECT year, genre AS most_frequent_value, count(*) as _count
FROM Oscar
GROUP BY year, genre) a
ORDER BY year, _count DESC 

Answer 1

Witrow_number()窗口函数：

select t.year, t.genre
from (
select year, genre, 
row_number() over (partition by year order by count(*) desc) rn
from Oscar
group by year, genre
) t
where t.rn = 1

请参阅演示
结果：

| year | genre   |
| ---- | ------- |
| 2016 | Action  |
| 2017 | Romance |
| 2018 | Fantasy |
| 2019 | Fantasy |
| 2020 | Action  |

如果希望在结果中显示平局，请使用rank()而不是row_number()