窗口函数
概念
窗口函数对一组查询执行类似于聚合的操作。然而,聚合操作将查询行分组为
单个结果行
,而窗口函数为每个查询行
生成一个结果;
查询中的每个窗口操作都通过包含一个
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() ----
分区
内的百分比排名, (rank - 1) / (rows - 1) - 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)]...
SELECT
DISTINCT 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_extent
frame_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
使用限制
- 不支持在
UPDATE
orDELETE
中使用 - 不允许嵌套窗口
练习
准备
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.计算每个用户购买日用品花费的金额及排名(升序排列)
SELECT
user_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
FROM
pay_record
WHERE
goods_type = '日用品'
GROUP BY# WINDOW 定义 命名窗口 上面有重复使用的时候 可以直接引用
user_name WINDOW group_goods_type_order_sum_type_money AS (
PARTITION BY goods_type
ORDER BY
sum( 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
WHERE
LEVEL <= 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用户 第二次后面的商品次数、第一次购买商品的时间,及最后一次购买商品的时间
SELECT
pay_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
FROM
pay_record
WHERE
user_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
(
SELECT
user_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
FROM
pay_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)