Cum se obține suma cumulativă (Programare, Server Sql, Sql Server 2008)

ps. a intrebat.
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

selectul de mai sus îmi returnează următoarele.

id  SomeNumt
1   10
2   12
3   3
4   15
5   23

Cum pot obține următoarele:

id  srome   CumSrome
1   10  10
2   12  22
3   3   25
4   15  40
5   23  63

Comentarii

  • Obținerea totalurilor curente în T-SQL nu este dificilă, există multe răspunsuri corecte, majoritatea destul de ușoare. Ceea ce nu este ușor (sau chiar posibil în acest moment) este să scriu o interogare adevărată în T-SQL pentru totalurile curente care să fie eficientă. Toate sunt O(n^2), deși ar putea fi cu ușurință O(n), cu excepția faptului că T-SQL nu optimizează pentru acest caz. Puteți obține O(n) folosind cursoare și/sau bucle While, dar atunci folosiți cursoare. (blech!) –  > Por RBarryYoung.
16 răspunsuri
D’Arcy Rittich
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

Exemplu SQL Fiddle

Ieșire

| ID | SOMENUMT | SUM |
-----------------------
|  1 |       10 |  10 |
|  2 |       12 |  22 |
|  3 |        3 |  25 |
|  4 |       15 |  40 |
|  5 |       23 |  63 |

Editați: aceasta este o soluție generalizată care va funcționa pe majoritatea platformelor db. Atunci când există o soluție mai bună disponibilă pentru platforma dvs. specifică (de exemplu, cea a lui Gareth), folosiți-o!

Comentarii

  • @Franklin Eficientă din punct de vedere al costurilor doar pentru tabelele mici. Costul crește proporțional cu pătratul numărului de rânduri. SQL Server 2012 permite ca acest lucru să fie realizat mult mai eficient. –  > Por Martin Smith.
  • FWIW, un DBA mi-a dat o palmă când am făcut acest lucru. Cred că motivul este că devine foarte scump, foarte repede. Acestea fiind spuse, aceasta este o întrebare excelentă pentru interviu, deoarece majoritatea analiștilor de date/științifici ar fi trebuit să rezolve această problemă o dată sau de două ori 🙂 –  > Por BenDundee.
  • @BenDundee De acord – am tendința de a oferi soluții SQL generalizate care vor funcționa pe majoritatea platformelor db. Ca întotdeauna, atunci când există o abordare mai bună disponibilă, de exemplu, gareths, folosiți-o! –  > Por D’Arcy Rittich.
Gareth Adamson

Cea mai recentă versiune a SQL Server (2012) permite următoarele.

SELECT 
    RowID, 
    Col1,
    SUM(Col1) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM tablehh
ORDER BY RowId

sau

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

Acest lucru este chiar mai rapid. Versiunea partiționată se finalizează în 34 de secunde peste 5 milioane de rânduri pentru mine.

Mulțumesc lui Peso, care a comentat pe firul SQL Team la care se face referire într-un alt răspuns.

Comentarii

    23

  • Pentru concizie, puteți utiliza ROWS UNBOUNDED PRECEDING în loc de ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. –  > Por Dan.
  • Notă: Dacă coloana pe care doriți să o însumați în mod cumulativ este deja o sumă sau un număr, puteți fie să înfășurați totul ca o interogare interioară, fie să faceți de fapt SUM(COUNT(*)) OVER (ORDER BY RowId ROWS UNBOUNDED PRECEDING) AS CumulativeSum. Nu mi s-a părut imediat evident că va funcționa, dar a funcționat 🙂 –  > Por Simon_Weaver.
  • Disponibil în PostgreSQL începând cu versiunea 8.4: postgresql.org/docs/8.4/sql-select.html –  > Por ADJenks.
Andrew Karakotov

Pentru SQL Server 2012 și mai departe ar putea fi ușor:

SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t

deoarece ORDER BY pentru SUM în mod implicit înseamnă RANGE UNBOUNDED PRECEDING AND CURRENT ROW pentru cadrul ferestrei („Observații generale” la https://msdn.microsoft.com/en-us/library/ms189461.aspx)

Neeraj Prasad Sharma

Să creăm mai întâi un tabel cu date fictive –>

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 

aici mă alătur aceluiași tabel (SELF Joining)

Select c1.ID, c1.SomeValue, c2.SomeValue
From CumulativeSum c1,  CumulativeSum c2
Where c1.id >= c2.ID
Order By c1.id Asc

REZULTAT :

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

Iată că acum trebuie doar să adunăm valoarea Somevalue din t2 și vom obține răspunsul.

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

PENTRU SQL SERVER 2012 și versiunile superioare (performanță mult mai bună)

Select c1.ID, c1.SomeValue, 
SUM (SomeValue) OVER (ORDER BY c1.ID )
From CumulativeSum c1
Order By c1.id Asc

Rezultatul dorit

ID  SomeValue   CumlativeSumValue
1   10          10
2   2           12
3   6           18
4   10          28

Drop Table CumulativeSum

Ștergeți tabelul fictiv

Comentarii

  • vă rugăm să vă editați răspunsul și să formatați codul pentru a-l face lizibil –  > Por kleopatra.
  • Ce se întâmplă dacă valorile mi „ID” se repetă? (acestea nu sunt, evident, cheie primară în tabelul meu) Nu am reușit să adaptez această interogare la acest caz? –  > Por pablete.
  • AFAIK aveți nevoie de un id unic pentru suma cumulativă , și îl puteți obține folosind row_number. verificați acel cod de mai jos: ;with NewTBLWITHUNiqueID as ( select row_number() over(order by id , somevalue) UniqueID , * From CUMULATIVESUMwithoutPK ) – –  > Por Neeraj Prasad Sharma.
  • Mulțumesc @NeerajPrasadSharma, de fapt am folosit rank() și o altă clauză order by pentru a o rezolva. –  > Por pablete.
Damir Sudarevic

O versiune CTE, doar pentru distracție:

;
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.

Returnări:

id          SomeNumt    MySum
----------- ----------- -----------
1           10          10
2           12          22
3           3           25
4           15          40
5           23          63

Aditya

Răspuns târziu, dar care arată încă o posibilitate…

Generarea sumei cumulative poate fi mai optimizată cu ajutorul funcției CROSS APPLY logică.

Funcționează mai bine decât INNER JOIN & OVER Clause atunci când se analizează planul de interogare 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

Comentarii

  • Nu sunt convins. „Costul interogării în raport cu lotul” este un lucru lipsit de sens pentru compararea performanțelor interogărilor. Costurile de interogare sunt estimări folosite de planificatorul de interogări pentru a cântări rapid diferite planuri și a alege cel mai puțin costisitor, dar aceste costuri sunt pentru compararea planurilor pentru aceeași interogare, și nu sunt relevante sau comparabile. între interogări, sau deloc. Acest eșantion de date este, de asemenea, prea mic pentru a vedea vreo diferență semnificativă între cele trei metode. Încercați din nou cu 1 milion de rânduri, uitați-vă la planurile de execuție reale, încercați cu set io statistics on și comparați timpii de procesare și cei reali. –  > Por Davos.
Ritesh Khatri

Select *, (Select SUM(SOMENUMT)
From @t S
Where S.id <= M.id)
From @t M

Comentarii

  • Este un mod foarte inteligent de a obține rezultatul și puteți adăuga mai multe condiții la sumă. –  > Por RaRdEvA.
  • @RaRdEvA Totuși, nu este grozav pentru performanță, rulează că correlated subquery pentru fiecare rând din setul de rezultate, scanând din ce în ce mai multe rânduri pe parcurs. Nu păstrează un total curent și nu scanează datele o singură dată, așa cum pot face funcțiile de fereastră. –  > Por Davos.
  • @Davos aveți dreptate, dacă îl folosiți devine foarte lent peste 100.000 de înregistrări. –  > Por RaRdEvA.
Afif Pratama

Puteți utiliza această interogare simplă pentru calculul progresiv :

select 
   id
  ,SomeNumt
  ,sum(SomeNumt) over(order by id ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as CumSrome
from @t

cezarm

Există o implementare CTE mult mai rapidă disponibilă în această postare excelentă:http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx

Problema din acest fir poate fi exprimată astfel:

    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.*

Porsh

Odată ce tabelul este creat –

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

Julian

Mai sus (Pre-SQL12) vedem exemple de genul acesta: –

SELECT
    T1.id, SUM(T2.id) AS CumSum
FROM 
    #TMP T1
    JOIN #TMP T2 ON T2.id < = T1.id
GROUP BY
    T1.id

Mai eficient…

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

bellonid

Încercați acest lucru

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;

Comentarii

  • Acest lucru funcționează cu SQL Server 2012 și mai sus, 2008 are un suport limitat pentru funcțiile de fereastră. –  > Por Peter Smit.
Sachin

Încercați acest lucru:

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

Groaker

Soluția SQL care combină „ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW” și „SUM” a făcut exact ceea ce am vrut să realizez. vă mulțumesc foarte mult!

Dacă poate ajuta pe cineva, iată care a fost cazul meu. Am vrut să cumulez +1 într-o coloană ori de câte ori un producător este găsit ca „Some Maker” (exemplu). În caz contrar, nu se incrementează, dar se afișează rezultatul incrementului anterior.

Deci, această bucată 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

Mi-a permis să obțin ceva de genul acesta:

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ția de mai sus: Începe numărătoarea pentru „un anumit producător” cu 0, se găsește un anumit producător și se face +1. Pentru utilizatorul 1, se găsește MakerC, așa că nu se face +1, ci numărătoarea verticală a „Some Maker” rămâne la 2 până la următorul rând.

Sunt la serviciu, nu vreau niciun merit pe acest răspuns, doar să vă mulțumesc și să arăt exemplul meu în cazul în care cineva se află în aceeași situație. Am încercat să combin SUM și PARTITION, dar sintaxa uimitoare „ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW” a finalizat sarcina.

Mulțumesc!Groaker

Rudar Daman Singla

Fără a utiliza niciun tip de JOIN, salariul cumulat pentru o persoană se obține utilizând următoarea interogare:

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

J P

De exemplu: Dacă aveți un tabel cu două coloane, una este ID și a doua este numărul și doriți să aflați suma cumulată.

SELECT ID,Number,SUM(Number)OVER(ORDER BY ID) FROM T