視窗函數大揭祕！輕鬆計算資料累計佔比，玩轉資料分析的絕佳利器
上一篇文章《如何用視窗函數實現排名計算》中小編為大家介紹了視窗函數在排名計算場景中的應用，但實際上視窗函數除了可以進行單行計算，還可以在每行上開啟一個指定大小的計算視窗，這個計算視窗可以由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計算視窗得靈活調整還有更多豐富特性，後續（第三篇）還會為大家介紹偏移計算場景。
擴充套件連結：