MySQL窗口函数大纵深作战理论指导

我的身前一尺是我的世界

于 2021-07-21 15:41:15 发布

阅读量327

点赞数 1

分类专栏： MySQL 文章标签：数据库 mysql 窗口函数

本文链接：https://blog.csdn.net/qq_39706570/article/details/117809821

版权

MySQL 专栏收录该内容

21 篇文章 3 订阅

订阅专栏

目标

理解窗口函数的概念和语法（本文以MySQL8.0版本为例）；
掌握常见的窗口函数的使用场景，并能做出相关优化，这里主要讲解非聚合函数的用法。

窗口函数概念和语法

概念

概念一

窗口操作不会将查询行组折叠为单个输出行。相反，它们为每一行生成一个结果。以查询每个学生总成绩为演示案例：

建立学生成绩表

CREATE TABLE `course_score` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键',
  `courseId` INT DEFAULT NULL COMMENT '课程主键',
  `studentId` INT DEFAULT NULL COMMENT '学生主键',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  `score` FLOAT(4,1) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生成绩表'

插入相关数据

INSERT INTO `course_score` (`id`, `courseId`, `studentId`, `create_time`, `score`)
VALUES (1, 100001, 200001, '2021-06-10 11:22:41', 99.5),
	(2, 100002, 200001, '2021-06-04 11:23:02', 115.5),
	(3, 100003, 200001, '2021-06-10 11:23:23', 150.0),
	(4, 100001, 200002, '2021-06-01 11:23:41', 85.0),
	(5, 100002, 200002, '2021-06-10 11:23:51', 140.0),
	(6, 100003, 200002, '2021-06-10 11:24:01', 130.0),
	(7, 100001, 200003, '2021-06-10 11:24:19', 120.0),
	(8, 100002, 200003, '2021-06-11 11:24:37', 120.0),
	(9, 100003, 200003, '2021-06-05 11:24:44', 150.0);

查询每个学生总成绩

SELECT courseId, studentId, SUM(score) OVER (PARTITION BY studentId ) AS score_group
FROM `course_score`
ORDER BY studentId

结果集

概念二

OVER子句可以为空，如果为空，则表示将所有查询行作为一个分区，以查询所有学生成绩总和为演示案例：

查询所有学生成绩总和

SELECT courseId, studentId, SUM(score) OVER () AS score_group
FROM `course_score`
ORDER BY courseId

结果集

概念三

OVER子句计算之前，先执行WHERE和HAVING（如果有），以条件查询部分学生部分课程的成绩总和为演示案例：

条件查询部分学生部分课程的成绩总和

SELECT DISTINCT courseId, studentId, SUM(score) OVER () AS score_group
FROM `course_score`
WHERE courseId IN (100001, 100002)
HAVING studentId IN (200001, 200002)
ORDER BY courseId

结果集

语法一

OVER(窗口规则)：

窗口规则可以为空，参考上述概念二部分；
窗口规则有几个可选项，包含：[窗口名称][分区子句][顺序子句][框架子句]

窗口规则

窗口名称：由WINDOW查询中其他地方的子句定义的窗口的名称。
分区子句：PARTITION BY子句如何将查询行分组。给定行的窗口函数结果基于包含该行的分区的行。
顺序子句：MySQL 扩展是允许表达式和列名。
框架子句：即ORDER BY排序。默认为ASC未指定方向。NULL升序排序首先排序，降序排序最后排序。

以天为分区，查询成绩总和

/*第一种*/
SELECT courseId, studentId, create_time , 
SUM(score) OVER (PARTITION BY LEFT(create_time, 10) ) AS score_group
FROM `course_score`
/*第二种*/
SELECT courseId, studentId, create_time , 
SUM(score) OVER (w) AS score_group
FROM `course_score`
WINDOW w AS (PARTITION BY LEFT(create_time, 10))

以课程排序，计算成绩总和

SELECT courseId, studentId, create_time ,
SUM(score) OVER (ORDER BY courseId) AS score_group
FROM `course_score`

语法二

OVER 窗口名称：

实际上是用WINDOW关键词定义窗口规则，方便利用别名调用窗口规则。

以天为分区，查询成绩总和

SELECT courseId, studentId, create_time , 
SUM(score) OVER w AS score_group
FROM `course_score`
WINDOW w AS (PARTITION BY LEFT(create_time, 10))

常用的窗口函数

ROW_NUMBER()

建立表

CREATE TABLE `train_course_rate` (
  `rateId` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  `courseId` BIGINT NOT NULL COMMENT '课程ID',
  `value` TINYINT NOT NULL DEFAULT '0' COMMENT '评分(0-100)',
  `employeeId` BIGINT NOT NULL COMMENT '评分人',
  PRIMARY KEY (`rateId`),
  KEY `idx_train_course_rate_courseId` (`courseId`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb3 COMMENT='课程评分'

插入相关数据

INSERT INTO train_course_rate (courseId, employeeId, rateId, VALUE)
VALUES (10001, 20001, NULL, 100),
	(10001, 20002, NULL, 100),
	(10001, 20003, NULL, 98),
	(10001, 20004, NULL, 90),
	(10002, 20001, NULL, 100),
	(10002, 20002, NULL, 98),
	(10002, 20003, NULL, 90),
	(10002, 20004, NULL, 90),
	(10003, 20001, NULL, 100),
	(10003, 20002, NULL, 98),
	(10003, 20003, NULL, 90),
	(10003, 20004, NULL, 92),
	(10004, 20001, NULL, 100),
	(10004, 20002, NULL, 100),
	(10004, 20003, NULL, 90),
	(10004, 20004, NULL, 90),
	(10005, 20001, NULL, 100),
	(10005, 20002, NULL, 100),
	(10005, 20003, NULL, 90),
	(10005, 20004, NULL, 90),
	(10006, 20001, NULL, 100),
	(10006, 20002, NULL, 99),
	(10006, 20003, NULL, 96),
	(10006, 20004, NULL, 95);

需求：分数相同不并列，格式如：1-2-3。

SELECT *
FROM (
	SELECT rateId, courseId, ROW_NUMBER() OVER (PARTITION BY courseId ORDER BY `value` DESC) AS score_order, `value`
	FROM train_course_rate
) t
WHERE score_order <= 3

结果集

DENSE_RANK()

需求：分数相同并列，格式如：1-1-1-2-2-3-3；

SELECT *
FROM (
	SELECT rateId, courseId, DENSE_RANK() OVER (PARTITION BY courseId ORDER BY `value` DESC) AS score_order, `value`
	FROM train_course_rate
) t
WHERE score_order <= 3

结果集

RANK()

需求：分数相同并列，且排名延后，格式如：1-1-1-4-5。

SELECT *
FROM (
	SELECT rateId, courseId, RANK() OVER (PARTITION BY courseId ORDER BY `value` DESC) AS score_order, `value`
	FROM train_course_rate
) t
WHERE score_order <= 3

结果集

NTH_VALUE()

需求：在窗口内查询每个学生分数为第1和第2的数据。

SELECT studentId, courseId, `score`
	, NTH_VALUE(`score`, 1) OVER w AS one_score
	, NTH_VALUE(`score`, 2) OVER w AS two_score
FROM `course_score`
WINDOW w AS (PARTITION BY studentId ORDER BY `score` DESC)

结果集

FIRST_VALUE()

需求：在窗口内查询每个学生分数为第1的数据。

SELECT studentId, courseId, `score`, FIRST_VALUE(`score`) OVER w AS first_score
FROM `course_score`
WINDOW w AS (PARTITION BY studentId ORDER BY `score` DESC)

结果集

LAST_VALUE()

需求：在窗口内查询每个学生分数为最低分数的数据。

SELECT studentId, courseId, `score`, LAST_VALUE(`score`) OVER w AS last_score
FROM `course_score`
WINDOW w AS (PARTITION BY studentId ORDER BY `score` DESC)

结果集

NTILE()

需求：按照课程ID，将评分按照从大到小排名到出前三名。

SELECT courseId, `value`, NTILE(3) OVER w AS `level`
FROM train_course_rate
WINDOW w AS (PARTITION BY courseId ORDER BY `value` DESC)

结果集

LAG()

需求：查询窗口内上一个分数。

SELECT courseId, `value`, LAG(`value`) OVER w AS `lag_value`
FROM train_course_rate
WINDOW w AS (PARTITION BY courseId ORDER BY `value` DESC)

结果集

LEAD()

需求：查询窗口内下一个分数。

SELECT courseId, `value`, LEAD(`value`) OVER w AS `lead_value`
FROM train_course_rate
WINDOW w AS (PARTITION BY courseId ORDER BY `value` DESC)

结果集

官方文档

窗口函数概念和语法

我的身前一尺是我的世界

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录