在SQL Server中计算运行总计的最有效方法是什么？

SQL Server 累积总和计算方法详解

在SQL查询中计算累积总和是一种常见需求。OVER子句为在Oracle和ANSI-SQL中执行此类计算提供了一种便捷的方法。然而，SQL Server对OVER子句的实现缺乏处理某些用例的灵活性。

更新技巧

尽管存在缺点，但SQL Server中计算累积总和的一个有效技巧是使用聚合集语句。此方法包括：

1. 创建一个与原始表具有相同列的临时表。
2. 将原始表中的数据插入到临时表中，同时将累积总和列设置为NULL。
3. 更新临时表以根据先前值计算累积总和。

此技巧效率很高，但存在潜在问题：

• UPDATE语句处理行的顺序可能并不总是与日期顺序相同。
• 更新技巧依赖于SQL Server未公开的实现细节。

基准测试比较

基准测试表明，在SQL Server的约束条件下，游标方法是计算累积总和最快、最安全的方法。更新技巧提供了最高的性能，但存在关于处理顺序的潜在问题。因此，对于生产代码，建议使用基于游标的方法。

示例代码和基准测试数据

以下代码提供了工作示例以及用于基准测试的测试数据：

测试数据设置：

<code class="language-sql">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;</code>

测试方法：

测试1：相关子查询

<code class="language-sql">SELECT ord,
       total,
       (SELECT SUM(total)
        FROM #t b
        WHERE b.ord <= a.ord) AS RunningTotal
FROM #t a
ORDER BY a.ord;</code>

测试2：交叉连接

<code class="language-sql">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;</code>

测试3：游标

<code class="language-sql">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;</code>

测试4：更新技巧

<code class="language-sql">DECLARE @total INT;
SET @total = 0;
UPDATE #t
SET running_total = @total,
    @total = @total + total;

SELECT *
FROM #t;</code>

通过比较以上四种方法的执行效率，可以得出在SQL Server中计算累积总和的最佳实践。 需要注意的是，实际性能可能因数据量和服务器配置而异。

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