Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH t AS (
- SELECT 1 c1, 100 C2, TO_DATE('24-MAY-13','dd-mon-rr') MYDATE FROM dual UNION ALL
- SELECT 2 c1, 200 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 2 c1, 201 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 3 c1, 300 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 4 c1, 400 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 6 c1, 600 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 1 c1, 150 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 2 c1, 250 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 3 c1, 350 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 4 c1, 450 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 5 c1, 550 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
- SELECT 7 c1, 750 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual
- )
- SELECT mydate,c1_aux,c1,c2
- FROM t
- model
- PARTITION BY (mydate)
- DIMENSION BY (c1 c1_aux,ROW_NUMBER()over(PARTITION BY mydate,c1 ORDER BY c2) rn)
- measures (c1,c2,MAX(c1)over(PARTITION BY mydate) max_c1,0 dummy)
- rules iterate(1e6) until(ITERATION_NUMBER+1>=max_c1[1,1])
- (
- dummy[ITERATION_NUMBER+1,1]=0
- )
- ORDER BY mydate,c1_aux;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement