分析函数进阶（一）

分析函数进阶（一）

一、常用分析函数介绍

--分析函数可以并行，这是它应用比较广泛的一个原因。

--分析函数可以实现以下几类功能
1.Rankings and percentiles    --排序和计算比例
2.Moving window calculations  --移动窗口计算
3.Lag/lead analysis           --    
4.First/last analysis         --
5.Linear regression statistics	--线性回归		

注意事项：

窗口计算时，对于处在边界上的数据，
分析函数直接处理满足条件的数据[可用的数据]返回结果而不报错
NULLS FIRST | NULLS LAST  --放置null位置，默认null比任何其他值都大
PARTITION BY              --分组，先分组再排序，默认全部结果为一组
ORDER BY                  --排序,默认升序
				

分析函数处理的步骤

1.处理joins,where,group by,having 
2.利用分析函数处理步骤1得到结果集
3.处理order by子句，得到用户需要的显示结果

oracle 11.2.0.4 有以下32个分析函数

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *


--常用的分析函数

select deptno,ename,sal,
rank() over (partition by deptno order by sal desc) as R_num,   
dense_rank() over (partition by deptno order by sal desc) as dense_R, 
avg(sal) over (partition by deptno)  avg_sal,   --部门平均工资
sum(sal) over (partition by deptno)  sum_sal,   --部门工资总和
min(sal) over (partition by deptno)  min_sal,   --本门最小工资
max(sal) over (partition by deptno)  max_sal,   --部门最大工资
count(*) over (partition by deptno)  count_num  --部门人数
from emp 
group by deptno,ename,sal;

    DEPTNO ENAME             SAL      R_NUM    DENSE_R    AVG_SAL    SUM_SAL    MIN_SAL    MAX_SAL  COUNT_NUM
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 KING             5000          1          1 2916.66667       8750       1300       5000          3
        10 CLARK            2450          2          2 2916.66667       8750       1300       5000          3
        10 MILLER           1300          3          3 2916.66667       8750       1300       5000          3
        20 FORD             3000          1          1       2175      10875        800       3000          5
        20 SCOTT            3000          1          1       2175      10875        800       3000          5
        20 JONES            2975          3          2       2175      10875        800       3000          5
        20 ADAMS            1100          4          3       2175      10875        800       3000          5
        20 SMITH             800          5          4       2175      10875        800       3000          5
        30 BLAKE            2850          1          1 1566.66667       9400        950       2850          6
        30 ALLEN            1600          2          2 1566.66667       9400        950       2850          6
        30 TURNER           1500          3          3 1566.66667       9400        950       2850          6
        30 MARTIN           1250          4          4 1566.66667       9400        950       2850          6
        30 WARD             1250          4          4 1566.66667       9400        950       2850          6
        30 JAMES             950          6          5 1566.66667       9400        950       2850          6

14 rows selected.

Plan hash value: 4115955660

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     14 |00:00:00.01 |       6 |       |       |          |
|   1 |  WINDOW BUFFER      |      |      1 |     14 |     14 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   2 |   SORT GROUP BY     |      |      1 |     14 |     14 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1539  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> 

--ROW_NUMBER()

SQL> SELECT * FROM (select deptno ,ename,sal,
ROW_NUMBER() OVER ( partition by deptno ORDER BY sal DESC) AS ROWN --排序
from emp
group by deptno,ename,sal) A  WHERE A.ROWN<3 ; 


    DEPTNO ENAME             SAL       ROWN
---------- ---------- ---------- ----------
        10 KING             5000          1
        10 CLARK            2450          2
        20 FORD             3000          1
        20 SCOTT            3000          2
        30 BLAKE            2850          1
        30 ALLEN            1600          2

6 rows selected.


--CUME_DIST(x) =  number of values in S coming before and including x in the specified order/ N

SQL> select deptno,ename,sal,CUME_DIST() OVER ( partition by deptno ORDER BY SUM(sal),ENAME) AS CUME_DIST_PERCENT from emp
group by deptno,ename,sal; 

    DEPTNO ENAME             SAL CUME_DIST_PERCENT
---------- ---------- ---------- -----------------
        10 MILLER           1300        .333333333
        10 CLARK            2450        .666666667
        10 KING             5000                 1
        20 SMITH             800                .2
        20 ADAMS            1100                .4
        20 JONES            2975                .6
        20 FORD             3000                .8
        20 SCOTT            3000                 1
        30 JAMES             950        .166666667
        30 MARTIN           1250        .333333333
        30 WARD             1250                .5
        30 TURNER           1500        .666666667
        30 ALLEN            1600        .833333333
        30 BLAKE            2850                 1

14 rows selected.

SQL> 

--PERCENT_RANK=(rank of row in its partition - 1) / (number of rows in the partition - 1)

SQL> select deptno,ename,sal,PERCENT_RANK() OVER ( partition by deptno ORDER BY SUM(sal)) AS CUME_DIST_PERCENT from emp
group by deptno,ename,sal;  

      DEPTNO ENAME             SAL CUME_DIST_PERCENT
---------- ---------- ---------- -----------------
        10 MILLER           1300                 0
        10 CLARK            2450                .5
        10 KING             5000                 1
        20 SMITH             800                 0
        20 ADAMS            1100               .25
        20 JONES            2975                .5
        20 SCOTT            3000               .75
        20 FORD             3000               .75
        30 JAMES             950                 0
        30 MARTIN           1250                .2
        30 WARD             1250                .2
        30 TURNER           1500                .6
        30 ALLEN            1600                .8
        30 BLAKE            2850                 1

14 rows selected.

--分桶函数



SQL> select deptno,ename,sal,NTILE(3) OVER ( partition by deptno ORDER BY SUM(sal) DESC) AS ROWN from emp
group by deptno,ename,sal; 

      DEPTNO ENAME             SAL       ROWN
---------- ---------- ---------- ----------
        10 KING             5000          1
        10 CLARK            2450          2
        10 MILLER           1300          3
        20 FORD             3000          1
        20 SCOTT            3000          1
        20 JONES            2975          2
        20 ADAMS            1100          2
        20 SMITH             800          3
        30 BLAKE            2850          1
        30 ALLEN            1600          1
        30 TURNER           1500          2
        30 MARTIN           1250          2
        30 WARD             1250          3
        30 JAMES             950          3

14 rows selected.