在SQL Server中計算運行總計的最有效方法是什麼？

SQL Server 累積總和運算方法詳解

在SQL查詢中計算累積總和是一種常見需求。 OVER子句為在Oracle和ANSI-SQL中執行此類計算提供了一種便捷的方法。然而，SQL Server對OVER子句的實作缺乏處理某些用例的彈性。

更新技巧

儘管有缺點，但SQL Server中計算累積總和的一個有效技巧是使用聚合集語句。此方法包括：

1. 建立一個與原始表具有相同列的臨時表。
2. 將原始表中的資料插入臨時表中，同時將累積總和列設為NULL。
3. 更新臨時表以根據先前值計算累積總和。

此技巧效率很高，但有潛在問題：

• UPDATE語句處理行的順序可能並不總是與日期順序相同。
• 更新技巧依賴SQL Server未公開的實作細節。

基準測試比較

基準測試表明，在SQL Server的約束條件下，遊標方法是計算累積總和最快、最安全的方法。更新技巧提供了最高的效能，但存在關於處理順序的潛在問題。因此，對於生產程式碼，建議使用基於遊標的方法。

範例程式碼和基準測試資料

以下程式碼提供了工作範例以及用於基準測試的測試資料：

檢定資料設定：

CREATE TABLE #t (
    ord INT PRIMARY KEY,
    total INT,
    running_total INT
);

SET NOCOUNT ON;
DECLARE @i INT;
SET @i = 0;
BEGIN TRAN;
WHILE @i < 10000
BEGIN
    INSERT INTO #t (ord, total) VALUES (@i, ABS(CHECKSUM(NEWID()) % 1000));
    SET @i = @i + 1;
END;
COMMIT TRAN;

檢定方法：

測試1：相關子查詢

SELECT ord,
       total,
       (SELECT SUM(total)
        FROM #t b
        WHERE b.ord <= a.ord) AS RunningTotal
FROM #t a
ORDER BY a.ord;

檢定2：交叉連結

SELECT a.ord,
       a.total,
       SUM(b.total) AS RunningTotal
FROM #t a
CROSS JOIN #t b
WHERE b.ord <= a.ord
GROUP BY a.ord, a.total
ORDER BY a.ord;

檢定3：遊標

DECLARE @TotalTable TABLE (
    ord INT PRIMARY KEY,
    total INT,
    running_total INT
);

DECLARE forward_cursor CURSOR FAST_FORWARD
FOR
SELECT ord,
       total
FROM #t
ORDER BY ord;

OPEN forward_cursor;

DECLARE @running_total INT,
        @ord INT,
        @total INT;

SET @running_total = 0;

FETCH NEXT FROM forward_cursor
INTO @ord,
      @total;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @running_total = @running_total + @total;
    INSERT @TotalTable VALUES (@ord,
                              @total,
                              @running_total);
    FETCH NEXT FROM forward_cursor
    INTO @ord,
          @total;
END;

CLOSE forward_cursor;
DEALLOCATE forward_cursor;

SELECT *
FROM @TotalTable;

測驗4：更新技巧

DECLARE @total INT;
SET @total = 0;
UPDATE #t
SET running_total = @total,
    @total = @total + total;

SELECT *
FROM #t;

透過比較以上四種方法的執行效率，可以得出在SQL Server中計算累積總和的最佳實務。 需要注意的是，實際效能可能會因資料量和伺服器配置而異。

以上是在SQL Server中計算運行總計的最有效方法是什麼？的詳細內容。更多資訊請關注PHP中文網其他相關文章！