2018年2月8日 星期四

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]]
)
註:20052008不支援ORDER BY子句。

將以23300050股票下單/庫存為例,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_VALUELAST_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)

沒有留言:

張貼留言