分析函数进阶（三）

分析函数进阶（三）



SELECT DEPTNO,
MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) "Worst",
MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) "Best"
FROM emp
GROUP BY deptno;

    DEPTNO      Worst       Best
---------- ---------- ----------
        10       1300       5000
        20        800       3000
        30        950       2850

3 rows selected.

--worst 返回部门里最低的工资
--best  返回部门里最高的工资



--LAG 把分组内指定列整体向下推移一行或多行，并能指定前几行的值。
--LEAD 把分组内指定列整体向上推移一行或多行，并能指定末尾几行的值。
--RESPECT NULLS,IGNORE NULLS 处理空值


SELECT DEPTNO,ENAME,SAL,
LAG(SAL,1,0) OVER (PARTITION BY DEPTNO ORDER BY SAL) LAG_SAL
FROM EMP
GROUP BY DEPTNO,ENAME,SAL;
--group by子句里的列必须是能做主键，不然会少记录

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

14 rows selected.

SELECT DEPTNO,ENAME,SAL,
LEAD(SAL,1,0) OVER (PARTITION BY DEPTNO ORDER BY SAL) LAG_SAL
FROM EMP
GROUP BY DEPTNO,ENAME,SAL;

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

14 rows selected.

--group by后不能做主键时，会自动去重复，因此少了记录

SQL> SELECT DEPTNO,SAL,
LAG(SAL,1,0) OVER (PARTITION BY DEPTNO ORDER BY SAL) LAG_SAL
FROM EMP
GROUP BY DEPTNO,SAL; 

    DEPTNO        SAL    LAG_SAL
---------- ---------- ----------
        10       1300          0
        10       2450       1300
        10       5000       2450
        20        800          0  --部门20与30各少了一条记录
        20       1100        800
        20       2975       1100
        20       3000       2975
        30        950          0
        30       1250        950
        30       1500       1250
        30       1600       1500
        30       2850       1600

12 rows selected.

SELECT DEPTNO,SAL,
LEAD(SAL,1,0) OVER (PARTITION BY DEPTNO ORDER BY SAL) LAG_SAL
FROM EMP
GROUP BY DEPTNO,SAL; 

    DEPTNO        SAL    LAG_SAL
---------- ---------- ----------
        10       1300       2450  
        10       2450       5000
        10       5000          0
        20        800       1100
        20       1100       2975
        20       2975       3000
        20       3000          0
        30        950       1250
        30       1250       1500
        30       1500       1600
        30       1600       2850
        30       2850          0

12 rows selected.


--RESPECT NULLS,没有指定默认为RESPECT NULLS

SQL> SELECT DEPTNO,ENAME,COMM,
LAG(COMM)  OVER (PARTITION BY DEPTNO ORDER BY COMM) LAG_SAL,
LEAD(COMM)  OVER (PARTITION BY DEPTNO ORDER BY COMM) LAG_SAL
FROM EMP
GROUP BY DEPTNO,ENAME,COMM;  

      DEPTNO ENAME            COMM    LAG_SAL    LAG_SAL
---------- ---------- ---------- ---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 ADAMS
        20 FORD
        20 JONES
        20 SCOTT
        20 SMITH
        30 TURNER              0                   300
        30 ALLEN             300          0        500
        30 WARD              500        300       1400
        30 MARTIN           1400        500
        30 BLAKE                       1400
        30 JAMES

14 rows selected.


--IGNORE NULLS

SQL> SELECT DEPTNO,ENAME,COMM,
LAG(COMM) IGNORE NULLS OVER (PARTITION BY DEPTNO ORDER BY COMM) LAG_SAL,
LEAD(COMM) IGNORE NULLS OVER (PARTITION BY DEPTNO ORDER BY COMM) lead_SAL
FROM EMP
GROUP BY DEPTNO,ENAME,COMM;   2    3    4    5  

    DEPTNO ENAME            COMM    LAG_SAL   LEAD_SAL
---------- ---------- ---------- ---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 ADAMS
        20 FORD
        20 JONES
        20 SCOTT
        20 SMITH
        30 TURNER              0                   300
        30 ALLEN             300          0        500
        30 WARD              500        300       1400
        30 MARTIN           1400        500
        30 BLAKE                       1400
        30 JAMES                       1400

14 rows selected.


SELECT DEPTNO,ENAME,SAL,

FIRST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) first_SAL,
last_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) last_SAL
FROM EMP
GROUP BY DEPTNO,ENAME,SAL; 


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

14 rows selected.


--查看部门里入职顺序
col name for a70;
select deptno,listagg(ename,'==>') within group ( order by hiredate,ename) name,
min(hiredate)  min_date 
from emp 
group by deptno;

    DEPTNO NAME                                                                   MIN_DATE
---------- ---------------------------------------------------------------------- ---------
        10 CLARK==>KING==>MILLER                                                  09-JUN-81
        20 SMITH==>JONES==>FORD==>SCOTT==>ADAMS                                   17-DEC-80
        30 ALLEN==>WARD==>BLAKE==>TURNER==>MARTIN==>JAMES                         20-FEB-81


暂时先介绍到这里，等以后有实际用的时候再学习回归线和方差相关。
回归线让我想到了边际成本。呵呵，学金融的望指正。

CORR *
COVAR_POP *
COVAR_SAMP *
NTH_VALUE *
PERCENTILE_CONT
PERCENTILE_DISC
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
VAR_POP *
VAR_SAMP *
VARIANCE *