MySQL 分组 Top N 以及窗口函数

1. 不分组 Top N

发贴表：

用户标题
用户1标题1
用户1标题2
用户2标题3
用户3标题4
用户3标题5
用户1标题6
用户3标题7
用户2标题8
用户2标题9
用户4标题10
用户4标题11
用户5标题12

求发贴数量前 N 的用户。

比如当 N = 2 时，因为用户 1、2、3 都发了 3 篇贴，用户 4 发了 2 篇贴，用户 5 发了 1 篇贴，所以结果集应该为：

用户数量
用户13
用户23
用户33

如果直接执行：

select 用户, count(*) as 数量 from 发贴表 group by 用户 order by 数量 desc limit 2;

那么将得到结果集：

用户数量
用户13
用户23

因此应该采用如下方案：

第一步：

-- 获取每个用户的发贴数量
select 用户, count(*) as 数量 from 发贴表 group by 用户;

得到临时表：

用户数量
用户13
用户23
用户33
用户42
用户51

第二步：

-- 获取比当前用户发贴量多的用户
select t1.用户, t1.数量, t2.id as t2_id from 临时表 t1 left join 临时表 t2 on t1.数量 < t2.数量;

得到临时表2：

用户数量t2_id
用户13null
用户23null
用户33null
用户421
用户422
用户423
用户511
用户512
用户513
用户514

第三步：

-- 获取 top N
select 用户, 数量 from 临时表2 group by 用户 having count(t2_id) < 2;

得到结果集：

用户数量
用户13
用户23
用户33

最后将这三步结合起来：

DROP TABLE IF EXISTS posts;

CREATE TABLE IF NOT EXISTS posts
(
    user_id bigint unsigned,
    title   varchar(1024)
);

INSERT INTO posts
VALUES
    ROW (1, 'title-1'), ROW
        (1, 'title-2'), ROW
        (2, 'title-3'), ROW
        (3, 'title-4'), ROW
        (3, 'title-5'), ROW
        (1, 'title-6'), ROW
        (3, 'title-7'), ROW
        (2, 'title-8'), ROW
        (2, 'title-9'), ROW
        (4, 'title-10'), ROW
        (4, 'title-11'), ROW
        (5, 'title-12');

WITH cte AS (SELECT user_id, COUNT(title) AS post_cnt
             FROM posts
             GROUP BY user_id)
SELECT user_id, post_cnt
FROM (SELECT t1.user_id, t1.post_cnt, t2.user_id as t2_user_id
      FROM cte t1
               LEFT JOIN cte t2 ON t1.post_cnt < t2.post_cnt) t
GROUP BY user_id
HAVING COUNT(t2_user_id) < 2;

使用窗口函数实现：

WITH cte AS (SELECT user_id, COUNT(title) AS post_cnt
             FROM posts
             GROUP BY user_id)
SELECT user_id, post_cnt
FROM (SELECT user_id, post_cnt, RANK() OVER (ORDER BY post_cnt DESC ) AS nth FROM cte) t
WHERE nth <= 2;

2. 分组 Top N

成绩表：

课程名学生ID成绩
课程1学生1100
课程1学生290
课程1学生395
课程2学生190
课程2学生2100
课程2学生395

求每科的前 N 名。

当 N = 2 时，结果集应该为：

课程名学生ID成绩
课程1学生1100
课程1学生395
课程2学生2100
课程2学生395

第一步：

-- 找出比当前学生成绩高的学生
select t1.课程名, t1.学生ID, t1.成绩, t2.学生ID as t2_student_id from 成绩表 t1 left join 成绩表 t2 on t1.课程名 = t2.课程名 and t1.成绩 < t2.成绩;

得到结果集：

课程名学生ID成绩t2_student_id
课程1学生1100null
课程1学生290学生1
课程1学生290学生3
课程1学生395学生1
课程2学生190学生2
课程2学生190学生3
课程2学生2100null
课程2学生395学生2

第二步：

-- 获取 top N
select 课程名, 学生ID, max(成绩) from 临时表 group by 课程名, 学生姓名 having count(t2_student_id) < 2;

得到最终结果：

课程号学生姓名成绩
课程1学生1100
课程1学生395
课程2学生2100
课程2学生395

最后将这两步结合起来：

DROP TABLE IF EXISTS grades;

CREATE TABLE IF NOT EXISTS grades
(
    course     varchar(1024),
    student_id varchar(1024),
    grade      int unsigned
);

INSERT INTO grades
VALUES
    ROW ('course-1', 'student-1', 100), ROW
        ('course-1', 'student-2', 90), ROW
        ('course-1', 'student-3', 95), ROW
        ('course-2', 'student-1', 90), ROW
        ('course-2', 'student-2', 100), ROW
        ('course-2', 'student-3', 95);


SELECT course, student_id, MAX(grade)
FROM (SELECT t1.course, t1.student_id, t1.grade, t2.student_id as t2_student_id
      FROM grades t1
               LEFT JOIN grades t2 ON t1.course = t2.course AND t1.grade < t2.grade) t
GROUP BY course, student_id
HAVING COUNT(t2_student_id) < 2;

使用窗口函数实现：

SELECT course, student_id, grade
FROM (SELECT course, student_id, grade, RANK() OVER (PARTITION BY course ORDER BY grade DESC ) AS nth
      FROM grades) t
WHERE nth <= 2;

3. 窗口函数