我认为使用
Partition By
在…内
Over
子句将创建所需的输出-
declare @xyz table (
Name varchar(50),
website varchar(50),
page varchar(50),
fruittype varchar(50),
yearweek varchar(50),
platform varchar(50),
totalviews int
)
insert into @xyz
select 'avocado' ,'avocado.com','aboutpage', 'sugar' , '2001-08' ,'mobile' ,18 union all
select 'avocado' ,'avocado.com','homepage' , 'sugar' , '2001-08' ,'desktop' ,10 union all
select 'avocado' ,'avocado.com','homepage' , 'sugar' , '2001-09' ,'desktop' ,12 union all
select 'avocado' ,'avocado.com','homepage' , 'sugar' , '2001-10' ,'desktop' ,6 union all
select 'banana' ,'banana.com' ,'aboutpage', 'fat' , '2001-08' ,'tablet' ,21 union all
select 'banana' ,'banana.com' ,'contactus', 'fat' , '2001-08' ,'tablet' ,14 union all
select 'banana' ,'banana.com' ,'homepage' , 'fat' , '2001-08' ,'desktop' ,15 union all
select 'oranges' ,'oranges.com','aboutpage', 'sugar' , '2001-09' ,'tablet' ,23 union all
select 'oranges' ,'oranges.com','aboutpage', 'sugar' , '2001-10' ,'tablet' ,15 union all
select 'oranges' ,'oranges.com','contactus', 'sugar' , '2001-08' ,'desktop' ,6
select *,
sum(totalviews) over (partition by name, website, page, fruittype, platform order by yearweek rows between unbounded preceding and current row)
from @xyz
