Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Date UserCountToday UserCount7days
- 20120911 907575
- 20120910 953629
- 20120909 1366180
- 20120908 1388916
- 20120907 1009425
- 20120906 918638
- 20120905 956770
- 20120904 1018152
- 20120903 1306341
- 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
- ;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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement