[SQL Server][T-SQL]累積總計Running Totals

2016-10-15

4121
0
T-SQL

這兩天協助客戶處理累積交易餘額的資料修補，因為要使用到累積總計(Running Totals)，但客戶還在使用SQL Server 2008 R2，筆記SQL Server 2012前後兩個版本的作法。

我們先用CTE準備8筆簡單測試資料

WITH Hero(name, ModifiedDate, Score) 
     AS (SELECT 'David','2015-01-01',30         UNION ALL 
         SELECT 'David','2016-01-01',20         UNION ALL 
         SELECT 'Charlemagne','2013-01-01',10   UNION ALL 
         SELECT 'Charlemagne','2014-01-01',20   UNION ALL 
         SELECT 'Charlemagne','2015-01-01',30   UNION ALL 
         SELECT 'Charlemagne','2016-01-01',40   UNION ALL 
         SELECT 'Caesar','2015-01-01',40        UNION ALL 
         SELECT 'Caesar','2016-01-01',30)
SELECT * from Hero

需求是每一筆資料列後面按照撲克牌老K人物為單位加上一個資料行(累積小計)，像是這樣:

SQL Server 2012以後的寫法(window function):

WITH Hero(name, ModifiedDate, Score) 
     AS (SELECT 'David','2015-01-01',30         UNION ALL 
         SELECT 'David','2016-01-01',20         UNION ALL 
         SELECT 'Charlemagne','2013-01-01',10   UNION ALL 
         SELECT 'Charlemagne','2014-01-01',20   UNION ALL 
         SELECT 'Charlemagne','2015-01-01',30   UNION ALL 
         SELECT 'Charlemagne','2016-01-01',40   UNION ALL 
         SELECT 'Caesar','2015-01-01',40        UNION ALL 
         SELECT 'Caesar','2016-01-01',30)
SELECT A.name,A.ModifiedDate,A.Score,SUM(a.Score) OVER (PARTITION BY a.name ORDER BY a.ModifiedDate) AS 'total'
    FROM Hero a
    ORDER BY A.name,A.ModifiedDate

執行結果:

SQL Server 2008 R2以前的寫法1(Self Join)

WITH Hero(name, ModifiedDate, Score) 
     AS (SELECT 'David','2015-01-01',30         UNION ALL 
         SELECT 'David','2016-01-01',20         UNION ALL 
         SELECT 'Charlemagne','2013-01-01',10   UNION ALL 
         SELECT 'Charlemagne','2014-01-01',20   UNION ALL 
         SELECT 'Charlemagne','2015-01-01',30   UNION ALL 
         SELECT 'Charlemagne','2016-01-01',40   UNION ALL 
         SELECT 'Caesar','2015-01-01',40        UNION ALL 
         SELECT 'Caesar','2016-01-01',30)

SELECT A.name,A.ModifiedDate,A.Score,SUM(B.Score) AS RUNNING_TOTAL 
FROM Hero A JOIN Hero B 
    ON  A.name = B.name                     -- Partition column
    AND A.ModifiedDate >= B.ModifiedDate    -- condition column
GROUP BY A.name,A.ModifiedDate,A.Score
ORDER BY A.name,A.ModifiedDate

執行結果:

SQL Server 2008 R2以前的寫法2(Sub Query)

(同事k提供)

WITH Hero(name, ModifiedDate, Score) 
     AS (SELECT 'David','2015-01-01',30         UNION ALL 
         SELECT 'David','2016-01-01',20         UNION ALL 
         SELECT 'Charlemagne','2013-01-01',10   UNION ALL 
         SELECT 'Charlemagne','2014-01-01',20   UNION ALL 
         SELECT 'Charlemagne','2015-01-01',30   UNION ALL 
         SELECT 'Charlemagne','2016-01-01',40   UNION ALL 
         SELECT 'Caesar','2015-01-01',40        UNION ALL 
         SELECT 'Caesar','2016-01-01',30)
SELECT a.*, (SELECT SUM(b.Score) FROM Hero b WHERE a.name = b.name and b.ModifiedDate <= a.ModifiedDate) AS 'total'
    FROM Hero a
ORDER BY A.name,A.ModifiedDate

執行結果: