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;

转载于:https://www.cnblogs.com/lcword/p/5719298.html