SUM() OVER()分析函數的使用-累計加總(Running Total)

累計加總（Running Total），此功能是提供依序將資料值逐筆累計加總，常出現於月生產明細報表中，顯示每天的生產數量以及累計產量，差異狀況可提供管理階層對產能的控管，累計加總功能對報表是常見且實用的功能。

MSSQL 2012（含）及ORALCE 8i（含）以上版本所提供的SUM() OVER()函數，可直接針對欄位進行累計加總，MSSQL 2005提供SUM() OVER()函數，函數中不支援ORDER BY子句，因此無法支援逐筆累計，所支援功能為分群小計，但可用自我連結（Self-Join）以產生笛卡兒積的方式達成。

ORACLE / MSSQL

SUM(expression) OVER ( [ PARTITION BY ] [ ORDER BY [windowing_clause]] )

註：2005、2008不支援ORDER BY子句。

將以2330及0050股票下單/庫存為例，SQL及執行結果如下。

	SQL
ORACLE	SELECT C.*     , SUM(Qty) OVER(PARTITION BY StkId ORDER BY DT)  Acc_Qty     , SUM(Qty) OVER(PARTITION BY StkId)                Total_Qty FROM   (   SELECT '0050' StkId, '20180102' Dt, 1 Qty  FROM DUAL   UNION ALL   SELECT '0050' StkId, '20180103' Dt, 2 Qty  FROM DUAL   UNION ALL   SELECT '0050' StkId, '20180104' Dt, 3 Qty  FROM DUAL   UNION ALL   SELECT '0050' StkId, '20180105' Dt, 4 Qty  FROM DUAL   UNION ALL   SELECT '0050' StkId, '20180108' Dt, 5 Qty  FROM DUAL   UNION ALL   SELECT '2330' StkId, '20180102' Dt, 6  Qty  FROM DUAL   UNION ALL   SELECT '2330' StkId, '20180103' Dt, 7 Qty  FROM DUAL   UNION ALL   SELECT '2330' StkId, '20180104' Dt, 8 Qty  FROM DUAL   UNION ALL   SELECT '2330' StkId, '20180105' Dt, 9 Qty  FROM DUAL   UNION ALL   SELECT '2330' StkId, '20180108' Dt, 10  Qty  FROM DUAL   ) C
MSSQL	SELECT STK.*               , SUM(Qty) OVER(PARTITION BY StkId ORDER BY DT)  Acc_Qty               , SUM(Qty) OVER(PARTITION BY StkId)              Total_Qty FROM (VALUES                 ('0050', '20180102',  1)               , ('0050', '20180103',  2)               , ('0050', '20180104',  3)               , ('0050', '20180105',  4)               , ('0050', '20180108',  5)               --------               , ('2330', '20180102',  6)               , ('2330', '20180103',  7)               , ('2330', '20180104',  8)               , ('2330', '20180105',  9)               , ('2330', '20180108',  10) ) STK(StkId, Dt, Qty) ORDER BY StkId, Dt

執行結果如上所示，Acc_Qty為各檔股票數量之逐日累計加總（ORDER BY），而Total_Qty則為各檔股票數量加總(小計)，欄位整理如下表。

欄位	SQL	說明
Total_Qty	SUM(Qty) OVER(PARTITION BY StkId)	不具ORDER子句，僅可達成資料分割區之合計。(2005)
Acc_Qty	SUM(Qty) OVER(PARTITION BY StkId ORDER BY DT)	具有ORDER BY子句，可達成資料分割區中逐日（筆）累計。(2012)

Acc_Qty運算中額外搭配ORDER BY子句，即代表採用視窗框架（WINDOW）並引用其預設值RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (自資料分割中的第一筆資料列(Row)為起始點，至目前資料列為結束點)，即達成逐日（筆）累計，可參考《FIRST_VALUE及LAST_VALUE分析函數的使用》所探討。

2012以下版本則可改用子查詢方式，執行結果同上。

SELECT C.*      , R.Acc_Qty      , SUM(Qty) OVER(PARTITION BY StkId) Total_Qty FROM #Stk C      CROSS APPLY      (      SELECT SUM(R.Qty)      FROM #Stk R      WHERE 1=1              AND C.StkId = R.StkId --1. 資料分割: PARTITION BY              AND R.Dt <= C.Dt       --2. 資料滾動: ORDER BY         ) R (Acc_Qty) ORDER BY c.StkId, c.Dt

--0. 建立暫存資料

SELECT STK.* INTO #Stk

FROM (VALUES

                ('0050', '20180102',  1)

              , ('0050', '20180103',  2)

              , ('0050', '20180104',  3)

              , ('0050', '20180105',  4)

              , ('0050', '20180108',  5)

              --------

              , ('2330', '20180102',  6)

              , ('2330', '20180103',  7)

              , ('2330', '20180104',  8)

              , ('2330', '20180105',  9)

              , ('2330', '20180108',  10)

     ) STK(StkId, Dt, Qty)