分析函式概述

先透過一個例子，直觀的感受下分析函式的簡潔方便

構造資料:
銷售表:
create table CRISS_SALES
(
  DEPT_ID    VARCHAR2(6),    --部門號
  SALE_DATE  DATE,           --銷售日期
  GOODS_TYPE VARCHAR2(4),    --貨物型別
  SALE_CNT   NUMBER(10)      --銷售數量
);

insert into CRISS_SALES values ('D01', TO_DATE('20140304', 'YYYYMMDD'), 'G00', 700);
insert into CRISS_SALES values ('D02', TO_DATE('20140306', 'YYYYMMDD'), 'G00', 500);
insert into CRISS_SALES values ('D01', TO_DATE('20140408', 'YYYYMMDD'), 'G01', 200);
insert into CRISS_SALES values ('D02', TO_DATE('20140427', 'YYYYMMDD'), 'G01', 300);
insert into CRISS_SALES values ('D01', TO_DATE('20140430', 'YYYYMMDD'), 'G03', 800);
insert into CRISS_SALES values ('D02', TO_DATE('20140502', 'YYYYMMDD'), 'G03', 900);
insert into CRISS_SALES values ('D01', TO_DATE('20140504', 'YYYYMMDD'), 'G02', 80);
insert into CRISS_SALES values ('D02', TO_DATE('20140408', 'YYYYMMDD'), 'G02', 100);


SQL> select *from criss_sales order by sale_date;
 
DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D01     2014/3/4    G00                700
D02     2014/3/6    G00                500
D01     2014/4/8    G01                200
D02     2014/4/8    G02                100
D02     2014/4/27   G01                300
D01     2014/4/30   G03                800
D02     2014/5/2    G03                900
D01     2014/5/4    G02                 80

求出全公每個時點司累計的銷售數量

即:希望得到的結果為如下形式: 
DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT   ALL_CMP_CNT
------- ----------- ---------- ----------- --------------
D01     2014/3/4    G00                700      700
D02     2014/3/6    G00                500     1200
           .........
D02     2014/5/2    G03                900     3500
D01     2014/5/4    G02                 80     3580


先不用分析函式解決這個問題
利用偽列
with t as 
(
select 
  DEPT_ID
 ,SALE_DATE
 ,GOODS_TYPE
 ,SALE_CNT
 ,rownum rn
from (select 
  DEPT_ID
 ,SALE_DATE
 ,GOODS_TYPE
 ,SALE_CNT
from criss_sales
order by sale_date
)tmp
) 
select 
  a.DEPT_ID
 ,a.SALE_DATE
 ,a.GOODS_TYPE
 ,a.SALE_CNT
 ,sum(b.SALE_CNT) ALL_CMP_CNT
from t a
left join t b
on (a.rn + 1 > b.rn  )
group by
  a.DEPT_ID
 ,a.SALE_DATE
 ,a.GOODS_TYPE
 ,a.SALE_CNT
order by SALE_DATE
;


DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT ALL_CMP_CNT
------- ----------- ---------- ----------- -----------
D01     2014/3/4    G00                700         700
D02     2014/3/6    G00                500        1200
D01     2014/4/8    G01                200        1400
D02     2014/4/8    G02                100        1500
D02     2014/4/27   G01                300        1800
D01     2014/4/30   G03                800        2600
D02     2014/5/2    G03                900        3500
D01     2014/5/4    G02                 80        3580


利用分析函式
select 
  DEPT_ID
 ,SALE_DATE
 ,GOODS_TYPE
 ,sum(SALE_CNT) over(order by SALE_DATE rows between unbounded preceding and current row) ALL_CMP_CNT
from criss_sales
;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT ALL_CMP_CNT
------- ----------- ---------- ----------- -----------
D01     2014/3/4    G00                700         700
D02     2014/3/6    G00                500        1200
D01     2014/4/8    G01                200        1400
D02     2014/4/8    G02                100        1500
D02     2014/4/27   G01                300        1800
D01     2014/4/30   G03                800        2600
D02     2014/5/2    G03                900        3500
D01     2014/5/4    G02                 80        3580


透過這個例子,可以看到利用分析函式解決某些場景下的問題非常的直觀簡便。

分析函式適用於哪些場景?
1.表內資料聚合累加
2.表內分組累加
3.分組排名
4.滾動統計
5.範圍求值SUM MAX MIN
6.相鄰行比較
等等...


Oracle提供的分析函式有哪些?
==================================================
統計方面:
Sum() Over ([Partition by ] [Order by ]) --統計全表

--統計前n行到後m行
Sum() Over ([Partition by ] [Order by ]  
     Rows Between n Preceding And m Following)

--統計前n行到當前行      
Sum() Over ([Partition by ] [Order by ]
     Rows Between n Preceding And Current Row)

--統計開始行到當前行
Sum() Over ([Partition by ] [Order by ]
     Rows Between unbounded Preceding And Current Row)

--統計開始行到當前行之後的第n行     
Sum() Over ([Partition by ] [Order by ]
     Rows Between unbounded Preceding n Following)
     
前面的例子就使用了
Sum() Over ([Partition by ] [Order by ]
     Rows Between unbounded Preceding And Current Row)
其他的有興趣可以自行實驗下結果

===================================================
有關排列或排名:
--非連續排名
Rank() Over ([Partition by ] [Order by ] [Nulls First/Last])

--連續排名
Dense_rank() Over ([Patition by ] [Order by ] [Nulls First/Last])

--排序行號
Row_number() Over ([Partitionby ] [Order by ] [Nulls First/Last])
   
Ntile() Over ([Partition by ] [Order by ])


create table s_score
( s_id number(6)
 ,score number(4,2)
);
insert into s_score values(001,98);
insert into s_score values(002,66.5);
insert into s_score values(003,99);
insert into s_score values(004,98);
insert into s_score values(005,98);
insert into s_score values(006,80);

select
    s_id 
   ,score
   ,rank() over(order by score desc) rank
   ,dense_rank() over(order by score desc) dense_rank
   ,row_number() over(order by score desc) row_number
from s_score;

   S_ID  SCORE       RANK DENSE_RANK ROW_NUMBER
------- ------ ---------- ---------- ----------
      3  99.00          1          1          1
      1  98.00          2          2          2
      4  98.00          2          2          3
      5  98.00          2          2          4
      6  80.00          5          3          5
      2  66.50          6          4          6
      

==========================================================
最大值/最小值查詢：
Min()/Max() Keep (Dense_rank First/Last [Partition by ] [Order by ])

==========================================================
首記錄/末記錄查詢：
First_value / Last_value(Sum() Over ([Patition by ] [Order by ]
       Rows Between  Preceding And  Following  ))
       
==========================================================
相鄰記錄之間比較 lead/lag：
Lag(Sum(), 1) Over([Patition by ] [Order by ])