Calcular un total acumulado en SQL Server

Resuelto codeulike asked hace 15 años • 15 respuestas

Imagine la siguiente tabla (llamada TestTable):

id     somedate    somevalue
--     --------    ---------
45     01/Jan/09   3
23     08/Jan/09   5
12     02/Feb/09   0
77     14/Feb/09   7
39     20/Feb/09   34
33     02/Mar/09   6

Me gustaría una consulta que devuelva un total acumulado en orden de fecha, como:

id     somedate    somevalue  runningtotal
--     --------    ---------  ------------
45     01/Jan/09   3          3
23     08/Jan/09   5          8
12     02/Feb/09   0          8
77     14/Feb/09   7          15  
39     20/Feb/09   34         49
33     02/Mar/09   6          55

Sé que hay varias formas de hacer esto en SQL Server 2000/2005/2008.

Estoy particularmente interesado en este tipo de método que utiliza el truco de agregación de declaraciones de conjuntos:

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

... esto es muy eficiente, pero he oído que hay problemas relacionados con esto porque no necesariamente se puede garantizar que la UPDATEdeclaración procese las filas en el orden correcto. Quizás podamos obtener algunas respuestas definitivas sobre ese tema.

¿Pero tal vez haya otras formas que la gente pueda sugerir?

editar: Ahora con un SqlFiddle con la configuración y el ejemplo de 'truco de actualización' anterior

codeulike avatar May 14 '09 06:05 codeulike
Aceptado

Actualización , si está ejecutando SQL Server 2012, consulte: https://stackoverflow.com/a/10309947

El problema es que la implementación de la cláusula Over en SQL Server es algo limitada .

Oracle (y ANSI-SQL) le permiten hacer cosas como:

 SELECT somedate, somevalue,
  SUM(somevalue) OVER(ORDER BY somedate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM Table

SQL Server no le ofrece una solución clara a este problema. Mi instinto me dice que este es uno de esos raros casos en los que un cursor es el más rápido, aunque tendré que realizar algunas evaluaciones comparativas para obtener resultados importantes.

El truco de actualización es útil pero creo que es bastante frágil. Parece que si está actualizando una tabla completa, procederá en el orden de la clave principal. Entonces, si estableces tu fecha como clave principal ascendente estarás probablya salvo. Pero usted está confiando en un detalle de implementación de SQL Server no documentado (además, si la consulta termina siendo realizada por dos procesos, me pregunto qué pasará, consulte: MAXDOP):

Muestra de trabajo completa:

drop table #t 
create table #t ( ord int primary key, total int, running_total int)

insert #t(ord,total)  values (2,20)
-- notice the malicious re-ordering 
insert #t(ord,total) values (1,10)
insert #t(ord,total)  values (3,10)
insert #t(ord,total)  values (4,1)

declare @total int 
set @total = 0
update #t set running_total = @total, @total = @total + total 

select * from #t
order by ord 

ord         total       running_total
----------- ----------- -------------
1           10          10
2           20          30
3           10          40
4           1           41

Usted pidió un punto de referencia, esta es la verdad.

La forma SEGURA más rápida de hacer esto sería el Cursor, es un orden de magnitud más rápido que la subconsulta correlacionada de unión cruzada.

La forma más rápida es el truco ACTUALIZAR. Mi única preocupación es que no estoy seguro de que en todas las circunstancias la actualización se realice de forma lineal. No hay nada en la consulta que lo diga explícitamente.

En pocas palabras, para el código de producción usaría el cursor.

Datos de prueba:

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 #t (ord, total) values (@i,  rand() * 100) 
    set @i = @i +1
end
commit

Prueba 1:

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

-- CPU 11731, Reads 154934, Duration 11135 

Prueba 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

-- CPU 16053, Reads 154935, Duration 4647

Prueba 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

-- CPU 359, Reads 30392, Duration 496

Prueba 4:

declare @total int 
set @total = 0
update #t set running_total = @total, @total = @total + total 

select * from #t

-- CPU 0, Reads 58, Duration 139
Sam Saffron avatar May 14 '2009 00:05 Sam Saffron

En SQL Server 2012 puede usar SUM() con la cláusula OVER() .

select id,
       somedate,
       somevalue,
       sum(somevalue) over(order by somedate rows unbounded preceding) as runningtotal
from TestTable

Violín SQL

Mikael Eriksson avatar Apr 25 '2012 05:04 Mikael Eriksson

Si bien Sam Saffron hizo un gran trabajo en ello, todavía no proporcionó un código de expresión de tabla común recursivo para este problema. Y para nosotros que trabajamos con SQL Server 2008 R2 y no con Denali, sigue siendo la forma más rápida de obtener el total acumulado, es aproximadamente 10 veces más rápido que el cursor en mi computadora de trabajo para 100000 filas, y también es una consulta en línea.
Entonces, aquí está (supongo que hay una ordcolumna en la tabla y su número secuencial sin espacios, para un procesamiento rápido también debería haber una restricción única en este número):

;with 
CTE_RunningTotal
as
(
    select T.ord, T.total, T.total as running_total
    from #t as T
    where T.ord = 0
    union all
    select T.ord, T.total, T.total + C.running_total as running_total
    from CTE_RunningTotal as C
        inner join #t as T on T.ord = C.ord + 1
)
select C.ord, C.total, C.running_total
from CTE_RunningTotal as C
option (maxrecursion 0)

-- CPU 140, Reads 110014, Duration 132

sql fiddle demo

actualización También tenía curiosidad acerca de esta actualización con una actualización variable o peculiar . Normalmente funciona bien, pero ¿cómo podemos estar seguros de que funciona siempre? bueno, aquí hay un pequeño truco (lo encontré aquí: http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258 ): simplemente verifique la asignación actual y anterior ordy use 1/0en caso de que sean diferentes de lo que estas esperando:

declare @total int, @ord int

select @total = 0, @ord = -1

update #t set
    @total = @total + total,
    @ord = case when ord <> @ord + 1 then 1/0 else ord end,
    ------------------------
    running_total = @total

select * from #t

-- CPU 0, Reads 58, Duration 139

Por lo que he visto, si tiene un índice agrupado/clave principal adecuado en su tabla (en nuestro caso sería índice por ord_id), la actualización se realizará de forma lineal todo el tiempo (nunca se encontrará con división por cero). Dicho esto, depende de ti decidir si quieres usarlo en el código de producción :)

actualización 2 Estoy vinculando esta respuesta, porque incluye información útil sobre la falta de confiabilidad de la peculiar actualización: concatenación de nvarchar/índice/nvarchar(max) comportamiento inexplicable .

Roman Pekar avatar Dec 06 '2012 13:12 Roman Pekar

El operador APPLY en SQL 2005 y superiores funciona para esto:

select
    t.id ,
    t.somedate ,
    t.somevalue ,
    rt.runningTotal
from TestTable t
 cross apply (select sum(somevalue) as runningTotal
                from TestTable
                where somedate <= t.somedate
            ) as rt
order by t.somedate
Mike Forman avatar Jun 05 '2009 18:06 Mike Forman
SELECT TOP 25   amount, 
    (SELECT SUM(amount) 
    FROM time_detail b 
    WHERE b.time_detail_id <= a.time_detail_id) AS Total FROM time_detail a

También puede usar la función ROW_NUMBER() y una tabla temporal para crear una columna arbitraria para usar en la comparación en la instrucción SELECT interna.

Sam Axe avatar May 14 '2009 00:05 Sam Axe