Oracle PL/SQL/Analytical Functions/PARTITION

Материал из SQL эксперт
Перейти к: навигация, поиск

Count(*) over partition

   <source lang="sql">
 

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>

 </source>
   
  


count(*) over partition by, order by and range unbounded preceding

   <source lang="sql">
 

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>

 </source>
   
  


Dense_rank over partition by

   <source lang="sql">
 

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>

 </source>
   
  


dense_rank() over partition by, order by

   <source lang="sql">
 

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>

 </source>
   
  


PARTITION BY: divide the groups into subgroups

   <source lang="sql">

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>


 </source>
   
  


PARTITION BY (JOB title) and right outer join

   <source lang="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> 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.


 </source>
   
  


partition clause

   <source lang="sql">
 

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> --

 </source>
   
  


Partitioning with PARTITION_BY

   <source lang="sql">

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>


 </source>
   
  


Partition Window

   <source lang="sql">
 

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> --

 </source>
   
  


rank and dense_rank over partition

   <source lang="sql">
 

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>

 </source>
   
  


SPREADSHEET PARTITION BY

   <source lang="sql">
 

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>


 </source>
   
  


sum salary over PARTITION BY

   <source lang="sql">
 

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.


 </source>
   
  


Top with partition

   <source lang="sql">
 

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> --

 </source>
   
  


Use partitioning in the OVER clause of the aggregate-analytical function like this

   <source lang="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> 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>


 </source>