sql-server - sumas - sumar resultados de una consulta sql
Cómo obtener la suma acumulada (14)
Última respuesta pero mostrando una posibilidad más ...
La generación suma acumulativa puede optimizarse con la lógica CROSS APPLY
.
Funciona mejor que la OVER Clause
INNER JOIN
& OVER Clause
cuando se analiza el plan de consulta real ...
/* Create table & populate data */
IF OBJECT_ID(''tempdb..#TMP'') IS NOT NULL
DROP TABLE #TMP
SELECT * INTO #TMP
FROM (
SELECT 1 AS id
UNION
SELECT 2 AS id
UNION
SELECT 3 AS id
UNION
SELECT 4 AS id
UNION
SELECT 5 AS id
) Tab
/* Using CROSS APPLY
Query cost relative to the batch 17%
*/
SELECT T1.id,
T2.CumSum
FROM #TMP T1
CROSS APPLY (
SELECT SUM(T2.id) AS CumSum
FROM #TMP T2
WHERE T1.id >= T2.id
) T2
/* Using INNER JOIN
Query cost relative to the batch 46%
*/
SELECT T1.id,
SUM(T2.id) CumSum
FROM #TMP T1
INNER JOIN #TMP T2
ON T1.id > = T2.id
GROUP BY T1.id
/* Using OVER clause
Query cost relative to the batch 37%
*/
SELECT T1.id,
SUM(T1.id) OVER( PARTITION BY id)
FROM #TMP T1
Output:-
id CumSum
------- -------
1 1
2 3
3 6
4 10
5 15
declare @t table
(
id int,
SomeNumt int
)
insert into @t
select 1,10
union
select 2,12
union
select 3,3
union
select 4,15
union
select 5,23
select * from @t
la selección de arriba me devuelve lo siguiente.
id SomeNumt
1 10
2 12
3 3
4 15
5 23
¿Cómo obtengo lo siguiente?
id srome CumSrome
1 10 10
2 12 22
3 3 25
4 15 40
5 23 63
Arriba (Pre-SQL12) vemos ejemplos como este:
SELECT
T1.id, SUM(T2.id) AS CumSum
FROM
#TMP T1
JOIN #TMP T2 ON T2.id < = T1.id
GROUP BY
T1.id
Más eficiente...
SELECT
T1.id, SUM(T2.id) + T1.id AS CumSum
FROM
#TMP T1
JOIN #TMP T2 ON T2.id < T1.id
GROUP BY
T1.id
Hay una implementación de CTE mucho más rápida disponible en esta excelente publicación: http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx
El problema en este hilo se puede expresar así:
DECLARE @RT INT
SELECT @RT = 0
;
WITH abcd
AS ( SELECT TOP 100 percent
id
,SomeNumt
,MySum
order by id
)
update abcd
set @RT = MySum = @RT + SomeNumt
output inserted.*
La última versión de SQL Server (2012) permite lo siguiente.
SELECT
RowID,
Col1,
SUM(Col1) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM tablehh
ORDER BY RowId
o
SELECT
GroupID,
RowID,
Col1,
SUM(Col1) OVER(PARTITION BY GroupID ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM tablehh
ORDER BY RowId
Esto es incluso más rápido. La versión particionada completa en 34 segundos más de 5 millones de filas para mí.
Gracias a Peso, quien comentó sobre el hilo del Equipo SQL mencionado en otra respuesta.
La solución SQL que combina "FILAS ENTRE SIN PRECEDENTES Y FILA ACTUAL" y "SUMA" hizo exactamente lo que yo quería lograr. Muchas gracias!
Si puede ayudar a alguien, aquí estaba mi caso. Quería acumular +1 en una columna cada vez que un fabricante se encuentra como "Some Maker" (ejemplo). Si no, no hay incremento, pero muestra el resultado del incremento anterior.
Entonces esta pieza de SQL:
SUM( CASE [rmaker] WHEN ''Some Maker'' THEN 1 ELSE 0 END)
OVER
(PARTITION BY UserID ORDER BY UserID,[rrank] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumul_CNT
Me permitió obtener algo como esto:
User 1 Rank1 MakerA 0
User 1 Rank2 MakerB 0
User 1 Rank3 Some Maker 1
User 1 Rank4 Some Maker 2
User 1 Rank5 MakerC 2
User 1 Rank6 Some Maker 3
User 2 Rank1 MakerA 0
User 2 Rank2 SomeMaker 1
Explicación de lo anterior: inicia el recuento de "algún fabricante" con 0, se encuentra Some Maker y hacemos +1. Para el usuario 1, se encuentra MakerC, por lo que no hacemos +1, sino que el recuento vertical de Some Maker queda bloqueado en 2 hasta la siguiente fila. La partición es por Usuario, por lo tanto, cuando cambiamos de usuario, el conteo acumulativo regresa a cero.
Estoy en el trabajo, no quiero ningún mérito en esta respuesta, solo agradezco y demuestro mi ejemplo en caso de que alguien esté en la misma situación. Intenté combinar SUM y PARTITION, pero la sintaxis asombrosa "FILAS ENTRE SIN PRECEDENTES Y FILA ACTUAL" completó la tarea.
¡Gracias! Groaker
Para SQL Server 2012 en adelante, podría ser fácil:
SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t
porque la cláusula ORDER BY
para SUM
por defecto significa RANGE UNBOUNDED PRECEDING AND CURRENT ROW
para window frame ("Observaciones generales" en https://msdn.microsoft.com/en-us/library/ms189461.aspx )
Prueba esto
select
t.id,
t.SomeNumt,
sum(t.SomeNumt) Over (Order by t.id asc Rows Between Unbounded Preceding and Current Row) as cum
from
@t t
group by
t.id,
t.SomeNumt
order by
t.id asc;
Prueba esto:
CREATE TABLE #t(
[name] varchar NULL,
[val] [int] NULL,
[ID] [int] NULL
) ON [PRIMARY]
insert into #t (id,name,val) values
(1,''A'',10), (2,''B'',20), (3,''C'',30)
select t1.id, t1.val, SUM(t2.val) as cumSum
from #t t1 inner join #t t2 on t1.id >= t2.id
group by t1.id, t1.val order by t1.id
Sin utilizar ningún tipo de salario acumulativo JOIN para una persona a buscar usando la siguiente consulta:
SELECT * , (
SELECT SUM( salary )
FROM `abc` AS table1
WHERE table1.ID <= `abc`.ID
AND table1.name = `abc`.Name
) AS cum
FROM `abc`
ORDER BY Name
Una versión CTE, solo por diversión:
;
WITH abcd
AS ( SELECT id
,SomeNumt
,SomeNumt AS MySum
FROM @t
WHERE id = 1
UNION ALL
SELECT t.id
,t.SomeNumt
,t.SomeNumt + a.MySum AS MySum
FROM @t AS t
JOIN abcd AS a ON a.id = t.id - 1
)
SELECT * FROM abcd
OPTION ( MAXRECURSION 1000 ) -- limit recursion here, or 0 for no limit.
Devoluciones:
id SomeNumt MySum
----------- ----------- -----------
1 10 10
2 12 22
3 3 25
4 15 40
5 23 63
Una vez que se crea la tabla,
select
A.id, A.SomeNumt, SUM(B.SomeNumt) as sum
from @t A, @t B where A.id >= B.id
group by A.id, A.SomeNumt
order by A.id
Select *, (Select SUM(SOMENUMT) From @t S Where S.id <= M.id) From @t M
Primero crea una tabla con datos ficticios ->
Create Table CUMULATIVESUM (id tinyint , SomeValue tinyint)
**Now let put some data in the table**
Insert Into CUMULATIVESUM
Select 1, 10 union
Select 2, 2 union
Select 3, 6 union
Select 4, 10
aquí me estoy uniendo a la misma mesa (SELF Joining)
Select c1.ID, c1.SomeValue, c2.SomeValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id >= c2.ID
Order By c1.id Asc
RESULTADO:
ID SomeValue SomeValue
1 10 10
2 2 10
2 2 2
3 6 10
3 6 2
3 6 6
4 10 10
4 10 2
4 10 6
4 10 10
aquí vamos ahora solo sumamos el Somevalue de t2 y obtendremos la ans
Select c1.ID, c1.SomeValue, Sum(c2.SomeValue) CumulativeSumValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id >= c2.ID
Group By c1.ID, c1.SomeValue
Order By c1.id Asc
PARA SQL SERVER 2012 y superior (mucho mejor rendimiento)
Select c1.ID, c1.SomeValue,
SUM (SomeValue) OVER (ORDER BY c1.ID )
From CumulativeSum c1
Order By c1.id Asc
Resultado deseado
ID SomeValue CumlativeSumValue
1 10 10
2 2 12
3 6 18
4 10 28
Drop Table CumulativeSum
Borrar la tabla dummy
select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum
from @t t1
inner join @t t2 on t1.id >= t2.id
group by t1.id, t1.SomeNumt
order by t1.id
Salida
| ID | SOMENUMT | SUM |
-----------------------
| 1 | 10 | 10 |
| 2 | 12 | 22 |
| 3 | 3 | 25 |
| 4 | 15 | 40 |
| 5 | 23 | 63 |
Editar: esta es una solución generalizada que funcionará en la mayoría de las plataformas de db. Cuando haya una mejor solución disponible para su plataforma específica (p. Ej., La de Gareth), úselo.