[edit]
1 蠍磯蓋 #
覿 8i 覯 persnal edition, enterprise edition 讌螻, 9i覯 覈 覯 讌. 覓碁 れ螻 螳.
SELECT Analytic_Function ( arguments )
OVER( [ PARTITION BY 豺朱 ] [ ORDER BY ] [ Windowing ] )
FROM 企 覈;
- Partition By : 豌 讌 蠍一 蠏碁９朱
- Order By : PARTITION BY 企 蠏碁９
- Windowing : 蠍一朱 覯襯 碁蟆 譟一 (覃伎: window IS a physical or logical SET of rows)
[edit]
2 Windowing る #
RANGE 螳企, ROWS 襯 覩誤.
- RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING
: BETWEEN Current_Row -50 AND 150 一 伎 覈 window
讀, Current_Row 螳 - 50 ~ Current_Row 螳 + 150 螳 覈 蠏碁９
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
: 蠍一朱 一 伎 / 蟇伎 Window
- RANGE UNBOUNDED PRECEDING
: 蠍一朱 一 伎 豌 覯讌 蟾讌螳 Window
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
: 蠍一朱 一 伎 豌 覯讌 覿 蟾讌螳 Window. 讀, 一 覈螳 Window
[edit]
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 : 蠏碁９ 觜
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
*/
鏤