select day,grade,acct_id,sm-lag(sm,1,0) over(order by day) chayi from ( select day,grade,acct_id,sum(num_count) sm from test_table group by day,grade,acct_id);
lag 和 Over是啥意思?不懂
select B.day ,A.day,(B.sm_b-A.sm_a) as add (select rownum as id,day,grade,acct_id,sum(num_count) sm_a from test_table) A, (select rownum-1 as id,day,grade,acct_id,sum(num_count) sm_b from test_table) B, where (A.id = B.id)
set pages 50 set feedback offcreate table lag_exp ( n number, m number );insert into lag_exp select rownum, mod(rownum * 19 , 13) from all_objects where rownum < 21;select n, m, LAG(m,1) over (order by n) "Previous M" from lag_exp;drop table lag_exp; N M Previous M ---------- ---------- ---------- 1 6 2 12 6 3 5 12 4 11 5 5 4 11 6 10 4 7 3 10 8 9 3 9 2 9 10 8 2 11 1 8 12 7 1 13 0 7 14 6 0 15 12 6 16 5 12 17 11 5 18 4 11 19 10 4 20 3 10 如果逆向可用lead 替代lag
from (
select day,grade,acct_id,sum(num_count) sm from test_table group by day,grade,acct_id);
(select rownum as id,day,grade,acct_id,sum(num_count) sm_a from test_table) A,
(select rownum-1 as id,day,grade,acct_id,sum(num_count) sm_b from test_table) B,
where (A.id = B.id)
set feedback offcreate table lag_exp (
n number,
m number
);insert into lag_exp
select rownum, mod(rownum * 19 , 13) from all_objects where rownum < 21;select n, m, LAG(m,1) over (order by n) "Previous M" from lag_exp;drop table lag_exp; N M Previous M
---------- ---------- ----------
1 6
2 12 6
3 5 12
4 11 5
5 4 11
6 10 4
7 3 10
8 9 3
9 2 9
10 8 2
11 1 8
12 7 1
13 0 7
14 6 0
15 12 6
16 5 12
17 11 5
18 4 11
19 10 4
20 3 10
如果逆向可用lead 替代lag
:)
[email protected]