Oracle PL/SQL/Analytical Functions/PARTITION
Содержание
- 1 Count(*) over partition
- 2 count(*) over partition by, order by and range unbounded preceding
- 3 Dense_rank over partition by
- 4 dense_rank() over partition by, order by
- 5 PARTITION BY: divide the groups into subgroups
- 6 PARTITION BY (JOB title) and right outer join
- 7 partition clause
- 8 Partitioning with PARTITION_BY
- 9 Partition Window
- 10 rank and dense_rank over partition
- 11 SPREADSHEET PARTITION BY
- 12 sum salary over PARTITION BY
- 13 Top with partition
- 14 Use partitioning in the OVER clause of the aggregate-analytical function like this
Count(*) over partition
SQL>
SQL> set echo on
SQL> break on deptno skip 1
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL>
SQL>
SQL> select deptno, ename, hiredate,
2 count(*) over (partition by deptno
3 order by hiredate nulls first
4 range 100 preceding) cnt_range,
5 count(*) over (partition by deptno
6 order by hiredate nulls first
7 rows 2 preceding) cnt_rows
8 from emp
9 where deptno in (10, 20)
10 order by deptno, hiredate
11 /
DEPTNO ENAME HIREDATE CNT_RANGE CNT_ROWS
------ ---------- --------- --------- --------
10 CLARK 09-JUN-81 1.00 1.00
KING 17-NOV-81 1.00 2.00
MILLER 23-JAN-82 2.00 3.00
20 SMITH 17-DEC-80 1.00 1.00
JONES 02-APR-81 1.00 2.00
FORD 03-DEC-81 1.00 3.00
SCOTT 09-DEC-82 1.00 3.00
ADAMS 12-JAN-83 2.00 3.00
8 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
count(*) over partition by, order by and range unbounded preceding
SQL>
SQL> set echo on
SQL>
SQL> break on deptno skip 1
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL> select * from ( select deptno, ename, sal,
2 count(*) over ( partition by deptno
3 order by sal desc
4 range unbounded preceding ) cnt
5 from emp )
6 where cnt <= 3
7 order by deptno, sal desc
8 /
DEPTNO ENAME SAL CNT
------ ---------- -------- --------
10 KING 5000.00 1.00
CLARK 2450.00 2.00
MILLER 1300.00 3.00
20 SCOTT 3000.00 2.00
FORD 3000.00 2.00
JONES 2975.00 3.00
30 BLAKE 2850.00 1.00
ALLEN 1600.00 2.00
TURNER 1500.00 3.00
9 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
Dense_rank over partition by
SQL>
SQL> set echo on
SQL> break on deptno skip 1
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL> select *
2 from ( select deptno, ename, sal,
3 dense_rank() over ( partition by deptno
4 order by sal desc ) dr
5 from emp )
6 where dr <= 3
7 order by deptno, sal desc
8 /
DEPTNO ENAME SAL DR
------ ---------- -------- --------
10 KING 5000.00 1.00
CLARK 2450.00 2.00
MILLER 1300.00 3.00
20 SCOTT 3000.00 1.00
FORD 3000.00 1.00
JONES 2975.00 2.00
ADAMS 1100.00 3.00
30 BLAKE 2850.00 1.00
ALLEN 1600.00 2.00
DEPTNO ENAME SAL DR
------ ---------- -------- --------
30 TURNER 1500.00 3.00
10 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
dense_rank() over partition by, order by
SQL>
SQL> create table t
2 as
3 select object_name ename,
4 mod(object_id,50) deptno,
5 object_id sal
6 from all_objects
7 where rownum <= 1000
8 /
Table created.
SQL>
SQL>
SQL> alter session set sql_trace=true;
Session altered.
SQL>
SQL> select *
2 from (select deptno, ename, sal,
3 dense_rank() over (partition by deptno
4 order by sal desc) dr
5 from t )
6 where dr <= 3
7 order by deptno, sal desc
8 /
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
0 V_$LOCK 1050.00 1.00
V_$BUFFER_POOL_STATISTICS 1000.00 2.00
V_$DLM_ALL_LOCKS 950.00 3.00
1 V$LOCK 1051.00 1.00
V$BUFFER_POOL_STATISTICS 1001.00 2.00
V$DLM_ALL_LOCKS 951.00 3.00
2 V_$SESSTAT 1052.00 1.00
V_$INSTANCE_RECOVERY 1002.00 2.00
V_$DLM_LOCKS 952.00 3.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
3 V$SESSTAT 1053.00 1.00
V$INSTANCE_RECOVERY 1003.00 2.00
V$DLM_LOCKS 953.00 3.00
4 V_$MYSTAT 1054.00 1.00
V_$CONTROLFILE 1004.00 2.00
V_$DLM_RESS 954.00 3.00
5 V$MYSTAT 1055.00 1.00
V$CONTROLFILE 1005.00 2.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
5 V$DLM_RESS 955.00 3.00
6 V_$SUBCACHE 1056.00 1.00
V_$LOG 1006.00 2.00
V_$HVMASTER_INFO 956.00 3.00
7 V$SUBCACHE 1057.00 1.00
V$LOG 1007.00 2.00
V$HVMASTER_INFO 957.00 3.00
8 V_$SYSSTAT 1058.00 1.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
8 V_$STANDBY_LOG 1008.00 2.00
V_$GCSHVMASTER_INFO 958.00 3.00
9 V$SYSSTAT 1059.00 1.00
V$STANDBY_LOG 1009.00 2.00
V$GCSHVMASTER_INFO 959.00 3.00
10 V_$STATNAME 1060.00 1.00
V_$DATAGUARD_STATUS 1010.00 2.00
V_$GCSPFMASTER_INFO 960.00 3.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
11 V$STATNAME 1061.00 1.00
V$DATAGUARD_STATUS 1011.00 2.00
V$GCSPFMASTER_INFO 961.00 3.00
12 V_$OSSTAT 1062.00 1.00
V_$THREAD 1012.00 2.00
GV_$DLM_TRAFFIC_CONTROLLER 962.00 3.00
13 V$OSSTAT 1063.00 1.00
V$THREAD 1013.00 2.00
GV$DLM_TRAFFIC_CONTROLLER 963.00 3.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
14 V_$ACCESS 1064.00 1.00
V_$PROCESS 1014.00 2.00
V_$DLM_TRAFFIC_CONTROLLER 964.00 3.00
15 V$ACCESS 1065.00 1.00
V$PROCESS 1015.00 2.00
V$DLM_TRAFFIC_CONTROLLER 965.00 3.00
16 V_$OBJECT_DEPENDENCY 1066.00 1.00
V_$BGPROCESS 1016.00 2.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
16 V_$GES_ENQUEUE 966.00 3.00
17 V$OBJECT_DEPENDENCY 1067.00 1.00
V$BGPROCESS 1017.00 2.00
V$GES_ENQUEUE 967.00 3.00
18 V_$DBFILE 1068.00 1.00
V_$SESSION 1018.00 2.00
V_$GES_BLOCKING_ENQUEUE 968.00 3.00
19 V$DBFILE 1069.00 1.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
19 V$SESSION 1019.00 2.00
V$GES_BLOCKING_ENQUEUE 969.00 3.00
20 V_$FILESTAT 1070.00 1.00
V_$LICENSE 1020.00 2.00
V_$GC_ELEMENT 970.00 3.00
21 V$FILESTAT 1071.00 1.00
V$LICENSE 1021.00 2.00
V$GC_ELEMENT 971.00 3.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
22 V_$TEMPSTAT 1072.00 1.00
V_$TRANSACTION 1022.00 2.00
V_$CR_BLOCK_SERVER 972.00 3.00
23 V$TEMPSTAT 1073.00 1.00
V$TRANSACTION 1023.00 2.00
V$CR_BLOCK_SERVER 973.00 3.00
24 V_$LOGFILE 1074.00 1.00
V_$BSP 1024.00 2.00
V_$CURRENT_BLOCK_SERVER 974.00 3.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
25 V$LOGFILE 1075.00 1.00
V$BSP 1025.00 2.00
V$CURRENT_BLOCK_SERVER 975.00 3.00
26 V_$FLASHBACK_DATABASE_LOGFILE 1076.00 1.00
V_$FAST_START_SERVERS 1026.00 2.00
V_$GC_ELEMENTS_W_COLLISIONS 976.00 3.00
27 V$FLASHBACK_DATABASE_LOGFILE 1077.00 1.00
V$FAST_START_SERVERS 1027.00 2.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
27 V$GC_ELEMENTS_WITH_COLLISIONS 977.00 3.00
28 V_$FLASHBACK_DATABASE_LOG 1078.00 1.00
V_$FAST_START_TRANSACTIONS 1028.00 2.00
V_$FILE_CACHE_TRANSFER 978.00 3.00
29 V$FLASHBACK_DATABASE_LOG 1079.00 1.00
V$FAST_START_TRANSACTIONS 1029.00 2.00
V$FILE_CACHE_TRANSFER 979.00 3.00
30 V_$FLASHBACK_DATABASE_STAT 1080.00 1.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
30 V_$LOCKED_OBJECT 1030.00 2.00
V_$TEMP_CACHE_TRANSFER 980.00 3.00
31 V$FLASHBACK_DATABASE_STAT 1081.00 1.00
V$LOCKED_OBJECT 1031.00 2.00
V$TEMP_CACHE_TRANSFER 981.00 3.00
32 V_$RESTORE_POINT 1082.00 1.00
V_$LATCH 1032.00 2.00
V_$CLASS_CACHE_TRANSFER 982.00 3.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
33 V$RESTORE_POINT 1083.00 1.00
V$LATCH 1033.00 2.00
V$CLASS_CACHE_TRANSFER 983.00 3.00
34 V_$ROLLNAME 1084.00 1.00
V_$LATCH_CHILDREN 1034.00 2.00
V_$BH 984.00 3.00
35 V$ROLLNAME 1085.00 1.00
V$LATCH_CHILDREN 1035.00 2.00
V$BH 985.00 3.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
36 V_$ROLLSTAT 1086.00 1.00
V_$LATCH_PARENT 1036.00 2.00
V_$LOCK_ELEMENT 986.00 3.00
37 V$ROLLSTAT 1087.00 1.00
V$LATCH_PARENT 1037.00 2.00
V$LOCK_ELEMENT 987.00 3.00
38 V_$UNDOSTAT 1088.00 1.00
V_$LATCHNAME 1038.00 2.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
38 V_$LOCKS_WITH_COLLISIONS 988.00 3.00
39 V$UNDOSTAT 1089.00 1.00
V$LATCHNAME 1039.00 2.00
V$LOCKS_WITH_COLLISIONS 989.00 3.00
40 V_$SGA 1090.00 1.00
V_$LATCHHOLDER 1040.00 2.00
V_$FILE_PING 990.00 3.00
41 V$LATCHHOLDER 1041.00 1.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
41 V$FILE_PING 991.00 2.00
V$SQL_OPTIMIZER_ENV 941.00 3.00
42 V_$LATCH_MISSES 1042.00 1.00
V_$TEMP_PING 992.00 2.00
V_$DLM_MISC 942.00 3.00
43 V$LATCH_MISSES 1043.00 1.00
V$TEMP_PING 993.00 2.00
V$DLM_MISC 943.00 3.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
44 V_$SESSION_LONGOPS 1044.00 1.00
V_$CLASS_PING 994.00 2.00
V_$DLM_LATCH 944.00 3.00
45 V$SESSION_LONGOPS 1045.00 1.00
V$CLASS_PING 995.00 2.00
V$DLM_LATCH 945.00 3.00
46 V_$RESOURCE 1046.00 1.00
V_$INSTANCE_CACHE_TRANSFER 996.00 2.00
V_$DLM_CONVERT_LOCAL 946.00 3.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
47 V$RESOURCE 1047.00 1.00
V$INSTANCE_CACHE_TRANSFER 997.00 2.00
V$DLM_CONVERT_LOCAL 947.00 3.00
48 V_$_LOCK 1048.00 1.00
V_$BUFFER_POOL 998.00 2.00
V_$DLM_CONVERT_REMOTE 948.00 3.00
49 V$_LOCK 1049.00 1.00
V$BUFFER_POOL 999.00 2.00
DEPTNO ENAME SAL DR
------ ------------------------------ -------- --------
49 V$DLM_CONVERT_REMOTE 949.00 3.00
150 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>
PARTITION BY: divide the groups into subgroups
SQL> CREATE TABLE all_sales (
2 year INTEGER,
3 month INTEGER,
4 prd_type_id INTEGER,
5 emp_id INTEGER ,
6 amount NUMBER(8, 2)
7 );
Table created.
SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);
1 row created.
SQL>
SQL> select * from all_sales;
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.64
25 rows selected.
SQL>
SQL> --PARTITION BY: divide the groups into subgroups.
SQL>
SQL>
SQL> SELECT
2 prd_type_id, month, SUM(amount),
3 RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank
4 FROM all_sales
5 where amount IS NOT NULL
6 GROUP BY prd_type_id, month
7 ORDER BY prd_type_id, month;
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
1 1 16034.84 2
1 2 28289.3 1
1 4 25026.45 2
1 5 17212.66 1
1 6 31128.92 1
1 7 15644.65 2
1 8 16434.82 2
1 9 19654.57 1
1 10 21764.19 1
1 11 26053.46 1
1 12 10032.64 2
2 1 42669.68 1
2 3 20167.83 1
2 4 25056.45 1
2 7 62654.82 1
2 8 26434.82 1
2 9 15644.65 2
2 10 21264.19 2
2 12 10034.64 1
19 rows selected.
SQL>
SQL> drop table all_sales;
Table dropped.
SQL>
SQL>
PARTITION BY (JOB title) and right outer join
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL> create table departments
2 ( deptno NUMBER(2) constraint D_PK primary key
3 , dname VARCHAR2(10)
4 , location VARCHAR2(8)
5 , mgr NUMBER(4)
6 ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7);
1 row created.
SQL> insert into departments values (20,"TRAINING", "DALLAS", 4);
1 row created.
SQL> insert into departments values (30,"SALES", "CHICAGO", 6);
1 row created.
SQL> insert into departments values (40,"HR", "BOSTON", 9);
1 row created.
SQL>
SQL> select d.dname as department
2 , e.job as job
3 , e.ename as emp
4 from emp e
5 PARTITION BY (JOB)
6 right outer join
7 departments d
8 using (deptno)
9 order by department, job;
DEPARTMENT JOB EMP
---------- -------- --------
ACCOUNTING Designer Chris
Peter
Manager Mike
Tester
TRAINER
HR Designer
Manager
Tester
TRAINER
SALES Designer Black
Manager Jane
Tester Mary
Jack
Wil
Take
TRAINER
DEPARTMENT JOB EMP
---------- -------- --------
TRAINING Designer Jane
Manager
Tester
TRAINER Fake
Smart
Tom
Ana
23 rows selected.
SQL>
SQL> --clear breaks
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table departments;
Table dropped.
partition clause
SQL>
SQL> create table department(
2 dept_no integer
3 ,dept_name varchar(20) not null
4 ,mgr_no integer
5 );
Table created.
SQL>
SQL> insert into department(dept_no, dept_name, mgr_no)values(1, "Design", 1);
1 row created.
SQL> insert into department(dept_no, dept_name, mgr_no)values(2, "Sales", 1);
1 row created.
SQL> insert into department(dept_no, dept_name, mgr_no)values(3, "Development", 1);
1 row created.
SQL>
SQL> create table employee(
2 emp_no integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,zip_4 varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,salary number(3)
14 ,birthdate date
15 ,hiredate date
16 ,title varchar2(20)
17 ,dept_no integer
18 ,mgr integer
19 ,region number
20 ,division number
21 ,total_sales number
22 );
Table created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","223","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created.
SQL>
SQL>
SQL> select lastname , dept_no, salary,
2 sum(salary) over (partition by dept_no) total_salary
3 from employee
4 order by dept_no, lastname
5 /
LASTNAME DEPT_NO SALARY TOTAL_SALARY
-------------------- ---------- ---------- ------------
Bush 1 22 59
Wash 1 12 59
Will 1 25 59
Anderson 2 4 21
Last 2 8 21
Pete 2 9 21
Bar 3 12 35
Horry 3 13 35
Roke 3 10 35
9 rows selected.
SQL>
SQL> drop table employee cascade constraints;
Table dropped.
SQL>
SQL> drop table department cascade constraints;
Table dropped.
SQL>
SQL> --
Partitioning with PARTITION_BY
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, NULL, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), NULL, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, NULL, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), NULL, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, NULL, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 58000 E
105 Robert 15-JAN-84 31000 W
116 Linda 30-JUL-87 53000 E
117 David 31-DEC-90 78000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL>
SQL>
SQL> --Partitioning with PARTITION_BY
SQL>
SQL>
SQL> SELECT empno, ename, region, curr_salary,
2 RANK() OVER(PARTITION BY region ORDER BY curr_salary desc)
3 rank
4 FROM employee
5 ORDER BY region;
EMPNO ENAME R CURR_SALARY RANK
---------- ---------- - ----------- ----------
122 Alison E 1
104 Celia E 58000 2
116 Linda E 53000 3
108 Jode E 29000 4
105 Robert W 1
117 David W 1
123 James W 32000 3
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
Partition Window
SQL> --Window
SQL>
SQL>
SQL> create table department(
2 dept_no integer primary key
3 ,dept_name varchar(20) not null
4 ,mgr_no integer
5 );
Table created.
SQL>
SQL> insert into department(dept_no, dept_name, mgr_no)values(1, "Design", 1);
1 row created.
SQL> insert into department(dept_no, dept_name, mgr_no)values(2, "Sales", 1);
1 row created.
SQL> insert into department(dept_no, dept_name, mgr_no)values(3, "Development", 1);
1 row created.
SQL>
SQL> create table employee(
2 emp_no integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,zip_4 varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,salary number(3)
14 ,birthdate date
15 ,hiredate date
16 ,title varchar2(20)
17 ,dept_no integer references department
18 ,mgr integer
19 ,region number
20 ,division number
21 ,total_sales number
22 );
Table created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","111","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created.
SQL>
SQL>
SQL> select lastname , dept_no, salary,
2 sum(salary) over (partition by dept_no
3 order by lastname
4 rows 1 preceding) "EMP+PREV"
5 from employee
6 order by dept_no, lastname
7 /
LASTNAME DEPT_NO SALARY EMP+PREV
-------------------- ---------- ---------- ----------
Bush 1 22 22
Wash 1 12 34
Will 1 25 37
Anderson 2 4 4
Last 2 8 12
Pete 2 9 17
Bar 3 12 12
Horry 3 13 25
Roke 3 10 23
9 rows selected.
SQL>
SQL> drop table employee;
Table dropped.
SQL> drop table department;
Table dropped.
SQL>
SQL> --
rank and dense_rank over partition
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL> select deptno, ename, sal,
2 dense_rank() over ( partition by deptno order by sal desc ) dr,
3 rank() over ( partition by deptno order by sal desc ) r
4 from emp
5 order by deptno, sal desc
6 /
DEPTNO ENAME SAL DR R
------ ---------- -------- -------- --------
10 KING 5000.00 1.00 1.00
CLARK 2450.00 2.00 2.00
MILLER 1300.00 3.00 3.00
20 SCOTT 3000.00 1.00 1.00
FORD 3000.00 1.00 1.00
JONES 2975.00 2.00 3.00
ADAMS 1100.00 3.00 4.00
SMITH 800.00 4.00 5.00
30 BLAKE 2850.00 1.00 1.00
DEPTNO ENAME SAL DR R
------ ---------- -------- -------- --------
30 ALLEN 1600.00 2.00 2.00
TURNER 1500.00 3.00 3.00
MARTIN 1250.00 4.00 4.00
WARD 1250.00 4.00 4.00
JAMES 950.00 5.00 6.00
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SPREADSHEET PARTITION BY
SQL>
SQL> create table myTable (
2 id varchar2(10),
3 animal varchar2(10),
4 group_id number,
5 percent number );
Table created.
SQL>
SQL> set feedback off
SQL>
SQL> insert into myTable values ("a","Cat",1,0.993);
SQL> insert into myTable values ("a","Cat",2,0.93);
SQL> insert into myTable values ("a","Dog",3,0.90);
SQL> insert into myTable values ("b","Cat",1,0.993);
SQL> insert into myTable values ("b","Dog",3,0.90);
SQL> set feedback on
SQL>
SQL> SELECT id, animal, group_id, s FROM myTable
2 SPREADSHEET PARTITION BY (id)
3 DIMENSION BY (animal, group_id)
4 MEASURES (percent s) IGNORE nav (s["Dog",-1]=s["Dog",1], s["Cat",-1]=s["Cat",1]);
ID ANIMAL GROUP_ID S
---------- ---------- ---------- ----------
a Cat 1 .993
a Cat 2 .93
a Dog 3 .9
b Cat 1 .993
b Dog 3 .9
a Cat -1 .993
a Dog -1 0
b Cat -1 .993
b Dog -1 0
9 rows selected.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
sum salary over PARTITION BY
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> break on mgr
SQL>
SQL> select mgr, ename, sal
2 , sum(sal) over
3 ( PARTITION BY mgr
4 order by mgr, sal, empno
5 range unbounded preceding
6 ) as cumulative
7 from emp
8 order by mgr, sal;
MGR ENAME SAL CUMULATIVE
---------- -------- ---------- ----------
4 Smart 3000 3000
Fake 3000 6000
6 Jane 800 800
Wil 1250 2050
Mary 1250 3300
Take 1500 4800
Jack 1600 6400
7 Mike 1300 1300
8 Ana 1100 1100
9 Chris 2450 2450
Black 2850 5300
Jane 2975 8275
13 Tom 800 800
Peter 5000 5000
14 rows selected.
SQL>
SQL> --clear breaks
SQL>
SQL>
SQL> drop table emp;
Table dropped.
Top with partition
SQL>
SQL>
SQL> create table department(
2 dept_no integer primary key
3 ,dept_name varchar(20) not null
4 ,mgr_no integer
5 );
Table created.
SQL>
SQL> insert into department(dept_no, dept_name, mgr_no)values(1, "Design", 1);
1 row created.
SQL> insert into department(dept_no, dept_name, mgr_no)values(2, "Sales", 1);
1 row created.
SQL> insert into department(dept_no, dept_name, mgr_no)values(3, "Development", 1);
1 row created.
SQL>
SQL> create table employee(
2 emp_no integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,zip_4 varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,salary number(3)
14 ,birthdate date
15 ,hiredate date
16 ,title varchar2(20)
17 ,dept_no integer
18 ,mgr integer
19 ,region number
20 ,division number
21 ,total_sales number
22 );
Table created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","111","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created.
SQL>
SQL>
SQL> select *
2 from (select dept_no, lastname, salary,
3 row_number() over (partition by dept_no
4 order by salary desc) as row_num
5 from employee)
6 where row_num <= 2
7 /
DEPT_NO LASTNAME SALARY ROW_NUM
---------- -------------------- ---------- ----------
1 Will 25 1
1 Bush 22 2
2 Pete 9 1
2 Last 8 2
3 Horry 13 1
3 Bar 12 2
6 rows selected.
SQL>
SQL> drop table employee;
Table dropped.
SQL> drop table department;
Table dropped.
SQL>
SQL> --
Use partitioning in the OVER clause of the aggregate-analytical function like this
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> select * from employee;
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL> -- Use partitioning in the OVER clause of the aggregate-analytical function like this:
SQL>
SQL> SELECT empno, ename, orig_salary, region,
2 ROUND(AVG(orig_salary) OVER(PARTITION BY region))
3 "Avg. Salary"
4 FROM employee
5 ORDER BY region, ename;
EMPNO ENAME ORIG_SALARY R Avg. Salary
---------- ---------- ----------- - -----------
122 Alison 45000 E 40500
104 Celia 53000 E 40500
108 Jode 21000 E 40500
116 Linda 43000 E 40500
117 David 78000 W 44000
123 James 23000 W 44000
105 Robert 31000 W 44000
7 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>