如何在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中文網其他相關文章！