ORACLEÔõô×öµ½ÕâÑù£¿°ïÏÂæ

ID    Ãû×Ö    ÏúÊÛ¶î        »·±È
1    aa      20.2          0
1    aa      15.2      (15.2-20.2)/20.2
1    aa      23.0      (23.0-15.2)/15.2
¾ÍÊÇÓà ÏÂÒ»ÐмõÈ¥ ÉÏÒ»ÐУ¬³ýÒÔ ÉÏÒ»ÐÐ,ÒòΪµÚÒ»ÐÐ µÄÉÏÒ»ÐУ¬Ã»ÓÐÊý¾ÝËùÒÔÈÃËûÏÔʾΪ0

Ë­ÄÜ°ïÏÂæ°¡¡£×îºÃÄÜд¸öÓï¾ä »ò´æ´¢¹ý³ÌµÄÀý×Ó£¬Ð»Ð»°¡£¡

---

LagºÍLeadº¯Êý¿ÉÒÔÔÚÒ»´Î²éѯÖÐÈ¡³öͬһ×ֶεÄÇ°NÐеÄÊý¾ÝºÍºóNÐеÄÖµ¡£ÕâÖÖ²Ù×÷¿ÉÒÔʹÓöÔÏàͬ±íµÄ±íÁ¬½ÓÀ´ÊµÏÖ£¬²»¹ýʹÓÃLAGºÍLEADÓиü¸ßµÄЧÂÊ

ѧϰÁË
SQL code:
with tt as (
select 1 ID,'aa' name,20.2 rmb from dual
union all
select 1,'aa',15.2 from dual
union all
select 1,'aa',22.5 from dual
)
select a.*,(a.rmb-a.lastrec)/a.lastrec as »·±È¡¡from
(
select tt.*,lag(tt.rmb,1) over (ORDER BY id) as lastrec
from tt
)a;

½á¹û
1 aa 20.2
1 aa 15.2 20.2 -0.247524752475248
1 aa 22.5 15.2 0.480263157894737

---

ÒýÓÃ

ID    Ãû×Ö    ÏúÊÛ¶î        »·±È
1    aa      20.2          0
1    aa      15.2      (15.2-20.2)/20.2
1