高级SQL分析函数-窗口函数(2)- 累计占比计算

收藏

本帖最后由 nimotea 于 2023-7-5 16:17 编辑

窗口函数

在上一Part中，我们介绍了窗口函数中排名计算的相关函数以及场景, 在排名场景中,SQL引擎会在行条件下去计算当前行的排名，非常实用。但实际上窗口函数可以除了单行计算，它还可以在每行上打开一个指定大小的计算窗口, 当然大小的程度可以由SQL 语句中具体指定，大到整个分区作用域,小到当前行指定的某个偏移行(比如当前行的上一行、下一行,整个计算窗口被称作 frame)，都可以根据需求灵活使用。那本章节就针对累计分析的场景来说明下窗口函数的妙用。内容中会介绍到 SUM(),MAX(),AVG() 等函数的使用方法。
( **注**: 这里的 SUM、MAX 等并非传统意义下的聚合函数，在窗口函数环境下会有不一样的计算结果)。

数据库要求

本次内容的要求版本和 Part1 中稍有不同,由于 frame 特性的实现差异,部分数据库会要求更高版本的对应数据库。
1. Mysql (>=8.0)
2. PostgreSQL(>=11)
3. SQL Server(>=2012)
4. Oracle(>=8i)
5. SQLite(>=3.28.0)

数据准备

我们这次准备的数据场景是，工厂耗材损耗，表结构中有关于耗材分类、每日的记录时间、每日的耗材耗损数、当月的月初耗材供给量，本文将针对该表信息获取如下统计指标
image.png72841795.png

1. 各个耗材的每日累计损耗量
2. 各个耗材的当月每日余量
3. 各个耗材的每月累计消耗占比

首先将附件中 csv 数据导入到数据库当中，这里我们给数据库起名为 material_data。

SQL

为了获得各个分类耗材的每日累计耗损量, 执行如下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 )

复制代码

如 part1 中提到的窗口函数语法细节, 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 能计算出对应的累计值了。
图片4.png832200317.png

   这里我们可以扩展下说明，确定计算窗口大小的关键字 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() 等聚合函数也适用于以上的规则, 我们可以在每一行的指定窗口内来计算最大值,平均值等聚合值。

案例

   关于我们要计算的2、3指标在理解了 frame 计算窗口的规则后，也就简单了

各个耗材的当月每日余量

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

复制代码

可简化为