认识窗口函数

窗口函数也称为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 BYORDER BY对窗口里的数进行排序
普通语句GROUP BYORDER 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';

image-20220115171316892

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';

image-20220115171106570

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';

image-20220115171212396

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';

image-20220115190654483

从上图可以看出,

滑动窗口函数

计算函数

统计

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';

image-20220115163654218

累加

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';

image-20220115163158510

求平均

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';

image-20220115163040541

取值函数

分组第一个值

  • 函数名: 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';

image-20220115164404962

分组最后一个值

  • 函数名 : 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';

image-20220115185655967

从上图可以看出,在每一行都会<font color="red">合并当前分组内的所有数据</font>。

窗口数据集

OVER 子句 用于为行为定义一个窗口(WINDOW) ,以便于进行特定的运算,可以<font color="red">把行的窗口简单认为是运算要操作的数据集。</font>

写窗口函数时,ORDER BY 后面可以有参数,ROWS/RANGEPRECEDING/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';

image-20220115172940387

从上图可以看出,在每一行都会求出 (当前行+前两行)数据的平均值。

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';

image-20220115184645351

从上图可以看出,在每一行都会求出 (当前行+当前行后两行) 数据的平均值。

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';

image-20220115173804699

从上图可以看出,在每一行都会求出 (当前行前一行+当前行+当前行后一行) 数据的平均值。

注意事项

  1. 原则上窗口函数只能写在 SELECT 子句中
  2. 窗口函数名的括号不能漏掉
  3. PARTITION BY 可以省略,但是结果就不再分组。
  4. NTILE 不支持 ROWS BETWEEN。

应用真题解析

题目1:找出每个部门工资第二高的员工

现有一张公司员工信息表 <u>EMPLOYEE</u> ,表中包含如下 4 个字段。

字段名字段类型字段备注
employee_idVARCHAR员工ID
employee_nameVARCHAR员工姓名
employee_salaryNUMBER员工薪资
departmentVARCHAR员工部门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');
Last modification:March 25th, 2022 at 04:00 pm
如果觉得我的文章对你有用,请随意赞赏