mysql8窗口函数（1）—语法

窗口函数是针对查询的每一行，使用对应改行相关的行进行计算。大多数聚合函数也可以用作窗口函数。

窗口函数

窗口函数的语法

函数() 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)

3. 实战使用

1. 获取每种类型消耗金额的top1000数据。

row_number() over(partition by type order by amount desc)解释：

  1. row_number()序号函数
  2. partition by窗口函数（多个窗口）；

注意：窗口函数处理的数据源是select字句的数据。不受当前sql中where和group by语句的影响。

partition by字句会将查询的结果集分成几组。窗口函数将作用于各个分区。

mysql>  SELECT
    row_num,
    user_no,
    amount 
FROM  -- 查询top1000的数据必须要含有子查询
    ( SELECT row_number ( ) over ( PARTITION BY user_no ORDER BY amount DESC ) AS row_num, user_no, amount FROM order_tab ) a 
WHERE
    row_num < 1000;


+---------+---------+--------+
| row_num | user_no | amount |
+---------+---------+--------+
|       1 | 001     |    900 |
|       2 | 001     |    800 |
|       3 | 001     |    500 |
|       4 | 001     |    300 |
|       5 | 001     |    100 |
|       1 | 002     |    800 |
|       2 | 002     |    800 |
|       3 | 002     |    600 |
|       4 | 002     |    500 |
|       5 | 002     |    300 |
+---------+---------+--------+
10 rows in set (0.01 sec)

推荐阅读

https://www.sohu.com/a/260324504_411876

mysql官网合并

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 199,271评论 5赞 466
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 83,725评论 2赞 376
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 146,252评论 0赞 328
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,634评论 1赞 270
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,549评论 5赞 359
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 47,985评论 1赞 275
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,471评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,128评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,257评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,233评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,235评论 1赞 328
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,940评论 3赞 316
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,528评论 3赞 302
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,623评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,858评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,245评论 2赞 344
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,790评论 2赞 339