WITH Tmp1 AS
(SELECT '001' ACC, TO_DATE('2014/8/18', 'yyyy/mm/dd') START_DT, 100 BAL_AMT FROM DUAL
-- union all SELECT '001' ACC, TO_DATE('2014/9/18', 'yyyy/mm/dd') START_DT, 100 BAL_AMT FROM DUAL
UNION ALL SELECT '001', TO_DATE('2014/10/3', 'yyyy/mm/dd'), 200 FROM DUAL
UNION ALL SELECT '001', TO_DATE('2014/10/5', 'yyyy/mm/dd'), 400 FROM DUAL
UNION ALL SELECT '001', TO_DATE('2014/10/21', 'yyyy/mm/dd'), 100 FROM DUAL
UNION ALL SELECT '002', TO_DATE('2014/10/6', 'yyyy/mm/dd'), 500 FROM DUAL
UNION ALL SELECT '002', TO_DATE('2014/10/8', 'yyyy/mm/dd'), 100 FROM DUAL
UNION ALL SELECT '002', TO_DATE('2014/10/14', 'yyyy/mm/dd'), 300 FROM DUAL
UNION ALL SELECT '002', TO_DATE('2014/10/24', 'yyyy/mm/dd'), 100 FROM DUAL
-- UNION ALL SELECT '002', TO_DATE('2014/10/31', 'yyyy/mm/dd'), 200 FROM DUAL
),
tmp2 as(select acc from Tmp1 group by acc) ,--acc应该有个维度表可以查到
tmp3 as (select t1.acc,t1.start_dt,nvl(t2.BAL_AMT,t1.BAL_AMT) BAL_AMT from (select acc,to_date('2014/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') START_DT,0 BAL_AMT from tmp2 )t1,
( select t.*,row_number() over(partition by ACC order by START_DT desc) rn from tmp1 t
where START_DT<=to_date('2014/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') ) t2
where t1.acc=t2.acc(+) and t2.rn(+)=1
union all
select t.* from tmp1 t
where START_DT>to_date('2014/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and START_DT
)
select acc,round(sum(bal_amt*n)/31,2) from (
select acc,BAL_AMT,START_DT,nvl(lead(START_DT) over(partition by ACC order by START_DT)-START_DT,
to_date('2014/10/31 00:00:00', 'yyyy/mm/dd hh24:mi:ss')-start_dt+1)n from tmp3 t3 )
group by acc