求一SQL

Question

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);

Answer 1

高人,sm-lag(sm,1,0) 中LAG是啥意思,
over(order by day) chayi可以得到日增长吗

Answer 2

sm-lag(sm,1,0) over(order by day) 
--这一段代码是计算日增长

Answer 3

我只有一天的数据,可是增长的情况是错的啊,如
SM CHAYI
1 1
2 1
1 -1
1 0
1 0
1 0
10 9
1 -9
2 1
1 -1
1 0
1 0
1 0
1 0
1 0
1 0
2 1
2 0
469 467
157 -312

Answer 4

day                   SM CHAYI
20040805               1 1
20040805               2 1
20040805               1 -1
20040805               1 0
20040805               1 0
20040805               1 0
20040805               10 9
20040805               1 -9
20040805               2 1
20040805               1 -1
20040805               1 0
20040805               1 0
20040805               1 0
20040805               1 0
20040805               1 0
20040805               1 0
20040805               2 1
20040805               2 0
20040805               469 467
20040805               157 -312
20040805               93 -64
20040805               132 39
20040805               77 -55

Answer 5

我的SQL是:
select Billing_Month  ,
    Time_ID                ,
   Vendor_ID              ,
   Network_type_ID        ,
   bureau_id              ,
   region_id ,
    sm,
    sm-lag(sm) over(order by Time_ID) chayi
from (
select  Billing_Month          ,
Time_ID                ,
Vendor_ID              ,
Network_type_ID        ,
a.calling_bureau_id     bureau_id         ,
a.calling_region_id     region_id,
count(1) sm
from f_net_settle  a
group by Billing_Month          ,
Time_ID                ,
Vendor_ID              ,
Network_type_ID        ,
a.calling_bureau_id              ,
a.calling_region_id      )

Answer 6

这是结果
    BILLING_MONTH TIME_ID VENDOR_ID NETWORK_TYPE_ID BUREAU_ID REGION_ID SM CHAYI
1 200408 20040805 1 19 126 1
2 200408 20040805 1 20 107 2 1
3 200408 20040805 1 20 184 1 -1
4 200408 20040805 1 21 102 1 0
5 200408 20040805 1 21 211 1 0
6 200408 20040805 1 21 228 1 0
7 200408 20040805 1 21 240 10 9
8 200408 20040805 1 22 96 1 -9
9 200408 20040805 1 22 266 2 1
10 200408 20040805 1 22 268 1 -1
11 200408 20040805 1 22 269 1 0
12 200408 20040805 1 23 84 1 0
13 200408 20040805 1 23 86 1 0
14 200408 20040805 1 23 88 1 0

Answer 7

try:
select Billing_Month  ,
    Time_ID,
   Vendor_ID,
   Network_type_ID        ,
   bureau_id              ,
   region_id ,
    sm,
    sm-lag(sm) over(partition by Billing_Month  ,
    Time_ID,
   Vendor_ID,
   Network_type_ID        ,
   bureau_id              ,
   region_id order by Time_ID) chayi
from (
select  Billing_Month          ,
Time_ID                ,
Vendor_ID              ,
Network_type_ID        ,
a.calling_bureau_id     bureau_id         ,
a.calling_region_id     region_id,
count(1) sm
from f_net_settle  a
group by Billing_Month          ,
Time_ID                ,
Vendor_ID              ,
Network_type_ID        ,
a.calling_bureau_id              ,
a.calling_region_id      );

Answer 8

如果数据库版本低,用这个试试
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)

Answer 9

TO:bzszp(SongZip)
chayi的值为空

Answer 10

基本上差不多了，因为你只有一天的数据，
没有比较的数据
多一些数据看看另：漏掉了参数
select Billing_Month  ,
    Time_ID,
   Vendor_ID,
   Network_type_ID        ,
   bureau_id              ,
   region_id ,
    sm,
    sm-lag(sm,1,0) over(partition by Billing_Month  ,
    Time_ID,
   Vendor_ID,
   Network_type_ID        ,
   bureau_id              ,
   region_id order by Time_ID) chayi
from (
select  Billing_Month          ,
Time_ID                ,
Vendor_ID              ,
Network_type_ID        ,
a.calling_bureau_id     bureau_id         ,
a.calling_region_id     region_id,
count(1) sm
from f_net_settle  a
group by Billing_Month          ,
Time_ID                ,
Vendor_ID              ,
Network_type_ID        ,
a.calling_bureau_id              ,
a.calling_region_id      );