Thanks for the response
Michael, November 16, 2016 - 1:29 pm UTC
After giving myself a mental break and coming back to it later, I came up with:
SELECT
REPORT_MONTH,
ATTR,
CATEGORY_A,
LAST_CATEGORY_A,
NEXT_CATEGORY_A,
ROW_NUMBER() OVER (PARTITION BY ATTR, LAST_CATEGORY_A ORDER BY REPORT_MONTH)-1 POSITION,
COUNT(*) OVER (PARTITION BY ATTR, LAST_CATEGORY_A) COUNTER,
LAST_CATEGORY_A + (NEXT_CATEGORY_A - LAST_CATEGORY_A) / COUNT(*) OVER (PARTITION BY ATTR, LAST_CATEGORY_A) * (ROW_NUMBER() OVER (PARTITION BY ATTR, LAST_CATEGORY_A ORDER BY REPORT_MONTH)-1) VALUE
FROM (
SELECT
REPORT_MONTH,
ATTR,
CATEGORY_A,
LAST_VALUE(CATEGORY_A) IGNORE NULLS OVER (PARTITION BY ATTR ORDER BY REPORT_MONTH RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) LAST_CATEGORY_A,
FIRST_VALUE(CATEGORY_A) IGNORE NULLS OVER (PARTITION BY ATTR ORDER BY REPORT_MONTH RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) NEXT_CATEGORY_A
FROM (
SELECT TO_DATE('01-JAN-2016') REPORT_MONTH, 'M' ATTR, 100 CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-FEB-2016') REPORT_MONTH, 'M' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAR-2016') REPORT_MONTH, 'M' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-APR-2016') REPORT_MONTH, 'M' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAY-2016') REPORT_MONTH, 'M' ATTR, 200 CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-JAN-2016') REPORT_MONTH, 'F' ATTR, 100 CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-FEB-2016') REPORT_MONTH, 'F' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAR-2016') REPORT_MONTH, 'F' ATTR, 160 CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-APR-2016') REPORT_MONTH, 'F' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAY-2016') REPORT_MONTH, 'F' ATTR, 200 CATEGORY_A FROM DUAL
ORDER BY 2
)
)
;
I do appreciate the response. I had a total mental block there.
Tried with pattern matching
Rajeshwaran, Jeyabal, November 16, 2016 - 2:31 pm UTC
Just tried this with pattern matching, i got this.
drop table t purge;
create table t as
select to_date('01-jan-2016') report_month, 100 category_a from dual
union all
select to_date('01-feb-2016') report_month, null category_a from dual
union all
select to_date('01-mar-2016') report_month, null category_a from dual
union all
select to_date('01-apr-2016') report_month, null category_a from dual
union all
select to_date('01-may-2016') report_month, 200 category_a from dual
union all
select to_date('01-jun-2016') report_month, 300 category_a from dual
union all
select to_date('01-jul-2016') report_month, null category_a from dual
union all
select to_date('01-aug-2016') report_month, null category_a from dual
union all
select to_date('01-sep-2016') report_month, null category_a from dual
union all
select to_date('01-oct-2016') report_month, 400 category_a from dual
demo@ORA12C> select *
2 from (
3 select t.* ,
4 last_value( category_a ignore nulls ) over(
5 order by report_month
6 rows between unbounded preceding
7 and current row) as last_value,
8 first_value( category_a ignore nulls ) over(
9 order by report_month
10 rows between current row
11 and unbounded following ) as first_value
12 from t
13 )
14 match_recognize(
15 order by report_month
16 measures
17 running count(*) as seq,
18 final count(*) as cnt,
19 case when category_a is not null then category_a
20 else last_value + (first_value - last_value) *
21 (running count(*) -1) / final count(*) end as diff
22 all rows per match
23 pattern( strt down*)
24 define
25 down as prev(last_value) = last_value )
26 /
REPORT_MONT SEQ CNT DIFF CATEGORY_A LAST_VALUE FIRST_VALUE
----------- ---------- ---------- ---------- ---------- ---------- -----------
01-JAN-2016 1 4 100 100 100 100
01-FEB-2016 2 4 125 100 200
01-MAR-2016 3 4 150 100 200
01-APR-2016 4 4 175 100 200
01-MAY-2016 1 1 200 200 200 200
01-JUN-2016 1 4 300 300 300 300
01-JUL-2016 2 4 325 300 400
01-AUG-2016 3 4 350 300 400
01-SEP-2016 4 4 375 300 400
01-OCT-2016 1 1 400 400 400 400
10 rows selected.
demo@ORA12C>
With MODEL clause
Rajeshwaran, Jeyabal, November 17, 2016 - 8:14 am UTC
demo@ORA12C> select report_month,cata as category_a,last_cat,nxt_cat,seq,cnt,
2 case when cata is not null then cata
3 else last_cat + ( nxt_cat - last_cat) *
4 (seq-1)/cnt end as apportioned
5 from t
6 model
7 dimension by ( row_number() over( order by report_month) rn )
8 measures( category_a cata, 0 last_cat, 0 nxt_cat,report_month,0 seq,0 cnt)
9 rules(
10 last_cat[any] order by rn = case when cata[cv()] is not null
11 then cata[cv()]
12 else last_cat[cv()-1]
13 end ,
14 nxt_cat[any] order by rn desc = case when cata[cv()] is not null
15 then cata[cv()]
16 else nxt_cat[cv()+1]
17 end ,
18 seq[any] order by rn = case when cv(rn)=1 then 1
19 when last_cat[cv()-1] = last_cat[cv()]
20 then seq[cv()-1]+1
21 when last_cat[cv()-1] <> last_cat[cv()]
22 then 1 end ,
23 cnt[any] order by rn = max(seq) keep(dense_rank first order by last_cat)[rn >= cv(rn)] )
24 order by report_month
25 /
REPORT_MONT CATEGORY_A LAST_CAT NXT_CAT SEQ CNT APPORTIONED
----------- ---------- ---------- ---------- ---------- ---------- -----------
01-JAN-2016 100 100 100 1 4 100
01-FEB-2016 100 200 2 4 125
01-MAR-2016 100 200 3 4 150
01-APR-2016 100 200 4 4 175
01-MAY-2016 200 200 200 1 1 200
01-JUN-2016 300 300 300 1 4 300
01-JUL-2016 300 400 2 4 325
01-AUG-2016 300 400 3 4 350
01-SEP-2016 300 400 4 4 375
01-OCT-2016 400 400 400 1 1 400
10 rows selected.
demo@ORA12C>
November 18, 2016 - 4:11 am UTC
nice stuff