Cumulating value of current row + sum of previous

问题:

How would you do to transform a Column in a table from this:

ColumnA   ColumnB
2           a
3           b
4           c
5           d
1           a

to this:

ColumnA          ColumnB
3                 a
6(=3+3)           b   
10(=4+3+3)        c   
15(=5+4+3+3)      d 

I'm interested to see esp. what method you would pick.

回答1:

Like this:

;WITH cte
AS
(
   SELECT ColumnB, SUM(ColumnA) asum 
   FROM @t 
   gROUP BY ColumnB

), cteRanked AS
(
   SELECT asum, ColumnB, ROW_NUMBER() OVER(ORDER BY ColumnB) rownum
   FROM cte
) 
SELECT (SELECT SUM(asum) FROM cteRanked c2 WHERE c2.rownum <= c1.rownum),
  ColumnB
FROM cteRanked c1;

This should give you:

ColumnA    ColumnB
3             a
6             b
10            c
15            d

---

Here is a live demo

回答2:

I'd generally avoid trying to do so, but the following matches what you've asked for:

declare @T table (ColumnA int,ColumnB char(1))
insert into @T(ColumnA,ColumnB) values
(2    ,       'a'),
(3   ,        'b'),
(4  ,         'c'),
(5 ,          'd'),
(1,           'a')

;With Bs as (
    select distinct ColumnB from @T
)
select
    SUM(t.ColumnA),b.ColumnB
from
    Bs b
        inner join
    @T t
        on
            b.ColumnB >= t.ColumnB
group by
    b.ColumnB

Result:

            ColumnB
----------- -------
3           a
6           b
10          c
15          d

For small data sets, this will be fine. But for larger data sets, note that the last row of the table relies on obtaining the SUM over the entire contents of the original table.

回答3:

Not sure if this is optimal, but how about (SQL Fiddle):

SELECT x.A + COALESCE(SUM(y.A),0) ColumnA, x.ColumnB
FROM
(
    SELECT SUM(ColumnA) A, ColumnB
    FROM myTable
    GROUP BY ColumnB
) x
LEFT OUTER JOIN
(
    SELECT SUM(ColumnA) A, ColumnB
    FROM myTable
    GROUP BY ColumnB
) y ON y.ColumnB < x.ColumnB
GROUP BY x.ColumnB, x.A

回答4:

create table #T
(
  ID int primary key,
  ColumnA int,
  ColumnB char(1)
);

insert into #T
select row_number() over(order by ColumnB),
       sum(ColumnA) as ColumnA,
       ColumnB
from YourTable
group by ColumnB;

with C as
(
  select ID,
         ColumnA,
         ColumnB
  from #T
  where ID = 1
  union all
  select T.ID,
         T.ColumnA + C.ColumnA,
         T.ColumnB
  from #T as T
    inner join C
      on T.ID = C.ID + 1
)
select ColumnA,
       ColumnB 
from C
option (maxrecursion 0);

drop table #T;

回答5:

DECLARE @t TABLE(ColumnA INT, ColumnB VARCHAR(50));

    INSERT INTO @t VALUES
    (2,           'a'),
    (3  ,         'b'),
    (4   ,        'c'),
    (5    ,       'd'),
    (1     ,      'a');

    ;WITH cte
    AS
    (
        SELECT  ColumnB, sum(ColumnA) value,ROW_NUMBER() OVER(ORDER BY ColumnB) sr_no FROM @t group by ColumnB    
    )

    SELECT ColumnB
    ,SUM(value) OVER (   ORDER BY  ColumnB  ROWS BETWEEN UNBOUNDED PRECEDING  AND  0  PRECEDING) 
    FROM cte c1;

回答6:

Try the below script,

DECLARE @T TABLE(ColumnA INT, ColumnB VARCHAR(50));

INSERT INTO @T VALUES
    (2, 'a'),
    (3, 'b'),
    (4, 'c'),
    (5, 'd'),
    (1, 'a');

SELECT  SUM(ColumnA) OVER(ORDER BY ColumnB) AS ColumnA,ColumnB
FROM    (   SELECT  SUM(ColumnA) AS ColumnA,ColumnB
            FROM    @T  GROUP BY ColumnB )T

回答7:

Let think you have a table with 3 column C_1, C_2, C_3 and ordered by C_1. Simply use [Over (Order By C_1)] to add a column for sum of C_3:

Select C_1, C_2, C_3, Sum(C_3) Over (Order By C_1)

if you want row number too, do it in the same way:

Select Row_Number() Over (Order By C_1), C_1, C_2, C_3, Sum(C_3) Over (Order By C_1)

回答8:

You can use below simple select statement for the same

SELECT COLUMN_A, COLUMN_B, 
(SELECT SUM(COLUMN_B) FROM #TBL T2 WHERE T2.ID  <= T1.ID) as SumofPreviousRow FROM #TBL T1;