MySQL-高级应用（三）

第一节 窗口函数

1.1 什么是窗口函数

• 窗口函数：窗口、函数（应用在窗口内的函数）——窗口类似于窗户，限定一个空间范围
• 窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。
• • 对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口；
  • 不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数的基本用法如下：

函数（[expr]）over句子

函数（） over（）——简写

 

• over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：
  • 三个分析子句：分组（partition by）子句，排序（order by）子句，窗口（rows）子句
  • 如果后面括号中什么都不写，则意味着窗口包含满足where 条件的所有行，窗口函数基于所有行进行计算
  • 如果不为空，则支持以下语法来设置窗口

函数名（[expr]） over(partition by<要分列的组> order by<要排序的列> rows between<数据范围>)

• 知识点总结
  • sum(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...)
  • avg(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...)
    • A：需要被加工的字段名称
    • B：分组的字段名称
    • C：排序的字段名称
    • D：计算的行数范围

rows between 2 preceding and current row -- 取当前行和前面两行
rows between unbounded preceding and current row -- 包括之前所有的行和本行
rows between current row and unbounded following -- 包括本行和之后所有的行
rows between 3 preceding and current row -- 包括本行和前面三行
rows between 3 preceding and 1 following -- 从前面三行和下面一行，总共五行

• • 当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row.——包括本行和之前的所有的行
  • 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following——包括之前所有的行和之后所有的行

 

1.2窗口函数应用

一般，可以把窗口函数分为两种：

• 专有窗口函数：
  • rank()
  • dense_rank()
  • row_number()
• 聚合类窗口函数：
  • 普通场景下，聚合函数往往和group by一起使用，但是窗口环境下，聚合函数也可以应
  • 用进来，那么此时它们就被称之为聚合类窗口函数，属于窗口函数的一种
    • sum()
    • count()
    • avg()
    • max()
    • min()
• 两者区别：

1.普通场景下的聚合函数是将多条记录聚合为一条（多到一）；窗口函数是每条记录都会执行，有几条记录执行完还是几条（多到多）。

2.分组(partition by)：记录按照字段进行分组，窗口函数在不同的分组上分别执行。

3.排序(order by)：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整个表的数据行。——和普通场景下的order by使用就很像

4.窗口(rows)：就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows子句来创建滑动窗口(rows)。

【小技巧】窗口函数是一对多，需要逐个计算的时候，通常会用到窗口函数，即便求和；group by 函数是多变成一，简单归一计算的时候，或者多数据需要分组的时候常会用到

 

1.2.1 累计函数

• 累积求和
  • SUM() OVER()

 

代码示例：

-- 需求1: 查询出2019年每月的支付总额和当年累积支付总额

-- STEP1 过滤出2019年数据+step2按照月份进行分组group by
SELECT MONTH
	( pay_time ) MONTH,
	round( sum( pay_amount ), 2 ) pay_amounts 
FROM
	user_trade 
WHERE
	YEAR ( pay_time )= 2019 
GROUP BY
	MONTH ( pay_time );

-- step 3 在前面的基础上使用窗口函数
SELECT
	a.mon,
	a.pay_amounts,
	sum( a.pay_amounts ) over ( ORDER BY a.mon ) 
FROM