上一篇文章《高级SQL分析函数-如何用窗口函数进行排名计算》中我们为大家介绍了窗口函数在排名计算场景中的应用，但实际上窗口函数除了可以进行单行计算，还可以在每行上打开一个指定大小的计算窗口，这个计算窗口可以由SQL中的语句具体指定，大到整个分区作用域，小到当前行指定的某个偏移行(比如 当前行的上一行、下一行，整个计算窗口被称作 frame)。今天就为大家介绍窗口函数在数据累计分析场景中的应用。
需要注意的是，如果您的数据库版本低于以下版本，将无法使用文章中使用到的窗口函数。
- Mysql (>=8.0)
- PostgreSQL(>=11)
- SQL Server(>=2012)
- Oracle(>=8i)
- SQLite(>=3.28.0)
需求背景
为了让大家更好的理解，我们以工厂的耗材损耗数据作为查询条件背景：假设现在有某个工厂刚刚完成了一次耗材的加工，在加工的过程中记录了耗材分类，每日的记录时间、每日的耗材耗损数和当月的月初耗材供给量数据信息，如下表所示：
现在这家公司的老板想看一下：
- 各个耗材的每日累计损耗量。
- 各个耗材的当月每日余量。
- 各个耗材的每月累计消耗占比。
查询各个耗材的每日累计损耗量
执行如下的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 )；
在这部分SQL中，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 能计算出对应的累计值了。
查询各个耗材的当月每日余量
查询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计算窗口的灵活调整还有更多丰富特性，后续（第三篇）还会为大家介绍偏移计算场景。
Wyn 商业智能软件| 下载试用
Wyn 是西安葡萄城自主研发的嵌入式商业智能软件，能帮助企业用户发现更多的数据潜在价值，为管理者制定决策提供数据支撑。Wyn 具备完整的嵌入式分析能力，能够与其他软件深度集成，也可独立部署使用，快速提升数据展示和分析能力。
Wyn 支持公有云、私有云和本地部署等多种部署方式，并能在Java、.net、PHP等开发平台中使用。您可以将其与ERP、CRM、OA等业务系统，以及钉钉、企业微信等APP进行深度集成，持续交付BI和报表功能，助力您的客户发现数据的价值。
了解更多关于 Wyn 嵌入式商业智能软件的内容，请点击此处访问官网，立即下载体验。
