MySQL总和排名，按流派分组，限制10

Question

我有这样的 MySQL 表，我想计算每个类型的 TOP10:

• rankings_2016(trackId、genreId、排名、时间戳)
• 流派(genreId，genreName)
• 轨道(trackId、trackName、genreId)
• 艺术家(artistId，artistName)
• artists_tracks (artistId, trackId)

我想获得每个流派、每个轨道和每个艺术家的 TOP10 排名。

一首轨道或一位艺术家最多可以有 2 种流派。排名可能是一样的。只是为了得到 LIMIT 2 的想法:

  genreId | trackId | ranking
 ---------------------------------
   0         1111      100
   0         2222       99
   1         1111      100
   1         2222       99

  genreId | artistId | ranking
 ---------------------------------
   0         1111      100
   0         2222       99
   1         1111      100
   1         2222       99

我找到的唯一解决方案是将所有内容都放在一个表中，然后在页面中限制 10 个，但它会占用我的数据库大小(我的资源有限)。

对于我写的轨道:

SELECT trackId, genreId, @newRank := SUM(ranking) as ranking
FROM rankings_2016
WHERE timestamp >= ( select unix_timestamp('2016-01-01') )
AND timestamp <= ( select unix_timestamp('2016-12-31') )
GROUP BY trackId, genreId

对于艺术家:

SELECT artistId, genreId, @newRank := SUM(a1.ranking) as ranking
FROM rankings_2016 a1
LEFT JOIN artists_tracks a2
ON a1.trackId = a2.trackId
WHERE timestamp >= ( select unix_timestamp('2016-01-01') )
AND timestamp <= ( select unix_timestamp('2016-12-31') )
GROUP BY artistId, genreId

提前感谢大家的提示。

---

更新

一般的逻辑(和接受的回复)需要良好的索引和高性能的服务器。

在我的案例中，ARTISTS 失败并出现错误 500，除非我增加 CPU。一般来说，用 INNER 替换 LEFT 可以节省 1 秒。

Answer 1

考虑一个相关的计数子查询，以按艺术家/轨道/流派分组对排名进行排序。然后在外部查询中使用这个rank 计算列来筛选每个分组的前 10 个:

艺术家排名 (每个艺术家和流派的前 10 名排名)

SELECT main.artistId, main.genreId, main.ranking
FROM
 (
   SELECT a.artistId, r.genreId, r.ranking,
          (SELECT COUNT(*) FROM rankings_2016 subr
           LEFT JOIN artists_tracks suba ON subr.trackId = suba.trackId
           WHERE suba.artistId = a.artistId
           AND subr.genreId = r.genreId
           AND subr.ranking >= r.ranking) AS rn
   FROM rankings_2016 r
   LEFT JOIN artists_tracks a ON r.trackId = a.trackId
   WHERE r.timestamp BETWEEN ( select unix_timestamp('2016-01-01') )
                         AND ( select unix_timestamp('2016-12-31') ) 
 ) AS main

WHERE main.rn <= 10

轨道排名 (每个轨道和流派的前 10 名排名)

SELECT main.trackId, main.genreId, main.ranking
FROM
 (
   SELECT r.trackId, r.genreId, r.ranking,
          (SELECT COUNT(*) FROM rankings_2016 subr               
           WHERE subr.genreId = r.genreId
           AND subr.trackId = r.trackId
           AND subr.ranking >= r.ranking) AS rn
   FROM rankings_2016 r
   WHERE r.timestamp BETWEEN ( select unix_timestamp('2016-01-01') )
                         AND ( select unix_timestamp('2016-12-31') ) 
 ) AS main

WHERE main.rn <= 10