Совокупный подсчет с группировкой по и случаем, когда

Я искал это и пытался заставить этот код работать уже неделю.

В моем наборе данных tbSubscriptions есть столбцы:

Subscription_Date (dd/mm/yyyy hh:mm:ss)  
Subscription_Id (char 6)  
Subscription_Type (char 1)

Чтобы получить количество подписок в неделю, я использую:

select   
   datepart(wk,Subscription_Date) as WeekNo,   
   sum(case when Subscription_Type = 1 then 1 else 0 end) as TotalSubcriptions  
from tbSubscriptions  
group by datepart(wk,Subscription_Date)  
order by 1

Этот запрос возвращает:

WeekNo  |Total Subscriptions  
21      |12  
22      |13  
23      |8  
24      |18

Мне нужен запрос, который возвращает:

WeekNo  |CumulativeSubscriptions  
21      |12  
22      |25 (=12+13)  
23      |33 (=25+8)  
24      |51 (=33+18)

Вот пример сценария создания набора данных:

GO
IF OBJECT_ID('tbSubscriptions') IS NOT NULL
  DROP TABLE tbSubscriptions
GO
CREATE TABLE tbSubscriptions (Subscription_Id INT, Subscription_Date datetime, Subscription_Type INT)
GO 
INSERT INTO tbSubscriptions (Subscription_Id, Subscription_Date, Subscription_Type) 
VALUES
(1, convert(datetime,'01-08-16 00:00:00 AM',5),1), 
(2, convert(datetime,'15-08-16 00:00:00 AM',5),1), 
(3, convert(datetime,'01-09-16 00:00:00 AM',5),1), 
(4, convert(datetime,'09-09-16 00:00:00 AM',5),1), 
(5, convert(datetime,'18-09-16 00:00:00 AM',5),0), 
(6, convert(datetime,'15-10-16 00:00:00 AM',5),1), 
(7, convert(datetime,'22-10-16 00:00:00 AM',5),0), 
(8, convert(datetime,'23-10-16 00:00:00 AM',5),0), 
(9, convert(datetime,'01-11-16 00:00:00 AM',5),1), 
(10, convert(datetime,'02-11-16 00:00:00 AM',5),1), 
(11, convert(datetime,'14-11-16 00:00:00 AM',5),0), 
(12, convert(datetime,'01-12-16 00:00:00 AM',5),1), 
(13, convert(datetime,'02-12-16 00:00:00 AM',5),1), 
(14, convert(datetime,'05-12-16 00:00:00 AM',5),1), 
(15, convert(datetime,'09-12-16 00:00:00 AM',5),1), 
(16, convert(datetime,'10-12-16 00:00:00 AM',5),1), 
(17, convert(datetime,'11-12-16 00:00:00 AM',5),1), 
(18, convert(datetime,'19-12-16 00:00:00 AM',5),0),
(19, convert(datetime,'25-12-16 00:00:00 AM',5),0),
(20, convert(datetime,'29-12-16 00:00:00 AM',5),0);
GO

Я пробовал оба метода для кумулятивной суммы (функции окна и самостоятельные соединения), но не смог приступить к работе.

Любая помощь приветствуется.

С уважением, Пауло.


sql sql-server sql-server-2008 cumulative-sum
person João Alqueres    schedule 04.12.2016    source источник
comment
ЭТО РАБОТАЛО С SELECT datepart(wk,Subscription_Date), ( SELECT Count(Subscription_Id) FROM tbSubscriptions t2 WHERE datepart(wk,t2.Subscription_Date)‹=datepart(wk,t1.Subscription_Date) и t2.Subscription_Type=1) AS running_total FROM tbSubscriptions t1 ГДЕ t1.Subscription_Type= 1 ГРУППИРОВАТЬ ПО datepart(wk,t1.Subscription_Date) ORDER BY datepart(wk,t1.Subscription_Date)   -  person João Alqueres    schedule 05.12.2016

Ответы (4)


arrow_upward
0
arrow_downward

Вы можете использовать CROSS APPLY:

with cte as(
    select   
        datepart(wk,Subscription_Date) as WeekNo,   
        sum(case when Subscription_Type = 1 then 1 else 0 end) as TotalSubcriptions
    from tbSubscriptions  
    group by datepart(wk,Subscription_Date)  
)
select
    c.Weekno,
    t.TotalSubcriptions
from cte c
cross apply(
    select sum(TotalSubcriptions) as TotalSubcriptions
    from cte
    where WeekNo <= c.WeekNo
) t
order by WeekNo

Если вы используете SQL Server 2012 и выше, вы можете использовать функцию SUM OVER:

with cte as(
    select   
       datepart(wk,Subscription_Date) as WeekNo,   
       sum(case when Subscription_Type = 1 then 1 else 0 end) as TotalSubcriptions  
    from tbSubscriptions  
    group by datepart(wk,Subscription_Date)  
)
select
    Weekno,
    sum(TotalSubcriptions) over(order by WeekNo) as CumulativeSubscriptions
from cte
order by WeekNo
person Felix Pamittan    schedule 04.12.2016
comment
Спасибо, но я использую Microsoft SQL Server 2008 R2, и, похоже, он не поддерживает запуск агрегатов с OVER (ORDER BY...) Подробнее здесь: stackoverflow.com/questions/12541355/ Есть мысли? - person João Alqueres; 05.12.2016
comment
@JoãoAlqueres Попробуйте решение CROSS APPLY. - person Felix Pamittan; 05.12.2016
comment
Спасибо Феликс! Он работал с другим методом @KthProg в stackoverflow.com/questions/860966/ В любом случае большое спасибо! - person João Alqueres; 05.12.2016

arrow_upward
0
arrow_downward

Просто используйте оконную функцию sum.

select distinct 
   datepart(wk,Subscription_Date) as WeekNo,   
   sum(case when Subscription_Type = 1 then 1 else 0 end) 
   over(partition by datepart(wk,Subscription_Date) 
        order by datepart(wk,Subscription_Date)) as TotalSubcriptions  
from tbSubscriptions  
--group by datepart(wk,Subscription_Date)  
order by 1

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

select datepart(week,subscription_date) wk
,count(case when subscription_type=1 then 1 end) + 
(select count(case when subscription_type=1 then 1 end) 
 from tbsubscriptions t1 
 where datepart(week,t.subscription_date)>datepart(week,t1.subscription_date)) cnt
from tbsubscriptions t
group by datepart(week,subscription_date)
person Vamsi Prabhala    schedule 04.12.2016
comment
Спасибо, но я использую Microsoft SQL Server 2008 R2, и, похоже, он не поддерживает запуск агрегатов с OVER (ORDER BY...) Подробнее здесь: stackoverflow.com/questions/12541355/ Есть мысли? - person João Alqueres; 05.12.2016

arrow_upward
0
arrow_downward

Вместо условной агрегации, как насчет того, чтобы поместить Subscription_Type в WHERE

Select Distinct 
       WeekNo            = datepart(wk,Subscription_Date)
      ,TotalSubcriptions = sum(1) over (Order By datepart(wk,Subscription_Date))
From   tbSubscriptions  
Where  Subscription_Type = 1
Order  By 1

Редактировать версию 2008

;with cte as (
      Select WeekNo=datepart(wk,Subscription_Date)
            ,Cnt=count(*)
      From   tbSubscriptions
      Where  Subscription_Type = 1
      Group By datepart(wk,Subscription_Date)
) 
Select A.WeekNo 
      ,TotalSubcriptions = sum(B.Cnt)
 From  cte A
 Join  cte B on (A.WeekNo>=B.WeekNo)
 Group By A.WeekNo
 Order By 1

Возвращает

WeekNo  TotalSubcriptions
32      1
34      2
36      3
37      4
42      5
45      7
49      9
50      12
51      13
person John Cappelletti    schedule 04.12.2016
comment
Спасибо, но я использую Microsoft SQL Server 2008 R2, и, похоже, он не поддерживает запуск агрегатов с OVER (ORDER BY...) Подробнее здесь: stackoverflow.com/questions/12541355/ Есть мысли? - person João Alqueres; 05.12.2016
comment
@JoãoAlqueres Достаточно честно. К вашему сведению, когда вы помечаете SQL Server, обычно предполагается, что в настоящее время поддерживаются версии 2012+. Просто для дальнейшего использования, просто не забудьте упомянуть 2008 год. - person John Cappelletti; 05.12.2016

arrow_upward
0
arrow_downward

Версия SQL Server 2008

with cte as (
   select
    datepart(year,Subscription_Date) as YearNo,
    datepart(week,Subscription_Date) as WeekNo,
    sum(case when Subscription_Type = 1 then 1 else 0 end) as Total
   from tbSubscriptions
   group by datepart(year,Subscription_Date), datepart(week,Subscription_Date)
)
select 
--t1.YearNo,
t1.WeekNo,
sum(t2.Total) as TotalSubcriptions
from cte t1
inner join cte t2 on (t1.YearNo*100+t1.WeekNo >= t2.YearNo*100+t2.WeekNo)
group by t1.YearNo, t1.WeekNo
order by t1.YearNo, t1.WeekNo;

Версия SQL Server 2012

select 
--YearNo,
WeekNo,
sum(Total) over (order by YearNo, WeekNo) as TotalSubcriptions
from (
   select
    datepart(year,Subscription_Date) as YearNo, 
    datepart(week,Subscription_Date) as WeekNo,
    sum(iif(Subscription_Type = 1,1,0)) as Total
   from tbSubscriptions
   group by datepart(year,Subscription_Date), datepart(week,Subscription_Date)
) q
order by YearNo, WeekNo;
person LukStorms    schedule 04.12.2016
comment
Спасибо, но я использую Microsoft SQL Server 2008 R2, и, похоже, он не поддерживает запуск агрегатов с OVER (ORDER BY...) Подробнее здесь: stackoverflow.com/questions/12541355/ Есть мысли? - person João Alqueres; 05.12.2016