MySQL-窗口函数

概念

窗口函数对一组查询执行类似于聚合的操作。然而，聚合操作将查询行分组为单个结果行，而窗口函数为每个查询行生成一个结果；

查询中的每个窗口操作都通过包含一个OVER子句来表示，该子句指定如何将查询行划分为组以供窗口函数处理：

• 第一个OVER子句为空，它将整个查询行集视为一个分区。窗口函数因此产生一个全局计算结果，但对每一行都这样做。
• 第二个OVER子句按对应的分区，生成每个组的计算结果。该函数为每个分组行生成次计算结果

窗口函数只允许在选择列表(类似于需要显示的字段)和ORDER BY子句中使用。查询结果行由FROM 子句确定，在WHERE、GROUP BY和HAVING处理之后，，窗口执行发生在ORDER BY、LIMIT和SELECT DISTINCT之前。

常用窗口函数

聚合窗口函数

• AVG() – 窗口内平均数
• COUNT() – 窗口内数据
• MAX() – 窗口内最大值
• MIN() – 窗口内最小值
• SUM() – 窗口内求和

当聚合函数带有OVER()子句是则该函数为聚合窗口函数

专用窗口函数

• ROW_NUMBER() ---- 行号
• RANK() · ---- 分区内的排名，有间隙（间隙指同排名会继续往后排）
• DENSE_RANK() ---- 分区内的排名，没有间隙
• PERCENT_RANK() ---- 分区内的百分比排名，没有间隙
• LAG() ---- 分区内向上平移
• LEAD() ---- 分区内向下平移
• NTILE() ---- 分区（平均的划分） 得到的当前行是第几桶
• FIRST_VALUE() ---- 窗口内的第一行
• LAST_VALUE() ---- 窗口内的最后一行
• NTH_VALUE() ---- 窗口内第几行的数据

专用窗口函数必须要有OVER()子句

分区与窗口的区别

分区为 partition by 执行过后的所以行 称之为分区 如果没有partition by 则所有行称之为分区

当我们的分区加上了 rows|range 他们指定的范围内的数据称之为窗口

语法

OVER子句

# 有两种形式定义OVER 子句
over_clause:{OVER (window_spec) | OVER window_name}

window_spec

# 以下几个部分都是可选的
window_spec:[window_name] [partition_clause] [order_clause] [frame_clause]

如果OVER() 为空，则分区由所有查询行组成，窗口函数使用所有行计算结果。否则，括号内的子句确认哪些查询行用于计算函数结果以及他们如何分区、窗口和排序：

window_name (命名窗口)

可以定义窗口并为他指定名称，以便在OVER子句中引用它们，使用WINDOW关键字定义，WINDOW子句位于 HAVING 和ORDER BY之间

WINDOW window_name as(window_spec)[,window_name as (window_spec)]...

SELECTDISTINCT year, country,# 可以继续添加 window_name 中没有的子句FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first, FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);

window_name可以互相引用但是不能循环引用

partition_clause 分区

一个PARTITION BY子句 指示如何将查询行分区。给定行的窗口函数结果基于包含该行的分区的行查询。

如果PARTITION BY忽略，则存在所有查询行组成的单个分区

PARTITION BY 语法
PARTITION BY expr [,expr1] ...

order_clause 排序

order_clause:ORDER BY expr [ASC|DESC] [,expr1 [ASC|DESC]] ...

frame_clause 范围 （指定窗口大小）

frame_clause:frame_units frame_extentframe_units:{ROWS | RANGE}frame_extent:{frame_start | frame_between}frame_between:BETWEEN frame_start AND frame_end
FOLLOWING
frame_start, frame_end: {CURRENT ROW         -- 当前行| UNBOUNDED PRECEDING -- 之前所有含当前行| UNBOUNDED FOLLOWING -- 之后所有含当前行| expr PRECEDING      -- 当前行 之前的expr行 不包含当前行| expr FOLLOWING      -- 当前行 之后的expr行 不包含当前行
}

如果没有 frame_clause 则会取决于order_clause是否存在

• 存在

  • 则当前分区的开始到当前行 包含当前行

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• 不存在

  • 则默认为当前分区的所有行

    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

使用限制

1. 不支持在UPDATE orDELETE中使用
2. 不允许嵌套窗口

练习

准备

INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (1, '可比克', '食品', 'a', 10, '2022-01-01');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (2, '乐事', '食品', 'b', 15, '2022-01-01');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (3, '布洛芬', '药品', 'a', 50, '2022-02-11');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (4, '999感冒灵', '药品', 'b', 20, '2022-03-01');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (5, '连花清瘟胶囊', '药品', 'c', 100, '2022-04-03');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (6, '扫把', '日用品', 'b', 10, '2022-05-05');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (7, '拖把', '日用品', 'b', 20, '2022-06-01');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (8, '口罩', '日用品', 'c', 50, '2022-07-01');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (9, '德芙', '食品', 'c', 120, '2022-08-05');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (10, '镜子', '日用品', 'c', 55, '2022-09-04');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (11, '卫龙', '食品', 'a', 8, '2022-10-01');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (12, '方便面', '食品', 'b', 5, '2022-11-01');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (13, '阿莫西林', '药品', 'b', 20, '2022-12-11');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (14, '青霉素', '药品', 'b', 20, '2022-03-11');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (15, '眼药水', '药品', 'c', 15, '2022-04-23');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (16, '桌子', '日用品', 'a', 150, '2022-05-15');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (17, '凳子', '日用品', 'b', 30, '2022-06-21');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (18, '笔', '日用品', 'c', 5, '2022-07-11');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (19, '热干面', '食品', 'a', 5, '2022-08-24');
INSERT INTO `test`.`pay_record` (`id`, `goods_name`, `goods_type`, `user_name`, `pay_money`, `pay_time`) VALUES (20, '水壶', '日用品', 'b', 50, '2022-09-27');

+----+--------------------+------------+-----------+-----------+------------+
| id | goods_name         | goods_type | user_name | pay_money | pay_time   |
+----+--------------------+------------+-----------+-----------+------------+
|  1 | 可比克             | 食品       | a         |        10 | 2022-01-01 |
|  2 | 乐事               | 食品       | b         |        15 | 2022-01-01 |
|  3 | 布洛芬             | 药品       | a         |        50 | 2022-02-11 |
|  4 | 999感冒灵          | 药品       | b         |        20 | 2022-03-01 |
|  5 | 连花清瘟胶囊       | 药品       | c         |       100 | 2022-04-03 |
|  6 | 扫把               | 日用品     | b         |        10 | 2022-05-05 |
|  7 | 拖把               | 日用品     | b         |        20 | 2022-06-01 |
|  8 | 口罩               | 日用品     | c         |        50 | 2022-07-01 |
|  9 | 德芙               | 食品       | c         |       120 | 2022-08-05 |
| 10 | 镜子               | 日用品     | c         |        55 | 2022-09-04 |
| 11 | 卫龙               | 食品       | a         |         8 | 2022-10-01 |
| 12 | 方便面             | 食品       | b         |         5 | 2022-11-01 |
| 13 | 阿莫西林           | 药品       | b         |        20 | 2022-12-11 |
| 14 | 青霉素             | 药品       | b         |        20 | 2022-03-11 |
| 15 | 眼药水             | 药品       | c         |        15 | 2022-04-23 |
| 16 | 桌子               | 日用品     | a         |       150 | 2022-05-15 |
| 17 | 凳子               | 日用品     | b         |        30 | 2022-06-21 |
| 18 | 笔                 | 日用品     | c         |         5 | 2022-07-11 |
| 19 | 热干面             | 食品       | a         |         5 | 2022-08-24 |
| 20 | 水壶               | 日用品     | b         |        50 | 2022-09-27 |
+----+--------------------+------------+-----------+-----------+------------+
20 rows in set (0.02 sec)

1.计算每个用户在各类商品的支付金额和所有支付金额

聚合窗口函数我们使用sum()来说明如何使用

SELECT# 利用OVER(partition by) 子句按照用户名分区 计算总金额* , SUM(sum_type_money) over(partition by user_name) as sum_money
FROM# 首先查询出每个用户在各类商品的支付金额 ( SELECT user_name, goods_type, sum( pay_money ) sum_type_money FROM pay_record GROUP BY user_name, goods_type ORDER BY user_name ) a;+-----------+------------+----------------+-----------+
| user_name | goods_type | sum_type_money | sum_money |
+-----------+------------+----------------+-----------+
| a         | 日用品     |            150 |       223 |
| a         | 药品       |             50 |       223 |
| a         | 食品       |             23 |       223 |
| b         | 日用品     |            110 |       190 |
| b         | 药品       |             60 |       190 |
| b         | 食品       |             20 |       190 |
| c         | 日用品     |            110 |       345 |
| c         | 药品       |            115 |       345 |
| c         | 食品       |            120 |       345 |
+-----------+------------+----------------+-----------+
9 rows in set (0.01 sec)

2.计算每个用户购买日用品花费的金额及排名(升序排列)

SELECTuser_name,sum( pay_money ),# 行号ROW_NUMBER() over ( group_goods_type_order_sum_type_money ) AS money_row_number,# 排名 有相同名次的情况 会跳数字RANK() over ( group_goods_type_order_sum_type_money ) AS money_rank,# 排名 有相同名次的情况 不会跳数字DENSE_RANK() over ( group_goods_type_order_sum_type_money ) AS money_dense_rank,# 排名 百分比排名 返回分区内小于当前行值的百分比  (rank - 1) / (rows - 1)PERCENT_RANK() over ( group_goods_type_order_sum_type_money ) AS money_percent_rank 
FROMpay_record 
WHEREgoods_type = '日用品' GROUP BY# WINDOW 定义 命名窗口 上面有重复使用的时候 可以直接引用user_name WINDOW group_goods_type_order_sum_type_money AS (PARTITION BY goods_type ORDER BYsum( pay_money ));+-----------+------------------+------------------+------------+------------------+--------------------+
| user_name | sum( pay_money ) | money_row_number | money_rank | money_dense_rank | money_percent_rank |
+-----------+------------------+------------------+------------+------------------+--------------------+
| b         |              110 |                1 |          1 |                1 |                  0 |
| c         |              110 |                2 |          1 |                1 |                  0 |
| a         |              150 |                3 |          3 |                2 |                  1 |
+-----------+------------------+------------------+------------+------------------+--------------------+
3 rows in set (0.01 sec)

3.查询支付金额排名 占前 30% 的购买记录

SELECT* 
FROM# 把整个分区当做一个组去分成 10 份( SELECT *, NTILE( 10 ) over ( ORDER BY pay_money DESC ) LEVEL FROM pay_record ) a 
WHERELEVEL <= 3+----+--------------------+------------+-----------+-----------+------------+-------+
| id | goods_name         | goods_type | user_name | pay_money | pay_time   | LEVEL |
+----+--------------------+------------+-----------+-----------+------------+-------+
| 16 | 桌子               | 日用品     | a         |       150 | 2022-05-15 |     1 |
|  9 | 德芙               | 食品       | c         |       120 | 2022-08-05 |     1 |
|  5 | 连花清瘟胶囊       | 药品       | c         |       100 | 2022-04-03 |     2 |
| 10 | 镜子               | 日用品     | c         |        55 | 2022-09-04 |     2 |
|  3 | 布洛芬             | 药品       | a         |        50 | 2022-02-11 |     3 |
|  8 | 口罩               | 日用品     | c         |        50 | 2022-07-01 |     3 |
+----+--------------------+------------+-----------+-----------+------------+-------+
6 rows in set (0.00 sec)

4.查询a用户 第二次后面的商品次数、第一次购买商品的时间，及最后一次购买商品的时间

SELECTpay_time,# 窗口内的第2行数据NTH_VALUE( pay_time, 2 ) over ( ORDER BY pay_time rows BETWEEN unbounded preceding AND unbounded following ) twoRow,# 窗口内的第一行数据FIRST_VALUE( pay_time ) over ( ORDER BY pay_time rows BETWEEN unbounded preceding AND unbounded following ) firstRow,# 窗口内的最后一行数据LAST_VALUE( pay_time ) over ( ORDER BY pay_time rows BETWEEN unbounded preceding AND unbounded following ) lastRow 
FROMpay_record 
WHEREuser_name = 'a'+------------+------------+------------+------------+
| pay_time   | twoRow     | firstRow   | lastRow    |
+------------+------------+------------+------------+
| 2022-01-01 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
| 2022-02-11 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
| 2022-05-15 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
| 2022-08-24 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
| 2022-10-01 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
+------------+------------+------------+------------+

# 5.查询两次购买时间间隔最长的天数用户

SELECT*,DATEDIFF( pay_time, following_pay_time ) paytime_diff,RANK() over ( ORDER BY DATEDIFF( pay_time, following_pay_time ) DESC ) paytime_diff_rank
FROM(SELECTuser_name,pay_time,# 分区内当前行的下一行数据lead( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time desc ) following_pay_time, # 分区内当前行的上一行数据lag( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time desc)  preceding_pay_time  FROMpay_record ) a limit 1+-----------+------------+--------------------+--------------------+--------------+-------------------+
| user_name | pay_time   | following_pay_time | preceding_pay_time | paytime_diff | paytime_diff_rank |
+-----------+------------+--------------------+--------------------+--------------+-------------------+
| a         | 2022-08-24 | 2022-05-15         | 2022-10-01         |          101 |                 1 |
+-----------+------------+--------------------+--------------------+--------------+-------------------+
1 row in set (0.00 sec)