一、前言
二、概述
窗口函数指一些需要借助OVER
关键字使用的特殊函数,它们用来实现对查询后数据的实时分组处理。这里的窗口,指的是函数生效的的范围。
窗口函数原则上只能在 SELECT 子句中使用
窗口函数可以分为三种:序号(排名)函数、聚合函数和前后头尾函数。列出部分参考:
序号函数 | 聚合函数 | 前后头尾函数 |
---|---|---|
row_number() |
max() |
lag() |
rank() |
min() |
lead() |
dense_rank() |
sum() |
first_value() |
count() |
last_vague() |
|
avg() |
over
关键字:用来创建指定规则的窗口,格式为:
SELECT
字段1,
字段2,
窗口函数() OVER (
[PARTITION BY 分组字段]
[ORDER BY 排序字段 [ASC|DESC]]
[ROWS BETWEEN 起始位置 AND 结束位置]
) AS 别名
FROM 表名;
PARTITION BY
:分组窗口的方式
ORDER BY
:窗口排序规则
rows
:函数计算范围,可用于移动平均
PARTITION BY
与GROUP BY
的区别:
GROUP BY
对要查询的数据进行分组,并仅为每个组返回**一行(聚合后)**的数据PARTITION BY
只能在OVER
子句中使用,用来对查询结果分组,每组数据视为一个独立的窗口。即使使用聚合函数,也会保留窗口中的每一行数据。
三、窗口命名
可以使用WINDOW
关键字,将相同的窗口规则命名为rule
,格式为WINDOW 名称 AS (规则1 规则2 规则3)
。
窗口别名需要定义在窗口函数执行之前,即select
执行之前。
SELECT category_id,sku_price,
ROW_NUMBER() OVER rule AS 排名,
RANK() OVER rule AS 名次
FROM commodity
WINDOW rule AS (PARTITION BY category_id ORDER BY sku_price);
四、窗口函数
4.1 序号函数
对于序号函数,如ROW_NUMBER()、RANK()等,OVER子句中的ORDER BY主要用于在窗口内对数据进行排序 |
ROW_NUMBER()
:能够从1开始为窗口内的每一行数据生成一个序号,序号连续且不重复
// 使用商品类别分组数据,每组数据按照价格从低到高排序,并为其添加序号
SELECT category_id,sku_name,sku_price,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sku_price) AS 排名
FROM commodity;
RANK()
与DENSE_RANK()
允许并列排序,区别在于DENSE_RANK()
并列排序后序号仍连续,而RANK()
序号会出现间断
4.2 聚合函数
当窗口函数为聚合函数,如MAX()、AVG()、COUNT()等时,OVER中的ORDER BY不仅能为窗口内的数据排序;还能限定窗口内函数每次生效的范围——从第一行开始到当前行结束 。 |
在仅有PARTITION BY
的窗口中使用聚合函数,函数会对每个组的所有数据进行聚合运算,所以同一组下每行计算的聚合结果相同。
在既有PARTITION BY
又有ORDER BY
的窗口中使用聚合函数,函数会按照顺序对第一行到当前行的所有数据进行聚合计算。所以同一组下每行计算的聚合结果不同,是累计的关系。
4.3 前后函数
LAG()
:用于获取前一行的数据。格式为LAG(字段名, 查阅的行数, 默认值)
。
查阅的行数:默认为1,如果前一行没有数据,
LAG
会记录为NULL
默认值:默认为
NULL
// 获取排在当前员工前2位的员工薪资,若没有符合该员工的数据,就标记结果为'未找到'
SELECT department,name,salary,
LAG(salary,2,'未找到') OVER (PARTITION BY department ORDER BY salary) AS 上一个人的薪水
FROM employees;
LEAD()
:与LAG
函数效果相反,用来获取下一行的的内容,用法与LAG
函数相同
4.4 头尾函数
FIRST_VALUE()
:获取每个窗口中的第一行数据
// 从订单表中查询出了账户id、支付金额、第一次消费金额
SELECT account_id, total_price,
FIRST_VALUE(total_price) OVER (PARTITION BY account_id ORDER BY order_time) AS 第一次消费金额
FROM orders
WHERE status = true;
LAST_VALUE()
:用来获取窗口计算范围内的最后一行数据
- 如果窗口没有使用
ORDER BY
排序,返回的是当前窗口的最后一行数据;- 如果窗口使用
ORDER BY
排序,返回的是当前行的内容。
错误示例:窗口使用ORDER BY
排序,返回的是当前行的内容。
// 找出 最后一个 上巴士且不超过重量限制的乘客
select last_value(person_name) over (order by turn) as person_name
from (
select sum(weight) over(order by turn) as total_weight,person_name,turn
from queue
) as p
where total_weight<=1000
| person_name |
| ----------- |
| Alice |
| Alex |
| John Cena |
五、移动平均
5.1 rows
移动平均是一种用于平滑时间序列数据的统计方法,这里的时间序列是指按照时间顺序排列的数据集合。它能够减少极大、极小等异常值对数据造成的误差,以此平滑平均数曲线。
窗口滑动范围:
当前行 | 之前的行 | 之后的行 | 无界限 | 表示从前面的起点 | 表示到后面的终点 |
---|---|---|---|---|---|
current row | preceding | following | unbounded | unbounded preceding | unbounded following |
常见的范围定义:
// 从第一行到当前行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
// 当前行前3行到后一行
ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING
// 当前行到最后一行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
使用实例:
// 计算近3个月的移动平均
SELECT
month,
sales,
AVG(sales) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM monthly_sales;
5.2 range
range是窗口函数中用于定义逻辑范围的子句,根据字段的实际值来确定窗口的边界。
它可以用来处理缺失值,比如计算近7天的累计值。range格式为:
窗口函数() OVER (
ORDER BY 排序字段
RANGE BETWEEN 下限 AND 上限
)
常见范围定义:
// 当前时间的前7天到当前时间
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
// 当前值 -100 到当前值 +200
RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING
// 从最小值到当前行值
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
使用示例:
// 计算近7天的累计销售额
SELECT
date,
amount,
SUM(amount) OVER (
ORDER BY date
RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
) AS rolling_7d_sum
FROM daily_sales;
range 与 rows: