从给定日期计算用户对于过去7天的数(count number of users for past 7

我有表，我必须更新在那个特定的日期和在过去7天的用户数量从给定日期的用户数量。 样本数据的模样

Date    UserCountToday  UserCount7days
20120911    907575  
20120910    953629  
20120909    1366180 
20120908    1388916 
20120907    1009425 
20120906    918638  
20120905    956770  
20120904    1018152 
20120903    1306341 

Answer 1:

尝试这个：

 with cte as 
        (select *,ROW_NUMBER() over(order by [Date]) as row_num
        from t_users)
 select [Date],UserCountToday, (select SUM(UserCountToday) 
 from   cte c1 where c.row_num>=c1.row_num 
 and    abs(c.row_num-c1.row_num)<7)  as UserCount7days 
 from   cte c

结果

Date    UserCountToday  UserCount7days
20120903    1306341 1306341
20120904    1018152 2324493
20120905    956770  3281263
20120906    918638  4199901
20120907    1009425 5209326
20120908    1388916 6598242
20120909    1366180 7964422
20120910    953629  7611710
20120911    907575  7501133

Answer 2:

;WITH CTE as (select row_number() over (order by date) as rn,Date,UserCountToday,UserCount7days from tbl5)

,CTERec as (
select rn,Date,UserCountToday ,UserCountToday  as UserCount7days from CTE where rn=1
union all
select c.rn,c.Date,c.UserCountToday ,c.UserCountToday+c1.UserCount7days  as UserCount7days from CTERec c1 inner join CTE c
on c.rn=c1.rn+1 
)

select Date,UserCountToday,CASE WHEN rn<=7 then UserCount7days else (UserCount7days-(select UserCount7days from CTERec where rn=c.rn-7)) end asUserCount7days  from CTERec c

文章来源: count number of users for past 7 days from a given date