Oracle中分析函数

 

 

1.

row_number() over(PARTITION BY ...ORDER BY...)--在求第一名成绩的时候，不能用，因为如果有两个并列第一，只会返回一个,ROW_NUMBER 为每一组的行按顺序生成一个连续序号
rank() over(PARTITION BY...ORDER BY ...)--跳跃排序，有两个第二名时接下来就是第四名,RANK（）也为每一组的行生成一个序号，与ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如两个相同的行生成序号3，那么接下来会生成序号5。
dense_rank() over(PARTITION BY...ORDER BY ...) --连续排序，有两个第二名时仍然跟着第三名
Count() over(PARTITION BY...ORDER BY ...)
Max() over(PARTITION BY...ORDER BY ...)
Min() over(PARTITION BY...ORDER BY ...)
Sum() over(PARTITION BY...ORDER BY ...)
AVG() over(PARTITION BY...ORDER BY ...)
first_value() over(PARTITION BY...ORDER BY ...)
last_value() over(PARTITION BY...ORDER BY ...)
lag() over(PARTITION BY...ORDER BY ...)
lead() over(PARTITION BY...ORDER BY ...)

SQL> desc criss_sales;
Name       Type        Nullable Default Comments 
---------- ----------- -------- ------- -------- 
DEPT_ID    VARCHAR2(6) Y                         
SALE_DATE  DATE        Y                         
GOODS_TYPE VARCHAR2(4) Y                         
SALE_CNT   NUMBER(10)  Y 


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

一.全统计
最常用的全统计就是均值或求和,有时会要求同一行记录包含不同范围的全统计。
例:
为数据集统计部门销售总和，全公司销售总和，部门销售均值，全公司销售均值

select
      dept_id
     ,sale_date
     ,goods_type
     ,sale_cnt
     ,sum(sale_cnt) over (partition by dept_id) dept_total
     ,sum(sale_cnt) over() cmp_total
     ,avg(sale_cnt) over (partition by dept_id) avg_dept
     ,avg(sale_cnt) over() avg_cmp
from criss_sales;

二.滚动统计
滚动统计最常用的一个场景之一是累计。

例:
计算部门和全公司的销售树量累计值。

select
      dept_id
     ,sale_date
     ,goods_type
     ,sale_cnt
     ,sum(sale_cnt) over(partition by dept_id order by dept_id,sale_date rows between unbounded preceding and current row) dept_cur_total
     ,sum(sale_cnt) over(order by dept_id,sale_date rows between unbounded preceding and current row) cmp_cur_total
from criss_sales;

三.范围统计
有时候，我们往往关注一定范围内的数据，例如时间范围(一周内的数据)，记录范围(前三条记录到当前记录)。

例:按日期排序，求相相邻三次销售记录的和

select
      dept_id
     ,sale_date
     ,goods_type
     ,sale_cnt
     ,sum(sale_cnt) over(order by sale_date rows between 1 preceding and 1 following) CON_1_CNT
from criss_sales;

时间范围例子:
按日期排序，求当前记录日期前三天到后天三的销售数量和

select
      dept_id
     ,sale_date
     ,goods_type
     ,sale_cnt
     ,sum(sale_cnt) over(order by sale_date range between interval '3' day preceding and interval '3' day following) sum_7_days
from criss_sales;

 

 

四.(相邻)行比较
其实用over(order by xxx rows between 1 preceding and 0 following)也能实现相邻行的对比。
但是，Oracle提供更方便的两个函数 
lead() 与后面某一行对比
lag()  与前面一行对比

按时间排序，显示当前记录的数量以及前后相邻记录的销售数量

select
      dept_id
     ,sale_date
     ,goods_type
     ,sale_cnt
     ,lag(sale_cnt,1) over(order by sale_date) lag_1
     ,lead(sale_cnt,1) over(order by sale_date) lead_1
     ,first_value(sale_cnt) over(order by sale_date rows between 1 preceding and 0 following)
from criss_sales;

最后一列是利用over(order by xxx rows between 1 preceding and 0 following)与 lag做对比。同样可以得到我们希望看到的结果。