SQL Server中计算流水账总和的优化方案

在商业开发中,业务开发人员经常需要处理一系列现金流入流出的累计总和问题。所谓的累计总和,指的是按照日期顺序,对现金流入流出的金额进行累加。例如,有一个日期、流入/流出类型和金额的流水账记录,需要计算出到每一笔记录为止的累计金额。

为了实现这个功能,可以在SQL Server中创建一个名为Moves的表,用来存储每笔现金流的日期、描述、类型、预计价值和实际价值等信息。

CREATE TABLE [dbo].[Moves]( [RecID] [int] IDENTITY(1,1) NOT NULL, [MoveDate] [datetime] NULL, [MoveDescr] [nvarchar](2000) NULL, [MoveSignID] [int] NULL, [MoveProjValue] [decimal](18,4) NULL, [MoveActualValue] [decimal](18,4) NULL, [MoveComments] [nvarchar](2000) NULL, CONSTRAINT [PK_Moves] PRIMARY KEY CLUSTERED ([RecID] ASC) ) ON [PRIMARY]

在表中,MoveSignID字段用于标识现金流的类型,1代表流入,2代表流出。接下来,可以创建一些测试数据,模拟大约6年的流水账记录,以便进行实际测试。

为了计算这些数据的累计总和,可以使用多种不同的SQL查询方法。首先,可以尝试使用简单的子查询方法,但这种方法在处理大量数据时可能会非常慢。

SELECT a.moveDate, a.moveprojValue, CASE WHEN a.moveSignID=1 THEN SUM(b.moveprojValue) ELSE SUM(-b.moveprojValue) END AS RunningTotal FROM moves a CROSS JOIN moves b WHERE (b.moveDate <= a.moveDate) GROUP BY a.moveDate,a.moveprojValue,a.moveSignID ORDER BY a.moveDate

这种方法在处理4万条记录时大约需要29秒。为了提高效率,可以尝试使用窗口函数OVER,这在SQL Server2012及更高版本中是支持的。

SELECT T1.*, SUM(CASE WHEN T1.MoveSignID=1 THEN T1.MoveProjValue ELSE -T1.MoveProjValue END) OVER (ORDER BY moveDate) AS RunningTotal FROM Moves T1 ORDER BY T1.MoveDate

使用窗口函数的方法在同样的数据集上只需要0.6秒,比之前的方法快了近15倍。

解决方案 SQL兼容性 CPU时间 (ms) 耗时 (ms)
CROSS JOIN 所有 28422 29155
SUBQUERY 所有 8890 13407
CURSOR 所有 N/A ~9000
OVER SQL 2012+ 375 588
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485