出现一组新数据时重置累积数字

Question

我有这个表(减去cuml列)：

¦  Name  ¦¦  website  ¦¦   page  ¦¦fruit type¦¦year week¦¦platform¦¦totalviews¦¦cuml¦
¦avocado ¦¦avocado.com¦¦aboutpage¦¦  sugar   ¦¦ 2001-08 ¦¦ mobile ¦¦     18   ¦¦ 18 ¦
¦avocado ¦¦avocado.com¦¦homepage ¦¦  sugar   ¦¦ 2001-08 ¦¦ desktop¦¦     10   ¦¦ 10 ¦
¦avocado ¦¦avocado.com¦¦homepage ¦¦  sugar   ¦¦ 2001-09 ¦¦ desktop¦¦     12   ¦¦ 22 ¦
¦avocado ¦¦avocado.com¦¦homepage ¦¦  sugar   ¦¦ 2001-10 ¦¦ desktop¦¦     6    ¦¦ 28 ¦
¦banana  ¦¦banana.com ¦¦aboutpage¦¦  fat     ¦¦ 2001-08 ¦¦tablet  ¦¦     21   ¦¦ 21 ¦
¦banana  ¦¦banana.com ¦¦contactus¦¦  fat     ¦¦ 2001-08 ¦¦tablet  ¦¦     14   ¦¦ 14 ¦
¦banana  ¦¦banana.com ¦¦homepage ¦¦  fat     ¦¦ 2001-08 ¦¦desktop ¦¦     15   ¦¦ 15 ¦
¦oranges ¦¦oranges.com¦¦aboutpage¦¦  sugar   ¦¦ 2001-09 ¦¦tablet  ¦¦     23   ¦¦ 23 ¦
¦oranges ¦¦oranges.com¦¦aboutpage¦¦  sugar   ¦¦ 2001-10 ¦¦tablet  ¦¦     15   ¦¦ 38 ¦
¦oranges ¦¦oranges.com¦¦contactus¦¦  sugar   ¦¦ 2001-08 ¦¦desktop ¦¦     6    ¦¦ 6  ¦

我想做的是返回相同的表，但这次末尾有 cuml 列。我试过这个...

SELECT 
[NAME]
, [WEBSITE]
, [PAGE]
, [FRUIT TYPE]
, [YEAR WEEK]
, [PLATFORM]
, [TOTALVIEWS]
, SUM(TOTALVIEWS) OVER(ORDER BY [REPORTING ISO YEAR WEEK] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUML
FROM WEBVIEWSFORFRUITS
------if i place this WHERE clause in the statement the cuml column works...
--where [NAME] = 'AVACADO' and [PLATFORM] = 'DESKTOP'
------but i would like to this without the where clause...
ORDER BY [NAME], [WEBSITE], [PAGE], 
[FRUIT TYPE], [PLATFORM], [REPORTING ISO YEAR WEEK]

这很好，但是正如您从我的表格中看到的那样，其他列带来了更大的挑战。我如何计算具有相同名称、网站、页面、水果类型、平台的每一行的累积频率，唯一的区别是年周的增量，当它点击一组不同的名称、网站、页面等时，我需要 cuml 列重置为该更改的总视图数，并继续计算 cuml，直到它达到一组新的数据等。需要重置。因此，在此示例中，第 2 行 (avocado.com) 具有 CUML。最多 28(第 4 行)，然后在出现新数据/周时重置为 21 的总观看次数，并不断重置，直到到达第 8 行和第 9 行，在那里它开始将前一行添加到 cuml 中 (23+15 = 38)...然后它重置为 6 作为其新数据等我不

完全确定我能做些什么来解决这个问题。

我在考虑滞后函数？ 与某种触发语句混合？

Answer 1

我认为在子句中使用Partition ByOver将创建所需的输出 -

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