Oblicz sumę bieżącą w programie SQL Server

Wyobraź sobie następującą tabelę (zwaną 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

Chciałbym zapytać, które zwraca sumę bieżącą w kolejności dat, na przykład:

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

Wiem, że w SQL Server 2000/2005/2008 można to zrobić na różne sposoby .

Szczególnie interesuje mnie tego rodzaju metoda, która wykorzystuje sztuczkę agregującą zestaw instrukcji:

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

... jest to bardzo wydajne, ale słyszałem, że istnieją problemy z tym związane, ponieważ nie zawsze można zagwarantować, że UPDATEinstrukcja przetworzy wiersze we właściwej kolejności. Może uda nam się uzyskać ostateczne odpowiedzi na ten temat.

Ale może istnieją inne sposoby, które ludzie mogą zasugerować?

edycja: Teraz z SqlFiddle z konfiguracją i przykładem „sztuczki z aktualizacją” powyżej

Aktualizacja , jeśli używasz programu SQL Server 2012, zobacz: https://stackoverflow.com/a/10309947

Problem polega na tym, że implementacja klauzuli Over w SQL Server jest nieco ograniczona .

Oracle (i ANSI-SQL) pozwalają na:

 SELECT somedate, somevalue,
  SUM(somevalue) OVER(ORDER BY somedate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM Table

SQL Server nie zapewnia prostego rozwiązania tego problemu. Moje przeczucie podpowiada mi, że jest to jeden z tych rzadkich przypadków, w których kursor jest najszybszy, chociaż będę musiał przeprowadzić pewne testy porównawcze dla dużych wyników.

Sztuczka aktualizacji jest przydatna, ale czuję, że jest dość delikatna. Wygląda na to, że jeśli aktualizujesz pełną tabelę, będzie to postępować w kolejności klucza podstawowego. Więc jeśli ustawisz datę jako klucz podstawowy rosnąco, będziesz probablybezpieczny. Ale polegasz na nieudokumentowanych szczegółach implementacji SQL Server (również jeśli zapytanie zostanie wykonane przez dwa procesy, zastanawiam się, co się stanie, zobacz: MAXDOP):

Pełna próbka robocza:

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

Poprosiłeś o test porównawczy, to jest podsumowanie.

Najszybszym BEZPIECZNYM sposobem zrobienia tego byłby Cursor, jest to o rząd wielkości szybsze niż skorelowane zapytanie podrzędne przy sprzężeniu krzyżowym.

Absolutnie najszybszym sposobem jest sztuczka UPDATE. Obawiam się tylko, że nie jestem pewien, czy w każdych okolicznościach aktualizacja będzie przebiegać liniowo. W zapytaniu nie ma nic, co wyraźnie to mówi.

Podsumowując, w przypadku kodu produkcyjnego podążałbym za kursorem.

Dane testowe:

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

Test 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 

Test 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

Test 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

Test 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

W SQL Server 2012 można używać funkcji SUM () z klauzulą OVER () .

select id,
       somedate,
       somevalue,
       sum(somevalue) over(order by somedate rows unbounded preceding) as runningtotal
from TestTable

SQL Fiddle

Chociaż Sam Saffron wykonał świetną robotę, nadal nie dostarczył rekurencyjnego wspólnego kodu wyrażeń tabelowych dla tego problemu. A dla nas, którzy pracujemy z SQL Server 2008 R2, a nie z Denali, jest to nadal najszybszy sposób na uzyskanie całkowitej liczby danych, jest około 10 razy szybszy niż kursor na moim komputerze roboczym dla 100000 wierszy, a także jest to zapytanie wbudowane.
A więc oto jest (przypuszczam, że ordw tabeli jest kolumna i jest to numer kolejny bez przerw, dla szybkiego przetwarzania również powinno być unikalne ograniczenie dla tej liczby):

;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

sql fiddle demo

aktualizacja Byłem również ciekawy tej aktualizacji ze zmienną lub dziwaczną aktualizacją . Czyli zwykle działa dobrze, ale skąd możemy mieć pewność, że działa za każdym razem? cóż, oto mała sztuczka (znalazłem ją tutaj - http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258 ) - po prostu sprawdzasz bieżące i poprzednie ordi używasz 1/0przypisania na wypadek, gdyby różniły się od tego spodziewasz się:

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

Z tego, co widziałem, jeśli masz odpowiedni indeks klastrowy / klucz podstawowy na swojej tabeli (w naszym przypadku byłby to indeks przez ord_id), aktualizacja będzie przebiegać w sposób liniowy przez cały czas (nigdy nie napotkano dzielenia przez zero). To powiedziawszy, to do Ciebie należy decyzja, czy chcesz użyć go w kodzie produkcyjnym :)

aktualizacja 2 Łączę tę odpowiedź, ponieważ zawiera przydatne informacje o zawodności dziwacznej aktualizacji - niewytłumaczalne zachowanie nvarchar concatenation / index / nvarchar (max) .

W tym celu działa operator APPLY w SQL 2005 i nowszych wersjach:

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

Możesz również użyć funkcji ROW_NUMBER () i tabeli tymczasowej, aby utworzyć dowolną kolumnę do użycia w porównaniu w wewnętrznej instrukcji SELECT.

Użyj skorelowanego zapytania podrzędnego. Bardzo proste, proszę bardzo:

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

Kod może nie być dokładnie poprawny, ale jestem pewien, że tak jest.

GROUP BY jest w przypadku, gdy data pojawia się więcej niż raz, chciałbyś zobaczyć ją tylko raz w zestawie wyników.

Jeśli nie masz nic przeciwko wyświetlaniu powtarzających się dat lub chcesz zobaczyć oryginalną wartość i identyfikator, to chcesz:

SELECT 
id,
somedate, 
somevalue,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
ORDER BY somedate

Możesz również zdenormalizować - przechowywać bieżące sumy w tej samej tabeli:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/denormalizing-to-enforce-business-rules-running-totals.aspx

Wybory działają znacznie szybciej niż jakiekolwiek inne rozwiązania, ale modyfikacje mogą być wolniejsze

Zakładając, że okienkowanie działa na SQL Server 2008 tak samo jak gdzie indziej (co próbowałem), spróbuj:

select testtable.*, sum(somevalue) over(order by somedate)
from testtable
order by somedate;

MSDN twierdzi, że jest dostępny w SQL Server 2008 (a może także 2005?), Ale nie mam instancji, aby ją wypróbować.

EDYCJA: cóż, najwyraźniej SQL Server nie zezwala na specyfikację okna („OVER (...)”) bez określenia „PARTITION BY” (dzielenie wyniku na grupy, ale nie agregowanie w taki sposób, jak robi to GROUP BY). Irytujące - odwołanie do składni MSDN sugeruje, że jest to opcjonalne, ale w tej chwili mam tylko wystąpienia SqlServer 2000.

Zapytanie, które podałem, działa zarówno w Oracle 10.2.0.3.0, jak i PostgreSQL 8.4-beta. Więc powiedz MS, żeby nadrobiło zaległości;)

Jeśli używasz Sql Server 2008 R2 powyżej. Wtedy byłaby to najkrótsza droga;

Select id
    ,somedate
    ,somevalue,
LAG(runningtotal) OVER (ORDER BY somedate) + somevalue AS runningtotal
From TestTable 

LAG służy do uzyskania wartości poprzedniego wiersza. Możesz zrobić Google, aby uzyskać więcej informacji.

[1]:

Uważam, że bieżącą sumę można osiągnąć za pomocą prostej operacji INNER JOIN poniżej.

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

Poniżej przedstawiono wymagane wyniki.

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

Posiadanie indeksu klastrowego w SomeDate znacznie poprawi wydajność.

Używanie złączenia Inną odmianą jest użycie złączenia. Teraz zapytanie mogłoby wyglądać następująco:

    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;

po więcej możesz odwiedzić ten link http://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12

Chociaż najlepszym sposobem jest użycie funkcji okna, można to również zrobić za pomocą prostego skorelowanego zapytania podrzędnego .

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;

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

Oto 2 proste sposoby obliczenia sumy bieżącej:

Podejście 1 : Można to zapisać w ten sposób, jeśli Twój DBMS obsługuje funkcje analityczne

SELECT     id
           ,somedate
           ,somevalue
           ,runningtotal = SUM(somevalue) OVER (ORDER BY somedate ASC)
FROM       TestTable

Podejście 2 : Możesz skorzystać z APLIKACJI ZEWNĘTRZNEJ, jeśli Twoja wersja bazy danych / sam DBMS nie obsługuje funkcji analitycznych

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;

Uwaga: - Jeśli musisz obliczyć sumę bieżącą dla różnych partycji osobno, można to zrobić w sposób opisany tutaj: Obliczanie sum bieżących w wierszach i grupowanie według identyfikatora