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