Блог

Главная - Вопросы по программированию - Добавить столбцы накопления в SQL

Добавить столбцы накопления в SQL

#sql #sql-server

#sql #sql-сервер

Вопрос:

Это моя таблица в SQL Server :

 Date            Number    weight
----------------------------------
2020-06-21      20         185
2020-06-21      10         90 
2020-06-22      50         289
2020-06-22      20         175 
2020-06-23      30         201
2020-06-24      10         95
2020-06-25      20         190

и я хочу, чтобы этот вывод был на SQL Server

 Date            Number  Sum(Number)  weight  Sum(weight)
--------------------------------------------------------
2020-06-21      30          30        275        275
2020-06-22      70          100       464        739
2020-06-23      30          130       201        940
2020-06-24      10          140       95         1035
2020-06-25      20          160       190        1225

Кто-нибудь знает, как я могу это сделать?

Комментарии:

1. Вы вообще что-нибудь пробовали???

Ответ №1:

Вам нужна совокупная сумма. Который использует оконные функции:

 select date, number, sum(number) over (order by date) as running_sum_number,
       weight, sum(weight) over (order by date) as running_sum_weight
from t
order by date;

Ответ №2:

Вы можете использовать любое решение, приведенное ниже, тремя различными способами:

 create table TblData ([Date] date, [Number] int, [weight] int)

insert into TblData values
('2020-06-21',20,185),
('2020-06-21',10,90 ),
('2020-06-22',50,289),
('2020-06-22',20,175) ,
('2020-06-23',30,201),
('2020-06-24',10,95 ),
('2020-06-25',20,190)

Способ 1:

 select [Date]  
 , number  
 , SUM(number) OVER(ORDER BY [Date]  
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumNumber
 , weight  
 , SUM(weight) OVER(ORDER BY [Date]  
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumWeight
from (
 Select date
  , sum(number) as [Number]
  , sum(weight) as [Weight]
  from TblData
  group by date
)TblData

Способ 2:

 select [Date]
 , number  
 , (SELECT SUM(number) FROM TblData t2 WHERE t2.[Date] <= TblData.[Date]) AS SumNumber
 , weight  
 , (SELECT SUM(weight) FROM TblData t2 WHERE t2.[Date] <= TblData.[Date]) AS SumWeight  
from
(
  Select date
  , sum(number) as [Number]
  , sum(weight) as [Weight]
  from TblData
  group by date
)
TblData

Способ 3:

 ;with cte as(  
  Select *  
  , ROW_NUMBER() OVER (ORDER BY [Date]) AS RN  
  from 
  (
   Select date
   , sum(number) as [Number]
   , sum(weight) as [Weight]
   from TblData
   group by date
  )tbl  
)select [Date]  
 , [Number]  
 , (SELECT SUM([Number]) FROM cte t2 WHERE t2.RN <= cte.RN) AS SumNumber
 , [Weight]  
 , (SELECT SUM([Weight]) FROM cte t2 WHERE t2.RN <= cte.RN) AS SumWeight
from cte

Вот живая демонстрация db<>fiddle.

Ответ №3:

Попробуйте приведенный ниже код для получения желаемого результата,

 declare @tbl as table([Date] date,Number  int,   [weight]  int)
----------------------------------
insert into @tbl values
('2020-06-21',20,185),
('2020-06-21',10,90 ),
('2020-06-22',50,289),
('2020-06-22',20,175) ,
('2020-06-23',30,201),
('2020-06-24',10,95 ),
('2020-06-25',20,190)

;with tblCTE
as
(
select date, sum(number) as number,sum(weight) as weight from @tbl group by date
)
select Date, Number, sum(number) over (order by date) as [Sum(Number)],
       weight, sum(weight) over (order by date) as [Sum(weight)]
from tblCTE 
order by date;