MySQL--窗口函数实例详细介绍

引言

• 在之前MySQL–常见的窗口函数介绍以及使用语法一文中，对窗口函数做了简单的介绍，此文对常用的窗口函数做详细介绍。

常用的窗口函数回顾

功能	名称	是否可携带参数	描述
序号函数	DENSE_RANK	否	根据ORDER BY子句为其分区中的每一行分配一个排名。 它为具有相同值的行分配相同的排名。 如果两行或更多行具有相同的等级，则排序值序列中将没有间隙。
	RANK	否	与DENSE_RANK（）函数类似，只是当两行或更多行具有相同的排名时，排序值序列中存在间隙。
	ROW_NUMBER	否	为其分区中的每一行分配一个连续整数。
分布函数	CUME_DIST	否	计算一组值中值的累积分布。
	PERCENT_RANK	否	计算分区或结果集中行的百分位数
前后函数	LAG	lag(expr,N)	返回分区中当前行之前的第N行的值。 如果不存在前一行，则返回NULL。
	LEAD	lead(expr,N)	返回分区中当前行之后的第N行的值。 如果不存在后续行，则返回NULL。
头尾函数	FIRST_VALUE	frst_value(expr)	返回指定表达式相对于窗口框架中第一行的值。
	LAST_VALUE	last_value(expr)	返回指定表达式相对于窗口框架中最后一行的值。
其他函数	NTH_VALUE	nth_value(expr,N)	返回窗口框架第N行的参数值。
	NTILE	ntile(N)	将每个窗口分区的行分配到指定数量的已排名组中。

准备数据库

依然是之前的数据库：

• 表名 ：trade 交易表
• 字段：
  • worker_id: 员工id
  • client_id:客户id
  • trade_type：交易类型
  • total：交易总量

序号函数

• 主要为rank()，dense_rank(),row_number()三个函数；
• 都是为分组的数据进行排序，给出对应的序号，但给出的序号有所不同：

rank()

• 计算每行数据在分区中的序号，如果前面有序号重复的，后续的序号会往后跳跃。

dense_rank()

• 计算每行数据在分区中的序号，即使前面有序号重复的，后续的序号仍然往后跳跃。

row_number()

• 根据over()子句的排序字段，为分区的每个数据分配一个不会重复的排列序号。

数据库实例

select worker_id,client_id,trade_type,total,
	RANK() over (PARTITION by trade_type order by total asc) 'rank-total排序',
	ROW_NUMBER() over (PARTITION by trade_type order by total asc) 'row_number-total排序',
	DENSE_RANK() over (PARTITION by trade_type order by total asc) 'dense_rank-total排序'
from trade;

• 采用不同的序号函数，得到的序号结果也不同，在实际情况中，可以根据需要选择适合的序号函数。

分布函数

• 主要为cume_dist()和percent_rank()两个函数；
• 计算当前行在分区总行数之间的比例，但具体也有差异：

cume_dist()

• 返回一组值中值的累积分布。它表示值小于或等于当前行的值的行数除以总行数的行数，返回值大于零且小于或等于1。

percent_rank()

• 用于计算分区或结果集中行的百分位数。该窗口函数常用来比较分区里行占所有行的百分比，从而判断当前行与其他行相比的优劣，因此常用order by条件一起使用。计算方式为(rank - 1) / (total_rows - 1) 。

数据库实例

select worker_id,client_id,trade_type,total,
	CUME_DIST() over (PARTITION by trade_type order by total asc) 'cume_dist-total',
	rank() over (PARTITION by trade_type order by total asc) 'rank',
	PERCENT_RANK() over (PARTITION by trade_type order by total asc) 'percent-rank-total'
from trade;

• 此处cume_dist函数，以trade_type为分区，total排序。以trade_type值为A的分区为例，第一行和第二行的total值为2，一共有两行的值小于或等于当前行的值，分区总行数为4，所以row_number_total的值为2/4 = 0.5；第三行的total值为3，一共有三行的total值小于或等于3，所以第三行的row_number_total的值为 3/4 = 0.75；以此类推。
• 此处percent_rank函数，以trade_type为分区，total排序。以trade_type值为A的分区为例，第一行和第二行的rank等于1，分区总行数为4，所以percent_number函数计算出来的值为 （1-1）/（4-1）= 0;
  第三行的rank值为3，所以percent_number函数计算出来的值为 （3-1）/（4-1）= 0.666666。又此表可以分析出trade_type为A的分区中，交易量(total)为2是最差的，交易量(total)为3已经超过了交易量总数的三分之一了；而交易量(total)为10是最好的，比其他任何交易都高。

前后函数

lag（expr,N）

• 返回分区中当前行之前的第N行的值，N为整数。 如果不存在前一行，则返回NULL。

lead（expr,N）

• 返回分区中当前行之后的第N行的值，N为整数。 如果不存在后续行，则返回NULL。

数据库实例

select worker_id,client_id,trade_type,total,
	lag(total,1) over (PARTITION by trade_type order by total asc) 'lag-total',
	LEAD(total,1) over (PARTITION by trade_type order by total asc) 'lead-total'
from trade;

• 此处以trade_type为分区，total排序，lag函数输出当前行前一行(N=1)的total值，若不存在值，显示null。lead函数输出当前行后一行(N=1)的total值，若不存在值，显示null。

头尾函数

first_value（expr）

• 返回指定表达式相对于窗口框架中第一行的值。

last_value（expr）

• 返回指定表达式相对于窗口框架中最后一行的值。

数据库实例

select worker_id,client_id,trade_type,total,
	FIRST_VALUE(total) over ( order by total  RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING) 'first-total',
	LAST_VALUE(total) over (  ORDER BY total  RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING) 'last-total'
from trade;

• 此处first_value函数以total值排序，以整个分区为计算区间，每一行都得到当前分区第一行的值，即为2；同理，last_value函数以total值排序，以整个分区为计算区间，每一行都得到当前分区最后一行的值，即为10.

其他两个函数

nth_value（expr，N）

• 返回窗口框架中第N行的参数值。当N为1时，可类似为first_value函数。

ntile（N）

• 将每个窗口分区的行分配到指定数量的已排名组中。函数将排序分区中的行划分为特定数量（N）的组。从每个组分配一个从一开始的桶号。对于每一行，NTILE()函数返回一个桶号，表示行所属的组。值得注意的是，每一组的记录数不能大于它上一组的记录数，也就是编号小的桶放的记录数不能小于编号更大的桶。

数据库实例

select worker_id,client_id,trade_type,total,
	nth_value(total,3) over (PARTITION by trade_type order by total asc) 'nth2total',
	nth_value(total,1) over (PARTITION by trade_type order by total asc) 'nthtotal',
	FIRST_VALUE(total) over (PARTITION by trade_type order by total asc) 'first-total',
	ntile(2) over (PARTITION by trade_type order by total asc) 'ntile-total'
from trade;

• 此处的nth_value(total,3)函数，以trade_type为分区，total为交易量，函数携带的参数为total和N=3，表示显示排序第三行的total值，由于计算到第一二行时还不知道第三行的值，所以显示不出第三行的值，所以显示结果为null，第三行往后都知道了第三行的total值为3，所以都显示3；nth_value(total,1),函数携带的参数为total和N=1，表示显示第一行的total值，所以均显示2。
• 此处的ntile(2),表示将分区中的行划分为两组，于是对每一行进行分配所存放的桶号。刚好四条数据平均每组两条数据，ntile_total用以区分分配到的桶号。

结尾

• 至此，对于常见的窗口函数已经有了一个更为细致的了解了，接下来就需要在实战中合理的使用了。