乐趣区

关于sql:窗口函数大揭秘轻松计算数据累计占比玩转数据分析的绝佳利器

上一篇文章《如何用窗口函数实现排名计算》中小编为大家介绍了窗口函数在排名计算场景中的利用,但实际上窗口函数除了能够进行单行计算,还能够在每行上关上一个指定大小的计算窗口,这个计算窗口能够由 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_cost

from 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_cost

from 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:

select

cate,

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_num

from material_data md;

也能够简写为

select

cate,

record_date,

init_value,

init_value - SUM(cost) over(partition by cate,MONTH(record_date) order by record_date ) as material_num

from material_data md;

查问各个耗材的每月累计耗费占比

select

md.cate,

record_date,

init_value,

cost/ sum(cost) over(partition by cate,MONTH(record_date) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cm_cost

from material_data md

同理,能够简写为:

select

md.cate,

record_date,

init_value,

cost/ sum(cost) over(partition by cate,MONTH(record_date)) as cm_cost

from material_data md

接着就能够依据每天的消耗量占比,来开掘理论业务场景, 对异样消耗量数据进行对应跟踪。

总结

累计运算也是窗口函数在业务场景中应用得最频繁得一个场景, 尤其是销售业务累计排名, 业务器材每日耗费水平, 每日余量警报等场景都会用到, 心愿能对各位有所帮忙。而对于 frame 计算窗口得灵便调整还有更多丰盛个性,后续(第三篇)还会为大家介绍偏移计算场景。

扩大链接:

如何疾速实现多人协同编辑?

Excel 中自定义手写签名

高级 SQL 剖析函数 - 窗口函数 (1)- 排名计算

退出移动版