oracle 窗口函数的统计使用问题

Question

熟悉oracle窗口函数的，下面两题应该还是挺简单，我就是写出来的语句有问题，
如何利用oracle自有的窗口函数实现当前行的某个减去上一行的值啊,例如
rownum  ||  name|| age ||  sal   
1       ||   A1  || 22 ||  100
2       ||   A2  || 24 ||  120
3       ||   A3  || 23 ||  150
4       ||   A3  || 25 ||  230统计的结果我想得到 rownum  ||  name|| age ||  sal   || 统计结果
1       ||   A1  || 22 ||  100   ||   100
2       ||   A2  || 24 ||  120   ||    20
3       ||   A3  || 23 ||  150   ||    30
4       ||   A3  || 25 ||  230   ||    80 
2.如何用函数十二个月的的日期函数,根据statdate字段，实时统计以它为基准的过去十二个月中sal字段的累积和，对于前十二个月的累积数，只需要累积到第一个月就行，
statdat           code      A1      sal
2009-1-31          0104 0 3
2009-2-28          0104 0 3
2009-3-31         0104 0 3
2009-4-30          0104 0 3
2009-5-31      0104 0 4
2009-6-30         0104 0 4
2009-7-31   0104 0 4
2009-8-31          0104 0 4
2009-9-30    0104 0 5
2009-10-31 0104 0 5
2009-11-30 0104 0 5
2009-12-31 0104 0 5
2010-1-31   0104 0 0
2010-2-28  0104 0 0
2010-3-31  0104 0 0
2010-4-30  0104 0 1
2010-5-31  0104 0 1

Answer 1

这是第二题的实验数据create table test_kpi_busi_claim_mon(statdate date,riskcode varchar2(5),A1 varchar2(1),sal number(2));insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-01-2009', 'dd-mm-yyyy'), '0104', '0', 3);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('28-02-2009', 'dd-mm-yyyy'), '0104', '0', 3);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-03-2009', 'dd-mm-yyyy'), '0104', '0', 3);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-04-2009', 'dd-mm-yyyy'), '0104', '0', 3);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-05-2009', 'dd-mm-yyyy'), '0104', '0', 4);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-06-2009', 'dd-mm-yyyy'), '0104', '0', 4);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-07-2009', 'dd-mm-yyyy'), '0104', '0', 4);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-08-2009', 'dd-mm-yyyy'), '0104', '0', 4);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-09-2009', 'dd-mm-yyyy'), '0104', '0', 5);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-10-2009', 'dd-mm-yyyy'), '0104', '0', 5);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-11-2009', 'dd-mm-yyyy'), '0104', '0', 5);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-12-2009', 'dd-mm-yyyy'), '0104', '0', 5);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-01-2010', 'dd-mm-yyyy'), '0104', '0', 0);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('28-02-2010', 'dd-mm-yyyy'), '0104', '0', 0);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-03-2010', 'dd-mm-yyyy'), '0104', '0', 0);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-04-2010', 'dd-mm-yyyy'), '0104', '0', 1);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-05-2010', 'dd-mm-yyyy'), '0104', '0', 1);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-06-2010', 'dd-mm-yyyy'), '0104', '0', 1);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-07-2010', 'dd-mm-yyyy'), '0104', '0', 1);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-08-2010', 'dd-mm-yyyy'), '0104', '0', 1);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-09-2010', 'dd-mm-yyyy'), '0104', '0', 1);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-10-2010', 'dd-mm-yyyy'), '0104', '0', 2);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-11-2010', 'dd-mm-yyyy'), '0104', '0', 2);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-12-2010', 'dd-mm-yyyy'), '0104', '0', 3);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-01-2011', 'dd-mm-yyyy'), '0104', '0', 0);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('28-02-2011', 'dd-mm-yyyy'), '0104', '0', 0);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-03-2011', 'dd-mm-yyyy'), '0104', '0', 0);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-04-2011', 'dd-mm-yyyy'), '0104', '0', 0);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-05-2011', 'dd-mm-yyyy'), '0104', '0', 0);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-06-2011', 'dd-mm-yyyy'), '0104', '0', 0);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-07-2011', 'dd-mm-yyyy'), '0104', '0', 6);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-08-2011', 'dd-mm-yyyy'), '0104', '0', 6);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-09-2011', 'dd-mm-yyyy'), '0104', '0', 6);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-10-2011', 'dd-mm-yyyy'), '0104', '0', 6);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('30-11-2011', 'dd-mm-yyyy'), '0104', '0', 6);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-12-2011', 'dd-mm-yyyy'), '0104', '0', 9);
insert into test_kpi_busi_claim_mon (STATDATE, RISKCODE, A1, SAL)
values (to_date('31-01-2012', 'dd-mm-yyyy'), '0104', '0', 0);

Answer 2

第一题：
with tb as (
select 'A1' as name,22 as price, 100 as sal from dual
union all
select 'A2',24,120 from dual
union all
select 'A3',23,150 from dual
union all
select 'A3',25,230 from dual
)select name,price,sal,sal-nvl(lag(sal) over(order by name),0) as calce from tb
NAME PRICE                  SAL                    CALCE                  
---- ---------------------- ---------------------- ---------------------- 
A1   22                     100                    100                    
A2   24                     120                    20                     
A3   23                     150                    30                     
A3   25                     230                    80  

Answer 3

第二题：
select to_char(statedate,'yyyy-mm') as statedate, sum(sal) as sal from tableName
where months_between(sysdate,statedate) <= 12--系统时间往前12个月
group by to_char(statedate,'yyyy-mm')--按年月分组
order by statedate--排序

Answer 4

----第一题
with t as(
select 1 r,'A1' name,22 age,100 sal from dual
union all
select 2,'A2',24,120 from dual
union all
select 3,'A3',23,150 from dual
union all
select 4,'A3',25,230 from dual
)select t.*,sal-nvl(lag(sal) over (order by sal),0) from t
         R NAME        AGE        SAL SAL-NVL(LAG(SAL)OVER(ORDERBYSA
---------- ---- ---------- ---------- ------------------------------
         1 A1           22        100                            100
         2 A2           24        120                             20
         3 A3           23        150                             30
         4 A3           25        230                             80

Answer 5

第二题：
select t.*,
       sum(sal) over(order by statdate rows 12 preceding) "前十二个月和"
  from test_kpi_busi_claim_mon t
STATDATE    RISKCODE A1 SAL       前十二个月和
----------- -------- -- --- ------------
2009-1-31   0104     0    3            3
2009-2-28   0104     0    3            6
2009-3-31   0104     0    3            9
2009-4-30   0104     0    3           12
2009-5-31   0104     0    4           16
2009-6-30   0104     0    4           20
2009-7-31   0104     0    4           24
2009-8-31   0104     0    4           28
2009-9-30   0104     0    5           33
2009-10-31  0104     0    5           38
2009-11-30  0104     0    5           43
2009-12-31  0104     0    5           48
2010-1-31   0104     0    0           48
2010-2-28   0104     0    0           45
2010-3-31   0104     0    0           42
2010-4-30   0104     0    1           40
2010-5-31   0104     0    1           38
2010-6-30   0104     0    1           35
2010-7-31   0104     0    1           32
2010-8-31   0104     0    1           29
 
STATDATE    RISKCODE A1 SAL       前十二个月和
----------- -------- -- --- ------------
2010-9-30   0104     0    1           26
2010-10-31  0104     0    2           23
2010-11-30  0104     0    2           20
2010-12-31  0104     0    3           18
2011-1-31   0104     0    0           13
2011-2-28   0104     0    0           13
2011-3-31   0104     0    0           13
2011-4-30   0104     0    0           13
2011-5-31   0104     0    0           12
2011-6-30   0104     0    0           11
2011-7-31   0104     0    6           16
2011-8-31   0104     0    6           21
2011-9-30   0104     0    6           26
2011-10-31  0104     0    6           31
2011-11-30  0104     0    6           35
2011-12-31  0104     0    9           42
2012-1-31   0104     0    0           39
 
37 rows selected

Answer 6

第2题是个难点，每条记录 都要 往前 12个月 进行 统计。
不可能不进行全表扫描的，这样的统计，不建议走查询SQL。
最好还是作为历史值，存在表里，最后进行查询。