认识窗口函数
窗口函数也称为OLAP(Online Anallytical Processing)函数,意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。
flowchart LR
a(窗口函数) --> b[窗口函数名] --> b1>静态窗口函数]
b -->b2>滑动窗口函数]
a --> c[窗口函数数据集]--- d[over]
b1>静态窗口函数]--- sort[排名函数] --- row_number
sort--- dense_rank
sort--- rank
sort --- ntile
d---part[partitiono by]
d --- order[order by]
d---rows[rows/range]
d --- preceding[preceding]---b2
d --- following[following]---b2
flowchart LR
b2>滑动窗口函数]
b2-->cal["计算函数:
MIN()、MAX()、SUM()、COUNT()、AVG()、
STDDEV_POP()、STDDEV_SAMP()、MEDIAN()"]
b2-->get["取值函数:
LISTAGG()、FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()"]
什么叫窗口?
窗口可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数,对于每条记录都要在此窗口内执行函数。
- 静态窗口:不同的记录对应相同大小的窗口,这种属于静态窗口。
- 滑动窗口:不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。
窗口函数的简单语法
窗口函数可以在保留原表中的全部数据的情况下,对某些字段做分组排序或者计算,而GROUP BY只能保留与分组字段聚合的结果。
<窗口函数名> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名>)
类型 | 分组 | 排序 | 作用 |
---|---|---|---|
窗口函数 | PARTITION BY | ORDER BY | 对窗口里的数进行排序 |
普通语句 | GROUP BY | ORDER BY | 对查询结果集整体进行排序 |
窗口函数的执行顺序
graph LR
A[FROM]-->B[WHERE]-->C[GROUP BY]-->D[HAVING]-->E[SELECT]-->F[WINDOW]-->G[ORDER BY]-->H[LIMIT]
静态窗口函数(排名函数)
ROW_NUMBER()
当前分组内<font color="red">连续不重复</font>排序
SELECT
GRID_ID,
STAFF_AGE,
ROW_NUMBER() OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) ROW_NUM
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
RANK()
当前分组内<font color="red">不连续重复</font>排序
SELECT
GRID_ID,
STAFF_AGE,
RANK() OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) RANK_NUM
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
DENSE_RANK()
当前分组内<font color="red">连续重复</font>排序
SELECT
GRID_ID,
STAFF_AGE,
DENSE_RANK() OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) DENSE_RANK_NUM
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
NTILE()
- 函数名: NTILE()
- 语法:
NTILE(n)
- 作用:用于<font color="red">将分组数据按照顺序切分成n片,返回当前切片值。</font>将一个有序的数据集划分为多个桶(bucket),并为每行分配一个适当的桶数(切片值,第几个切片,第几个分区等概念)。它可用于将数据划分为相等的小切片,为每一行分配该小切片的数字序号。
SELECT
PROV_ID,
GRID_ID,
STAFF_AGE,
NTILE(4) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) NTILE_NUM
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
从上图可以看出,
滑动窗口函数
计算函数
统计
SELECT
GRID_ID,
STAFF_AGE,
COUNT(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) AVG_AGE
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
累加
SELECT
GRID_ID,
STAFF_AGE,
SUM(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) AVG_AGE
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
求平均
SELECT
GRID_ID,
STAFF_AGE,
AVG(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) AVG_AGE
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
取值函数
分组第一个值
- 函数名: FIRST_VALUE()
SELECT
GRID_ID,
STAFF_AGE,
FIRST_VALUE(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) FIRST_VAL
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
分组最后一个值
- 函数名 : LAST_VALUE()
SELECT
GRID_ID,
STAFF_AGE,
LAST_VALUE(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) LAST_VAL
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
分组第N个值
- 函数名: NTH_VALUE()
SELECT
GRID_ID,
STAFF_AGE,
NTH_VALUE(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE) NTH_VAL
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
合并多行数据
- 函数名: LISTAGG()
- 语法:
LISTAGG(measure_expr[, 'delimiter']) WITHIN GROUP (order_by_clause) OVER query_partition_clause
- 返回类型:
VARCHAR
SELECT
GRID_ID,
STAFF_AGE,
LISTAGG(STAFF_NAME,',') WITHIN GROUP(ORDER BY STAFF_CODE) OVER(PARTITION BY GRID_ID ) LIST_AGG_NAME
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
从上图可以看出,在每一行都会<font color="red">合并当前分组内的所有数据</font>。
窗口数据集
OVER 子句 用于为行为定义一个窗口(WINDOW) ,以便于进行特定的运算,可以<font color="red">把行的窗口简单认为是运算要操作的数据集。</font>
写窗口函数时,ORDER BY 后面可以有参数,ROWS
/RANGE
和 PRECEDING
/FOLLOWING
,在组合使用这些参数后,窗口就会变成滑动窗口,因为涉及到动态窗口,所以在理解上比较抽象。
PRECEDING
PRECEDING 的意思是之前 N 行,理解为不含当前行的情况下,截至到之前 N行 ,这种方法叫做 <font color="red">移动平均</font> 。
SELECT
GRID_ID,
STAFF_AGE,
AVG(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE ROWS 2 PRECEDING) ROW_NUM
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
从上图可以看出,在每一行都会求出 (当前行+前两行)数据的平均值。
FOLLOWING
SELECT
GRID_ID,
STAFF_AGE,
AVG(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE
ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING ) PRE_FOLLOW_AVG
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
从上图可以看出,在每一行都会求出 (当前行+当前行后两行) 数据的平均值。
PRECEDING 和 FOLLOWING 连用
SELECT
GRID_ID,
STAFF_AGE,
AVG(STAFF_AGE) OVER(PARTITION BY GRID_ID ORDER BY STAFF_AGE
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) PRE_FOLLOW_AVG
FROM
ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
WHERE MONTH_ID='202111' AND PROV_ID='076';
从上图可以看出,在每一行都会求出 (当前行前一行+当前行+当前行后一行) 数据的平均值。
注意事项
- 原则上窗口函数只能写在 SELECT 子句中
- 窗口函数名的括号不能漏掉
- PARTITION BY 可以省略,但是结果就不再分组。
- NTILE 不支持 ROWS BETWEEN。
应用真题解析
题目1:找出每个部门工资第二高的员工
现有一张公司员工信息表 <u>EMPLOYEE</u> ,表中包含如下 4 个字段。
字段名 | 字段类型 | 字段备注 |
---|---|---|
employee_id | VARCHAR | 员工ID |
employee_name | VARCHAR | 员工姓名 |
employee_salary | NUMBER | 员工薪资 |
department | VARCHAR | 员工部门ID |
EMPLOYEE表的<u>数据如下表</u>所示:
还有一张部门信息表department,表中包含如下两个字段。
department_id(部门ID):VARCHAR。
department_name(部门名称):VARCHAR。
department表的数据如下表所示。
数据导入格式
DROP TABLE IF EXISTS employee;
CREATE TABLE employee(
mployee_id VARCHAR(8),
employee_name VARCHAR(8),
employee_salary INT(8),
department VARCHAR(8)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
employee (employee_id,employee_name,employee_salary,department)
VALUE ('a001','Bob',7000,'b1')
,('a002','Jack',9000,'b1')
,('a003','Alice',8000,'b2')
,('a004','Ben',5000,'b2')
,('a005','Candy',4000,'b2')
,('a006','Allen',5000,'b2')
,('a007','Linda',10000,'b3');
DROP TABLE IF EXISTS department;
CREATE TABLE department(
department_id VARCHAR(8),
department_name VARCHAR(8)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
department (department_id,department_name)
VALUE ('b1','Sales')
,('b2','IT')
,('b3','Product');