Рассчитать промежуточную сумму в SQL Server
Представьте себе следующую таблицу (называется 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
Я хотел бы запрос, который возвращает промежуточную сумму в порядке дат, например:
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
Я знаю, что есть разные способы сделать это в SQL Server 2000 / 2005 / 2008.
Я особенно заинтересован в таком методе, который использует трюк с агрегирующим набором операторов:
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
... это очень эффективно, но я слышал, что есть проблемы, связанные с этим, потому что вы не можете гарантировать, что UPDATE оператор будет обрабатывать строки в правильном порядке. Может быть, мы сможем получить некоторые окончательные ответы по этому вопросу.
Но, может быть, есть другие способы, которые люди могут предложить?
редактировать: теперь с SqlFiddle с настройкой и примером обновления трюк выше
14 ответов
Обновите, если вы используете SQL Server 2012, см. /questions/46159134/rasschitat-promezhutochnuyu-summu-v-sql-server/46159181#46159181
Проблема в том, что реализация SQL Server предложения Over несколько ограничена.
Oracle (и ANSI-SQL) позволяют вам делать такие вещи, как:
SELECT somedate, somevalue,
SUM(somevalue) OVER(ORDER BY somedate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM Table
SQL Server не дает вам чистого решения этой проблемы. Моя интуиция говорит мне, что это один из тех редких случаев, когда курсор является самым быстрым, хотя мне придется сделать несколько сравнительных тестов для больших результатов.
Уловка обновления удобна, но я чувствую ее довольно хрупкую. Кажется, что если вы обновляете полную таблицу, то она будет действовать в порядке первичного ключа. Так что если вы установите дату в качестве первичного ключа по возрастанию, вы будете probably быть в безопасности. Но вы полагаетесь на недокументированные детали реализации SQL Server (также, если запрос завершается выполнением двумя процессами, мне интересно, что произойдет, см.: MAXDOP):
Полный рабочий образец:
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
Вы просили эталон, это низкий уровень.
Самым быстрым БЕЗОПАСНЫМ способом сделать это был бы Курсор, он на порядок быстрее, чем коррелированный подзапрос перекрестного соединения.
Абсолютно быстрый способ - трюк ОБНОВЛЕНИЯ. Меня беспокоит только то, что я не уверен, что при любых обстоятельствах обновление будет происходить линейным образом. В запросе нет ничего, что прямо говорит об этом.
Итог, для производственного кода я бы пошел с курсором.
Тестовые данные:
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
Тест 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
Тест 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
Тест 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
Тест 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
Хотя Сэм Саффрон проделал большую работу над этим, он все еще не предоставил рекурсивный общий код табличного выражения для этой проблемы. А для нас, которые работают с SQL Server 2008 R2, а не с Denali, это все еще самый быстрый способ получить итоговое значение, он примерно в 10 раз быстрее, чем курсор на моем рабочем компьютере для 100000 строк, и это также встроенный запрос.
Итак, вот оно (я полагаю, что есть ord столбец в таблице и его порядковый номер без пробелов, для быстрой обработки также должно быть уникальное ограничение на этот номер):
;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
Обновление Мне также было интересно узнать об этом обновлении с переменным или необычным обновлением. Обычно это работает нормально, но как мы можем быть уверены, что это работает каждый раз? хорошо, вот небольшой трюк (нашел его здесь - http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx) - вы просто проверяете текущий и предыдущий ord и использовать 1/0 назначение в случае, если они отличаются от того, что вы ожидали:
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
Из того, что я видел, если у вас есть правильный кластеризованный индекс / первичный ключ в вашей таблице (в нашем случае это будет индекс по ord_id) обновление будет происходить линейно все время (никогда не встречалось деление на ноль). Тем не менее, вам решать, хотите ли вы использовать его в рабочем коде:)
Оператор APPLY в SQL 2005 и выше работает для этого:
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
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
Вы также можете использовать функцию ROW_NUMBER() и временную таблицу для создания произвольного столбца, который будет использоваться при сравнении внутреннего оператора SELECT.
Используйте коррелированный подзапрос. Очень просто, здесь вы идете:
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
Код может быть не совсем правильным, но я уверен, что идея такова.
GROUP BY - в случае, если дата появляется более одного раза, вы бы хотели видеть ее только один раз в наборе результатов.
Если вы не против увидеть повторяющиеся даты или хотите увидеть исходное значение и идентификатор, то вам нужно следующее:
SELECT
id,
somedate,
somevalue,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
ORDER BY somedate
Вы также можете денормализовать - сохранить промежуточные итоги в той же таблице:
Выбирает работу намного быстрее, чем любые другие решения, но модификации могут быть медленнее
Если вы используете Sql server 2008 R2 выше. Тогда это будет кратчайший путь;
Select id
,somedate
,somevalue,
LAG(runningtotal) OVER (ORDER BY somedate) + somevalue AS runningtotal
From TestTable
LAG используется для получения значения предыдущей строки. Вы можете сделать Google для получения дополнительной информации.
[1]:
Хотя лучший способ сделать это - использовать оконную функцию, это также можно сделать с помощью простого коррелированного подзапроса.
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;
Предполагая, что управление окнами работает на SQL Server 2008 так же, как и в других местах (что я пробовал), попробуйте:
select testtable.*, sum(somevalue) over(order by somedate)
from testtable
order by somedate;
MSDN говорит, что он доступен в SQL Server 2008 (а может быть, и в 2005 году?), Но у меня нет экземпляра, чтобы попробовать его.
РЕДАКТИРОВАТЬ: ну, очевидно, SQL Server не разрешает спецификацию окна ("OVER(...)") без указания "PARTITION BY" (деление результата на группы, но без агрегирования, как это делает GROUP BY). Раздражает - ссылка на синтаксис MSDN предполагает, что это необязательно, но в данный момент у меня есть только экземпляры SqlServer 2000.
Заданный мною запрос работает как в Oracle 10.2.0.3.0, так и в PostgreSQL 8.4-beta. Так что скажи MS наверстать;)
Вот 2 простых способа рассчитать промежуточную сумму:
Подход 1. Это можно написать так, если ваша СУБД поддерживает аналитические функции.
SELECT id
,somedate
,somevalue
,runningtotal = SUM(somevalue) OVER (ORDER BY somedate ASC)
FROM TestTable
Подход 2: вы можете использовать OUTER APPLY, если ваша версия базы данных / сама СУБД не поддерживает аналитические функции.
SELECT T.id
,T.somedate
,T.somevalue
,runningtotal = OA.runningtotal
FROM TestTable T
OUTER APPLY (
SELECT runningtotal = SUM(TI.somevalue)
FROM TestTable TI
WHERE TI.somedate <= S.somedate
) OA;
Примечание:- Если вам нужно рассчитать промежуточную сумму для разных разделов отдельно, это можно сделать, как указано здесь: Расчет промежуточных итогов по строкам и группировка по идентификатору
Я считаю, что промежуточный итог может быть достигнут с помощью простой операции INNER JOIN ниже.
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
Следующее даст необходимые результаты.
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
Наличие кластеризованного индекса на SomeDate значительно улучшит производительность.
Использование соединения Еще одним вариантом является использование соединения. Теперь запрос может выглядеть так:
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;
Для получения дополнительной информации вы можете посетить эту ссылку http://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12
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