그룹핑 GRUOPING , WINDOW FUNCTION 윈도우 함수

SQLD

그룹핑 GRUOPING , WINDOW FUNCTION 윈도우 함수

쿵도리 2018. 4. 11. 17:53

SYS@orcl>startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 557846096 bytes

Database Buffers 285212672 bytes

Redo Buffers 5132288 bytes

Database mounted.

Database opened.

SYS@orcl>conn hr/jr

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

@>conn hr/hr

Connected.

HR@orcl>!clear

HR@orcl>select department_id, sum(salary),

2 count(employee_id)

3 from employees

4 group by department_id;

DEPARTMENT_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ----------- ------------------

14240 2

20 22800 2

90 59312 3

110 24360 2

80 44155.92 6

50 21000 5

10 5280 1

60 24098.4 3

8 rows selected.

HR@orcl>ed

Wrote file afiedt.buf

1 select department_id, job_id, sum(salary),

2 count(employee_id)

3 from employees

4* group by department_id,job_id

HR@orcl>/

DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ---------- ----------- ------------------

110 AC_ACCOUNT 9960 1

IT_PROG 5000 1

80 SA_REP 31555.92 5

90 AD_VP 51612 2

50 ST_CLERK 14040 4

110 AC_MGR 14400 1

90 SA_REP 7700 1

60 SA_REP 6098.4 1

50 ST_MAN 6960 1

80 SA_MAN 12600 1

20 MK_MAN 15600 1

DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ---------- ----------- ------------------

60 IT_PROG 18000 2

SA_REP 9240 1

10 AD_ASST 5280 1

20 MK_REP 7200 1

15 rows selected.

HR@orcl>ed

Wrote file afiedt.buf

1 select department_id, job_id, sum(salary),

2 count(employee_id)

3 from employees

4* group by ROLLUP (department_id,job_id)

HR@orcl>/

DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ---------- ----------- ------------------

SA_REP 9240 1

IT_PROG 5000 1

14240 2

10 AD_ASST 5280 1

10 5280 1

20 MK_MAN 15600 1

20 MK_REP 7200 1

20 22800 2

50 ST_MAN 6960 1

50 ST_CLERK 14040 4

50 21000 5

DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ---------- ----------- ------------------

60 SA_REP 6098.4 1

60 IT_PROG 18000 2

60 24098.4 3

80 SA_MAN 12600 1

80 SA_REP 31555.92 5

80 44155.92 6

90 AD_VP 51612 2

90 SA_REP 7700 1

90 59312 3

110 AC_MGR 14400 1

110 AC_ACCOUNT 9960 1

DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ---------- ----------- ------------------

110 24360 2

215246.32 24

24 rows selected.

HR@orcl>ed

Wrote file afiedt.buf

1 select department_id, job_id, sum(salary),

2 count(employee_id)

3 from employees

4* group by cube (department_id,job_id)

HR@orcl>/

DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ---------- ----------- ------------------

14240 2

215246.32 24

AD_VP 51612 2

AC_MGR 14400 1

MK_MAN 15600 1

MK_REP 7200 1

SA_MAN 12600 1

SA_REP 9240 1

SA_REP 54594.32 8

ST_MAN 6960 1

AD_ASST 5280 1

DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ---------- ----------- ------------------

IT_PROG 5000 1

IT_PROG 23000 3

ST_CLERK 14040 4

AC_ACCOUNT 9960 1

10 5280 1

10 AD_ASST 5280 1

20 22800 2

20 MK_MAN 15600 1

20 MK_REP 7200 1

50 21000 5

50 ST_MAN 6960 1

DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ---------- ----------- ------------------

50 ST_CLERK 14040 4

60 24098.4 3

60 SA_REP 6098.4 1

60 IT_PROG 18000 2

80 44155.92 6

80 SA_MAN 12600 1

80 SA_REP 31555.92 5

90 59312 3

90 AD_VP 51612 2

90 SA_REP 7700 1

110 24360 2

DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)

------------- ---------- ----------- ------------------

110 AC_MGR 14400 1

110 AC_ACCOUNT 9960 1

35 rows selected.

HR@orcl>select department_id, job_id, sum(salary)

2 from employees

3 group by department_id, job_id;

DEPARTMENT_ID JOB_ID SUM(SALARY)

------------- ---------- -----------

110 AC_ACCOUNT 9960

IT_PROG 5000

80 SA_REP 31555.92

90 AD_VP 51612

50 ST_CLERK 14040

110 AC_MGR 14400

90 SA_REP 7700

60 SA_REP 6098.4

50 ST_MAN 6960

80 SA_MAN 12600

20 MK_MAN 15600

DEPARTMENT_ID JOB_ID SUM(SALARY)

------------- ---------- -----------

60 IT_PROG 18000

SA_REP 9240

10 AD_ASST 5280

20 MK_REP 7200

15 rows selected.

HR@orcl>c/by/by rollup

3* group by rollup department_id, job_id

HR@orcl>l

1 select department_id, job_id, sum(salary)

2 from employees

3* group by rollup department_id, job_id

HR@orcl>/

group by rollup department_id, job_id

ERROR at line 3:

ORA-00933: SQL command not properly ended

HR@orcl>l

1 select department_id, job_id, sum(salary)

2 from employees

3* group by rollup department_id, job_id

HR@orcl>ed

Wrote file afiedt.buf

1 select department_id, job_id, sum(salary)

2 from employees

3* group by rollup (department_id, job_id)

HR@orcl>/

DEPARTMENT_ID JOB_ID SUM(SALARY)

------------- ---------- -----------

SA_REP 9240

IT_PROG 5000

14240

10 AD_ASST 5280

10 5280

20 MK_MAN 15600

20 MK_REP 7200

20 22800

50 ST_MAN 6960

50 ST_CLERK 14040

50 21000

DEPARTMENT_ID JOB_ID SUM(SALARY)

------------- ---------- -----------

60 SA_REP 6098.4

60 IT_PROG 18000

60 24098.4

80 SA_MAN 12600

80 SA_REP 31555.92

80 44155.92

90 AD_VP 51612

90 SA_REP 7700

90 59312

110 AC_MGR 14400

110 AC_ACCOUNT 9960

DEPARTMENT_ID JOB_ID SUM(SALARY)

------------- ---------- -----------

110 24360

215246.32

24 rows selected.

HR@orcl>c/by rollup/ by cube

3* group by cube (department_id, job_id)

HR@orcl>l

1 select department_id, job_id, sum(salary)

2 from employees

3* group by cube (department_id, job_id)

HR@orcl>/

DEPARTMENT_ID JOB_ID SUM(SALARY)

------------- ---------- -----------

14240

215246.32

AD_VP 51612

AC_MGR 14400

MK_MAN 15600

MK_REP 7200

SA_MAN 12600

SA_REP 9240

SA_REP 54594.32

ST_MAN 6960

AD_ASST 5280

DEPARTMENT_ID JOB_ID SUM(SALARY)

------------- ---------- -----------

IT_PROG 5000

IT_PROG 23000

ST_CLERK 14040

AC_ACCOUNT 9960

10 5280

10 AD_ASST 5280

20 22800

20 MK_MAN 15600

20 MK_REP 7200

50 21000

50 ST_MAN 6960

DEPARTMENT_ID JOB_ID SUM(SALARY)

------------- ---------- -----------

50 ST_CLERK 14040

60 24098.4

60 SA_REP 6098.4

60 IT_PROG 18000

80 44155.92

80 SA_MAN 12600

80 SA_REP 31555.92

90 59312

90 AD_VP 51612

90 SA_REP 7700

110 24360

DEPARTMENT_ID JOB_ID SUM(SALARY)

------------- ---------- -----------

110 AC_MGR 14400

110 AC_ACCOUNT 9960

35 rows selected.

HR@orcl>ed

Wrote file afiedt.buf

1 select department_id, job_id, sum(salary),

2 grouping(department_id), grouping (job_id)

3 from employees

4* group by rollup (department_id, job_id)

HR@orcl>/

DEPARTMENT_ID JOB_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)

------------- ---------- ----------- ----------------------- ----------------

SA_REP 9240 0 0

IT_PROG 5000 0 0

14240 0 1

10 AD_ASST 5280 0 0

10 5280 0 1

20 MK_MAN 15600 0 0

20 MK_REP 7200 0 0

20 22800 0 1

50 ST_MAN 6960 0 0

50 ST_CLERK 14040 0 0

50 21000 0 1

DEPARTMENT_ID JOB_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)

------------- ---------- ----------- ----------------------- ----------------

60 SA_REP 6098.4 0 0

60 IT_PROG 18000 0 0

60 24098.4 0 1

80 SA_MAN 12600 0 0

80 SA_REP 31555.92 0 0

80 44155.92 0 1

90 AD_VP 51612 0 0

90 SA_REP 7700 0 0

90 59312 0 1

110 AC_MGR 14400 0 0

110 AC_ACCOUNT 9960 0 0

DEPARTMENT_ID JOB_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)

------------- ---------- ----------- ----------------------- ----------------

110 24360 0 1

215246.32 1 1

24 rows selected.

HR@orcl>ed

Wrote file afiedt.buf

1 select department_id, job_id, sum(salary),

2 grouping(department_id) did, grouping (job_id) jid

3 from employees

4* group by rollup (department_id, job_id)

HR@orcl>/

DEPARTMENT_ID JOB_ID SUM(SALARY) DID JID

------------- ---------- ----------- ---------- ----------

SA_REP 9240 0 0

IT_PROG 5000 0 0

14240 0 1

10 AD_ASST 5280 0 0

10 5280 0 1

20 MK_MAN 15600 0 0

20 MK_REP 7200 0 0

20 22800 0 1

50 ST_MAN 6960 0 0

50 ST_CLERK 14040 0 0

50 21000 0 1

DEPARTMENT_ID JOB_ID SUM(SALARY) DID JID

------------- ---------- ----------- ---------- ----------

60 SA_REP 6098.4 0 0

60 IT_PROG 18000 0 0

60 24098.4 0 1

80 SA_MAN 12600 0 0

80 SA_REP 31555.92 0 0

80 44155.92 0 1

90 AD_VP 51612 0 0

90 SA_REP 7700 0 0

90 59312 0 1

110 AC_MGR 14400 0 0

110 AC_ACCOUNT 9960 0 0

DEPARTMENT_ID JOB_ID SUM(SALARY) DID JID

------------- ---------- ----------- ---------- ----------

110 24360 0 1

215246.32 1 1

24 rows selected.

HR@orcl>select department_id, job_

HR@orcl>ed

Wrote file afiedt.buf

1 select department_id, job_id, manager_id, avg(salary)

2 from employees

3 group by grouping sets

4* ((department_id, job_id),(job_id,manager_id))

HR@orcl>/

DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)

------------- ---------- ---------- -----------

AC_MGR 101 14400

MK_MAN 100 15600

ST_MAN 100 6960

AD_ASST 101 5280

IT_PROG 102 10800

IT_PROG 103 7200

AC_ACCOUNT 205 9960

SA_REP 7700

IT_PROG 5000

AD_VP 100 25806

SA_REP 103 6098.4

DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)

------------- ---------- ---------- -----------

ST_CLERK 124 3510

SA_REP 149 12498.64

SA_REP 107 1100

MK_REP 201 7200

SA_MAN 100 12600

110 AC_ACCOUNT 9960

IT_PROG 5000

80 SA_REP 6311.184

90 AD_VP 25806

50 ST_CLERK 3510

110 AC_MGR 14400

DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)

------------- ---------- ---------- -----------

90 SA_REP 7700

60 SA_REP 6098.4

50 ST_MAN 6960

80 SA_MAN 12600

20 MK_MAN 15600

60 IT_PROG 9000

SA_REP 9240

10 AD_ASST 5280

20 MK_REP 7200

31 rows selected.

HR@orcl>show pagesize

pagesize 14

HR@orcl>set pagesize 1000

HR@orcl>l

1 select department_id, job_id, manager_id, avg(salary)

2 from employees

3 group by grouping sets

4* ((department_id, job_id),(job_id,manager_id))

HR@orcl>/

DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)

------------- ---------- ---------- -----------

AC_MGR 101 14400

MK_MAN 100 15600

ST_MAN 100 6960

AD_ASST 101 5280

IT_PROG 102 10800

IT_PROG 103 7200

AC_ACCOUNT 205 9960

SA_REP 7700

IT_PROG 5000

AD_VP 100 25806

SA_REP 103 6098.4

ST_CLERK 124 3510

SA_REP 149 12498.64

SA_REP 107 1100

MK_REP 201 7200

SA_MAN 100 12600

110 AC_ACCOUNT 9960

IT_PROG 5000

80 SA_REP 6311.184

90 AD_VP 25806

50 ST_CLERK 3510

110 AC_MGR 14400

90 SA_REP 7700

60 SA_REP 6098.4

50 ST_MAN 6960

80 SA_MAN 12600

20 MK_MAN 15600

60 IT_PROG 9000

SA_REP 9240

10 AD_ASST 5280

20 MK_REP 7200

31 rows selected.

HR@orcl>show execution plan

SP2-0158: unknown SHOW option "execution"

SP2-0158: unknown SHOW option "plan"

HR@orcl>show plan

SP2-0158: unknown SHOW option "plan"

HR@orcl>conn scott/scott

Connected.

SCOTT@orcl>select job, ename, sal

2 rank() over(orderby sal desc)all_rank,

3 rank() over (partition by job order by sal desc) job_rank

4 from emp;

rank() over(orderby sal desc)all_rank,

ERROR at line 2:

ORA-00923: FROM keyword not found where expected

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select job, ename, sal,

2 rank() over(orderby sal desc)all_rank,

3 rank() over (partition by job order by sal desc) job_rank

4* from emp

SCOTT@orcl>/

rank() over(orderby sal desc)all_rank,

ERROR at line 2:

ORA-00907: missing right parenthesis

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select job, ename, sal,

2 rank()over(orderby sal desc)all_rank,

3 rank()over(partition by job order by sal desc)job_rank

4* from emp

SCOTT@orcl>/

rank()over(orderby sal desc)all_rank,

ERROR at line 2:

ORA-00907: missing right parenthesis

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select job, ename, sal,

2 rank()over(order by sal desc)all_rank,

3 rank()over(partition by job order by sal desc)job_rank

4* from emp

SCOTT@orcl>/

JOB ENAME SAL ALL_RANK JOB_RANK

--------- ---------- ---------- ---------- ----------

PRESIDENT KING 5000 1 1

ANALYST FORD 3000 2 1

ANALYST SCOTT 3000 2 1

MANAGER JONES 2975 4 1

MANAGER BLAKE 2850 5 2

MANAGER CLARK 2450 6 3

SALESMAN ALLEN 1600 7 1

SALESMAN TURNER 1500 8 2

CLERK MILLER 1300 9 1

SALESMAN WARD 1250 10 3

SALESMAN MARTIN 1250 10 3

CLERK ADAMS 1100 12 2

CLERK JAMES 950 13 3

CLERK SMITH 800 14 4

14 rows selected.

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select job, ename, sal,

2 rank()over(order by sal desc)all_rank,

3 rank()over(partition by job order by sal desc)job_rank

4 from emp

5* order by 1

SCOTT@orcl>/

JOB ENAME SAL ALL_RANK JOB_RANK

--------- ---------- ---------- ---------- ----------

ANALYST FORD 3000 2 1

ANALYST SCOTT 3000 2 1

CLERK MILLER 1300 9 1

CLERK ADAMS 1100 12 2

CLERK JAMES 950 13 3

CLERK SMITH 800 14 4

MANAGER JONES 2975 4 1

MANAGER BLAKE 2850 5 2

MANAGER CLARK 2450 6 3

PRESIDENT KING 5000 1 1

SALESMAN ALLEN 1600 7 1

SALESMAN TURNER 1500 8 2

SALESMAN WARD 1250 10 3

SALESMAN MARTIN 1250 10 3

14 rows selected.

SCOTT@orcl>select job, ename, sal,

2 rank() over (partition by job order by sal desc) job_rank

3 from emp;

JOB ENAME SAL JOB_RANK

--------- ---------- ---------- ----------

ANALYST FORD 3000 1

ANALYST SCOTT 3000 1

CLERK MILLER 1300 1

CLERK ADAMS 1100 2

CLERK JAMES 950 3

CLERK SMITH 800 4

MANAGER JONES 2975 1

MANAGER BLAKE 2850 2

MANAGER CLARK 2450 3

PRESIDENT KING 5000 1

SALESMAN ALLEN 1600 1

SALESMAN TURNER 1500 2

SALESMAN MARTIN 1250 3

SALESMAN WARD 1250 3

14 rows selected.

SCOTT@orcl>2

2* rank() over (partition by job order by sal desc) job_rank

SCOTT@orcl>select job, ename, sal,

2 rank() over (order by sal desc) rank,

3 dense_rank() over (order by sal desc) dense_rank <<<<1224가 아니라 12234로 가는 것을 알 수 있다

4 from emp;

JOB ENAME SAL RANK DENSE_RANK

--------- ---------- ---------- ---------- ----------

PRESIDENT KING 5000 1 1

ANALYST FORD 3000 2 2

ANALYST SCOTT 3000 2 2

MANAGER JONES 2975 4 3

MANAGER BLAKE 2850 5 4

MANAGER CLARK 2450 6 5

SALESMAN ALLEN 1600 7 6

SALESMAN TURNER 1500 8 7

CLERK MILLER 1300 9 8

SALESMAN WARD 1250 10 9

SALESMAN MARTIN 1250 10 9

CLERK ADAMS 1100 12 10

CLERK JAMES 950 13 11

CLERK SMITH 800 14 12

14 rows selected.

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select job, ename, sal,

2 rank() over (order by sal desc) rank,

3 row_number() over (order by sal desc) row_number <<<< row_id 처럼 중복없이 유니크한 번호를 던져준다.

4* from emp

SCOTT@orcl>/

JOB ENAME SAL RANK ROW_NUMBER

--------- ---------- ---------- ---------- ----------

PRESIDENT KING 5000 1 1

ANALYST FORD 3000 2 2

ANALYST SCOTT 3000 2 3

MANAGER JONES 2975 4 4

MANAGER BLAKE 2850 5 5

MANAGER CLARK 2450 6 6

SALESMAN ALLEN 1600 7 7

SALESMAN TURNER 1500 8 8

CLERK MILLER 1300 9 9

SALESMAN WARD 1250 10 10

SALESMAN MARTIN 1250 10 11

CLERK ADAMS 1100 12 12

CLERK JAMES 950 13 13

CLERK SMITH 800 14 14

14 rows selected.

SCOTT@orcl>select mgr, ename, sal, sum(sal) over (partition by mgr) mgr_sum from emp; <<< 매니저 아이디 별로 sum(sal)한 값 출력

MGR ENAME SAL MGR_SUM

---------- ---------- ---------- ----------

7566 FORD 3000 6000

7566 SCOTT 3000 6000

7698 JAMES 950 6550

7698 ALLEN 1600 6550

7698 WARD 1250 6550

7698 TURNER 1500 6550

7698 MARTIN 1250 6550

7782 MILLER 1300 1300

7788 ADAMS 1100 1100

7839 BLAKE 2850 8275

7839 JONES 2975 8275

7839 CLARK 2450 8275

7902 SMITH 800 800

KING 5000 5000

14 rows selected.

SCOTT@orcl>select mgr, ename, sal, avg(sal) over (partition by mgr) avg_sum << 매니저 아이디 별로 avg(sal)한 값

2 from emp;

MGR ENAME SAL AVG_SUM

---------- ---------- ---------- ----------

7566 FORD 3000 3000

7566 SCOTT 3000 3000

7698 JAMES 950 1310

7698 ALLEN 1600 1310

7698 WARD 1250 1310

7698 TURNER 1500 1310

7698 MARTIN 1250 1310

7782 MILLER 1300 1300

7788 ADAMS 1100 1100

7839 BLAKE 2850 2758.33333

7839 JONES 2975 2758.33333

MGR ENAME SAL AVG_SUM

---------- ---------- ---------- ----------

7839 CLARK 2450 2758.33333

7902 SMITH 800 800

KING 5000 5000

14 rows selected.

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select mgr, ename, sal, count(sal) over (partition by mgr) count_sum << 매니저 아이디 별로 count한 값

2* from emp

SCOTT@orcl>/

MGR ENAME SAL COUNT_SUM

---------- ---------- ---------- ----------

7566 FORD 3000 2

7566 SCOTT 3000 2

7698 JAMES 950 5

7698 ALLEN 1600 5

7698 WARD 1250 5

7698 TURNER 1500 5

7698 MARTIN 1250 5

7782 MILLER 1300 1

7788 ADAMS 1100 1

7839 BLAKE 2850 3

7839 JONES 2975 3

MGR ENAME SAL COUNT_SUM

---------- ---------- ---------- ----------

7839 CLARK 2450 3

7902 SMITH 800 1

KING 5000 1

14 rows selected.

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select mgr, ename, sal, sum(sal) over (partition by mgr order by sal range unbounded preceding) as mgr_sum

2* from emp

SCOTT@orcl>/

MGR ENAME SAL MGR_SUM

---------- ---------- ---------- ----------

7566 SCOTT 3000 6000

7566 FORD 3000 6000

7698 JAMES 950 950

7698 WARD 1250 3450

7698 MARTIN 1250 3450

7698 TURNER 1500 4950

7698 ALLEN 1600 6550

7782 MILLER 1300 1300

7788 ADAMS 1100 1100

7839 CLARK 2450 2450

7839 BLAKE 2850 5300

MGR ENAME SAL MGR_SUM

---------- ---------- ---------- ----------

7839 JONES 2975 8275

7902 SMITH 800 800

KING 5000 5000

14 rows selected.

SCOTT@orcl>SELECT MGR, ENAME, HIREDATE, SAL,

2 ROUND(AVG(SAL)OVER(PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS MGR_AVG

3 FROM EMP;

MGR ENAME HIREDATE SAL MGR_AVG <<행의 본인 앞과 뒤를 비교해서 평균 낸 값을 출력

---------- ---------- --------- ---------- ----------

7566 FORD 03-DEC-81 3000 3000

7566 SCOTT 19-APR-87 3000 3000

7698 ALLEN 20-FEB-81 1600 1425

7698 WARD 22-FEB-81 1250 1450

7698 TURNER 08-SEP-81 1500 1333

7698 MARTIN 28-SEP-81 1250 1233

7698 JAMES 03-DEC-81 950 1100

7782 MILLER 23-JAN-82 1300 1300

7788 ADAMS 23-MAY-87 1100 1100

7839 JONES 02-APR-81 2975 2913

7839 BLAKE 01-MAY-81 2850 2758

MGR ENAME HIREDATE SAL MGR_AVG

---------- ---------- --------- ---------- ----------

7839 CLARK 09-JUN-81 2450 2650

7902 SMITH 17-DEC-80 800 800

KING 17-NOV-81 5000 5000

14 rows selected.

SCOTT@orcl>ED

Wrote file afiedt.buf

1 SELECT DEPTNO, ENAME, SAL,

2 FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH

3* FROM EMP

SCOTT@orcl>/

DEPTNO ENAME SAL DEPT_RICH

---------- ---------- ---------- ----------

10 KING 5000 KING

10 CLARK 2450 KING

10 MILLER 1300 KING

20 SCOTT 3000 SCOTT

20 FORD 3000 SCOTT

20 JONES 2975 SCOTT

20 ADAMS 1100 SCOTT

20 SMITH 800 SCOTT

30 BLAKE 2850 BLAKE

30 ALLEN 1600 BLAKE

30 TURNER 1500 BLAKE

DEPTNO ENAME SAL DEPT_RICH

---------- ---------- ---------- ----------

30 MARTIN 1250 BLAKE

30 WARD 1250 BLAKE

30 JAMES 950 BLAKE

14 rows selected.

SCOTT@orcl>ed

Wrote file afiedt.buf

1 SELECT DEPTNO, ENAME, SAL,

2 last_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH

3* FROM EMP

SCOTT@orcl>/

DEPTNO ENAME SAL DEPT_RICH

---------- ---------- ---------- ----------

10 KING 5000 KING

10 CLARK 2450 CLARK

10 MILLER 1300 MILLER

20 SCOTT 3000 SCOTT

20 FORD 3000 FORD

20 JONES 2975 JONES

20 ADAMS 1100 ADAMS

20 SMITH 800 SMITH

30 BLAKE 2850 BLAKE

30 ALLEN 1600 ALLEN

30 TURNER 1500 TURNER

DEPTNO ENAME SAL DEPT_RICH

---------- ---------- ---------- ----------

30 MARTIN 1250 MARTIN

30 WARD 1250 WARD

30 JAMES 950 JAMES

14 rows selected.

-------------밀러----------------------

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select deptno, ename, sal, last_value(ename) over

2 (partition by deptno order by sal desc rows between current row and unbounded following) as dept_poor

3* from emp

SCOTT@orcl>/

DEPTNO ENAME SAL DEPT_POOR

---------- ---------- ---------- ----------

10 KING 5000 MILLER

10 CLARK 2450 MILLER

10 MILLER 1300 MILLER

20 SCOTT 3000 SMITH

20 FORD 3000 SMITH

20 JONES 2975 SMITH

20 ADAMS 1100 SMITH

20 SMITH 800 SMITH

30 BLAKE 2850 JAMES

30 ALLEN 1600 JAMES

30 TURNER 1500 JAMES

DEPTNO ENAME SAL DEPT_POOR

---------- ---------- ---------- ----------

30 MARTIN 1250 JAMES

30 WARD 1250 JAMES

30 JAMES 950 JAMES

14 rows selected.

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select ename, hiredate, sal, lag(sal) over (order by hiredate) as prev_sal

2* from emp

SCOTT@orcl>/

ENAME HIREDATE SAL PREV_SAL

---------- --------- ---------- ----------

SMITH 17-DEC-80 800

ALLEN 20-FEB-81 1600 800

WARD 22-FEB-81 1250 1600

JONES 02-APR-81 2975 1250

BLAKE 01-MAY-81 2850 2975

CLARK 09-JUN-81 2450 2850

TURNER 08-SEP-81 1500 2450

MARTIN 28-SEP-81 1250 1500

KING 17-NOV-81 5000 1250

JAMES 03-DEC-81 950 5000

FORD 03-DEC-81 3000 950

ENAME HIREDATE SAL PREV_SAL

---------- --------- ---------- ----------

MILLER 23-JAN-82 1300 3000

SCOTT 19-APR-87 3000 1300

ADAMS 23-MAY-87 1100 3000

14 rows selected.

SCOTT@orcl>c/lag/lead

SP2-0023: String not found.

SCOTT@orcl>ed

Wrote file afiedt.buf

1 select ename, hiredate, sal, lead(sal) over (order by hiredate) as prev_sal

2* from emp

SCOTT@orcl>/

ENAME HIREDATE SAL PREV_SAL

---------- --------- ---------- ----------

SMITH 17-DEC-80 800 1600

ALLEN 20-FEB-81 1600 1250

WARD 22-FEB-81 1250 2975

JONES 02-APR-81 2975 2850

BLAKE 01-MAY-81 2850 2450

CLARK 09-JUN-81 2450 1500

TURNER 08-SEP-81 1500 1250

MARTIN 28-SEP-81 1250 5000

KING 17-NOV-81 5000 950

JAMES 03-DEC-81 950 3000

FORD 03-DEC-81 3000 1300

ENAME HIREDATE SAL PREV_SAL

---------- --------- ---------- ----------

MILLER 23-JAN-82 1300 3000

SCOTT 19-APR-87 3000 1100

ADAMS 23-MAY-87 1100

14 rows selected.

ALTER USER SCOTT IDENTIFIED BY SCOTT ACCOUNT UNLOCK

GRANT CONNECT, RESOURCE TO HR

$ORACLE_HOME에 있는 UTLSAMPL.SQL 찾아서 윈도우에 넣기

저작자표시 (새창열림)