现在有一组数据如下:
DATE MTM CHANGE
20030101 0 0
20030102 2 3
20030103 7 8
20030104 0 5
20030105 3 4
20030106 2 2
20030107 0 0
20030108 5 3 现需要得到以下结果
DATE MTM CHANGE RETURN
20030101 0 0 0
20030102 2 3 2/(0+3)
20030103 7 8 (7-8)/2
20030104 0 5 (0-5)/7
20030105 3 4 3/(0+4)
20030106 2 2 (2-2)/3
20030107 0 0 (0-0)/2
20030108 5 3 5/(0+3) 就是当前一天的MTM为0时,RETURN=当天MTM/(前一天MTM+当天CHANGE)
否则,RETURN=(当天MTM-当天CHANGE)/前一天MTM
似乎很简单,但想不出如何写语句啊,请各位高手慷慨相助,小弟不胜感激啊
DATE MTM CHANGE
20030101 0 0
20030102 2 3
20030103 7 8
20030104 0 5
20030105 3 4
20030106 2 2
20030107 0 0
20030108 5 3 现需要得到以下结果
DATE MTM CHANGE RETURN
20030101 0 0 0
20030102 2 3 2/(0+3)
20030103 7 8 (7-8)/2
20030104 0 5 (0-5)/7
20030105 3 4 3/(0+4)
20030106 2 2 (2-2)/3
20030107 0 0 (0-0)/2
20030108 5 3 5/(0+3) 就是当前一天的MTM为0时,RETURN=当天MTM/(前一天MTM+当天CHANGE)
否则,RETURN=(当天MTM-当天CHANGE)/前一天MTM
似乎很简单,但想不出如何写语句啊,请各位高手慷慨相助,小弟不胜感激啊
select date,mtm,change,decode(mtm,0,decode(change,0,0,(mtm+pre_mtm)/change)),
decode(pre_mtm,0,0,(mtm-change)/pre_mtm)) return
from
(
select date,mtm,change,nvl(lag (mtm) over (order by date),0) pre_mtm
from a)
CASE
WHEN c.mtm = 0
THEN ''|| d.mtm || c.mtm || d.CHANGE--d.mtm / (c.mtm + d.CHANGE)
ELSE ''||d.mtm || d.CHANGE|| c.mtm--(d.mtm - d.CHANGE) / c.mtm
END
FROM (SELECT a.ddate, a.mtm, b.ddate cdate
FROM (SELECT ddate, mtm, CHANGE,
TO_CHAR (TO_DATE (ddate, 'yyyymmdd') + 1,
'yyyymmdd'
) adate
FROM test_b) a,
test_b b
WHERE a.adate = b.ddate) c,
test_b d
WHERE c.cdate (+)= d.ddate
form tb t,tb y
where to_date(y.date,'yyyymmdd')=to_date(t.date,'yyyymmdd')-1
select ls_date,mtm,change,bmtm,decode(bmtm,0,decode(change,0,0,mtm/change),(mtm - change)/bmtm) myrtn
from
(
select atable.ls_date ls_date,atable.mtm mtm,atable.change change,nvl(btable.mtm,0) bmtm from
(select rownum - 1 r1,ls_date,mtm,change from myreturn order by ls_date) atable,
(select rownum r2,ls_date,mtm,change from myreturn order by ls_date) btable
where atable.r1=btable.r2(+)
)
order by ls_date;结果为: LS_DATE MTM CHANGE BMTM MYRTN
---------- ---------- ---------- ---------- ----------
20030101 0 0 0 0
20030102 2 3 0 .666666667
20030103 7 8 2 -.5
20030104 0 5 7 -.71428571
20030105 3 4 0 .75
20030106 2 2 3 0
20030107 0 0 2 0
20030108 5 3 0 1.66666667
from tb t left join tb y
on to_date(y.vdate,'yyyymmdd')=to_date(t.vdate,'yyyymmdd')-1
order by t.vdate
改了一下 这样完善点
from
(
select ddate,mtm,change,nvl(lag (mtm) over (order by ddate),0) pre_mtm
from test_a)
对一楼的修改:select ddate,mtm,change,decode(pre_mtm,0,decode(change,0,0,mtm/(pre_mtm+change)),(mtm-change)/pre_mtm) return
from
(
select ddate,mtm,change,nvl(lag (mtm) over (order by ddate),0) pre_mtm
from test_a)
from
(
select ls_date,mtm,change,lag(mtm,1,0) over(order by ls_date) bmtm from myreturn
)
order by ls_date;