如何在SQL Server中有效计算运行总计？

在SQL Server中计算运行总计

简介

计算运行总计是数据分析中的一项常见任务。在SQL Server中，有多种方法可以实现此目标，包括使用窗口函数、相关子查询和基于游标的解决方案。“聚合集语句技巧”是一种引发讨论的方法。此技术涉及将行插入到具有空运行总计的临时表中，按特定顺序更新表，最后选择更新的运行总计。

聚合集语句技巧

以下查询使用聚合集语句技巧来计算运行总计：

INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal) 
SELECT id, somedate, somevalue, null
FROM TestTable
ORDER BY somedate

DECLARE @RunningTotal int
SET @RunningTotal = 0

UPDATE @AnotherTbl
SET @RunningTotal = runningtotal = @RunningTotal + somevalue
FROM @AnotherTbl

这种方法被认为是高效的，因为它按检索到的顺序更新行。但是，人们对其在所有场景中的可靠性表示担忧。

聚合集语句技巧的替代方法

1. 窗口函数：

SQL Server 2012引入了OVER子句，允许使用窗口函数（例如带有累积聚合的SUM）。这消除了对额外表操作的需求，并简化了查询：

SELECT somedate, somevalue,
    SUM(somevalue) OVER(ORDER BY somedate) AS RunningTotal
FROM TestTable

2. 相关子查询：

相关子查询可用于检索每一行的运行总计：

SELECT somedate, somevalue,
(
    SELECT SUM(somevalue) 
    FROM TestTable b 
    WHERE b.somedate < a.somedate
) AS RunningTotal
FROM TestTable a

3. 基于游标的解决方案：

游标提供了一种逐步处理行（按特定顺序）的机制。以下基于游标的解决方案增量计算运行总计：

DECLARE @TotalTable table(id int primary key, somedate date, somevalue int, runningtotal int)

DECLARE forward_cursor CURSOR FAST_FORWARD 
FOR 
SELECT id, somedate, somevalue
FROM TestTable 
ORDER BY somedate


OPEN forward_cursor 

DECLARE @running_total int, 
    @id int, 
    @somedate date, 
    @somevalue int
SET @running_total = 0

FETCH NEXT FROM forward_cursor INTO @id, @somedate, @somevalue 
WHILE (@@FETCH_STATUS = 0)
BEGIN
     SET @running_total = @running_total + @somevalue
     INSERT @TotalTable VALUES(@id, @somedate, @somevalue, @running_total)
     FETCH NEXT FROM forward_cursor INTO @id, @somedate, @somevalue 
END

CLOSE forward_cursor
DEALLOCATE forward_cursor

SELECT * FROM @TotalTable

结论

在SQL Server中，计算运行总计的方法选择取决于数据大小、性能要求和SQL Server版本等因素。“聚合集语句技巧”仍然是高效更新的一种可行选择，而窗口函数在较新版本的SQL Server中提供了更直接的语法。应考虑相关子查询和基于游标的解决方案以实现灵活性和控制。建议进行基准测试以确定特定场景下的最佳方法。

以上是如何在SQL Server中有效计算运行总计？的详细内容。更多信息请关注PHP中文网其他相关文章！