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

妖狐艹你老母 2022-10-06 04:56 85阅读 0赞

**目录**

目标

窗口函数概念和语法

概念

概念一

概念二

概念三

语法一

语法二

常用的窗口函数

ROW\_NUMBER()

DENSE\_RANK()

RANK()

NTH\_VALUE()

FIRST\_VALUE()

LAST\_VALUE()

NTILE()

LAG()

LEAD()

--------------------

# 目标 #

*  理解窗口函数的概念和语法（本文以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);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70][]

**查询每个学生总成绩**

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

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 1][]

--------------------

### 概念二 ###

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

**查询所有学生成绩总和**

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

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 2][]

--------------------

### **概念三** ###

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

**结果集**

![20210719181446391.png][]

--------------------

### 语法一 ###

**OVER(窗口规则)**：

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

**窗口规则**

1.  窗口名称：由WINDOW查询中其他地方的子句定义的窗口的名称。
2.  分区子句：PARTITION BY子句如何将查询行分组。给定行的窗口函数结果基于包含该行的分区的行。
3.  顺序子句：MySQL 扩展是允许表达式和列名。
4.  框架子句：即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))

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 3][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 4][]

--------------------

### 语法二 ###

**OVER 窗口名称**：

1.  实际上是用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))

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 5][]

--------------------

# 常用的窗口函数 #

## 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

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 6][]

--------------------

## 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

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 7][]

--------------------

## 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

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 8][]

--------------------

## 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)

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 9][]

--------------------

## **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)

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 10][]

--------------------

## **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)

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 11][]

--------------------

## 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)

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 12][]

--------------------

## 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)

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 13][]

--------------------

## 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)

**结果集**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 14][]

--------------------

# 官方文档 #

**[窗口函数概念和语法][Link 1]**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70]: /images/20221005/8a3b2debbb374710b25a56bda44e141b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 1]: /images/20221005/e6776a75d7c54364b215170a0e40748e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 2]: /images/20221005/3d6f126eafbc4afdad85eac1bf90cbc3.png
[20210719181446391.png]: /images/20221005/6bb4944b67624e0bb54c625e9c3c3562.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 3]: /images/20221005/b4ad0860cb4e48948ff8042925f86e42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 4]: /images/20221005/e2939f40ad0b4e1891d98702b4b5b413.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 5]: /images/20221005/8dd32cc1de6c4df3a3d9da867b15db24.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 6]: /images/20221005/27ea3abea88a499f8449e3986ca7ce4a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 7]: /images/20221005/682cb23931f04e63901f8a32454b7183.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 8]: /images/20221005/da63ad5f652e41aa9847a697dd4b8c85.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 9]: /images/20221005/8953e0e494fb4b00bbc58dd8c67ae558.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 10]: /images/20221005/c1fd15c59a5b4841a38640ef52c138c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 11]: /images/20221005/bc5461dd696949778ab1ab009f8bd69f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 12]: /images/20221005/f8c81da7b86c4965ba8aa670292118db.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 13]: /images/20221005/6f977e6d4c4641a48e5f1167c81e2d91.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzA2NTcw_size_16_color_FFFFFF_t_70 14]: /images/20221005/6284163063164e80a9d2faa1e9e2e5d1.png
[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html