0%

MySQL8.0 : 窗口函数

测试数据

1
2
3
4
5
6
7
8
9
10
11
root@test 03:53:  select * from t1;
+----+-------+------+
| id | name | flag |
+----+-------+------+
| 1 | lu | 1 |
| 2 | heng | 1 |
| 3 | xing | 2 |
| 4 | li | 2 |
| 5 | wang | 3 |
| 6 | zhang | 2 |
+----+-------+------+

ROW_NUMBER

ROW_NUMBER用于返回分区范围内的行号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
root@test 03:07:  select id,name,row_number() over w as 'row_number',flag from t1 window w as (partition by flag order by name);
+----+------+------------+------+
| id | name | row_number | flag |
+----+------+------------+------+
| 2 | heng | 1 | 1 |
| 1 | lu | 2 | 1 |
| 4 | li | 1 | 2 |
| 3 | xing | 2 | 2 |
| 5 | wang | 1 | 3 |
+----+------+------------+------+

root@test 03:08: select id,name,row_number() over(partition by flag order by name) as 'row_number',flag from t1;
+----+------+------------+------+
| id | name | row_number | flag |
+----+------+------------+------+
| 2 | heng | 1 | 1 |
| 1 | lu | 2 | 1 |
| 4 | li | 1 | 2 |
| 3 | xing | 2 | 2 |
| 5 | wang | 1 | 3 |
+----+------+------------+------+

RANK

RANK用于返回分区中的排名,相同条件下的值获得相同排名,因此结果集中会带有间隙。如果不指定order by则所有数据都获得相同的排名

1
2
3
4
5
6
7
8
9
10
root@test 03:12:  select flag,rank() over w as 'rank' from t1 WINDOW w AS (ORDER BY flag);
+------+------+
| flag | rank |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 2 | 3 |
| 2 | 3 |
| 3 | 5 |
+------+------+

如果想要消除间隙,可以使用DENSE_RANK

1
2
3
4
5
6
7
8
9
10
root@test 03:12:  select flag,dense_rank() over w as 'dense_rank' from t1 WINDOW w AS (ORDER BY flag);
+------+------------+
| flag | dense_rank |
+------+------------+
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 3 | 3 |
+------+------------+

PERCENT_RANK

PERCENT_RANK用于返回分区值小于当前值的百分比(不包括最高值),范围为0~1。(rank - 1) / (rows - 1)

1
2
3
4
5
6
7
8
9
10
root@test 03:19:  select flag,PERCENT_RANK() over w as 'percent_rank' from t1 WINDOW w AS (ORDER BY flag);
+------+--------------+
| flag | percent_rank |
+------+--------------+
| 1 | 0 |
| 1 | 0 |
| 2 | 0.5 |
| 2 | 0.5 |
| 3 | 1 |
+------+--------------+

NTILE

语法

1
NTILE(N) over_clause

将一个分区划分为N个桶(buckets),为分区中的每一行分配桶号,并返回该分区中当前行的桶号。例如NTILE设置为4,则将行分为4个桶。

MySQL8.0.22中N不能为空,可以定义的数值范围为1~2的63次方,它支持下列任意格式:

  • 无符号常量
  • 一种位置标记
  • 用户定义的变量
  • 存储过程中的局部变量
1
2
3
4
5
6
7
8
9
10
root@test 03:43:  select id,ntile(2) over w as 'ntile' from t1 WINDOW w AS (ORDER BY id);
+----+-------+
| id | ntile |
+----+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+-------+

NTH_VALUE

语法

1
NTH_VALUE(expr, N) [from_first_last] [null_treatment] over_clause

NTH_VALUE用于返回窗口函数中第N行中的expr值,如果没有则返回NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
root@test 03:47:  select id,flag,nth_value(name,2) over w as 'nth_value' from t1 WINDOW w AS (partition by flag ORDER BY id);
+----+------+-----------+
| id | flag | nth_value |
+----+------+-----------+
| 1 | 1 | NULL |
| 2 | 1 | heng |
| 3 | 2 | NULL |
| 4 | 2 | li |
| 5 | 3 | NULL |
+----+------+-----------+

root@test 03:48: select id,flag,nth_value(name,3) over w as 'nth_value' from t1 WINDOW w AS (partition by flag ORDER BY id);
+----+------+-----------+
| id | flag | nth_value |
+----+------+-----------+
| 1 | 1 | NULL |
| 2 | 1 | NULL |
| 3 | 2 | NULL |
| 4 | 2 | NULL |
| 6 | 2 | zhang |
| 5 | 3 | NULL |
+----+------+-----------+

LEAD

语法

1
LAG(expr [, N[, default]]) [null_treatment] over_clause

在分区中,从当前行之前或之后的N行返回expr值,如果没有返回值为default,如果N和default都缺失则默认值分别为1和default

1
2
3
4
5
6
7
8
9
10
11
root@test 04:04:  select id,flag,lead(name,1,'default') over w as 'lead' from t1 WINDOW w AS ( ORDER BY id);
+----+------+---------+
| id | flag | lead |
+----+------+---------+
| 1 | 1 | heng |
| 2 | 1 | xing |
| 3 | 2 | li |
| 4 | 2 | wang |
| 5 | 3 | zhang |
| 6 | 2 | default |
+----+------+---------+

LAST_VALUE

语法

1
LAST_VALUE(expr) [null_treatment] over_clause

LAST_VALUE用于返回窗口最后一行的expr值

1
2
3
4
5
6
7
8
9
10
11
root@test 04:12:  select id,flag,LAST_value(name) over w as 'last_value' from t1 WINDOW w AS (ORDER BY flag);
+----+------+------------+
| id | flag | last_value |
+----+------+------------+
| 1 | 1 | heng |
| 2 | 1 | heng |
| 3 | 2 | zhang |
| 4 | 2 | zhang |
| 6 | 2 | zhang |
| 5 | 3 | wang |
+----+------+------------+

LAG

语法

1
LAG(expr [, N[, default]]) [null_treatment] over_clause

在分区中,从当前行之前或之后的N行返回expr值,如果没有返回值为default,如果N和default都缺失则默认值分别为1和default。与LEAD的差别在于方向不同

1
2
3
4
5
6
7
8
9
10
11
root@test 04:15:  select id,flag,lag(name,1,'default') over w as 'lag' from t1 WINDOW w AS ( ORDER BY id);
+----+------+---------+
| id | flag | lag |
+----+------+---------+
| 1 | 1 | default |
| 2 | 1 | lu |
| 3 | 2 | heng |
| 4 | 2 | xing |
| 5 | 3 | li |
| 6 | 2 | wang |
+----+------+---------+

CUME_DIST

返回一个值在一组值中的累积分布;即分区值小于或等于当前行值的百分比

1
2
3
4
5
6
7
8
9
10
11
root@test 04:19:  select id,flag,cume_dist() over w as 'cume_dist' from t1 WINDOW w AS ( partition by flag order BY id);
+----+------+--------------------+
| id | flag | cume_dist |
+----+------+--------------------+
| 1 | 1 | 0.5 |
| 2 | 1 | 1 |
| 3 | 2 | 0.3333333333333333 |
| 4 | 2 | 0.6666666666666666 |
| 6 | 2 | 1 |
| 5 | 3 | 1 |
+----+------+--------------------+

Tips:Window Function Descriptions