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 谁能帮下忙啊。最好能写个语句 或存储过程的例子,谢谢啊!
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(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME HIRE_DATE SALARY PREV_SAL
------------------------- --------- ---------- ----------
Khoo 18-MAY-95 3100 0
Tobias 24-JUL-97 2800 3100
Baida 24-DEC-97 2900 2800
Himuro 15-NOV-98 2600 2900
Colmenares 10-AUG-99 2500 2600
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
FROM (SELECT ID,名字,销售额,LAG(销售额,1,0) OVER(ORDER BY rownum) AS num FROM tb);