mysql - 如何使用 MySQL 中的 RANK() 窗口函数获取每个组的前 N ​​条记录？

Question

使用 MySQL 中的新窗口函数，我可以通过以下方式获取前 K 条记录

SELECT user_id, score, RANK() OVER w AS 'rank' FROM user_scores WINDOW w AS (ORDER BY score desc)LIMIT k;

但是，如果我想在每个组中获得前 k 名，请使用

SELECT user_id, score, platform, RANK() OVER w AS 'rank' FROM user_scores WINDOW w AS (PARTITION BY platform ORDER BY score desc)LIMIT k;

它只获得排名的 k 记录，这不是我想要的。当尝试将限制添加到窗口时

SELECT user_id, score, platform, RANK() OVER w AS 'rank' FROM user_scores WINDOW w AS (PARTITION BY platform ORDER BY score des LIMIT k);

它报告语法错误。有没有什么办法可以利用这个窗口功能，实现在每组中获取top-k并显示结果？示例是：示例表数据：

+--------------------+----------+-------+
| user_id          | platform | score |
+--------------------+----------+-------+
|  62225661637535243 | steam     |  5349 |
|  71061003851867964 | xbox     |   325 |
| 180508709444817882 | psn      |  8710 |
| 207545417195670901 | steam     | 13094 |
| 224382767175085374 | steam     | 17112 |
+--------------------+----------+-------+

前 10 名的预期输出（与样本数据不匹配）：

+------------+-------+----------+------+
| user_id  | score | platform | rank |
+------------+-------+----------+------+
| 3081837904 | 19981 | bnet     |    1 |
| 2937016388 | 19625 | bnet     |    2 |
| 2548218375 | 19026 | bnet     |    3 |
| 3379170403 | 18715 | bnet     |    4 |
| 2161236967 | 18591 | bnet     |    5 |
|  580312064 | 17867 | bnet     |    6 |
|  998283136 | 17698 | bnet     |    7 |
| 4104228350 | 17632 | bnet     |    8 |
| 1296585900 | 17594 | bnet     |    9 |
|  778412200 | 17012 | bnet     |   10 |
|  726955649 | 19774 | psn      |    1 |
|  397001285 | 19590 | psn      |    2 |
| 3154882519 | 19256 | psn      |    3 |
| 3502267467 | 18926 | psn      |    4 |
| 1351561694 | 18915 | psn      |    5 |
| 3397147261 | 18567 | psn      |    6 |
| 3698675484 | 18160 | psn      |    7 |
|  158948633 | 17704 | psn      |    8 |
| 1345737383 | 17512 | psn      |    9 |
| 2788225545 | 17454 | psn      |   10 |
....

标签： mysqldense-rank