Как получить накопительную сумму
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
Приведенный выше выбор возвращает мне следующее.
id SomeNumt
1 10
2 12
3 3
4 15
5 23
Как я могу получить следующее
id srome CumSrome
1 10 10
2 12 22
3 3 25
4 15 40
5 23 63
14 ответов
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
Выход
| ID | SOMENUMT | SUM |
-----------------------
| 1 | 10 | 10 |
| 2 | 12 | 22 |
| 3 | 3 | 25 |
| 4 | 15 | 40 |
| 5 | 23 | 63 |
Редактировать: это обобщенное решение, которое будет работать на большинстве платформ БД. Когда есть лучшее решение для вашей конкретной платформы (например, Gareth), используйте его!
Последняя версия SQL Server (2012) допускает следующее.
SELECT
RowID,
Col1,
SUM(Col1) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM tablehh
ORDER BY RowId
или же
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
Это еще быстрее. Разбитая версия завершается за 34 секунды для меня более 5 миллионов строк.
Спасибо Peso, который прокомментировал поток SQL Team, упомянутый в другом ответе.
Для SQL Server 2012 и более поздних версий это может быть легко:
SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t
так как ORDER BY пункт для SUM по умолчанию означает RANGE UNBOUNDED PRECEDING AND CURRENT ROW для оконной рамы ("Общие замечания" по адресу https://msdn.microsoft.com/en-us/library/ms189461.aspx)
Давайте сначала создадим таблицу с фиктивными данными ->
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
здесь я присоединяюсь к той же таблице (SELF Joining)
Select c1.ID, c1.SomeValue, c2.SomeValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id >= c2.ID
Order By c1.id Asc
РЕЗУЛЬТАТ:
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
здесь мы идем теперь просто суммируем Somevalue t2, и мы получим ответ
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
ДЛЯ SQL SERVER 2012 и выше (намного эффективнее)
Select c1.ID, c1.SomeValue,
SUM (SomeValue) OVER (ORDER BY c1.ID )
From CumulativeSum c1
Order By c1.id Asc
Желаемый результат
ID SomeValue CumlativeSumValue
1 10 10
2 2 12
3 6 18
4 10 28
Drop Table CumulativeSum
Очистить dummytable
Версия CTE, просто для удовольствия:
;
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.
Возвращает:
id SomeNumt MySum
----------- ----------- -----------
1 10 10
2 12 22
3 3 25
4 15 40
5 23 63
Поздний ответ, но еще одна возможность...
Совокупная генерация сумм может быть более оптимизирована с CROSS APPLY логика.
Работает лучше чем INNER JOIN & OVER Clause когда проанализировал фактический план запроса...
/* 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
Вы можете использовать этот простой запрос для прогрессивного расчета:
select
id
,SomeNumt
,sum(SomeNumt) over(order by id ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as CumSrome
from @t
В этом превосходном посте доступна гораздо более быстрая реализация CTE: http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx
Проблема в этой теме может быть выражена так:
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.*
Например: ЕСЛИ у вас есть таблица с двумя столбцами, один - это идентификатор, а второй - номер, и вы хотите узнать совокупную сумму.
SELECT ID,Number,SUM(Number)OVER(ORDER BY ID) FROM T
Выше (до SQL12) мы видим такие примеры:
SELECT
T1.id, SUM(T2.id) AS CumSum
FROM
#TMP T1
JOIN #TMP T2 ON T2.id < = T1.id
GROUP BY
T1.id
Более эффективным...
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
После того, как таблица создана -
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
Решение SQL, которое сочетает в себе "строки между незанятым PRECEDING и CURRENT ROW" и "SUM", сделало именно то, что я хотел достичь. Спасибо вам большое!
Если это может кому-нибудь помочь, вот мой случай. Я хотел накапливать +1 в столбце всякий раз, когда создатель определяется как "Some Maker" (пример). Если нет, то нет приращения, но отображается предыдущий результат приращения.
Итак, этот кусок 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
Позволил мне получить что-то вроде этого:
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
Объяснение выше: счетчик "Some Maker" начинается с 0, Some Maker найден, и мы делаем +1. Для пользователя 1 MakerC найден, поэтому мы не делаем +1, но вместо этого вертикальный отсчет Some Maker привязывается к 2 до следующего ряда. Разделение выполняется пользователем, поэтому при смене пользователя совокупный счет возвращается к нулю.
Я на работе, я не хочу никаких заслуг в этом ответе, просто скажите спасибо и покажите мой пример на случай, если кто-то окажется в такой же ситуации. Я пытался объединить SUM и PARTITION, но удивительный синтаксис "ROWS МЕЖДУ UNBOUNDED PRECEDING И CURRENT ROW" выполнил задачу.
Спасибо! Groaker
Попробуй это:
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
Попробуй это
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;
Без использования какого-либо совокупного оклада JOIN для человека, полученного с помощью следующего запроса:
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