oracle 统计/分析函数

   Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

语法：

<analytic-function>(<argument>,<argument>,...)
over(
<query-partition-clause>
<order-by-clause>
<windowing-clause>
)

<analytic-function>(<argument>,<argument>,...) 
over( 
<query-partition-clause> 
<order-by-clause> 
<windowing-clause> 
) 

说明：
<1> over是关键字，用于标识分析函数。
<2> <analytic-function>是指定的分析函数的名字。
<3> <argument>为参数，分析函数可以选取0-3个参数。
<4> 分区子句<query-partition-clause>的格式为：
     partition by<value_exp>[,value_expr]...
    关键字partition by子句根据由分区表达式的条件逻辑地将单个结果集分成N组。这里的"分区partition"和"组group" 都是同义词。
<5> 排序子句order-by-clause指定数据是如何存在分区内的。其格式为：
order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last]
其中：
     A.asc|desc:指定了排列顺序。
     B.nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。
<6>窗口子句windowing-clause
    给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中，
可用该子句让分析函数计算出它的值。
格式：
{rows|range}
{between
{unbounded preceding|current row |<value_expr>{preceding|following}
}and
{unbounded preceding|current row |<value_expr>{preceding|following}
}|{unbounded preceding|current row |<value_expr>{preceding|following
}}
    A.rows|range:此关键字定义了一个window。
     B.between...and...:为窗品指一个起点和终点。
     C.unbounded preceding：指明窗口是从分区(partition)的第一行开始。
     D.current row:指明窗口是从当前行开始。

开窗函数：
       开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

• over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
• over（partition by deptno）按照部门分区
• over（order by salary range between 50 preceding and 150 following）每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
• over（order by salary rows between 50 preceding and 150 following）每行对应的数据窗口是之前50行，之后150行
• over（order by salary rows between unbounded preceding and unbounded following）每行对应的数据窗口是从第一行到最后一行，等效： over（order by salary range between unbounded preceding and unbounded following）

1、Oracle ROLLUP和CUBE 用法    
      Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是Group by  ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

      如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，……

      如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0。
示例：

select nvl(area_code,'合计') area_code,sum(local_fare) local_fare
 from t
group by rollup(nvl(area_code,'合计'));

select nvl(area_code,'合计') area_code,sum(local_fare) local_fare
 from t
group by rollup(nvl(area_code,'合计'));

 

select area_code,bill_month,sum(local_fare) local_fare
from t
group by cube(area_code,bill_month)
order by area_code,bill_month nulls last;

select area_code,bill_month,sum(local_fare) local_fare
from t
group by cube(area_code,bill_month)
order by area_code,bill_month nulls last;

 

select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,
 decode(grouping(bill_month),1,'all month',bill_month) bill_month,
 sum(local_fare) local_fare
from t
group by cube(area_code,bill_month)
order by area_code,bill_month nulls last;

select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,
 decode(grouping(bill_month),1,'all month',bill_month) bill_month,
 sum(local_fare) local_fare
from t
group by cube(area_code,bill_month)
order by area_code,bill_month nulls last;

    简单点说：为了生成数据统计以及横向小计统计，可以在GROUP BY子句中使用ROLLUP操作符。为了生成数据统计、横向小计、纵向小计结果，可以使用CUBE操作符。
2、Rank的用法

       功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，则没有序数2，序列将给组中的下一行分配值3，DENSE_RANK则没有任何跳跃。
       rank()是跳跃排序，比如有两个第二名时接下来就是第四名（同样是在各个分组内）

       dense_rank()是连续排序，比如有两个第二名时仍然跟着第三名。
示例：

select area_code,sum(local_fare) local_fare,
rank() over (order by sum(local_fare) desc) fare_rank
from t
group by area_code;

select area_code,sum(local_fare) local_fare,
rank() over (order by sum(local_fare) desc) fare_rank
from t
group by area_code;

结果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765      104548.72   1
5761      54225.41    2
5763      54225.41    2
5764      53156.77    4
5762      52039.62    5

select area_code,sum(local_fare) local_fare,
dense_rank() over (order by sum(local_fare) desc ) fare_rank
from t
group by area_code;

select area_code,sum(local_fare) local_fare,
dense_rank() over (order by sum(local_fare) desc ) fare_rank
from t
group by area_code;

结果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765      104548.72   1
5761      54225.41    2
5763      54225.41    2
5764      53156.77    3 这是这里出现了第三名
5762      52039.62    4

ROW_NUMBER
功能描述：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。

select area_code,sum(local_fare) local_fare,
row_number() over (order by sum(local_fare) desc ) fare_rank
from t
group by area_code;

select area_code,sum(local_fare) local_fare,
row_number() over (order by sum(local_fare) desc ) fare_rank
from t
group by area_code;

结果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765     104548.72    1
5761     54225.41     2
5763     54225.41     3
5764     53156.77     4
rank()示例：
a. 取出数据库中最后入网的n个用户

select user_id,tele_num,user_name,user_status,create_date
from (
select user_id,tele_num,user_name,user_status,create_date,
rank() over (order by create_date desc) add_rank
from user_info
)
where add_rank <= :n;

select user_id,tele_num,user_name,user_status,create_date 
from (
select user_id,tele_num,user_name,user_status,create_date,
rank() over (order by create_date desc) add_rank
from user_info
)
where add_rank <= :n;

b.根据object_name删除数据库中的重复记录
create table t as select obj#,name from sys.obj$;
再insert into t1 select * from t1 数次.

delete from t1 where rowid in (
select row_id from (
select rowid row_id,row_number() over (partition by obj# order by rowid ) rn
) where rn <> 1
);

delete from t1 where rowid in (
select row_id from (
select rowid row_id,row_number() over (partition by obj# order by rowid ) rn
) where rn <> 1
);

c. 取出各地区的话费收入在各个月份排名.

select bill_month,area_code,sum(local_fare) local_fare,
 rank() over (partition by bill_month order by sum(local_fare) desc) area_rank
 from t
 group by bill_month,area_code

select bill_month,area_code,sum(local_fare) local_fare,
 rank() over (partition by bill_month order by sum(local_fare) desc) area_rank
 from t
 group by bill_month,area_code

结果：
BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK
--------------- --------------- -------------- ----------
200405        5765    25057.74      1
200405        5761    13060.43      2
200405        5763    13060.43      2
200405        5762    12643.79      4
200405        5764    12487.79      5
200406        5765    26058.46      1
200406        5761    13318.93      2
200406        5763    13318.93      2
200406        5764    13295.19      4
200406        5762    12795.06      5
200407        5765    26301.88      1
200407        5761    13710.27      2
200407        5763    13710.27      2
200407        5764    13444.09      4
200407        5762    13224.30      5

3、First/Last的用法
    First功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
     Last功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。

示例：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最高的值。

SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id in (20,80)
ORDER BY department_id, salary;

SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id in (20,80)
ORDER BY department_id, salary;

结果：
LAST_NAME DEPARTMENT_ID SALARY Worst Best
------------------------- ------------- ---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000

4、FIRST_VALUE/LAST_VALUE的用法

      FIRST_VALUE、LAST_VALUE是两个分析函数。返回结果集中排在第一位和最后一位的值。语法是：
FIRST_VALUE (expr) OVER ( analytic_clause)
示例：
计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字。

SELECT department_id, last_name, salary, FIRST_VALUE(last_name)
OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal
FROM employees
WHERE department_id in(20,30);

SELECT department_id, last_name, salary, FIRST_VALUE(last_name)
OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal
FROM employees
WHERE department_id in(20,30);

结果：
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------------------- ---------- --------------
20 Fay 6000 Fay
20 Hartstein 13000 Fay
30 Colmenares 2500 Colmenares
30 Himuro 2600 Colmenares
30 Tobias 2800 Colmenares
30 Baida 2900 Colmenares
30 Khoo 3100 Colmenares
30 Raphaely 11000 Colmenares

5、Lag/Lead的用法

       功能描述：可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD。
示例：lag和lead函数介绍取出每个月的上个月和下个月的话费总额

select area_code,bill_month, local_fare cur_local_fare,
 lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
 lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
 lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
 lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
 from (
 select area_code,bill_month,sum(local_fare) local_fare
 from t
 group by area_code,bill_month
 )

select area_code,bill_month, local_fare cur_local_fare,
 lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
 lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
 lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
 lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
 from (
 select area_code,bill_month,sum(local_fare) local_fare
 from t
 group by area_code,bill_month
 )

结果：
AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE
--------- ---------- -------------- -------------- --------------- --------------- ---------------
5761 200405 13060.433 0 0 13318.93 13710.265
5761 200406 13318.93 0 13060.433 13710.265 14135.781
5761 200407 13710.265 13060.433 13318.93 14135.781 0
5761 200408 14135.781 13318.93 13710.265 0 0
5762 200405 12643.791 0 0 12795.06 13224.297
5762 200406 12795.06 0 12643.791 13224.297 13376.468
5762 200407 13224.297 12643.791 12795.06 13376.468 0
5762 200408 13376.468 12795.06 13224.297 0 0

6.RATIO_TO_REPORT用法

功能描述：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献。
格式：RATIO_TO_REPORT (expr) OVER (query_partition_clause)
示例：计算每个员工的工资占该类员工总工资的百分比

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
WHERE job_id = 'PU_CLERK';

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
WHERE job_id = 'PU_CLERK';

7.GROUPING SETS用法
     从Oracle9i开始，使用GROUPING SETS操作符可以合并多个分组的结果。并且可以用GROUP BY GROUPING SETS来代替GROUP BY CUBE。你可以应用来指定你感兴趣的总数组合。因为它不必计算它不需要集合（也不会产生太多结果），所以对SQL引擎来说更为高效。
其格式为：
GROUP BY GROUPING SETS ((list), (list) ... )
      这里（list）是圆括号中的一个列序列，这个组合生成一个总数。要增加一个总和，必须增加一个(NUlL)分组集。

示例：如果只要生成每项产品（包括所有顾客和通道）和每个顾客/通道组合（包括所有产品）的总数。

SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id < 3
GROUP BY GROUPING SETS (
(prod_id), (cust_id, channel_id) ,NULL
);

SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id < 3
GROUP BY GROUPING SETS (
(prod_id), (cust_id, channel_id) ,NULL
);

示例：统计人员的获奖数

SELECT
      (CASE WHEN grouping(a.c_xm)=1 AND grouping(a.n_jxdm)=1 AND grouping(a.c_rydm)=1
            THEN '合计'
            WHEN grouping(a.n_jxdm)=1 AND grouping(a.c_rydm)=1
                 THEN '小计'
                   ELSE a.c_xm
        END) c_xm
      ,a.n_jxdm,a.c_rydm,COUNT(*)
FROM T_SK_JPGL_HJMD a
GROUP BY grouping sets (a.c_xm,(a.c_xm,a.n_jxdm,a.c_rydm),NULL)