MYSQL窗口函数

糖逗

参考链接：https://zhuanlan.zhihu.com/p/92654574

USE train;

#创建表
DROP TABLE IF EXISTS class;
CREATE TABLE class(
        学号 VARCHAR(255) PRIMARY KEY,
        班级 VARCHAR(255) NOT NULL,
        成绩 FLOAT(3) NOT NULL
);

#插入数据
INSERT INTO class
VALUES ('0001', '1', 86),
        ('0002', '1', 95),
        ('0003', '2', 89),
        ('0004', '1', 83),
        ('0005', '2', 86),
        ('0006', '3', 92),
        ('0007', '3', 86),
        ('0008', '1', 88);

#每个班级成绩排序
SELECT *, 
        RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
FROM class


#直接按照成绩排序不考虑班级
SELECT *,
        RANK() OVER(ORDER BY 成绩 DESC) AS ranking,
        DENSE_RANK() OVER(ORDER BY 成绩 DESC) AS dense_ranking,
        ROW_NUMBER() OVER(ORDER BY 成绩 DESC) AS row_num
FROM class;

#聚合函数
SELECT *,
   SUM(成绩) OVER (ORDER BY 学号) AS current_sum,
   AVG(成绩) OVER (ORDER BY 学号) AS current_avg,
   COUNT(成绩) OVER (ORDER BY 学号) AS current_count,
   MAX(成绩) OVER (ORDER BY 学号) AS current_max,
   MIN(成绩) OVER (ORDER BY 学号) AS current_min
FROM class;