preceding - sum over sql server
Calcular un total acumulado en SQL Server (14)
Aunque la mejor forma de hacerlo es usar una función de ventana, también se puede hacer usando una sub consulta simple correlacionada .
Select id, someday, somevalue, (select sum(somevalue)
from testtable as t2
where t2.id = t1.id
and t2.someday <= t1.someday) as runningtotal
from testtable as t1
order by id,someday;
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 hacerlo en SQL Server 2000/2005/2008.
Estoy particularmente interesado en este tipo de método que utiliza el truco de agregación de conjunto de declaraciones:
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 escuchado que hay problemas al respecto porque no necesariamente se puede garantizar que la instrucción UPDATE procesará las filas en el orden correcto. Tal vez podamos obtener algunas respuestas definitivas sobre ese tema.
Pero tal vez hay otras formas en que la gente puede sugerir?
editar: Ahora con un SqlFiddle con la configuración y el ejemplo de ''truco de actualización'' anterior
Creo que se puede lograr un total acumulado usando la simple operación INNER JOIN a continuación.
SELECT
ROW_NUMBER() OVER (ORDER BY SomeDate) AS OrderID
,rt.*
INTO
#tmp
FROM
(
SELECT 45 AS ID, CAST(''01-01-2009'' AS DATETIME) AS SomeDate, 3 AS SomeValue
UNION ALL
SELECT 23, CAST(''01-08-2009'' AS DATETIME), 5
UNION ALL
SELECT 12, CAST(''02-02-2009'' AS DATETIME), 0
UNION ALL
SELECT 77, CAST(''02-14-2009'' AS DATETIME), 7
UNION ALL
SELECT 39, CAST(''02-20-2009'' AS DATETIME), 34
UNION ALL
SELECT 33, CAST(''03-02-2009'' AS DATETIME), 6
) rt
SELECT
t1.ID
,t1.SomeDate
,t1.SomeValue
,SUM(t2.SomeValue) AS RunningTotal
FROM
#tmp t1
JOIN #tmp t2
ON t2.OrderID <= t1.OrderID
GROUP BY
t1.OrderID
,t1.ID
,t1.SomeDate
,t1.SomeValue
ORDER BY
t1.OrderID
DROP TABLE #tmp
El operador APPLY en SQL 2005 y superior 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
Lo siguiente producirá los resultados requeridos.
SELECT a.SomeDate,
a.SomeValue,
SUM(b.SomeValue) AS RunningTotal
FROM TestTable a
CROSS JOIN TestTable b
WHERE (b.SomeDate <= a.SomeDate)
GROUP BY a.SomeDate,a.SomeValue
ORDER BY a.SomeDate,a.SomeValue
Tener un índice agrupado en SomeDate mejorará en gran medida el rendimiento.
Si bien Sam Saffron hizo un gran trabajo al respecto, aún no proporcionó el 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 un 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 columna ord en la tabla y es un número secuencial sin espacios, para un procesamiento rápido también debería haber una restricción única sobre 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
actualización También tenía curiosidad acerca de esta actualización con actualización variable o peculiar . Por lo general, funciona bien, pero ¿cómo podemos estar seguros de que funciona todo el tiempo? bueno, aquí hay un pequeño truco (lo encontré aquí - http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258 ) - solo verifica el orden actual y anterior y usa la asignación 1/0 en caso de que son diferentes de lo que esperas:
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 la clave principal / índice agrupado adecuada en su tabla (en nuestro caso, sería índice por ord_id ), la actualización procederá de forma lineal todo el tiempo (nunca se ha encontrado dividir por cero). Dicho eso, depende de usted decidir si desea usarlo en el código de producción :)
Si está utilizando Sql server 2008 R2 arriba. Entonces, sería la forma más corta de hacerlo;
Select id
,somedate
,somevalue,
LAG(runningtotal) OVER (ORDER BY somedate) + somevalue AS runningtotal
From TestTable
LAG se usa para obtener el valor de fila anterior. Puedes hacer google para obtener más información.
[1]:
Suponiendo que las ventanas funcionen en SQL Server 2008 como lo hace en cualquier otro lugar (eso lo he intentado), pruebe esto:
select testtable.*, sum(somevalue) over(order by somedate)
from testtable
order by somedate;
OVER() dice que está disponible en SQL Server 2008 (¿y quizás también en 2005?) Pero no tengo una instancia a mano para probarlo.
EDITAR: bueno, aparentemente SQL Server no permite una especificación de ventana ("OVER (...)") sin especificar "PARTITION BY" (dividiendo el resultado en grupos pero sin agregar de forma bastante similar a GROUP BY). Molesto: la referencia de sintaxis de MSDN sugiere que es opcional, pero solo tengo instancias de SqlServer 2000 por el momento.
La consulta que proporcioné funciona tanto en Oracle 10.2.0.3.0 como en PostgreSQL 8.4-beta. Así que dile a MS que se ponga al día;)
También puede desnormalizar: almacenar totales acumulados en la misma tabla:
Selecciona el trabajo mucho más rápido que cualquier otra solución, pero las modificaciones pueden ser más lentas
Usando join Otra variación es usar join. Ahora la consulta podría verse así:
SELECT a.id, a.value, SUM(b.Value)FROM RunTotalTestData a,
RunTotalTestData b
WHERE b.id <= a.id
GROUP BY a.id, a.value
ORDER BY a.id;
para obtener más información, visite este enlace http://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12
Use una subconsulta correlacionada. Muy simple, aquí tienes:
SELECT
somedate,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
GROUP BY somedate
ORDER BY somedate
Es posible que el código no sea exactamente correcto, pero estoy seguro de que la idea es correcta.
GROUP BY está en caso de que una fecha aparezca más de una vez; solo querrá verla una vez en el conjunto de resultados.
Si no le importa ver las fechas de repetición, o si desea ver el valor original y la identificación, entonces lo siguiente es lo que desea:
SELECT
id,
somedate,
somevalue,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
ORDER BY somedate
Actualización , si está ejecutando SQL Server 2012, consulte: https://.com/a/10309947
El problema es que la implementación de SQL Server de la cláusula Over 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 brinda una solución limpia 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 hacer una evaluación comparativa de grandes resultados.
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 configura su fecha como una clave primaria ascendente, probably estará a salvo. Pero está confiando en un detalle de implementación de SQL Server no documentado (también, si la consulta termina siendo ejecutada por dos procesadores, me pregunto qué sucederá, 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 más segura de hacer esto sería el Cursor, es un orden de magnitud más rápido que la subconsulta correlacionada de combinación cruzada.
La manera más rápida absoluta es el truco de ACTUALIZAR. Mi única preocupación es que no estoy seguro de que bajo ninguna circunstancia la actualización proceda de forma lineal. No hay nada en la consulta que lo diga explícitamente.
En pocas palabras, para el código de producción iría con 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
BEGIN TRAN
CREATE TABLE #Table (_Id INT IDENTITY(1,1) ,id INT , somedate VARCHAR(100) , somevalue INT)
INSERT INTO #Table ( id , somedate , somevalue )
SELECT 45 , ''01/Jan/09'', 3 UNION ALL
SELECT 23 , ''08/Jan/09'', 5 UNION ALL
SELECT 12 , ''02/Feb/09'', 0 UNION ALL
SELECT 77 , ''14/Feb/09'', 7 UNION ALL
SELECT 39 , ''20/Feb/09'', 34 UNION ALL
SELECT 33 , ''02/Mar/09'', 6
;WITH CTE ( _Id, id , _somedate , _somevalue ,_totvalue ) AS
(
SELECT _Id , id , somedate , somevalue ,somevalue
FROM #Table WHERE _id = 1
UNION ALL
SELECT #Table._Id , #Table.id , somedate , somevalue , somevalue + _totvalue
FROM #Table,CTE
WHERE #Table._id > 1 AND CTE._Id = ( #Table._id-1 )
)
SELECT * FROM CTE
ROLLBACK TRAN
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.