重庆设计网站,网站建设上如何提高市场竞争力,肇庆seo,主机怎么做网站二次跳转1 什么是窗口函数 MySQL从8.0开始支持窗口函数#xff0c;有的也叫分析函数#xff08;处理相对复杂的报表统计分析场景#xff09;#xff0c;这个功能在大多商业数据库和部分开源数据库中早已支持。
窗口函数#xff1a;窗口、函数#xff08;应用在窗口内的函数…1 什么是窗口函数 MySQL从8.0开始支持窗口函数有的也叫分析函数处理相对复杂的报表统计分析场景这个功能在大多商业数据库和部分开源数据库中早已支持。
窗口函数窗口、函数应用在窗口内的函数-----窗口类似于窗户限定一个空间范围
那什么叫窗口呢?
窗口的概念非常重要它可以理解为记录集合窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数窗口大小都是固定的这种属于静态窗口不同的记录对应着不同的窗口这种动态变化的窗口叫滑动窗口。
窗口函数的基本用法如下
函数名[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 2 窗口函数应用
一般我们可以把窗口函数分为两种
专有窗口函数
rank()dense_rank()row_number()
聚合类窗口函数
普通场景下聚合函数往往和group by一起使用但是窗口环境下聚合函数也可以应 用进来那么此时它们就被称之为聚合类窗口函数属于窗口函数的一种
sum()count()avg()max()min()
窗口函数专有窗口函数聚合类窗口函数和普通场景下的聚合函数也很容易混淆二者区别如下
普通场景下的聚合函数是将多条记录聚合为一条多到一窗口函数是每条记录都会执行有几条记录执行完还是几条多到多。分组(partition by)记录按照字段进行分组窗口函数在不同的分组上分别执行。排序(order by)按照哪些字段进行排序窗口函数将按照排序后的记录顺序进行编号可以和partition子句配合使用也可以单独使用。如果没有partition子句数据范围则是整 个表的数据行。窗口(rows)就是进行函数分析时要处理的数据范围属于当前分区的一个子集通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额则可以设置rows子句来创建滑动窗口(rows)。
现有20182020某电商平台订单信息表user_trade
表结构如下:
列名释义user_name用户名piece购买数量price价格pay_amount支付金额goods_category商品品类pay_time支付日期
# 建立数据表
use legou;
create table user_trade
(user_name varchar(20),piece int,price double,pay_amount double,goods_category varchar(20),pay_time date
);
查看前10行数据 ------累计计算函数应用、排序函数应用、偏移分析函数应用
2.1 累计计算函数
累计求和sum() over()
需求1: 查询出2019年每月的支付总额和当年累积支付总额
/*
需求1: 查询出2019年每月的支付总额和当年累积支付总额
*/-- step1 过滤出2019年数据
select * from user_trade where year(pay_time)2019;-- step2 在1的基础上按照月份进行group by 分组统计每个月份的支付总额
SELECT MONTH( pay_time ),sum( pay_amount )
FROMuser_trade
WHEREYEAR ( pay_time ) 2019
GROUP BYMONTH ( pay_time );-- step3 在2的基础上应用窗口函数实现需求
SELECTa.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_amountFROMuser_tradeWHEREYEAR ( pay_time ) 2019GROUP BYMONTH ( pay_time )) a
结果如下 需求2查询出2018-2019年每月的支付总额和当年累积支付总额
SELECT a.year,
a.month,
a.pay_amount,
sum(a.pay_amount) over(partition by a.year order by a.month)
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;
结果如下 移动平均avg() over()
需求3: 查询出2019年每个月的近三月移动平均支付金额
SELECT a.month,a.pay_amount,avg(a.pay_amount) over(order by a.month rows between 2 preceding
and current row)
FROM(SELECT month(pay_time) month,sum(pay_amount) pay_amountFROM user_tradeWHERE year(pay_time)2019GROUP BY month(pay_time))a;
结果如下 最大/小值max()/min() over()
需求4: 查询出每四个月的最大月总支付金额
SELECT a.month,a.pay_amount,max(a.pay_amount) over(order by a.month rows between 3 preceding
and current row)
FROM(SELECT substr(pay_time,1,7) as month,sum(pay_amount) as pay_amountFROM user_tradeGROUP BY substr(pay_time,1,7))a;
结果如下 2.2 排序函数
row_number() over(......)rank() over(......)dense_rank() over(......)
需求5: 2020年1月购买商品品类数的用户排名
/*
需求5: 2020年1月购买商品品类数的用户排名
2020年1月基础数据范围
一个商品属于某一个品类
A用户购买了100件商品那么可能涉及到了10个品类
B用户购买了50件商品那么可能涉及到了15个品类
根据所购买商品涉及的品类数量进行排名给用户
思路
1先把各个用户所购买商品涉及的品类数给统计出来
2) 在1的基础上排名使count()用到排名窗口函数
*/-- 1先把各个用户所购买商品涉及的品类数给统计出来
SELECTuser_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
FROMuser_trade
WHEREsubstring( pay_time, 1, 7 ) 2020-01
GROUP BYuser_name;
结果如下 知识点总结
这三个函数的作用都是返回相应规则的排序序号
row_number() over(partition by ...A... order by ...B... )
rank() over(partition by ...A... order by ...B... )
dense_rank() over(partition by ...A... order by ...B... )
A:分组的字段名称
B:排序的字段名称 注意:
row_number()、rank() 和dense_rank()紧邻的括号内是不加任何字段名称的。row_number:它会为查询出来的每一行记录生成一个序号依次排序且不会重复。rankdense_rank:如果使用rank函数来生成序号over子句中排序字段值相同的序号是一样 的后面字段值不相同的序号将跳过相同的排名号排下一个也就是相关行之前的排名数加一。dense_rank函数在生成序号时是连续的而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时将不跳过相同排名号rank值紧接上一次的rank值。在各个分组内rank()是跳跃排序有两个第一名时接下来就是第三名dense_rank()是连续排 序有两个第一名时仍然跟着第二名。 ntile(n) over(......)
需求6: 查询出将2020年2月的支付用户按照支付金额分成5组后的结果
SELECT user_name,sum(pay_amount) pay_amount,ntile(5) over(order by sum(pay_amount) desc) level
FROM user_trade
WHERE substr(pay_time,1,7)2020-02
GROUP BY user_name;
结果如下 需求7: 查询出2020年支付金额排名前30%的所有用户
SELECT a.user_name,a.pay_amount,a.level
FROM(SELECT user_name,sum(pay_amount) pay_amount,ntile(10) over(order by sum(pay_amount) desc) levelFROM user_tradeWHERE year(pay_time)2020GROUP BY user_name)a
WHERE a.level in (1,2,3);
结果如下 知识点总结
ntile(n) over(partition by ...A... order by ...B... )
n:切分的片数
A:分组的字段名称
B:排序的字段名称
ntile(n)用于将分组数据按照顺序切分成n片返回当前切片值 NTILE不支持ROWS BETWEEN。 2.3 偏移分析函数
lag(...) over(......)lead(...) over(......)
需求8: 查询出King和West的时间偏移(前N行)
SELECT user_name,pay_time,lag(pay_time,1,pay_time) over(partition by user_name order by pay_time) lag1,-- 没有传入偏移量那么默认就是1找不到的话此处也没有给默认值为nulllag(pay_time) over(partition by user_name order by pay_time) lag1_s,lag(pay_time,2,pay_time) over(partition by user_name order by pay_time) lag2,lag(pay_time,2) over(partition by user_name order by pay_time) lag2_s
FROM user_trade
WHERE user_name in (King,West);
结果如下 需求9: King和West的时间偏移(后N行)
SELECT user_name,pay_time,lead(pay_time,1,pay_time) over(partition by user_name order by pay_time) lead1,lead(pay_time) over(partition by user_name order by pay_time) lead2,lead(pay_time,2,pay_time) over(partition by user_name order by pay_time) lead3,lead(pay_time,2) over(partition by user_name order by pay_time) lead4
FROM user_trade
WHERE user_name in (King,West);
结果如下 知识点总结
Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead) 作为独立的列。
在实际应用当中若要用到取今天和昨天的某字段差值时Lag和Lead函数的应用就显得尤为重要。 lag(exp_str,offset,defval) over(partion by ......order by ......)
lead(exp_str,offset,defval) over(partion by ......order by ......)
exp_str是字段名称。 offset是偏移量即是上1个或上N个的值假设当前行在表中排在第5行则offset 为3则表示我们所要找的数据行就是表中的第2行(即5-32)。offset默认值为1。defval默认值当两个函数取上N/下N个值当在表中从当前行位置向前数N行已经超出了表的范围时lag()函数将defval这个参数值作为函数的返回值若没有指定默认值则返回NULL那么在数学运算中总要给一个默认值才不会出错。
补充练习 需求10: 查询出支付时间间隔超过100天的用户数
SELECT count(distinct user_name)
FROM(SELECT user_name,pay_time,lead(pay_time) over(partition by user_name order by pay_time) lead_dtFROM user_trade)a
WHERE a.lead_dt is not null
and datediff(a.lead_dt,a.pay_time)100;
结果如下 需求11: 查询出每年支付时间间隔最长的用户
SELECTyears,b.user_name,b.pay_days
FROM(SELECTyears,a.user_name,datediff(a.pay_time,a.lag_dt) pay_days,rank() over(partition by years order by datediff(a.pay_time,a.lag_dt) desc) rank1FROM(SELECTyear(pay_time) as years,user_name,pay_time,lag(pay_time) over(partition by user_name,year(pay_time) order by pay_time) lag_dtFROM user_trade)aWHERE a.lag_dt is not null)b
WHERE b.rank11;
结果如下 发布于 2023-07-19 09:21・IP 属地上海
