Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- RESUME_TABLE:
- WEEK | TOTAL_NEW | TOTAL_SOLVED
- 1 | 10 | 3
- 2 | 13 | 15
- 3 | 6 | 7
- REPORT_TABLE:
- WEEK | PREV_TOTAL | NEW_CASES | SOLVED_CASES | NEW_TOTAL
- 1 | 0 | 10 | 3 | 7
- 2 | 7 | 13 | 15 | 5
- 3 | 5 | 6 | 7 | 4
- 12:57:06 HR@vm_xe> l
- 1 select week
- 2 ,lag(total_cases_by_now - total_solved_by_now) over (order by week) prev_total
- 3 ,total_new new_cases
- 4 ,total_solved solved_cases
- 5 ,total_cases_by_now - total_solved_by_now new_total
- 6 from (
- 7 select week
- 8 ,total_new
- 9 ,total_solved
- 10 ,sum(total_new) over(order by week asc) as total_cases_by_now
- 11 ,sum(total_solved) over (order by week asc) as total_solved_by_now
- 12 from resume_table
- 13* )
- 12:57:07 HR@vm_xe> /
- WEEK PREV_TOTAL NEW_CASES SOLVED_CASES NEW_TOTAL
- ---------- ------------ ---------- ------------ ----------
- 1 10 3 7
- 2 7 13 15 5
- 3 5 6 7 4
- 3 rows selected.
- Elapsed: 00:00:00.01
- with resume_table as
- (
- select 1 week, 10 total_new, 3 total_solved from dual union all
- select 2 week, 13 total_new, 15 total_solved from dual union all
- select 3 week, 6 total_new, 7 total_solved from dual
- )
- select week, prev_total, total_new, total_solved, new_total
- from resume_table
- model
- dimension by (week)
- measures (total_new, total_solved, 0 prev_total, 0 new_total)
- rules sequential order
- (
- new_total[any] order by week =
- nvl(new_total[cv(week)-1], 0) + total_new[cv()] - total_solved[cv()]
- ,prev_total[any] order by week = nvl(new_total[cv(week)-1], 0)
- )
- order by week;
- RESUME_LEFT
- WEEK | LEFT
- 1 | 7
- 2 | -2
- 3 | -1
- CREATE VIEW resume_left
- (SELECT week,total_new-total_solved "left" FROM resume_table)
- PREV_TOTAL=(SELECT sum(left) FROM RESUME_LEFT WHERE week<REPORT_TABLE.week)
- PREV_TOTAL=(SELECT sum(total_new)-sum(total_solved)
- FROM resume_table
- WHERE week<REPORT_TABLE.week)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement