Contents

1 蠍磯蓋
2 Windowing る
3
4 豢螳襭


1 蠍磯蓋 #

覿 8i 覯 persnal edition, enterprise edition 讌螻, 9i覯 覈 覯 讌. 覓碁 れ螻 螳.

SELECT Analytic_Function ( arguments )
       OVER( [ PARTITION BY 豺朱 ] [ ORDER BY  ] [ Windowing ] )
FROM 企 覈;

  • Partition By : 豌 讌 蠍一 蠏碁9朱
  • Order By : PARTITION BY 企 蠏碁9
  • Windowing : 蠍一朱 覯襯 碁蟆 譟一 (覃伎: window IS a physical or logical SET of rows)

2 Windowing る #

RANGE 螳企, ROWS 襯 覩誤.
- RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING
  : BETWEEN Current_Row -50 AND 150  一 伎 覈 window
    讀, Current_Row 螳 - 50 ~ Current_Row 螳 + 150 螳 覈  蠏碁9

- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  :  蠍一朱 一 伎 / 蟇伎 Window

- RANGE UNBOUNDED PRECEDING
  :   蠍一朱 一 伎 豌 覯讌 蟾讌螳 Window

- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING     
  :  蠍一朱 一 伎 豌 覯讌 覿  蟾讌螳 Window. 讀, 一 覈螳 Window

3 #

蟆襷 危危覃 覿 .
 sql
               
select
        ename
      , sal
      , sum(sal) over(partition by deptno order by sal) ASC朱_覿覲_sal
      , sum(sal) over(partition by deptno) 覿覲_sal螻
      , sum(sal) over(order by deptno, sal) deptno_sal_ASC_
      , sum(sal) over() sal豐螻
from emp
order by deptno, sal;
--over()  oder by   企 螻 伎 覈 豺 蟆 螻 - 蠍一 SUM()
--over()  partition by group by  . 覿  覯襯 讌 .

/*
ENAME            SAL ASC朱_覿覲_SAL 覿覲_SAL螻 DEPTNO_SAL_ASC_    SAL豐螻
---------- --------- ------------------------ -------------- ------------------------- ----------
MILLER       1300.00                     1300           8750                      1300      29025
CLARK        2450.00                     3750           8750                      3750      29025
KING         5000.00                     8750           8750                      8750      29025
SMITH         800.00                      800          10875                      9550      29025
ADAMS        1100.00                     1900          10875                     10650      29025
JONES        2975.00                     4875          10875                     13625      29025
SCOTT        3000.00                    10875          10875                     19625      29025
FORD         3000.00                    10875          10875                     19625      29025
JAMES         950.00                      950           9400                     20575      29025
WARD         1250.00                     3450           9400                     23075      29025
MARTIN       1250.00                     3450           9400                     23075      29025
TURNER       1500.00                     4950           9400                     24575      29025
ALLEN        1600.00                     6550           9400                     26175      29025
BLAKE        2850.00                     9400           9400                     29025      29025

14 rows selected
*/


-- 譴 讌覓企襦 螳 襷 蠍 覦  覯, 讌覓企, 蠍 豢ロ
 
SELECT T1.EMPNO, T2.JOB, T2.MAX_SAL
FROM EMP T1,
          (SELECT JOB, MAX(SAL) AS MAX_SAL 
            FROM EMP
            GROUP BY JOB) T2
WHERE T1.JOB = T2.JOB
AND T1.SAL = T2.MAX_SAL;

/*

EMPNO JOB          MAX_SAL
----- --------- ----------
 7788 ANALYST         3000
 7902 ANALYST         3000
 7934 CLERK           1300
 7566 MANAGER         2975
 7839 PRESIDENT       5000
 7499 SALESMAN        1600

6 rows selected
*/

-- 螳 覿襯 覃  螳 蟆郁骸襯 螳語  . 
--殊 蟆 企 覯襷 曙伎 豌襴襯   る 蟆企. 
select empno, job, max_job_sal sal
from (
select 
         empno
       , job
       , sal
       , max(sal) over (partition by job) max_job_sal 
from emp ) 
where sal = max_job_sal;

/*
EMPNO JOB              SAL
----- --------- ----------
 7788 ANALYST         3000
 7902 ANALYST         3000
 7934 CLERK           1300
 7566 MANAGER         2975
 7839 PRESIDENT       5000
 7499 SALESMAN        1600

6 rows selected
*/

--覈  蠍 蠏 螳 蠏殊  覯螻 蠍 豢
select empno, sal
from (
      select
              empno
            , min(abs_sal) over() min_sal
            , abs_sal
            , sal
      from (
            select
                    empno
                  , sal
                  , abs(sal - avg(sal) over()) abs_sal
            from emp) )
where abs_sal = min_sal;

/*
SQL> select avg(sal) from emp;

  AVG(SAL)
----------
2073.21428

EMPNO       SAL
----- ---------
 7782   2450.00
*/ 

--覿覲襦 朱覯碁ゼ 覿企 螳螳 覿襷 1襦  朱覯碁ゼ 覿碁.

SELECT
        deptno
      , empno
      , ename
      , row_number() over(PARTITION BY deptno ORDER BY deptno)
FROM emp;

/*
DEPTNO EMPNO ENAME      ROW_NUMBER()OVER(PARTITIONBYDE
------ ----- ---------- ------------------------------
    10  7782 CLARK                                   1
    10  7839 KING                                    2
    10  7934 MILLER                                  3
    20  7369 SMITH                                   1
    20  7876 ADAMS                                   2
    20  7902 FORD                                    3
    20  7788 SCOTT                                   4
    20  7566 JONES                                   5
    30  7499 ALLEN                                   1
    30  7698 BLAKE                                   2
    30  7654 MARTIN                                  3
    30  7900 JAMES                                   4
    30  7844 TURNER                                  5
    30  7521 WARD                                    6

14 rows selected
*/

--覿覲 蠍 襷 襦 蟲蠍
SELECT
        empno
      , ename
      , sal
      , rank() over(PARTITION BY deptno ORDER BY sal DESC)
FROM emp;

/*
--rank() over(PARTITION BY deptno ORDER BY sal DESC)襯 
--rank() over(PARTITION BY deptno ORDER BY sal ASC)襦 螻豺覃  . 
--rank()    狩 螳 襴危讌襷 螻給葦 蠍企.
--讀, 1 企豪  2螳企 2 譟伎讌 螻, 覦襦 3朱 願.

EMPNO ENAME            SAL RANK()OVER(PARTITIONBYDEPTNOOR
----- ---------- --------- ------------------------------
 7839 KING         5000.00                              1
 7782 CLARK        2450.00                              2
 7934 MILLER       1300.00                              3
 7788 SCOTT        3000.00                              1
 7902 FORD         3000.00                              1
 7566 JONES        2975.00                              3
 7876 ADAMS        1100.00                              4
 7369 SMITH         800.00                              5
 7698 BLAKE        2850.00                              1
 7499 ALLEN        1600.00                              2
 7844 TURNER       1500.00                              3
 7521 WARD         1250.00                              4
 7654 MARTIN       1250.00                              4
 7900 JAMES         950.00                              6
*/



SELECT
        empno
      , ename
      , sal
      , dense_rank() over(PARTITION BY deptno ORDER BY sal DESC)
FROM emp;

/*
dense_rank() rank() 襴 1螳 2螳 譟伎覃 れ  2螳 .

EMPNO ENAME            SAL DENSE_RANK()OVER(PARTITIONBYDE
----- ---------- --------- ------------------------------
 7839 KING         5000.00                              1
 7782 CLARK        2450.00                              2
 7934 MILLER       1300.00                              3
 7788 SCOTT        3000.00                              1
 7902 FORD         3000.00                              1
 7566 JONES        2975.00                              2
 7876 ADAMS        1100.00                              3
 7369 SMITH         800.00                              4
 7698 BLAKE        2850.00                              1
 7499 ALLEN        1600.00                              2
 7844 TURNER       1500.00                              3
 7521 WARD         1250.00                              4
 7654 MARTIN       1250.00                              4
 7900 JAMES         950.00                              5
*/

--TOP n螳 螳語り鍵
SELECT 
         empno
       , ename
       , sal
       , rank
FROM (
      SELECT
              empno
            , ename
            , sal
            , rank() over(PARTITION BY deptno ORDER BY sal DESC) rank
      FROM emp)
WHERE rank <= 3;    

/*

蟆曙一 磯殊 rank(), dense_rank()襯 . 

EMPNO ENAME            SAL       RANK
----- ---------- --------- ----------
 7839 KING         5000.00          1
 7782 CLARK        2450.00          2
 7934 MILLER       1300.00          3
 7788 SCOTT        3000.00          1
 7902 FORD         3000.00          1
 7566 JONES        2975.00          3
 7698 BLAKE        2850.00          1
 7499 ALLEN        1600.00          2
 7844 TURNER       1500.00          3

*/

--朱
SELECT
         deptno
       , max(CASE WHEN rank = 1 THEN sal END) rank_1
       , max(CASE WHEN rank = 2 THEN sal END) rank_2
       , max(CASE WHEN rank = 3 THEN sal END) rank_3
FROM (
      SELECT 
               sal
             , deptno
             , rank
      FROM (
            SELECT
                    sal
                  , deptno
                  , rank() over(PARTITION BY deptno ORDER BY sal DESC) rank
            FROM emp)
      WHERE rank <= 3 )
GROUP BY deptno      

/*
DEPTNO     RANK_1     RANK_2     RANK_3
------ ---------- ---------- ----------
    10       5000       2450       1300
    20       3000                  2975
    30       2850       1600       1500
*/      

-- ろ 螳語り鍵 lead() lag()
--殊 谿伎 磯ジ / 蠍谿
SELECT 
         empno
       , ename
       , sal - lag(sal, 1) over(ORDER BY hiredate) pre
       , sal
       , sal - lead(sal, 1) over(ORDER BY hiredate) NEXT
FROM emp;

/*
EMPNO ENAME             PRE       SAL       NEXT
----- ---------- ---------- --------- ----------
 7369 SMITH                    800.00       -800
 7499 ALLEN             800   1600.00        350
 7521 WARD             -350   1250.00      -1725
 7566 JONES            1725   2975.00        125
 7698 BLAKE            -125   2850.00        400
 7782 CLARK            -400   2450.00        950
 7844 TURNER           -950   1500.00        250
 7654 MARTIN           -250   1250.00      -3750
 7839 KING             3750   5000.00       4050
 7900 JAMES           -4050    950.00      -2050
 7902 FORD             2050   3000.00       1700
 7934 MILLER          -1700   1300.00      -1700
 7788 SCOTT            1700   3000.00       1900
 7876 ADAMS           -1900   1100.00 
*/

--轟 覯伎 豌/ 
SELECT DEPTNO,ENAME,SAL,
       FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO 
                               ORDER BY SAL DESC
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND UNBOUNDED FOLLOWING) AS FV,
       LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO
                              ORDER BY SAL DESC
                              ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND UNBOUNDED FOLLOWING) AS LV                                        
FROM EMP;

/*
DEPTNO ENAME            SAL FV         LV
------ ---------- --------- ---------- ----------
    10 KING         5000.00 KING       MILLER
    10 CLARK        2450.00 KING       MILLER
    10 MILLER       1300.00 KING       MILLER
    20 SCOTT        3000.00 SCOTT      SMITH
    20 FORD         3000.00 SCOTT      SMITH
    20 JONES        2975.00 SCOTT      SMITH
    20 ADAMS        1100.00 SCOTT      SMITH
    20 SMITH         800.00 SCOTT      SMITH
    30 BLAKE        2850.00 BLAKE      JAMES
    30 ALLEN        1600.00 BLAKE      JAMES
    30 TURNER       1500.00 BLAKE      JAMES
    30 WARD         1250.00 BLAKE      JAMES
    30 MARTIN       1250.00 BLAKE      JAMES
    30 JAMES         950.00 BLAKE      JAMES
*/


--RATIO_TO_REPORT : 蠏碁9   觜
SELECT JOB, ENAME, SAL, 
       RATIO_TO_REPORT(SAL) OVER(PARTITION BY JOB) AS RR
FROM EMP
ORDER BY JOB, ENAME;

/*
JOB       ENAME            SAL         RR
--------- ---------- --------- ----------
ANALYST   FORD         3000.00        0.5
ANALYST   SCOTT        3000.00        0.5
CLERK     ADAMS        1100.00 0.26506024
CLERK     JAMES         950.00 0.22891566
CLERK     MILLER       1300.00 0.31325301
CLERK     SMITH         800.00 0.19277108
MANAGER   BLAKE        2850.00 0.34441087
MANAGER   CLARK        2450.00 0.29607250
MANAGER   JONES        2975.00 0.35951661
PRESIDENT KING         5000.00          1
SALESMAN  ALLEN        1600.00 0.28571428
SALESMAN  MARTIN       1250.00 0.22321428
SALESMAN  TURNER       1500.00 0.26785714
SALESMAN  WARD         1250.00 0.22321428
*/

--
SELECT ENAME,SAL,
       COUNT(*) OVER(ORDER BY SAL
                     RANGE BETWEEN 149 PRECEDING 
                               AND 0 FOLLOWING) AS m_cnt
FROM EMP;

/*
ENAME            SAL      M_CNT
---------- --------- ----------
SMITH         800.00          1
JAMES         950.00          2  ---> 950 - 150  ~  950 + 0, 襷 149襦 覯襯 譴企 1襦 . 
ADAMS        1100.00          2
WARD         1250.00          3
MARTIN       1250.00          3
MILLER       1300.00          3
TURNER       1500.00          1
ALLEN        1600.00          2
CLARK        2450.00          1
BLAKE        2850.00          1
JONES        2975.00          2
SCOTT        3000.00          4
FORD         3000.00          4
KING         5000.00          1
*/

4 豢螳襭 #