MySQL8.0版本如何正确的使用窗口函数详解_Mysql

前言

mysql数据库从8.0开始支持窗口函数了，它是一种强大的数据分析工具，旨在帮助你快速获得场景数据。在正式介绍这类函数前，博主还是解释一下为什么这么称呼这类函数为“窗口”函数。窗口——一个数据记录的集合，也就是你的数据操作范围只限于这个数据集，再无其他。它与group by类似，但是最大的区别是窗口函数会为每个查询行生成一个结果（add column）。

恭喜你，有这个认知后，博主可以正式介绍它了，请紧随博主，以防迷路。

窗口函数必学必会

既然窗口函数是服务于数据分析的，那么先来看看它长什么样，有句话说得好：“没吃过猪肉，还没见过猪跑么”。当我们get一个新知时，也要怀着同样的预期和先行一步的姿态去对待它。那咱们先看看它的语法结构吧。

1. 基本语法

1.1 匿名窗口

select 
	<窗口函数名> over (partition by <分组列名> order by <排序列名>)
from `你的表名`

1.2 显式窗口

select 
	<窗口函数名> over win
from `你的表名` 
window win as (partition by <分组列名> order by <排序列名>)

其中，窗口函数名必须指定，partition by（可选），order by（可选）。

2. 包括哪些

窗口函数主要包含两大类：常见的聚合函数（count、sum、avg等）和专用的窗口函数（比如排序等）。

2.1 聚合函数

大多数的聚合函数皆可用作窗口函数，通常与group by子句使用，将统计值分组到子集中。

聚合函数	用途说明
avg()	返回平均值
bit_and()	按位 and 运算，代表逻辑与
bit_or()	按位 or 运算，代表逻辑或
bit_xor()	按位 xor 运算，代表逻辑异或
count()	返回行数
count(distinct)	返回去重后的行数
group_concat()	分组后，返回一个自动连接的字符串
json_arrayagg()	返回一个json数组
json_objectagg()	返回一个json对象
max()	返回最大值
min()	返回最小值
std()	返回整体标准偏差
stddev()	返回整体标准偏差
stddev_pop()	返回整体标准偏差
stddev_samp()	返回样本标准偏差
sum()	返回总和
var_pop()	返回整体标准方差
var_samp()	返回样本方差
variance()	返回整体标准方差

提示：除非另有说明，否则聚合函数会忽略null值。

如果在不包含group by子句的sql中使用聚合函数，则相当于对所有行进行分组。对于数值参数，方差和标准偏差函数返回一个double值。sum（）和avg（）函数如果为精确值参数（整数或decimal）返回decimal值，如果为近似值参数（float或double）返回double值。

如使一个聚合函数转换为一个窗口函数执行，需按如下格式执行（over子句）：

# 添加over子句
sum([distinct] expr) [over_clause]

示例1-普通聚合：这是一个普通聚合函数写法（来自官网）：

select 
     country, 
     sum(profit) as country_profit
from sales
group by country
order by country;

示例2-窗口函数：这是一个转为窗口函数写法（来自官网）：

select
     year, country, product, profit,
     sum(profit) over() as total_profit,
     sum(profit) over(partition by country) as country_profit
from sales
order by country, year, product, profit;

是不是很简单？

2.2 专用窗口函数

我们已知窗口函数是对一个记录集执行类似聚合的操作。然而，虽然聚合操作将查询行分组为单个结果行，但窗口函数会为每个查询行生成一个结果。

窗口函数	用途说明
row_number()	为结果集中的每行记录分配唯一的连续整数序号
rank()	为结果集中的每行记录分配一个排名
dense_rank()	为结果集中的每行分配一个排名，但不会跳过相同的排名
percent_rank()	用于计算某行在结果集中的相对排名比，其值介于0-1间，表示相对位置
cume_dist()	用于计算某行在结果集中的累积分布值，其值介于0-1间，表示累计分布比例
lag(expr,n)	返回当前行的前 n 行的expr值
lead(expr,n)	返回当前行的后 n 行的expr的值
first_value(expr)	返回第一个expr的值
last_value(expr)	返回最后一个expr的值
ntile()	返回当前行在其分区内的桶数
nth_value()	返回窗口内第n行的参数值

over_clause表示over子句。

某些窗口函数允许使用null_treation子句，该子句指定在计算结果时如何处理null值，本选项款为可选。它是sql标准的一部分，但mysql实现只允许respect null（这也是默认值）。这意味着在计算结果时会考虑null值。

博主这里提供5个示例（来自官网）。请注意sql中的over子句。

示例1：

select
     val,
     row_number()   over w as 'row_number',
     cume_dist()    over w as 'cume_dist',
     percent_rank() over w as 'percent_rank'
from numbers
window w as (order by val);

示例2：

 select
      time, subject, val,
      first_value(val)  over w as 'first',
      last_value(val)   over w as 'last',
      nth_value(val, 2) over w as 'second',
      nth_value(val, 4) over w as 'fourth'
from observations
window w as (partition by subject order by time rows unbounded preceding);

示例3：

select
    t, val,
    lag(val)        over w as 'lag',
    lead(val)       over w as 'lead',
    val - lag(val)  over w as 'lag diff',
    val - lead(val) over w as 'lead diff'
from series
window w as (order by t);

示例4：

select
     val,
     row_number() over w as 'row_number',
     ntile(2)     over w as 'ntile2',
     ntile(4)     over w as 'ntile4'
from numbers
window w as (order by val);

示例5：

select
     val,
     row_number() over w as 'row_number',
     rank()       over w as 'rank',
     dense_rank() over w as 'dense_rank'
from numbers
window w as (order by val);