网站经常被攻击,石家庄市规划建设局网站,网络营销效果评估,宁波网站排名提升上一篇文章《如何用窗口函数实现排名计算》中小编为大家介绍了窗口函数在排名计算场景中的应用#xff0c;但实际上窗口函数除了可以进行单行计算#xff0c;还可以在每行上打开一个指定大小的计算窗口#xff0c;这个计算窗口可以由SQL中的语句具体指定#xff0c;大到整个…上一篇文章《如何用窗口函数实现排名计算》中小编为大家介绍了窗口函数在排名计算场景中的应用但实际上窗口函数除了可以进行单行计算还可以在每行上打开一个指定大小的计算窗口这个计算窗口可以由SQL中的语句具体指定大到整个分区作用域小到当前行指定的某个偏移行(比如 当前行的上一行、下一行,整个计算窗口被称作 frame)。今天小编就为大家介绍窗口函数在累计分析场景中的应用。
需要注意的是如果您的数据库版本低于以下版本将无法使用文章中使用到的窗口函数。
1.Mysql (8.0)
2. PostgreSQL(11) 3. SQL Server(2012) 4. Oracle(8i) 5. SQLite(3.28.0)
需求背景
和上一篇文章一样为了让大家更好的理解我将以工厂的耗材损耗数据作为查询条件背景假设现在有某个工厂刚刚完成了一次耗材的加工在加工的过程中记录了耗材分类每日的记录时间、每日的耗材耗损数和当月的月初耗材供给量如下表所示 现在这家公司的老板想看一下
1. 各个耗材的每日累计损耗量。
2. 各个耗材的当月每日余量。
3. 各个耗材的每月累计消耗占比。
查询各个耗材的每日累计损耗量
执行如下的SQL语句。
select cate,record_date,init_value,SUM(cost) over(partition by cate,MONTH(record_date) order by record_date ) as cm_costfrom material_data md;可以看到通过上述 SQL 查询就已经得到了每个分类每月的每日累计耗损量。这里为大家解释下SQL中的重点部分
SUM(cost) over(partition by cate,MONTH(record_date) order by record_date )在上一篇文章中我们介绍过partition by 指定了计算分区, order by 决定了计算的行顺序, 那累计效果又是谁来完成的呢, 这里小编把刚刚的 SQL 稍微改造一下就会更清晰。
select cate,record_date,init_value,SUM(cost) over(partition by cate,MONTH(record_date) order by record_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as cm_costfrom material_data md;改造后的SQL和最开始的查询SQL达成的效果是一致的, 我们可以看到改造SQL在 order by 后加了一段代码
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW小编为大家拆解一下这个代码第一个 ROWS 表示接下来的 Frame 窗口指定为行模式, BETWEEN 关键字表示接下来的语句效果是指定 窗口范围, UNBOUNDED 和PRECEDING 是两个关键字的组合,前者表示 该计算窗口在 ↑ 方向的边界为最顶部对应到 partion by 分区中 6 月份的计算域UNBOUNDED PRECEDING 表示6月份每一行的窗口上界为 order by record_date 顺序下的最小值即 2023/06/01号的记录, 同样的 接下来的 AND CURRENT ROW 则指定了计算frame 窗口的 ↓ 边界为当前行。 最后我们重新梳理下这个计算窗口, 在每月每个分类的计算分区下每一行的计算窗口为 从本月的最小日期 到当前行的所有记录,联系到最开始 SUM(cost) 聚合就能够理解 为什么这条 SQL 能计算出对应的累计值了。 这里可以扩展说明一下确定计算窗口大小的关键字 除了UNBOUNDED PRECEDING和CURRENT ROW 之外还有 UNBOUNDED FOLLOWING, 如果 UNBOUNDED PRECEDING 表示上边界的顶部, 那 UNBOUNDED FOLLOWING 就表示下边界的底部。所以如果指定计算窗口为 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, 则表示在整个分区计算域中进行聚合运算。另外, UNBOUNDED 其实是非必须的, 这里可以替换为任意数字表示 针对当前行的偏移行数。比如 1 PRECEDING 表示 当前行的上一行, 1 FOLLOWING 表示当前行的下一行, 我们通过指定计算窗口为 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 就能计算 每一行从上一行到下一行之间这三行的累计值。至于说 CURRENT ROW 则指定为当前行这也是为什么能做累计求和的关键。 类似的MAX()、AVG() 等聚合函数也适用于以上的规则, 我们可以在每一行的指定窗口内来计算最大值,平均值等聚合值。
查询各个耗材的当月每日余量
查询Sql
selectcate,record_date,init_value,init_value - SUM(cost) over(partition by cate,MONTH(record_date) order by record_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as material_numfrom material_data md;也可以简写为
selectcate,record_date,init_value,init_value - SUM(cost) over(partition by cate,MONTH(record_date) order by record_date ) as material_numfrom material_data md;查询各个耗材的每月累计消耗占比
selectmd.cate,record_date,init_value,cost/ sum(cost) over(partition by cate,MONTH(record_date) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cm_costfrom material_data md同理可以简写为
selectmd.cate,record_date,init_value,cost/ sum(cost) over(partition by cate,MONTH(record_date)) as cm_costfrom material_data md接着就可以根据每天的消耗量占比来挖掘实际业务场景, 对异常消耗量数据进行对应跟踪。 总结
累计运算也是窗口函数在业务场景中使用得最频繁得一个场景,尤其是销售业务累计排名,业务器材每日消耗程度, 每日余量警报等场景都会用到, 希望能对各位有所帮助。而关于 frame计算窗口得灵活调整还有更多丰富特性后续第三篇还会为大家介绍偏移计算场景。
扩展链接
如何快速实现多人协同编辑
Excel中自定义手写签名
高级SQL分析函数-窗口函数(1)- 排名计算
