SQL集合运算参考及案例(一)：列值分组累计求和

 

概述

目前企业应用系统使用的大多数据库都是关系型数据库，关系数据库依赖的 理论就是针对集合运算的关系代数。关系代数是一种抽象的查询语言，是关系数据操纵语言的一种传统表达方式。不过我们在工作中发现，很多人在面对复杂的数据 库运算逻辑时会采用游标、循环、自定义函数等方式处理，因为游标是一种比较熟悉和舒适的面向过程的编程方式，很符合我们一般的逻辑思维习惯，可很不幸，这 会导致糟糕的性能。显然，SQL的总体目的是你要实现什么，而不是怎样实现。大道至简，我们在工作与学习的过程中经常会发现，更好的解决方案往往是简单 的，是高效的，是优雅的。

      本人曾经用T-SQL重写了一个基于游标的存储过程，那个表只有100,000条记录，原来的存储过程用了40分钟才执行完毕，而新的存储过程只用了不到1秒。在这里，我想将自己遇到和收集到的关于集合运算与游标操作的对比展现给大家，以供参考。

问题描述

      我们有时会遇到这样一个问题，类似于某一列的值累计求和（即本条记录的某个值=前几列该值的合计）。我将解决的核心部分抽取出来。

--- 原始数据如下：

OID	Period	Amount	Balance
1	2009	3500.00	0.00
2	2009	5100.00	0.00
3	2009	10000.00	0.00
4	2010	2560.00	0.00
5	2010	4700.00	0.00

-- 预期结果如下（求Balance的值）：

OID	Period	Amount	Balance
1	2009	3500.00	3500.00
2	2009	5100.00	8600.00
3	2009	10000.00	18600.00
4	2010	2560.00	2560.00
5	2010	4700.00	7260.00

创建测试数据的SQL脚本

CREATE TABLE tPeriod
(
      OID       INT IDENTITY PRIMARY KEY
    , Period    NVARCHAR(20)
    , Amount    DECIMAL(18, 2) DEFAULT 0
    , Balance   DECIMAL(18, 2) DEFAULT 0
    , Balance2  DECIMAL(18, 2) DEFAULT 0
    , Balance3  DECIMAL(18, 2) DEFAULT 0
)
GO

DECLARE @i INT
SET @i = 1900
WHILE @i <= 2013
BEGIN

    INSERT INTO tPeriod(Period, Amount)
              SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
    
    SET @i = @i + 1
END

INSERT INTO tPeriod(Period, Amount)
          SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
GO

SELECT * FROM tPeriod;
GO

传统解答：使用游标

DECLARE   @OID              INT
        , @vPeriod_Pre      NVARCHAR(20)
        , @vPeriod_Current  NVARCHAR(20)
        , @dcAmount         DECIMAL(18, 2)
        , @dcBalance        DECIMAL(18, 2)
DECLARE cursor1 CURSOR FOR 
    SELECT t.OID, t.Period, t.Amount from tPeriod AS t
OPEN cursor1

FETCH NEXT FROM cursor1 INTO @OID, @vPeriod_Current, @dcAmount
SELECT @vPeriod_Pre = @vPeriod_Current, @dcBalance = 0

WHILE @@FETCH_STATUS = 0 
BEGIN
    IF @vPeriod_Current = @vPeriod_Pre
    BEGIN
        SET @dcBalance = @dcBalance + @dcAmount
    END  
    ELSE
    BEGIN
        SELECT @vPeriod_Pre = @vPeriod_Current, @dcBalance = @dcAmount
    END
        
    UPDATE tPeriod
    SET Balance = @dcBalance
    WHERE   OID = @OID

    FETCH NEXT FROM cursor1 INTO @OID, @vPeriod_Current, @dcAmount
END

CLOSE cursor1
DEALLOCATE cursor1

推荐解答：集合运算

-- 参考答案2
UPDATE    tPeriod
SET    Balance3 = ( SELECT SUM(Amount) 
                FROM tPeriod AS t 
                WHERE t.Period = tPeriod.Period AND t.OID <= tPeriod.OID
              )
GO


-- 参考答案3（SQLSERVER）
DECLARE @dcAmt DECIMAL(18, 2), @period CHAR(4)

UPDATE T1
SET @dcAmt = CASE WHEN Period = @period THEN @dcAmt + Amount ELSE Amount END,
    @Period = Period,
    Balance2 = @dcAmt
FROM    tPeriod AS T1
GO

-- 参考答案3（Oracle）
SELECT t.*, sum(t.amount) over(partition BY t.Period order by t.OID) as acc 
FROM tPeriod t;