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)

FIRST_VALUE及LAST_VALUE函數IGNORE NULLS關鍵字用途-取得最後結算匯率為例

以下將以休假日引用前一營日之結算匯率(價格)為例，說明ORACLE之FIRST_VALUE及LAST_VALUE函數中IGNORE NULLS關鍵字用途及MSSQL對應做法。如下圖中，由於2018-01-13及2018-01-14為星期六日例假日匯市休市，因此將取得前一營業日（2018-01-12）為結算參考價，如下圖：

ORACLE的FIRST_VALUE及LAST_VALUE語法如下，可指定RESPECT NULLS（預設值）及IGNORE NULLS RESPECT NULLS將直接傳回第一筆（或最後一筆）運算結果，無論其是否為NULL，此為ORACLE函數預設值(MSSQL現行版本之設定值)；IGNORE NULL則指定僅回傳運算結果中的第一筆非NULL值之資料，本範例將應用此特性，如當天為休假日時，由於無參考匯率/價格可供使用（即NULL），則需取得最後營業日匯率為參考匯率，本範例介紹此參數之使用方式，並說明MSSQL如何達成。

ORACLE

FIRST_VALUE | LAST_VALUE ( expression [ RESPECT NULLS | IGNORE NULLS ] ) OVER ( [ PARTITION BY ] [ ORDER BY [windowing_clause]] )

ORACLE語法如下，結果如上圖。RESPECT NULLS為預設值，請注意視框架（WINDOW）設定為ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW即由排序資料集中的第一筆資料為起始點而目前資料列(Row)為結束點，當使用LAST_VALUE函數預設將取得目前資料列（當天）；但如額外使用IGNORE NULLS關鍵字，則會往前找到非NULL值（營業日）的資料，即為所求，此功能非常實用有效，可往前遞推找到最後一筆具值的資料，可解決相當多難以克服的問題，如仍無法解決所需則建議可採用MODEL指令，此將原資料集視為EXCEL，可達成類似在EXCEL進行互相參照功能。

SELECT D.DT

     , R.Fx_Rate

      , LAST_VALUE(R.Fx_Rate IGNORE NULLS)

           OVER (ORDER BY D.DT ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW) "IGNORE"

     , LAST_VALUE(R.Fx_Rate RESPECT NULLS)

           OVER (ORDER BY D.DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "RESPECT"

     , LAST_VALUE(R.DT IGNORE NULLS)

           OVER (ORDER BY D.DT ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)            FX_DT

FROM

    (

    --產生6天[日曆日]

    SELECT TO_CHAR(DATE'2018-01-11' + LEVEL -1, 'YYYYMMDD') DT

    FROM DUAL

    CONNECT BY LEVEL <= 6 

    ) D

    LEFT OUTER JOIN

    (

    --產生4天[營業日]匯率資料

    SELECT '20180111' DT, 29.604 Fx_Rate FROM DUAL

    UNION ALL

    SELECT '20180112' DT, 29.631 Fx_Rate FROM DUAL

    UNION ALL

    SELECT '20180115' DT, 29.536 Fx_Rate FROM DUAL

    UNION ALL

    SELECT '20180116' DT, 29.550 Fx_Rate FROM DUAL

    ) R   

    ON D.DT = R.DT

ORDER BY D.DT

MSSQL並無對應關鍵字可用，但可透過子查詢達成，為避免SQL過於龐大而難以理解，請先建立測試資料。

--Step 00. 建立測試資料

--DROP TABLE #Forex

SELECT D.DT

     , R.Fx_Rate

        INTO #Forex

FROM

    (

--產生6天[日曆日]

       SELECT CONVERT(CHAR(8), DATEADD(dd, number, '20180111'), 112) DT

       FROM master.dbo.spt_values

       WHERE 1=1

              AND type ='P'

              AND number<6

    ) D

    LEFT OUTER JOIN

--產生4天[營業日]匯率資料

    (VALUES('20180111', 29.604)

               , ('20180112', 29.631)

               , ('20180115', 29.536)

               , ('20180116', 29.550)

       ) R(DT, Fx_Rate)  

    ON D.DT = R.DT

ORDER BY D.DT

以下將說明類似的二種方法，方法-1為常用之寫法並附上LAST_VALUE函數以進行比較，方法-2則在模擬ORACLE參數用法，用分析函數設計想法來推導。

--方法-1:

SELECT C.*

   , COALESCE(C.Fx_Rate, P.Fx_Rate) Fx_Rate --當日有值(非NULL)優先使用，否則用子查詢(前一營業日)

   , LAST_VALUE(C.Fx_Rate )

           OVER (ORDER BY C.DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "RESPECT"

FROM #Forex C

     OUTER APPLY

     (

     SELECT TOP 1 *   -- 2. TOP 1: First_Value

     FROM #Forex P

     WHERE 1=1

          AND C.DT > P.DT       -- 1.主查詢使用COALESC且判斷並優先使用當日，因此>或>=結果相同

          AND P.Fx_Rate IS NOT NULL-- 2.剔除NULL值, 即可找到

     ORDER BY P.DT DESC

     ) P

--方法-2:

WITH Store AS 

(

SELECT F.*

     , ROW_NUMBER() OVER (ORDER BY DT) SEQ

FROM #Forex F

)

SELECT C.*

   , COALESCE(C.Fx_Rate, P.Fx_Rate) Fx_Rate--如當日有值(非NULL)優先使用，否則採子查詢(前一營業日)

FROM Store C

     OUTER APPLY

     (

     SELECT TOP 1 *               -- 3.TOP 1: First_Value

     FROM Store P

     WHERE 1=1

         AND P.SEQ BETWEEN C.SEQ-1-- 1.模擬WINDOW: 1 PRECEDING(僅用1天呈現效果,實際則10筆較適合)

         AND C.SEQ                -- 1.模擬WINDOW: CURRENET ROW

         AND P.Fx_Rate IS NOT NULL-- 2.剔除NULL值, 即可找到

     ORDER BY P.SEQ DESC          -- 3.配合TOP

    ) P

此方法使用ROW_NUMBER次序函數建立對應序號，再以視窗框架(WINDOW)取得前一筆（1 PRECEDING）至目前資料列(CURRENET ROW)，其次再將NULL值剔除（Filter out），最後以序號順序取得第1第資料（非NULL），為呈現視窗框架(WINDOW)效果，而於範例中僅使用前1筆資料，以休假日大部分都在10天內，因此可設定10至15筆較為合適。

方法-2中設定起始點為前1筆，此也展示LEAD/LAG函數於本範例不適合使用，由於往前遞推天數不確定，難以達成。