获取每年的热门项目

Question

我有一个包含一些记录的数据表。使用mysql，我可以得到一个按特定时期(年(和用户分组的结果，并按物种数量排序(按降序(。

SELECT YEAR(entry_date) AS period, uid AS user, COUNT(DISTINCT pid) AS species
FROM records
WHERE YEAR(entry_date)<YEAR(CURDATE())
GROUP BY period, uid
ORDER by period, species DESC

请参阅随附的结果图片。但是，如果我只想获得每年的顶级用户(和物种数量((红色标记的行(呢？我怎样才能做到这一点？

稍后我可以在php代码中处理这个问题，但最好已经在mysql查询中对其进行了排序。

谢谢你的帮助！

Answer 1

如果您运行的是MySQL 8.0，您可以使用RANK()根据物种计数对年份分区中的记录进行排名，然后过滤每个组中排名靠前的记录：

SELECT *
FROM (
SELECT 
YEAR(entry_date) AS period, 
uid AS user, 
COUNT(DISTINCT pid) AS species,
RANK() OVER(PARTITION BY YEAR(entry_date) ORDER BY COUNT(DISTINCT pid) DESC) rn
FROM records
WHERE entry_date < DATE_FORMAT(CURRENT_DATE, '%Y-01-01')
GROUP BY period, uid
) t
WHERE rn = 1
ORDER by period

这样可以保留顶部系带(如果有的话(。请注意，对WHERE子句中的日期使用了索引友好型筛选器。

在早期版本中，等效的选项是使用HAVING子句和相关子查询进行筛选：

SELECT 
YEAR(entry_date) AS period, 
uid AS user, 
COUNT(DISTINCT pid) AS species
FROM records r
WHERE entry_date < DATE_FORMAT(CURRENT_DATE, '%Y-01-01')
GROUP BY period, uid
HAVING COUNT(DISTINCT pid) = (
SELECT COUNT(DISTINCT r1.pid) species1
FROM records r1
WHERE YEAR(r1.entry_date) = period
GROUP BY r1.uid
ORDER BY species1 DESC
LIMIT 1
)
ORDER by period