MySQL8窗口函数应用
怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？累计求和如何计算？
对于这样的需求，使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点，需要在单表中满足某些条件的结果集内部做一些函数操作，不是简单的表连接，也不是简单的聚合可以实现的，通常费了大半天时间写出来一堆长长的晦涩难懂的SQL，且性能低下，难以维护。要解决此类问题，最方便的就是使用窗口函数。
1.窗口函数初识
MySQL从8.0开始支持窗口函数，这个功能在大多数据库中早已支持，有的也叫分析函数。
窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。简单的说窗口函数就是对于查询的每一行，都使用与该行相关的行进行计算。
窗口函数也叫OLAP函数(Online Analytical Processing,联机分析处理)，可以对数据进行实时分析处理。窗口函数和group by有类似之处，其区别在于窗口会对每个分组之后的数据进行分别操作，而group by一般对分组之后的数据使用聚合函数汇总。
2.窗口函数和普通聚合函数的区别
聚合函数是将多条记录聚合为一条。
窗口函数是每条记录都会执行，有几条记录执行完还是几条。
聚合函数也可以用于窗口函数。
3.常见的窗口函数
注：‘参数’列说明该函数是否可以加参数。“否”说明该函数的括号内不可以加参数。expr即可以代表字段，也可以代表在字段上的计算，比如sum(col)等。以下相同。
将上述函数按照功能划分，可以把MySQL支持的窗口函数分为如下几类：
序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
分布函数：PERCENT_RANK()、CUME_DIST()
前后函数：LAG()、LEAD()
头尾函数：FIRST_VALUE()、LAST_VALUE()
其它函数：NTH_VALUE()、NTILE()
4.窗口函数的使用
窗口函数的语法：
window_function_name(expression) over([partition_definition] [order_definition] [frame_definition])
即：窗口函数名称([字段名称]) over([partition by 分组字段] [order by 排序字段 [desc]] [窗口分区])
窗口函数的一个概念是当前行属于某个窗口，窗口由over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：
1、partition by子句：按照指定字段进行分区，两个分区由边界分隔，窗口函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
2、order by子句：按照指定字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
3、frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用：
rows | range between start_expr and end_expr
其中rows和range为二选其一：
rows是物理范围，即根据order by子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）；
range是逻辑范围，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内
between…and…用来指定范围的起始点和终结点，start_expr为起始点，end_expr为终结点
start_expr为起始点，起始点有下面几种选项：
unbounded preceding：指明窗口开始于分组的第一行，以排序之后的第一行为起点；
current row：以当前行为起点；
n preceding：以当前行的前面第n行为起点；
n following：以当前行的后面第n行为起点；
end_expr为终结点，终结点有下面几种选项：
unbounded following：以排序之后的最后一行为终点；
current row：以当前行为终点；
n preceding：以当前行的前面第n行为终点；
n following：以当前行的后面第n行为终点；
5.示例代码
创建tb_customer_shopping表和tb_score表：
CREATE TABLE tb_customer_shopping(
order_id INT COMMENT '订单id',
username VARCHAR(20) COMMENT '顾客姓名',
create_date date COMMENT '购买日期',
cost INT COMMENT '购买金额'
);
#插入数据
INSERT INTO tb_customer_shopping
VALUES
( 1, 'Jack', '2017-01-01', 10 ),
( 2, 'Tony', '2017-01-02', 15 ),
( 3, 'Jack', '2017-02-03', 23 ),
( 4, 'Tony', '2017-01-04', 29 ),
( 5, 'Jack', '2017-01-05', 46 ),
( 6, 'Jack', '2017-04-06', 42 ),
( 7, 'Tony', '2017-01-07', 50 ),
( 8, 'Jack', '2017-01-08', 55 ),
( 9, 'King', '2017-04-08', 62 ),
( 10, 'King', '2017-04-09', 68 ),
( 11, 'Paul', '2017-05-10', 12 ),
( 12, 'King', '2017-04-11', 75 ),
( 13, 'Paul', '2017-06-12', 80 ),
( 14, 'King', '2017-04-13', 94 );
CREATE TABLE tb_score(
stu_id int,
name VARCHAR(20),
subject VARCHAR(10),
score INT
);
#插入数据
INSERT INTO tb_score
VALUES
( 1, '孙悟空', '语文', 87 ),
( 1, '孙悟空', '数学', 100 ),
( 1, '孙悟空', '英语', 68 ),
( 2, '唐僧', '语文', 94 ),
( 2, '唐僧', '数学', 56 ),
( 2, '唐僧', '英语', 84 ),
( 3, '沙僧', '语文', 87 ),
( 3, '沙僧', '数学', 97 ),
( 3, '沙僧', '英语', 84 ),
( 4, '八戒', '语文', 65 ),
( 4, '八戒', '数学', 85 ),
( 4, '八戒', '英语', 78 ),
( 5, '蜘蛛侠', '语文', 55 ),
( 5, '蜘蛛侠', '数学', 97 ),
( 5, '蜘蛛侠', '英语', 98 ),
( 6, '美国队长', '语文', 56 ),
( 6, '美国队长', '数学', 99 ),
( 6, '美国队长', '英语', 87 ),
( 7, '钢铁侠', '语文', 94 ),
( 7, '钢铁侠', '数学', 100 ),
( 7, '钢铁侠', '英语', 85 );
1)序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
用途：显示分区中的当前行号，对查询结果进行排序。
ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2
应用场景：常用
执行如下SQL语句，代码运行结果如图所示:
SELECT
stu_id,
NAME,
SUBJECT,
score,
ROW_NUMBER() OVER ( PARTITION BY SUBJECT ORDER BY score DESC ) AS row_number_,
DENSE_RANK() OVER ( PARTITION BY SUBJECT ORDER BY score DESC ) AS dense_rank_,
RANK() OVER ( PARTITION BY SUBJECT ORDER BY score DESC ) AS rank_
FROM
tb_score ts;
2)分布函数：PERCENT_RANK()、CUME_DIST()
PERCENT_RANK()
用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
应用场景：不常用
给窗口指定别名：WINDOW win AS (PARTITION BY stu_id ORDER BY score)
总行数rows = 3
执行如下SQL语句，代码运行结果如图所示：
SELECT
stu_id,
NAME,
score,
RANK() OVER win AS rank_,
PERCENT_RANK() OVER win AS precent_rank_
FROM
tb_score
WHERE
stu_id = 1 WINDOW win AS ( PARTITION BY stu_id ORDER BY score );
CUME_DIST()
用途：分组内小于、等于当前rank值的行数 / 分组内总行数
应用场景：查询小于等于当前成绩(score)的比例
cm1：没有分区，则所有数据均为一组，总行数为6
cm2：按照name分成了两组，行数各为3
注意：CUME_DIST、PERCENT_RANK均不支持WINDOW子句S(between…and…)
执行如下SQL语句，代码运行结果如图所示：
SELECT
stu_id,
NAME,
score,
CUME_DIST() OVER ( ORDER BY score ) AS cm1,
CUME_DIST() OVER ( PARTITION BY NAME ORDER BY score ) AS cm2
FROM
tb_score
WHERE
NAME IN ( '孙悟空', '唐僧' );
3)前后函数：LAG()、LEAD()
LAG和LEAD分析函数可以在同一次查询中取出同一字段的前N行的数据(LAG)和后N行的数据(LEAD)作为独立的列
在实际应用当中，若要用到取今天和昨天的某字段差值时，LAG和LEAD函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG和LEAD与LEFT JOIN、RIGHT JOIN等自连接相比，效率更高，SQL更简洁。下面我就对这两个函数做一个简单的介绍。
函数语法如下：
lag(exp_str,offset,defval) OVER(PARTITION BY …ORDER BY …)
lead(exp_str,offset,defval) OVER(PARTITION BY …ORDER BY …)
参数说明：
exp_str是字段名
offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第10行，则offset 为3，则表示我们所要找的数据行就是表中的第7行(即10-3=7)。
defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，LAG()函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。
执行如下SQL语句，代码运行结果如图所示：
SELECT
order_id,
username,
create_date,
cost,
LAG( cost, 1, 0 ) OVER ( PARTITION BY username ORDER BY create_date ) cost_lag,
LEAD( cost, 1, 0 ) OVER ( PARTITION BY username ORDER BY create_date ) cost_lead
FROM
tb_customer_shopping;
改变偏移offset：
SELECT
order_id,
username,
create_date,
cost,
LAG( cost, 2, 0 ) OVER ( PARTITION BY username ORDER BY create_date ) cost_lag,
LEAD( cost, 2, 0 ) OVER ( PARTITION BY username ORDER BY create_date ) cost_lead
FROM
tb_customer_shopping;
4)头尾函数：FIRST_VALUE(expr)、LAST_VALUE(expr)
用途：返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr))expr的值
应用场景：截止到当前的cost，按照日期排序查询第1个和最后1个cost值
执行如下SQL语句，代码运行结果如图所示：
SELECT
order_id,
username,
create_date,
cost,
FIRST_VALUE( cost ) OVER ( PARTITION BY username ORDER BY create_date DESC ) first_cost,
LAST_VALUE( cost ) OVER ( PARTITION BY username ORDER BY create_date DESC ) last_cost
FROM
tb_customer_shopping;
FIRST_VALUE()的结果容易理解，直接在结果的所有行记录中输出同一个满足条件的首个记录；
LAST_VALUE()默认统计范围是 rows between unbounded preceding and current row，也就是取当前行数据与当前行之前的数据的比较。
那么如果我们直接在每行数据中显示最后的那个数据，需在order by 条件的后面加上语句：rows between unbounded preceding and unbounded following ， 也就是前面无界和后面无界之间的行比较。
执行如下SQL语句，代码运行结果如图所示：
SELECT
order_id,
username,
create_date,
cost,
FIRST_VALUE( cost ) OVER ( PARTITION BY username ORDER BY create_date DESC ) first_cost,
LAST_VALUE( cost ) OVER ( PARTITION BY username ORDER BY create_date DESC rows BETWEEN unbounded preceding AND unbounded following ) last_cost
FROM
tb_customer_shopping;
5)其它函数：NTH_VALUE(expr, n)、NTILE(n)
NTH_VALUE(expr, n)
其中NTH_VALUE(expr, n)中的第二个参数是指这个函数取排名第几的记录，返回窗口中第n个expr的值。expr可以是表达式，也可以是列名。
执行如下SQL语句，代码运行结果如图所示：
SELECT
order_id,
username,
create_date,
cost,
NTH_VALUE( cost, 3 ) OVER ( ORDER BY username ASC ) nth_cost
FROM
tb_customer_shopping;
SELECT
order_id,
username,
create_date,
cost,
NTH_VALUE( cost, 3 ) OVER ( PARTITION BY username ORDER BY create_date ASC ) nth_cost
FROM
tb_customer_shopping;
SELECT
order_id,
username,
create_date,
cost,
NTH_VALUE( cost, 2 ) OVER ( PARTITION BY username ORDER BY create_date ASC rows BETWEEN unbounded preceding AND unbounded following ) nth_cost
FROM
tb_customer_shopping;
NTILE(n)函数
用途：将分区中的有序数据分为n个等级，记录等级数
应用场景：将每个顾客按cost分成4组
NTILE(n)函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到n个并行的进程分别计算，此时就可以用NTILE(n)对数据进行分组(由于记录数不一定被n整除，所以数据不一定完全平均)，然后将不同桶号的数据再分配。
执行如下SQL语句，代码运行结果如图所示：
SELECT
order_id,
username,
cost,
NTILE( 4 ) OVER w AS nf
FROM
tb_customer_shopping
WHERE
username IN ( 'Jack', 'King' ) WINDOW w AS ( PARTITION BY username ORDER BY cost );
6)聚合函数作为开窗函数
在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count)，可以动态计算在指定的窗口内的各种聚合函数值。
示例：每个用户截止到当前的累计购买金额/平均购买金额/最大购买金额/最小购买金额/购买数量。
执行如下SQL语句，代码运行结果如图所示：
SELECT
*,
SUM( cost ) OVER ( PARTITION BY username ORDER BY create_date ) sum_cost,
AVG( cost ) OVER ( PARTITION BY username ORDER BY create_date ) avg_cost,
MAX( cost ) OVER ( PARTITION BY username ORDER BY create_date ) max_cost,
MIN( cost ) OVER ( PARTITION BY username ORDER BY create_date ) min_cost,
COUNT( cost ) OVER ( PARTITION BY username ORDER BY create_date ) count_cost
FROM
tb_customer_shopping;
扫一扫
1225
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
