Como obter a soma cumulativa
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
a opção acima devolve-me o seguinte.
id SomeNumt
1 10
2 12
3 3
4 15
5 23
Como consigo o seguinte
id srome CumSrome
1 10 10
2 12 22
3 3 25
4 15 40
5 23 63
14 answers
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
Resultado
| ID | SOMENUMT | SUM |
-----------------------
| 1 | 10 | 10 |
| 2 | 12 | 22 |
| 3 | 3 | 25 |
| 4 | 15 | 40 |
| 5 | 23 | 63 |
Edit: esta é uma solução generalizada que irá funcionar na maioria das plataformas db. Quando houver uma melhor solução disponível para sua plataforma específica (por exemplo, gareth), use-a!
A última versão do servidor SQL (2012) permite o seguinte.
SELECT
RowID,
Col1,
SUM(Col1) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM tablehh
ORDER BY RowId
Ou
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
Isto é ainda mais rápido. A versão particionada completa em 34 segundos mais de 5 milhões de linhas para mim.
Obrigado ao Peso, que comentou sobre o SQL Team thread referido em outra resposta.
Para o servidor SQL 2012 em diante pode ser fácil:
SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t
Porque ORDER BY
cláusula para SUM
por omissão significa RANGE UNBOUNDED PRECEDING AND CURRENT ROW
para a janela ("Observações gerais" em https://msdn.microsoft.com/en-us/library/ms189461.aspx)
Vamos primeiro criar uma tabela com dados fictícios -->
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
Aqui estou eu a juntar-me à mesma mesa (auto-adesão)
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
Aqui vamos nós somar o Somethingvalue de t2 e vamos buscar o 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 o servidor SQL 2012 e superior (Muito Melhor desempenho)
Select c1.ID, c1.SomeValue,
SUM (SomeValue) OVER (ORDER BY c1.ID )
From CumulativeSum c1
Order By c1.id Asc
Resultado Desejado
ID SomeValue CumlativeSumValue
1 10 10
2 2 12
3 6 18
4 10 28
Drop Table CumulativeSum
Limpar o manequim
Uma versão CTE, só por Diversão:
;
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.
O Devolve:
id SomeNumt MySum
----------- ----------- -----------
1 10 10
2 12 22
3 3 25
4 15 40
5 23 63
A geração cumulativa da Soma pode ser mais optimizada com a lógica CROSS APPLY
.
Funciona melhor que o INNER JOIN
& OVER Clause
quando analisado o plano 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
Select *, (Select SUM(SOMENUMT)
From @t S
Where S.id <= M.id)
From @t M
Existe uma implementação CTE muito mais rápida disponível neste excelente post: http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx
O problema neste tópico pode ser expresso assim.:
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.*
Uma vez criada a tabela -
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
Acima (pré-SQL12) vemos exemplos 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
Mais 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
Tenta isto
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;
Tenta isto:
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
A solução SQL que combina "linhas entre a linha anterior sem limites e a actual "e" soma " fez exactamente o que eu queria alcançar. Muito obrigado!
Se pode ajudar alguém, aqui está o meu caso. Eu queria cumular +1 em uma coluna sempre que um fabricante é encontrado como "algum fabricante" (exemplo). Caso contrário, não haverá aumento, mas sim um resultado anterior. Então este pedaço 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
Permitiu-me obter algo assim:
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
Explicação acima: começa a contagem de "algum criador" com 0, algum Criador é encontrado e nós fazemos +1. Para o Usuário 1, MakerC é encontrado para que não façamos +1, mas em vez de contagem vertical de algum fabricante é preso a 2 até a próxima linha.
O particionamento é pelo usuário, então quando mudamos o usuário, a contagem cumulativa volta a zero.
Estou no trabalho, não quero nenhum mérito nesta resposta, apenas diga obrigado e mostre o meu exemplo no caso de alguém estar na mesma situação. Eu estava tentando combinar soma e partição, mas a sintaxe incrível " linhas entre Sem limites A linha anterior e actual" completou a tarefa.
Obrigado!
Groaker
Sem usar qualquer tipo de salário cumulativo de adesão para uma pessoa buscar usando a seguinte 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