SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student_score
-- ----------------------------
DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`class` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '班级',
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
`score` int(0) NOT NULL COMMENT '分数',
`create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '记录时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 23 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '窗口函数练习专用表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student_score
-- ----------------------------
INSERT INTO `student_score` VALUES (1, '1班', '小明', 90, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (2, '1班', '小红', 95, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (3, '1班', '小刚', 95, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (4, '1班', '小李', 88, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (5, '2班', '小王', 92, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (6, '2班', '小张', 92, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (7, '2班', '小赵', 85, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (8, '3班', '小陈', 100, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (9, '3班', '小林', 90, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (10, '3班', '小蔡', 90, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (11, '3班', '小魏', 80, '2026-03-26 11:20:41');
INSERT INTO `student_score` VALUES (12, '1班', '小明1', 92, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (13, '1班', '小红1', 95, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (14, '1班', '小刚1', 96, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (15, '1班', '小李1', 88, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (16, '2班', '小王11', 92, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (17, '2班', '小张1', 92, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (18, '2班', '小赵1', 86, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (19, '3班', '小陈1', 101, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (20, '3班', '小林1', 90, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (21, '3班', '小蔡1', 91, '2026-03-26 11:45:10');
INSERT INTO `student_score` VALUES (22, '3班', '小魏1', 80, '2026-03-26 11:45:10');
SET FOREIGN_KEY_CHECKS = 1;
#窗口函数
#执行顺序
FROM → WHERE → GROUP BY → HAVING → SELECT → 窗口函数 → DISTINCT → ORDER BY → LIMIT
#排序排名类
#连续行号，不重复、不跳过：1,2,3,4
select id,class,`name`,score,ROW_NUMBER() over (partition by class order by score desc) as rn from student_score;
#跳跃排名：相同值同名次，后面跳过：1,2,2,4
select id,class,`name`,score,rank() over (partition by class order by score desc) as rn from student_score;
#密集排名：相同值同名次，不跳过：1,2,2,3
select id,class,`name`,score,dense_rank() over (partition by class order by score desc) as rn from student_score;
#把数据均匀分成 n 组，返回组号：1,1,2,2,3
select id,class,`name`,score,ntile(3) over (partition by class order by score asc) as rn from student_score;
#偏移取值类、对比前一行、后一行、第一行、最后一行
#LAG (列名，偏移行，默认值) 取上一行对应字段的值 没找到返回null
select id,class,`name`,score,lag(score) over (partition by class order by score desc) as rn from student_score;
select id,class,`name`,score,lag(score,2,0) over (partition by class order by score desc) as rn from student_score;
#LEAD (列名，偏移行，默认值) 取下一行对应字段的值
select id,class,`name`,score,lead(score) over (order by score desc) as rn from student_score;
select id,class,`name`,score,lead(score,2,0) over (partition by class order by score desc) as rn from student_score;
#FIRST_VALUE (列名) 取分组内第一行的值
FIRST_VALUE(列名) OVER (
PARTITION BY 分组列 -- 可选：按某列分组
ORDER BY 排序列 -- 必选：指定排序规则
)
select id,class,`name`,score,first_value(score) over (partition by class order by score desc) as rn from student_score;
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY class
ORDER BY score DESC
) AS rn
FROM student_score
)
SELECT *
FROM ranked
WHERE rn = 1;
#LAST_VALUE (列名) 取分组内最后一行的值
LAST_VALUE(列名) OVER (
PARTITION BY 分组列
ORDER BY 排序列
[ROWS/RANGE 窗口子句]
)
select id,class,`name`,score,LAST_value(score) over (partition by class order by score desc rows between unbounded preceding and unbounded following) as rn from student_score;
WITH ranked AS (
SELECT
*,
rank() OVER (
PARTITION BY class
ORDER BY score asc
) AS rn
FROM student_score
)
SELECT *
FROM ranked
WHERE rn = 1;
#默认窗口范围
#从第一行到当前行
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
#从第一行到最后一行
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
#聚合窗口函数
#分组内累计求和 / 总和
select id,class,`name`,score,sum(score) over (partition by class) as rn from student_score;
select id,class,`name`,score,sum(score) over (partition by class ORDER BY score desc rows between unbounded preceding and unbounded following) as rn from student_score;
select id,class,`name`,score,sum(score) over () as rn from student_score;
#ORDER BY 字段有重复值时，SUM () OVER (ORDER BY ...) 会把所有相同值一次性累加，而不是逐行加
select id,class,`name`,score,sum(score) over (ORDER BY score desc) as rn from student_score;
#想要不管重不重复，一行一行加，要写成：
select id,class,`name`,score,sum(score) over (ORDER BY score desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rn from student_score;
select sum(score) from student_score;
#组内平均值
#AVG() OVER() → 总平均分
#AVG() OVER(ORDER BY) → 累计平均分
#有重复值时，默认同值同行算一批，加 ROWS 才逐行算
#规则和sum类似
select id,class,`name`,score,avg(score) over (partition by class ORDER BY score desc rows between unbounded preceding and unbounded following) as rn from student_score;
select id,class,`name`,score,avg(score) over (partition by class) as rn from student_score;
select id,class,`name`,score,avg(score) over (ORDER BY score) as rn from student_score;
select id,class,`name`,score,avg(score) over (ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rn from student_score;
select id,class,`name`,score,avg(score) over () as rn from student_score;
select avg(score) from student_score;
#组内行数
select id,class,`name`,score,count(score) over (partition by class ORDER BY score desc rows between unbounded preceding and unbounded following) as rn from student_score;
select id,class,`name`,score,count(score) over (partition by class ) as rn from student_score;
select id,class,`name`,score,count(score) over (ORDER BY score desc ) as rn from student_score;
select id,class,`name`,score,count(score) over (ORDER BY score desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rn from student_score;
select id,class,`name`,score,count(score) over () as rn from student_score;
select count(score) from student_score;
#组内最大值
select id,class,`name`,score,max(score) over (partition by class ORDER BY score asc rows between unbounded preceding and unbounded following) as rn from student_score;
select id,class,`name`,score,max(score) over (partition by class) as rn from student_score;
select id,class,`name`,score,max(score) over (ORDER BY score asc) as rn from student_score;
select id,class,`name`,score,max(score) over (ORDER BY score asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rn from student_score;
select id,class,`name`,score,max(score) over () as rn from student_score;
select max(score) from student_score;
#组内最小值
select id,class,`name`,score,min(score) over (partition by class ORDER BY score desc rows between unbounded preceding and unbounded following) as rn from student_score;
select id,class,`name`,score,min(score) over (partition by class) as rn from student_score;
select id,class,`name`,score,min(score) over () as rn from student_score;
select min(score) from student_score;
#分布 / 百分位类（统计用）计算当前行在分组内的相对排名百分比
#累计分布， <= 当前值行数 / 总行数
#假设有分数：70、90、90、100
70：1/4 = 0.25
90：3/4 = 0.75
90：3/4 = 0.75
100：4/4 = 1
select id,class,`name`,score,cume_dist() over (partition by class order by score desc) as rn from student_score;
select id,class,`name`,score,cume_dist() over (order by score desc) as rn from student_score;
SELECT * xuyaoFROM (
SELECT
id,
class,
`name`,
score,
-- 按班级分组，按分数从高到低，计算累计分布
CUME_DIST() OVER (PARTITION BY class ORDER BY score DESC) AS dist
FROM student_score
) t
-- 筛选每个班前 20%
WHERE dist <= 0.2;
#百分比排名，(当前行排名 - 1) / (分组总行数 - 1)
#假设有分数：70、90、90、100
100：(1-1)/(5-1) = 0
90：(2-1)/(5-1) = 0.25
80：(3-1)/(5-1) = 0.5
70：(4-1)/(5-1) = 0.75
70：(4-1)/(5-1) = 0.75
select id,class,`name`,score,percent_rank() over (partition by class order by score desc ) as rn from student_score;
select id,class,`name`,score,percent_rank() over (order by score desc) as rn from student_score;
#PERCENT_RANK：第一名永远是 0，
#最后一名 = 1只有在所有行的 ORDER BY 值都不重复时才成立
#一旦有并列最后一名，最后一名那行的 PERCENT_RANK < 1
#CUME_DIST：第一名是 1/n，最后一名 1
#CUME_DIST：看 “累计到多少比例”，最后必为 1
#PERCENT_RANK：看 “排名百分比”，并列最后不到 1
`