窗口函数是针对查询的每一行，使用对应改行相关的行进行计算。大多数聚合函数也可以用作窗口函数。
窗口函数
窗口函数的语法
函数() over()
over()字句是关键字，用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算。
函数() over(partition by 列1,列2 order by 列3) as 列别名
partition by子句也可以被称为查询分区字句，非常类似于group by，都是将数据按照边界值分组，而over()之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算
窗口函数的执行时机
1. from子句
2. on子句
3. where子句
4. group by子句
5. having子句
6. select子句（注意：此处得到的是结果集）
7. 窗口函数（注意：窗口函数时处理的select结果集）
8. distinct去重子句
9. order by子句
10 limit子句
窗口函数分区：partition by函数
partition by字句会将查询的结果集分成几组。窗口函数将作用于各个分区。
mysql> select row_number() over(partition by user_no order by amount) as row_num,user_no,amount from order_tab;
+---------+---------+--------+
| row_num | user_no | amount |
+---------+---------+--------+
| 1 | 001 | 100 |
| 2 | 001 | 300 |
| 3 | 001 | 500 |
| 4 | 001 | 800 |
| 5 | 001 | 900 |
| 1 | 002 | 300 |
| 2 | 002 | 500 |
| 3 | 002 | 600 |
| 4 | 002 | 800 |
| 5 | 002 | 800 |
+---------+---------+--------+
10 rows in set (0.01 sec)
上述是在结果集中对user_no进行分区，窗口函数会单独的作用到每个分区数据，partition by的执行时机是select子句后，distinct去重关键字前。
窗口函数排序：order by函数
order by子句暗示如何对每个分区中的行进行排序。根据order by子句相等的分区行被视为对等行。如果order by省略，则分区是无序的，没有暗含的处理顺序，并且所有分区行都是对等的（注意：窗口函数中有无排序的影响很大）。
按照功能划分
按照功能划分，可以将MySQL支持的窗口函数分为如下几类：
| 功能 | 函数 |
|---|---|
| 序号函数 | row_number()/rank()/dense_rank() |
| 分布函数 | percent_rank() / cume_dist() |
| 前后函数 | lag() / lead() |
| 头尾函数 | first_val() / last_val() |
| 其他函数 | nth_value() / nfile() |
数据准备
CREATE TABLE `order_tab` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`user_no` varchar(100) DEFAULT NULL,
`amount` double DEFAULT NULL,
`create_data` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001;
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (1, '001', 100, '2020-05-19 21:53:12');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (2, '001', 300, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (3, '001', 500, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (4, '001', 800, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (5, '001', 900, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (6, '002', 500, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (7, '002', 600, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (8, '002', 300, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (9, '002', 800, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (10, '002', 800, '2020-05-19 21:54:14');
2.1 序号函数
序号函数有三个：
| 名称 | 描述 |
|---|---|
| row_number() | 分区中当前行的数量 |
| rank() | 当前行在其分区内的排名，有间隔 |
| dense()_rank() | 当前行在其分区内的排名，无间隔 |
如下图所示：
mysql> select rank() over w rank_num,
row_number() over w row_num,
dense_rank() over w dense_rank_num,
user_no,
amount
from order_tab
window w as (order by amount);
+----------+---------+----------------+---------+--------+
| rank_num | row_num | dense_rank_num | user_no | amount |
+----------+---------+----------------+---------+--------+
| 1 | 1 | 1 | 001 | 100 |
| 2 | 2 | 2 | 001 | 300 |
| 2 | 3 | 2 | 002 | 300 |
| 4 | 4 | 3 | 001 | 500 |
| 4 | 5 | 3 | 002 | 500 |
| 6 | 6 | 4 | 002 | 600 |
| 7 | 7 | 5 | 001 | 800 |
| 7 | 8 | 5 | 002 | 800 |
| 7 | 9 | 5 | 002 | 800 |
| 10 | 10 | 6 | 001 | 900 |
+----------+---------+----------------+---------+--------+
10 rows in set (0.01 sec)
- rank()/dense_rank()函数应用于order by将分区行按所需顺序排序，如果没有order by，所有行都是对等体。
mysql> select rank() over() rank_num,dense_rank() over() dense_rank_num,amount from order_tab;
+----------+----------------+--------+
| rank_num | dense_rank_num | amount |
+----------+----------------+--------+
| 1 | 1 | 100 |
| 1 | 1 | 300 |
| 1 | 1 | 500 |
| 1 | 1 | 800 |
| 1 | 1 | 900 |
| 1 | 1 | 500 |
| 1 | 1 | 600 |
| 1 | 1 | 300 |
| 1 | 1 | 800 |
| 1 | 1 | 800 |
+----------+----------------+--------+
10 rows in set (0.01 sec)
-
RANK()函数返回当前行在其分区内的间隔（带间隔）。若排序得到字段值相等，那么
RANK()将获取相同的等级。并结果是不连续的等级编号。（eg：成绩排名，2个学生分数相同的是并列第2名，那么下一个是第4名。） -
DENSE_RANK()返回当前行在其分区内的位置，没有空格。若排序得到字段值相等，那么
DENSE_RANK()将获取相同的等级。但结果是连续的等级编号。 -
ROW_NUMBER()返回当前分区内当前行的编号。行数范围从1到分区行数。但是ROW_NUMBER()为同级分配不同的行号。要为同级分配相同的值，请使用
RANK()或DENSE_RANK()。
2.2 分布函数
cume_dist()函数
此函数应用于order by将分区按所需顺序排序。如果没有order by，则所有行都是对等端，并且值N/N=1，其中N是分区大小。
该函数表示：窗口分区当前行之前或对等的行数/窗口分区的总行数。返回的范围是0到1（即某分区中小于等于某字段的行数与总行数的比例）。
应用场景：大于等于当前订单金额的订单比例。
mysql> select rank() over w rank_num,
row_number() over w row_num,
cume_dist() over w cust_dist_rate,
amount
from order_tab
window w as (order by amount);
+----------+---------+----------------+--------+
| rank_num | row_num | cust_dist_rate | amount |
+----------+---------+----------------+--------+
| 1 | 1 | 0.1 | 100 |
| 2 | 2 | 0.3 | 300 |
| 2 | 3 | 0.3 | 300 |
| 4 | 4 | 0.5 | 500 |
| 4 | 5 | 0.5 | 500 |
| 6 | 6 | 0.6 | 600 |
| 7 | 7 | 0.9 | 800 |
| 7 | 8 | 0.9 | 800 |
| 7 | 9 | 0.9 | 800 |
| 10 | 10 | 1 | 900 |
+----------+---------+----------------+--------+
10 rows in set (0.01 sec)
percent_rank()
公式：(rankl-1)/(rows-1)
其中：rank是RANK()函数产生的序号，rows为当前窗口记录的总行数。
2.3 头尾函数
得到分区中的第一个/最后一个指定参数的值。
mysql> select first_value(amount) over(),amount,order_id,user_no from order_tab;
+----------------------------+--------+----------+---------+
| first_value(amount) over() | amount | order_id | user_no |
+----------------------------+--------+----------+---------+
| 100 | 100 | 1 | 001 |
| 100 | 300 | 2 | 001 |
| 100 | 500 | 3 | 001 |
| 100 | 800 | 4 | 001 |
| 100 | 900 | 5 | 001 |
| 100 | 500 | 6 | 002 |
| 100 | 600 | 7 | 002 |
| 100 | 300 | 8 | 002 |
| 100 | 800 | 9 | 002 |
| 100 | 800 | 10 | 002 |
+----------------------------+--------+----------+---------+
10 rows in set (0.01 sec)
因为没有使用order by函数，所以所有行都是对等端。返回该分区中的第一列的值。
mysql> select last_value(amount) over(partition by user_no order by amount),amount,order_id,user_no from order_tab;
+---------------------------------------------------------------+--------+----------+---------+
| last_value(amount) over(partition by user_no order by amount) | amount | order_id | user_no |
+---------------------------------------------------------------+--------+----------+---------+
| 100 | 100 | 1 | 001 |
| 300 | 300 | 2 | 001 |
| 500 | 500 | 3 | 001 |
| 800 | 800 | 4 | 001 |
| 900 | 900 | 5 | 001 |
| 300 | 300 | 8 | 002 |
| 500 | 500 | 6 | 002 |
| 600 | 600 | 7 | 002 |
| 800 | 800 | 9 | 002 |
| 800 | 800 | 10 | 002 |
+---------------------------------------------------------------+--------+----------+---------+
因为使用了order by分组函数，此时分区行记录不对等。
- 第一条记录是获取分区（此时分区只有一条记录）最后一条记录，即100。
- 第二条记录是获取分区（此时分区中存在2条记录）最后一条记录，即300。
- 第二条记录是获取分区（此时分区中存在3条记录）最后一条记录，即500。
依次类推...
2.4 聚合函数用于窗口函数
许多聚合函数都可以使用over()函数，因此取决于over()子句是否存在，它们可以用于窗口或者非窗口函数。
AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()
例如：因为over()函数中没有使用order by函数，所以各个行均是对等的。返回的是分区中的最大值。
mysql> select max(amount) over(),user_no,amount from order_tab;
+--------------------+---------+--------+
| max(amount) over() | user_no | amount |
+--------------------+---------+--------+
| 900 | 001 | 100 |
| 900 | 001 | 300 |
| 900 | 001 | 500 |
| 900 | 001 | 800 |
| 900 | 001 | 900 |
| 900 | 002 | 500 |
| 900 | 002 | 600 |
| 900 | 002 | 300 |
| 900 | 002 | 800 |
| 900 | 002 | 800 |
+--------------------+---------+--------+
10 rows in set (0.01 sec)
网友评论