oracle over()解析関数の例

oracle over()  , oracle 8i    ,           .
                             ,    group by           ,            group by             group by      .

--1、over()  (9i over        ,     ,        10g    )
--       
select a.empno, a.ename, sum(a.sal) over() total from emp a;

EMPNO	ENAME	TOTAL
7369	SMITH	29025
7499	ALLEN	29025
7521	WARD	29025
7566	JONES	29025
7654	MARTIN	29025
7698	BLAKE	29025
7782	CLARK	29025
7788	SCOTT	29025
7839	KING	29025
7844	TURNER	29025
7876	ADAMS	29025
7900	JAMES	29025
7902	FORD	29025
7934	MILLER	29025

--2、over(partition by ...)     
--         
select a.empno,
       a.ename,
       b.dname,
       to_char(round(avg(a.sal) over(partition by b.dname), 2),'$999,999,999.99') dept_avg
  from emp a, dept b
 where a.deptno = b.deptno;

EMPNO	ENAME	DNAME	        DEPT_AVG
7934	MILLER	ACCOUNTING      $2,916.67
7839	KING	ACCOUNTING      $2,916.67
7782	CLARK	ACCOUNTING      $2,916.67
7876	ADAMS	RESEARCH	    $2,175.00
7902	FORD	RESEARCH	    $2,175.00
7566	JONES	RESEARCH	    $2,175.00
7369	SMITH	RESEARCH	    $2,175.00
7788	SCOTT	RESEARCH	    $2,175.00
7521	WARD	SALES	        $1,566.67
7844	TURNER	SALES	        $1,566.67
7499	ALLEN	SALES	        $1,566.67
7900	JAMES	SALES	        $1,566.67
7698	BLAKE	SALES	        $1,566.67
7654	MARTIN	SALES	        $1,566.67

--                         
select b.ename, t.ename, t.mgr, t.cnt
  from (select a.empno,
               a.ename,
               a.mgr,
               count(1) over(partition by a.mgr) cnt
          from emp a) t,
       emp b
 where t.mgr = b.empno;

ENAME	ENAME	MGR	CNT
JONES	SCOTT	7566	2
JONES	FORD	7566	2
BLAKE	WARD	7698	5
BLAKE	TURNER	7698	5
BLAKE	ALLEN	7698	5
BLAKE	JAMES	7698	5
BLAKE	MARTIN	7698	5
CLARK	MILLER	7782	1
SCOTT	ADAMS	7788	1
KING	BLAKE	7839	3
KING	JONES	7839	3
KING	CLARK	7839	3
FORD	SMITH	7902	1

--3、over(order by ...)     
select a.empno,
       a.deptno,
       a.ename,
       a.sal,
       sum(a.sal) over(order by a.ename) sum
  from emp a;

EMPNO	DEPTNO	ENAME	SAL	SUM
7876	20	ADAMS	1100.00	1100
7499	30	ALLEN	1600.00	2700
7698	30	BLAKE	2850.00	5550
7782	10	CLARK	2450.00	8000
7902	20	FORD	3000.00	11000
7900	30	JAMES	950.00	11950
7566	20	JONES	2975.00	14925
7839	10	KING	5000.00	19925
7654	30	MARTIN	1250.00	21175
7934	10	MILLER	1300.00	22475
7788	20	SCOTT	3000.00	25475
7369	20	SMITH	800.00	26275
7844	30	TURNER	1500.00	27775
7521	30	WARD	1250.00	29025

--4、over(partition by ... order by ...)       
--             
select t.*
  from (select rank() over(partition by b.dname order by a.sal desc) rk,
               a.empno,
               a.ename,
               b.dname,
               a.sal
          from emp a, dept b
         where a.deptno = b.deptno) t
 where t.rk <= 3;

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