1 窗口函数1.1 什么是窗口函数窗口函数也叫分析函数用于处理相对复杂的报表统计分析场景窗口可以理解为记录集合窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数窗口大小是固定的称为静态窗口不同的记录对应着不同的窗口这种动态变化的窗口叫滑动窗口。窗口函数基本用法函数名([expr]) over子句其中over是关键字用来指定函数执行的窗口范围包含三个分析子句:分组(partitionby)子句排序(orderby)子句窗口(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 currentrow. # 当order by和窗口从句都缺失窗口规范默认是 rows between unbounded preceding and unboundedfollowing1.2 窗口函数应用一般我们可以把窗口函数分为两种:专有窗口函数:rank()dense_rank()row_number()聚合类窗口函数:普通场景下聚合函数往往和group by一起使用但是窗口环境下聚合函数也可以应用进来那么它们称为聚合类窗口函数属于窗口函数的一种sum()count()avg()max()min()窗口函数和普通场景下的聚合函数也很容易混淆二者区别如下:聚合函数是将多条记录聚合为一条多到一窗口函数是每条记录都会执行有几条记录执行完还是几条多到多。分组(partitionby):窗口按照字段进行分组窗口函数在不同的分组上分别执行。排序(order by):按照哪些字段进行排序窗口函数将按照排序后的记录顺序进行编号可以和partition子句配合使用也可以单独使用。如果没有partition子句数据范围则是整个表的数据行。窗口(rows):就是进行函数分析时要处理的数据范围属于当前分区的一个子集通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额则可以设置rows子句来创建滑动窗口(rows)。1.3 累积计算函数应用建立数据表use lagou; create table user_trade ( user_name varchar(20), piece int, price double, pay_amount double, goods_category varchar(20), pay_time date );问题1查询出19年每月的支付总额和当年累积支付总额-- step1 过滤出2019年数据 select month(pay_time), sum(pay_amount) from user_trade where year(pay_time)2019 -- step2 在1基础上按照月份进行分组统计每月的支付总额 group by month(pay_time); -- step3 在2的基础上应用窗口函数计算当年累计支付总额 select a.month, --月份 a.pay_amount, --当月支付总额 sum(a.pay_amount) over(order by a.month) --2019年的数据因此不需要分组 -- 此时没有指定rows指定窗口数据范围默认当前行及其之前的所有行 from (select month(pay_time) month, sum(pay_amount) pay_amount from user_trade where year(pay_time)2019 group by month(pay_time); ) a实例2查询出2018-2019年每月的支付总额和当年累计支付总额-- step1 过滤出2018-2019年数据 select year(pay_time), month(pay_time), sum(pay_amount) from user_trade where year(pay_time) IN(2018,2019) -- step2 在1基础上按照年份和月份进行分组统计每月的支付总额 group by year(pay_time),month(pay_time) ; -- step3 在2的基础上应用窗口函数计算当年累计支付总额 select a.year, --年份 a.month, --月份 a.pay_amount, --当月支付总额 sum(a.pay_amount) over(partition by a.year order by a.month) --基于年分组 -- 此时没有指定rows指定窗口数据范围默认当前行及其之前的所有行 from (select year(pay_time) year, month(pay_time) month, sum(pay_amount) pay_amount from user_trade where year(pay_time) IN(2018,2019) group by year(pay_time),month(pay_time) ; ) a实例3查询出2019年每个月的近三月移动平均支付金额select a.month, --月份 a.pay_amount, --当月支付总额 avg(a.pay_amount) over(order by a.month rows between 2 preceding and current row) avg_pay_amount --基于年分组 from (select month(pay_time) month, sum(pay_amount) pay_amount from user_trade where year(pay_time)2019 group by month(pay_time) ; ) a实例4查询出每四个月的最大月总支付金额-- step1 把每个月的月度支付总额算出来分组聚合sum统计 select a.month, a.pay_amount, max(a.pay_amount) over(order by a.month rows between 3 preceding and current row) max_pay_amount from (select substring(pay_time,1,7) month, sum(pay_amount) pay_amount from user_trade group by substring(pay_time,1,7) )a1.4 排序窗口函数应用实例12020年1月购买商品品类数的用户排名-- step1 先把2020年1月每个用户所购买商品涉及的品类数统计出来 select user_name, count(distinct goods_category) category_count, row_number() over(order by count(distinct goods_category)) order1 --row_number生成了行的编号从1开始 rank() over(order by count(distinct goods_category)) order2 dense_rank() over(order by count(distinct goods_category)) order3 from user_trade where substring(pay_time,1,7)2020-01 group by user_name;)a实例2查询出2020年2月的支付用户按支付金额分成5组的结果select user_name, sum(pay_amount) pay_amount, -- 按支付金额分成5组 ntile(5) over(order by sum(pay_amount) desc) level from user_trade where substring(pay_time,1,7)2020-02 group by user_name;实例3查询出2020年支付金额排名前30%的所有用户select a.user_name, a.pay_amount, a.level from (select user_name, sum(pay_amount) pay_amount, -- 按支付金额分成5组 ntile(10) over(order by sum(pay_amount) desc) level from user_trade where year(pay_time)2020 group by user_name;)a where a.level in(1,2,3);1.5 偏移函数应用实例1查询出支付时间间隔超过100天的用户数-- 同一用户相邻的订单进行下单时间比较如果相邻订单下单时间间隔超过100天那么这个用户就是你要统计的 select count(distinct user_name) from (select user_name, pay_time, lead(pay_time,1) over(partition by user_name order by pay_time) lead_time from user_trade)a where datediff(a.lead_time,a.pay_time)100;实例2查询出每年支付时间间隔最长的用户select b.years, b.user_name, b.inteerval_days from (select a.years years, a.user_name user_name, datediff(a.pay_time,a.lag_time) inteerval_days -- 计算订单时间间隔 -- 接下来在年度内按照间隔进行排名可以使用排名函数 rank() over(partition by a.years order by datediff(a.pay_time,a.lag_time) desc) rank1 from (select user_name, year(pay_time) years, pay_time, lag(pay_time) over(partition by user_name,year(pay_time) order by pay_time asc) lag_time from user_trade)a)b where b.rank11