+오라클설치 만큼 중요한 삭제하기!!

삭제가 중요한이유는 삭제를 잘해야! 설치도 잘되기 때문이다. (뭐 당연한 말이지만;;)

보통 프로그램들은 '제어판-프로그램 추가/삭제' 한번으로 간단히 끝난다.

하지만!! 오라클의 경우에는 얘기가 조금 다르다.

프로그램도 지워야하고, 저장되어있는 DB도 지워야 하기때문이다.

그렇다고 겁먹을 필요는 없다.

정해진 순서를 따라 하면되니까

(길다고 훅훅 내려보지말고 천천히 따라하길바란다!)

우선! 순서를 간단하게 살펴보자.

1. 서비스중지

2. 오라클 설치해제

3. 레지스트리 정리

4. C:혹은 D: 오라클 폴더삭제

5. 리부팅

5단계면 끝난다.

의외로 간단하지 않은가?!

그럼 이제 본격 적으로 삭제를 실습해보자!

1.서비스중지

제어판 - 관리도구 - 서비스 - oracle 로 시작하는 서비스 중지하기 !

2. 오라클 설치해제

시작 - Oracle installation Products - Universal installer - 제품 설치 해제




 




3.레지스트리 정리!

시작 - 실행 - regedit

아래 사진 처럼 경로를 보고 oracle~ 로 시작하는 것들을 다 지운다!

               HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
               HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
               HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle~
               HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle~
               HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Oracle~
               HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Oracle~
               HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle~


 




4. C:혹은 D: 오라클 폴더삭제

C:\oracle 삭제, C:\Program Files/oracle 삭제

 




 

※ 만약 지울수 없다고 나온다면 리부팅을 먼저한다!


시작프로그램에 바로가기도 지워주는 센스 !!!

5.리부팅!!!

+이것으로 삭제가 끝났다.

생각보다 쉽지않았는가! 단지 할일이 초큼 많을 뿐이다.

설치 할때 오류 없이 편히 하려면

이정도 수고쯤이야~

다들 좋은(?)삭제 했길바란다!!


++보너스!!! 삭제 잘됐나 확인하기 'ㅁ'//




'먹고살기 > Oracle' 카테고리의 다른 글

Analytic Function  (0) 2011.08.10
SQL -- Analytic Function  (0) 2011.08.10
GROUP BY의 고급 응용  (0) 2011.08.10
DECODE와 CASE WHEN ...  (0) 2011.08.10
Oracle case문  (0) 2011.08.10

Analytic Function (분석 함수)

분석 함수는 Aggregate Function 계산을 지정하는 그룹을 기반으로 계산하여 그룹에 대해 여러 행을 반환 있는 Function 말합니다. 일반적으로 누적 계산, 집계 보고용 결과를 질의 유용하게 사용 있으며 복잡한 질의를 보다 간편하고 빠르게 실행 있게 도와 줍니다.

ex.

SQL> select empno, ename, sal, deptno, sum(sal) dept_tot

from emp ;

ERROR at line 1:

ORA-00937: not a single-group group function

위의 문장은 SUM 이라는 Aggregate Function ( Group Function ) GROUP BY 없이 일반 컬럼들과 함께 사용하였기에 발생한 에러 입니다.

하지만 다음의 문장은 어떤가요?

SQL> select empno, ename, sal, deptno,

sum(sal) over(partition by deptno) dept_tot

from emp ;

EMPNO ENAME SAL DEPTNO DEPT_TOT

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

7782 CLARK 2450 10 8750

7839 KING 5000 10 8750

7934 MILLER 1300 10 8750

7566 JONES 2975 20 10875

7902 FORD 3000 20 10875

7876 ADAMS 1100 20 10875

7369 SMITH 800 20 10875

7788 SCOTT 3000 20 10875

7521 WARD 1250 30 9400

7844 TURNER 1500 30 9400

7499 ALLEN 1600 30 9400

7900 JAMES 950 30 9400

7698 BLAKE 2850 30 9400

7654 MARTIN 1250 30 9400

그룹당 동일한 Function 결과를 반복 출력하며 에러 없이 실행 가능 합니다.

만약 위와 같은 결과를 확인 하고자 했을 분석함수를 사용하지 않는다면 어떤 문장을 사용해야 할까요?

SQL> select a.empno, a.ename, a.sal, a.deptno, b.dept_tot

from emp a, ( select deptno, sum(sal) dept_tot

from emp

group by deptno ) b

where a.deptno = b.deptno ;

결과는 같게 나옵니다. 허나 불필요한 Sub-Query 이용해야 하며 원본 집합인 EMP 테이블을 반복적으로 Access 하는 비효율적인 문장이 되어 버립니다.

분석함수는 원하는 결과를 가져다 주는 SQL 보다 쉽게 만들 있도록 도와주며 성능 역시 향상 시켜주므로 쓰임새를 익혀둘 필요가 분명 존재 합니다.

분석 함수 정의

SQL> select empno, ename, sal, deptno,

sum(sal) over(partition by deptno) dept_tot

from emp ;

분석 함수는 Aggregate Function 뒤에 Analytic Clause (OVER ) 통해 그룹의 정의를 지정하고 그룹당 결과 값을 반복하여 출력 합니다. 여기서 그룹의 범위를 WINDOW 부릅니다. 하나의 WINDOW 계산을 수행하는데 사용되는 행들의 집합을 결정하게 되며 PARTITION BY, ORDER BY, WINDOWING 통하여 조절하게 됩니다.

또한 분석 함수는 Join 문장, WHERE, GROUP BY, HAVING 등과 함께 쓰일 가장 마지막에 연산(집계) 진행하며 SELECT 절과 ORDER BY 절에서만 사용이 가능 합니다.

매뉴얼 상의 문법을 먼저 확인하면 다음과 같습니다.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i88893

analytic function :

analytic clause :

query partition clause :

order by clause :

windowing clause :

항상 그렇듯이 매뉴얼에 나와 있는 부분은 왠지 부담이 가는 것은 사실입니다. 하지만 가장 정확한 내용을 버전에 맞게 설명하는 것은 사실이니 부담이 되더라도 항상 매뉴얼을 함께 참조하는 습관은 함께 하시길 바랍니다.

분석함수 사용 OVER 절의 사용법만 정확히 이해 하신다면 나머지는 Aggregate Function 들의 특징 가지만 이해 하시면 됩니다. 우선은 OVER 절을 먼저 살펴보도록 하겠습니다.


PARTITION BY GROUP BY 절과 동일한 역할을 진행 합니다.

, GROUP BY 절을 사용하지 않고 필요한 집합으로 (WINDOW) 행들을 그룹화 시킴.

ex.

SQL> select empno, ename, sal,

avg(sal) over( ) avg_sal

from emp ; <= 전체의 평균 급여

EMPNO ENAME SAL AVG_SAL

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

7369 SMITH 800 2073.21429

7499 ALLEN 1600 2073.21429

7521 WARD 1250 2073.21429

7566 JONES 2975 2073.21429

SQL> select empno, ename, sal, deptno, job,

avg(sal) over(PARTITION BY deptno, job) avg_sal

from emp ; <= 부서별, 업무별 평균 급여

EMPNO ENAME SAL DEPTNO JOB AVG_SAL

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

7934 MILLER 1300 10 CLERK 1300

7782 CLARK 2450 10 MANAGER 2450

7839 KING 5000 10 PRESIDENT 5000

7788 SCOTT 3000 20 ANALYST 3000

SQL> select d.deptno, d.dname, e.ename, e.sal,

avg(e.sal) over(PARTITION BY d.deptno) avg_sal

from emp e, dept d

where d.deptno = e.deptno ; <= 조인 부서별 평균 급여

DEPTNO DNAME ENAME SAL AVG_SAL

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

10 ACCOUNTING CLARK 2450 2916.66667

10 ACCOUNTING KING 5000 2916.66667

10 ACCOUNTING MILLER 1300 2916.66667

20 RESEARCH JONES 2975 2175

20 RESEARCH FORD 3000 2175

Partition by 절을 사용 함으로 GROUP BY 없이 다양한 GROUPING 집합의 집계 결과들을 함께 출력 있습니다.
ORDER BY
Partition by 정의된 WINDOW 내에서의 행들의 정렬 순서를 정의 한다.

ex.

SQL> select empno, ename, sal, deptno,

row_number( ) over ( ORDER BY sal ASC ) rnum

from emp ; <= 전체 급여를 오름차순으로 정렬 했을 경우 Row Number

EMPNO ENAME SAL DEPTNO RNUM

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

7369 SMITH 800 20 1

7900 JAMES 950 30 2

7876 ADAMS 1100 20 3

7521 WARD 1250 30 4

SQL> select empno, ename, sal, deptno,

row_number( ) over ( PARTITION BY deptno ORDER BY sal DESC ) rnum

from emp ; <= 부서별 급여를 내림차순으로 정렬 했을 경우 Row Number

EMPNO ENAME SAL DEPTNO RNUM

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

7839 KING 5000 10 1

7782 CLARK 2450 10 2

7934 MILLER 1300 10 3

7788 SCOTT 3000 20 1

7902 FORD 3000 20 2

부서 번호가 바뀔 Row Number 새로 시작 되는 것을 확인 있습니다.

SQL> select empno, ename, sal, comm,

DENSE_RANK( ) over ( ORDER BY comm ASC ) rnum

from emp

where deptno = 30 ; <= NULL 값은 정렬 가장 값으로 인식 (기본설정)

EMPNO ENAME SAL COMM RNUM

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

7844 TURNER 1500 0 1

7499 ALLEN 1600 300 2

7521 WARD 1250 500 3

7654 MARTIN 1250 1400 4

7900 JAMES 950 5

7698 BLAKE 2850 5

7782 CLARK 2450 5


SQL> select empno, ename, sal, comm,

DENSE_RANK( ) over ( ORDER BY comm ASC NULLS FIRST ) rnum

from emp

where deptno = 30 ; <= NULL 값을 가장 작은 값으로 설정

EMPNO ENAME SAL COMM RNUM

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

7900 JAMES 950 1

7698 BLAKE 2850 1

7844 TURNER 1500 0 2

7499 ALLEN 1600 300 3

7521 WARD 1250 500 4

7654 MARTIN 1250 1400 5

ORDER BY 절은 Partition by 의해 그룹화 행들의 정렬 순서를 결정하며 NULL 값을 가지고 있는 행이 있을 경우 NULL 대한 값을 FIRST, LAST 보낼 있도록 조절 가능 합니다.

RANK ( ), DENSE_RANK ( ), ROW_NUMBER ( ) 차이점

SQL> select empno, ename, deptno, sal,

rank( ) over ( PARTITION BY deptno ORDER BY sal DESC ) rank,

dense_rank( ) over ( PARTITION BY deptno ORDER BY sal DESC ) d_rank,

row_number( ) over ( PARTITION BY deptno ORDER BY sal DESC ) rnum

from emp

where deptno in (10,20) ;

EMPNO ENAME DEPTNO SAL RANK D_RANK RNUM

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

7839 KING 10 5000 1 1 1

7782 CLARK 10 2450 2 2 2

7934 MILLER 10 1300 3 3 3

7788 SCOTT 20 3000 1 1 1

7902 FORD 20 3000 1 1 2

7566 JONES 20 2975 3 2 3

7876 ADAMS 20 1100 4 3 4

7369 SMITH 20 800 5 4 5

현재 결과는 부서별 급여를 기준으로 내림차순으로 정렬 하였을 순번을 보여 준다.

이때 RANK 동등 순위 발생 중복된 값만큼 증가시킨 3번이 다음 번호로 확인되며

DENSE_RANK 동등 순위 번호는 같게 나오고 다음 순위를 2번으로 보여 준다.

, 중복된 값만큼의 증가치는 없다는 것이며 ROW_NUMBER 동등 순위 자체를 인식하지 않고 매번 증가되는 번호를 부여 한다.

WINDOWING 일부 Aggregate Function 함께 쓰일 있으며 행들의 그룹을 물리적, 논리적으로 조절하여 Function 적용될 WINDOW 정의 합니다.

, PARTITON BY 절은 컬럼에 같은 값을 기준으로만 그룹화를 시키나 WINDOWING 절은 ROWS RANGE 이용하여 하나의 WINDOW 결정하는 범위를 보다 자유롭게 조정할 있습니다.

ROWS (물리적인 단위 결정)

SQL> select empno, ename, sal,

sum(sal) over ( ORDER BY empno

ROWS BETWEEN UNBOUNDED PRECEDING AND

CURRENT ROW ) sum_sal

from emp

where deptno in (10,20) ;

EMPNO ENAME SAL SUM_SAL

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

7369 SMITH 800 800

7566 JONES 2975 3775 <= 800 + 2975

7782 CLARK 2450 6225 <= 3775 + 2450

7788 SCOTT 3000 9225 <= 6225 + 3000

7839 KING 5000 14225 <= 9225 + 5000

7876 ADAMS 1100 15325 <= 14225 + 1100

7902 FORD 3000 18325 <= 15325 + 3000

7934 MILLER 1300 19625 <= 18325 + 1300

ROWS WINDOW 범위를 정의할 물리적인 행을 지정 하는 부분입니다. 어떤 행에서 시작해서 어떤 까지가 하나의 WINDOW 영역으로 정의 할지 범위를 BETWEEN 통하여 정의 있습니다.

UNBOUNDED PRECEDING 번째 행을 가리키며 UNBOUNDED FOLLOWING 마지막 행을 의미 합니다. CURRENT ROW 현재 행을 의미합니다.

그럼 위의 결과는 empno 순으로 오름차순 정렬한 집합에서 번째 행부터 현재 행까지의 SUM(sal) 계산하는 누적 집계를 보여 줍니다.

이는 다음의 문장을 실행해도 동일한 결과를 보여 줍니다.

SQL> select empno, ename, sal,

sum(sal) over ( ORDER BY empno

ROWS UNBOUNDED PRECEDING ) sum_sal

-- 또는 sum(sal) over ( ORDER BY empno )

from emp where deptno in (10,20) ;

, BETWEEN 사용하지 않고 시작되는 행만을 지정하면 종료 행은 CURRENT ROW 지정 되게 됩니다.

그렇다면 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 사용하면 어떨까요? 번째 행부터 마지막 행까지를 하나의 WINDOW 정의 있게 됩니다.

SQL> select empno, ename, sal,

sum(sal) over ( ORDER BY empno

ROWS BETWEEN UNBOUNDED PRECEDING AND

UNBOUNDED FOLLOWING ) sum_sal

from emp

where deptno in (10,20) ;

EMPNO ENAME SAL SUM_SAL

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

7369 SMITH 800 19625

7566 JONES 2975 19625

7782 CLARK 2450 19625

7788 SCOTT 3000 19625

7839 KING 5000 19625

7876 ADAMS 1100 19625

7902 FORD 3000 19625

7934 MILLER 1300 19625

PARTITION BY 절을 사용하여 동일한 VALUE 기반으로 하는 WINDOW 영역을 그대로 사용할 경우에는 WINDOWING 절은 사용상 의미가 없습니다. WINDOWING 절은 PARTITION BY 만으로 그룹화를 시키는 것이 아닌 좀더 세밀한 조정이 필요할 사용 합니다.

ROWS 물리적인 행을 기반으로 그룹화가 가능 합니다.

SQL> select empno, ename, sal,

sum(sal) over ( ORDER BY empno

ROWS BETWEEN 1 PRECEDING AND

CURRENT ROW ) sum_sal

from emp

where deptno in (10,20) ;

EMPNO ENAME SAL SUM_SAL

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

7369 SMITH 800 800

7566 JONES 2975 3775 <= 800 + 2975

7782 CLARK 2450 5425 <= 2975 + 2450

7788 SCOTT 3000 5450 <= 2450 + 3000

7839 KING 5000 8000 <= 3000 + 5000

7876 ADAMS 1100 6100 <= 5000 + 1100

7902 FORD 3000 4100 <= 1100 + 3000

7934 MILLER 1300 4300 <= 3000 + 1300

시작 행을 이전 ( 1 PRECEDING ) 으로 설정 하였으므로 현재 행과 이전 행만의 합계를 구하는 문장이 됩니다.

RANGE 논리적인 값을 근거로 WINDOW 범위를 설정 가능하게 합니다.

SQL> select ename, to_char(hiredate,'YYYY/MM/DD') hiredate, sal,

sum(sal) over ( ORDER BY hiredate

RANGE BETWEEN INTERVAL '3' MONTH PRECEDING

AND INTERVAL '3' MONTH FOLLOWING ) sum_sal

from emp

where deptno in (10,20) ;

ENAME HIREDATE SAL SUM_SAL

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

SMITH 1980/12/17 800 800 <= 전후 3개월에 해당되는 없음

JONES 1981/04/02 2975 5425 <= 2975 + 2450 (04/02 ~ 06/09)

CLARK 1981/06/09 2450 5425 <= 2975 + 2450 (04/02 ~ 06/09)

KING 1981/11/17 5000 9300 <= 5000 + 3000 + 1300 (11/17 ~ 01/23)

FORD 1981/12/03 3000 9300 <= 5000 + 3000 + 1300 (11/17 ~ 01/23)

MILLER 1982/01/23 1300 9300 <= 5000 + 3000 + 1300 (11/17 ~ 01/23)

SCOTT 1987/04/19 3000 4100 <= 3000 + 1100 (04/19 ~ 05/23)

ADAMS 1987/05/23 1100 4100 <= 3000 + 1100 (04/19 ~ 05/23)

입사 일자를 기준으로 정렬을 하였을 경우 현재 행이 가지고 있는 입사일자 전후로 3개월씩, 6개월 사이의 합계를 구하게 됩니다. RANGE 이렇게 논리적인 평가를 통하여 시작 행과 종료 행을 지정하게 합니다.


분석함수 종류 ( v10g )

분석함수의 종류는 다음과 같습니다. ( * 있는 것은 WINDOWING 사용 가능 )

AVG *
CORR *
COVAR_POP *
COVAR_SAMP *
COUNT *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
MAX *
MIN *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *

각각의 링크를 통해서 매뉴얼을 참고 있습니다.

FIRST / LAST

WINDOW 영역에서 FIRST/LAST 행의 하나의 행만을 추출 하려는 경우 사용 가능.

ORDER BY 이용하여 WINDOW 내의 정렬을 진행하고 DENSE_RANK FIRST/LAST 그들 하나의 행을 선택할 있다. 이때 동일한 ranking 가지고 있는 집합들 Aggregate Function 결과를 보여 준다.

ex. 급여를 가장 많이 받는 사원과 가장 적게 받는 사원의 이름

SQL> select min(ename) keep ( dense_rank FIRST order by sal desc ) max_ename ,

min(ename) keep ( dense_rank LAST order by sal desc ) min_ename

from emp ;


MAX_ENAME MIN_ENAME

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

KING SMITH

ex. 부서별 급여를 가장 많이, 적게 받는 사원들의 이름

SQL> select deptno,

min(ename) keep ( dense_rank FIRST order by sal desc ) max_ename ,

min(ename) keep ( dense_rank LAST order by sal desc ) min_ename

from emp

group by deptno ;

DEPTNO MAX_ENAME MIN_ENAME

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

10 KING MILLER

20 FORD SMITH

30 BLAKE JAMES

NTILE

분석용 Function 이며 WINDOW 그룹의 행을 정렬 지정한 개수의 범위(등급)으로 나눈 값이 가지고 있는 등급 값을 보여준다.

ex) 사원들의 급여를 많이 받는 순서대로 5개의 등급으로 나눠 표시

SQL> select empno, ename, sal,

NTILE (5) over ( order by sal desc ) grade

from emp ;

EMPNO ENAME SAL GRADE

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

7839 KING 5000 1

7902 FORD 3000 1

7788 SCOTT 3000 1

7566 JONES 2975 2

7698 BLAKE 2850 2

7782 CLARK 2450 2

7499 ALLEN 1600 3

7844 TURNER 1500 3

7934 MILLER 1300 3

7521 WARD 1250 4

7654 MARTIN 1250 4

7876 ADAMS 1100 4

7900 JAMES 950 5

7369 SMITH 800 5


CUME_DIST (cumulative distribution) / PERCENT_RANK

모두 계산식에 차이가 있으나 WINDOW 그룹 내에서 누적 분포를 계산할 사용 가능.

값의 범위는 0 ~ 1 까지 사용되며 PERCENT_RANK 항상 시작 값이 0 부터 시작 .

ex. 급여를 내림차순 기준으로 정렬 하였을 경우 사원의 누적 분포를 계산

SQL> select ename, sal,

round ( PERCENT_RANK ( ) over ( order by sal desc ),2) as per_rank,

round ( CUME_DIST( ) over ( order by sal desc ),2) as cume_dist,

RANK( ) over ( order by sal desc ) as rank,

ROW_NUMBER ( ) over ( order by sal desc ) as row_num

from emp ;

ENAME SAL PER_RANK CUME_DIST RANK ROW_NUM

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

KING 5000 0 .07 1 1

FORD 3000 .08 .21 2 2

SCOTT 3000 .08 .21 2 3

JONES 2975 .23 .29 4 4

BLAKE 2850 .31 .36 5 5

CLARK 2450 .38 .43 6 6

ALLEN 1600 .46 .5 7 7

TURNER 1500 .54 .57 8 8

MILLER 1300 .62 .64 9 9

WARD 1250 .69 .79 10 10

MARTIN 1250 .69 .79 10 11

ADAMS 1100 .85 .86 12 12

JAMES 950 .92 .93 13 13

SMITH 800 1 1 14 14

PERCENT_RANK :( RANK - 1) / (COUNT(*) - 1)

CUME_DIST : ( RANK or ROW_NUMBER ) / COUNT(*)

(동등 순위의 RANK 발생 해당 RANK 마지막 ROW_NUMBER 사용)

SQL> select round( (1-1)/(14-1),2) per ,round( 1 / 14 ,2) cume from dual union all

select round( (2-1)/(14-1),2) ,round( 3 / 14 ,2) from dual union all -- row_num 사용

select round( (2-1)/(14-1),2) ,round( 3 / 14 ,2) from dual union all -- row_num 사용

select round( (4-1)/(14-1),2) ,round( 4 / 14 ,2) from dual union all

select round( (5-1)/(14-1),2) ,round( 5 / 14 ,2) from dual ;

PER CUME

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

0 .07

.08 .21

.08 .21

.23 .29

.31 .36

PERCENTILE_CONT / PERCENTILE_DISC

cume_dist, percent_rank 결과가 누적 분포도를 계산 한다면 PERCENTILE_CONT, PERCENTILE_DISC 분포도 (지정되는 백분율) 역으로 계산하여 실제의 값을 가져온다.

ex. 30 부서번호를 가지고 있는 사원들을 급여를 기준으로 내림차순 정렬

SQL> select empno, ename, sal, deptno,

round( cume_dist ( ) over ( order by sal desc ),2) as cume_dist

from emp

where deptno = 30 ;

EMPNO ENAME SAL DEPTNO CUME_DIST

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

7698 BLAKE 2850 30 .17

7499 ALLEN 1600 30 .33

7844 TURNER 1500 30 .5

7521 WARD 1250 30 .83

7654 MARTIN 1250 30 .83

7900 JAMES 950 30 1

위의 결과에서 1500 급여가 전체 WINDOW 중의 50% 범위에 해당 하는 것을 확인

ex. 30 부서번호를 가지고 있는 사원들 급여를 기준으로 내림차순 정렬을 하였을 50% 범위에 해당하는 급여는 얼마인가?

SQL> select PERCENTILE_CONT(0.5) within group ( order by sal desc ) as CONT ,

PERCENTILE_DISC(0.5) within group ( order by sal desc ) as DISC

from emp

where deptno = 30 ;

CONT DISC

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

1375 1500

PERCENTILE_CONT 선형 보간법을 이용하여 평균에 근거하는 결과를 보여주므로 실제의 값을 보여주는 PERCENTILE_DISC 보다는 정확한 값을 보여주지는 않을 있다.

보다 자세한 계산 공식은 매뉴 참고 가능 (MEDIAN 함수도 같은 결과 확인 가능하다.)

SQL> select MEDIAN(sal)

from emp

where deptno = 30 ;

MEDIAN(SAL)

-----------

1375

MEDIAN PERCENTILE_CONT(0.5) 같은 결과

Hypothetical Functions

가정에 근거하여 함수에 맞는 값을 확인 가능.

ex. 급여가 2000 이라면 각각 순위 백분율은 얼마인가?

SQL> select RANK (2000) WITHIN GROUP ( order by sal desc ) rank,

DENSE_RANK (2000) WITHIN GROUP ( order by sal desc ) dense_rank,

CUME_DIST (2000) WITHIN GROUP ( order by sal desc ) cume_dist,

PERCENT_RANK (2000) WITHIN GROUP ( order by sal desc ) per_rank

from emp ;

RANK DENSE_RANK CUME_DIST PER_RANK

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

7 6 .466666667 .428571429

FIRST_VALUE / LAST_VALUE

FIRST / LAST 비슷하게 WINDOW 내의 처음과 마지막 행의 값을 가져 있으며 WINDOWING 절을 지정하여 원하는 WINDOW 정의가 가능하다. 또한 NULL 제외 시키고 작업 가능 (FIRST / LAST WINDOWING 사용이 불가능하며 NULL 포함되어 계산 )

SQL> select ename, sal, comm,

FIRST_VALUE (comm IGNORE NULLS)

OVER ( order by comm desc

rows between unbounded preceding and unbounded following ) fv_c1 ,

FIRST_VALUE (comm)

OVER ( order by comm desc

rows between unbounded preceding and unbounded following ) fv_c2 ,

FIRST_VALUE (comm IGNORE NULLS)

OVER ( order by comm desc ) fv_c3 ,

LAST_VALUE (comm IGNORE NULLS)

OVER ( order by comm desc

rows between unbounded preceding and unbounded following ) lv

from emp

where deptno = 30 ;


ENAME SAL COMM FV_C1 FV_C2 FV_C3 LV

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

BLAKE 2850 1400 0

JAMES 950 1400 0

MARTIN 1250 1400 1400 1400 0

WARD 1250 500 1400 1400 0

ALLEN 1600 300 1400 1400 0

TURNER 1500 0 1400 1400 0

우선 검색된 6 행의 COMM 컬럼 값을 확인. NULL 값이 2 존재하며 0 ~ 1400 범위

OVER 절의 ORDER BY 이용하여 comm 기준으로 내림차순 정렬 진행 . ROWS 사용하여 window 범위는 전체 범위로 설정 컬럼이 있으며 FV_C3 WINDOWING 절이 생략되었으므로 번째 행부터 현재 행까지 만의 범위 안에서 계산.

이렇게 FIRST_VALUE LAST_VALUE ROWS / RANGE 이용하여 WINDOW 직접 조절할 있다는 특징이 있습니다. 때문에 FIRST_VALUE / LAST_VALUE 사용 인라인 뷰를 사용해서 어떤 행이 번째 행으로 하느냐를 조절할 필요도 존재.

자세한 사항은 매뉴얼 참고

LEAD / LAG

지정된 개수의 이전, 이후 행의 가져오기. WINDOWING 절을 지정하지 못하며 NULL 값을 대체하는 값을 지정할 있음. (NVL 불필요)

ex. 30 부서의 사원을 이름순으로 정렬하여 검색하며 이전,다음 행의 급여를 함께 표시

SQL> select empno, ename, sal,

LAG (sal,1,0) over ( order by ename ) prev_sal,

LEAD (sal,1,0) over ( order by ename ) next_sal

from emp where deptno = 30 ;

EMPNO ENAME SAL PREV_SAL NEXT_SAL

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

7499 ALLEN 1600 0 2850

7698 BLAKE 2850 1600 950

7900 JAMES 950 2850 1250

7654 MARTIN 1250 950 1500

7844 TURNER 1500 1250 1250

7521 WARD 1250 1500 0


RATIO_TO_REPORT

WINDOW 영역의 합계 내에서 현재 값이 차지하는 백분율. 별도의 WINDOWING 절을 설정하는 것은 불가능

ex. 사원 정보를 출력하면서 부서별 급여의 합계 해당 사원이 받는 급여의 백분율을 표시하고 부서별 급여의 합계도 함께 출력

SQL> break on deptno skip 1

SQL> compute sum label 'total' of sal on deptno

SQL> select deptno, ename,

round ( RATIO_TO_REPORT (sal) over ( partition by deptno ) , 2) ratio ,

sal

from emp ;

SQL> clear compute

SQL> clear break

DEPTNO ENAME RATIO SAL

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

10 CLARK .28 2450

KING .57 5000

MILLER .15 1300

********** ----------

total 8750

20 JONES .27 2975

FORD .28 3000

ADAMS .1 1100

SMITH .07 800

SCOTT .28 3000

********** ----------

total 10875

30 WARD .13 1250

TURNER .16 1500

ALLEN .17 1600

JAMES .1 950

BLAKE .3 2850

MARTIN .13 1250

********** ----------

total 9400

sqlplus 몇몇 계산 명령어를 이용하면 원하는 결과를 보다 쉽게 가져 있습니다.


'먹고살기 > Oracle' 카테고리의 다른 글

window 오라클 삭제하는 방법(oracle 10g)  (0) 2011.08.10
SQL -- Analytic Function  (0) 2011.08.10
GROUP BY의 고급 응용  (0) 2011.08.10
DECODE와 CASE WHEN ...  (0) 2011.08.10
Oracle case문  (0) 2011.08.10

○ Analytic Function

1. 정의

① 쿼리를 수행하는 구문 중에 가장 마지막에 실행된다. (order by 보다는 먼저 실행)

② 쿼리 구문을 수행하고 2차로 값을 가공한다.

2. 구문

select column_name, column_name over([partition by column_name],

[order by column_nae, ... ])

from table;

3. 누적합을 구하는 Analytic Function

select deptno, empno, ename, sal,

sum(sal) over (partition by deptno

order by sal) as dept_avg_sal
from emp;

: deptno별 누적 sal을 구하고 sal로 order by 하는데 같은 값의 row가 있으면 제대로 된 값이 안나올 수 있으므로 order에 구분할 컬럼을 더 해준다.

select deptno, empno, ename, sal,

sum(sal) over (partition by deptno

order by sal, deptno) as dept_avg_sal
from emp;

DEPTNO EMPNO ENAME SAL DEPT_AVG_SAL
10 7934 MILLER 1300 1300
10 7782 CLARK 2450 3750
10 7839 KING 5000 8750
20 7369 SMITH 800 800
20 7876 ADAMS 1100 1900
20 7566 JONES 2975 4875

4. rank를 구하는 Analytic Function

① rank() : 순서를 정하는데 같은 값은 똑같은 순서로 정하고, 다음 순번은 없앤다. (1,2,2,4)

② dense_rank() : 다음 순번을 없애니 않는다. (1,2,2,3)

③ row_number() : 같은 값이라도 위치나 이름에 따라 순번을 정한다. (1,2,3,4)

select deptno, empno, ename, sal, rank() over(order by sal) "Rank",
dense_rank() over(order by sal) "Dense Rank",
row_number() over(order by sal) "Rownum"
from emp;

>>

select deptno, empno, ename, sal, rank() over(partition by deptno
order by sal) "Rank",
dense_rank() over(partition by deptno
order by sal) "Dense Rank",
row_number() over(partition by deptno
order by sal) "Rownum"
from emp;

>>

5. Moving Aggregates

select empno, sal, avg(sal) over (order by sal, empno) as ret1, --- 누적된 row의 평균
avg(sal) over (order by sal, empno rows 2 preceding) as ret2,

--- 자신을 포함한 위의 2 row의 평균

avg(sal) over (order by sal, empno rows between current row and 2 fllowing) as ret3

-- 자신을 포함한 밑의 2 row의 평균
from emp;

>>

EMPNO SAL RET1 RET2 RET3
7369 800 800 800 950
7900 950 875 875 1100
7876 1100 950 950 1200
7521 1250 1025 1100 1266.66667
7654 1250 1070 1200 1350
7934 1300 1108.33333 1266.66667 1466.66667

6. LAG , LEAD

① 자신의 row를 기준으로 위와 아래의 row

② example

select empno, ename, sal, lag(sal, 1, 0) over (order by sal) as prevsal
from emp;

: 현재 row의 1칸 위의 sal, 처음의 row는 위에 값이 없을 때, null을 return하므로 숫자를 넣을 수 있다.

select empno, ename, sal, lead(sal, 1, 0) over (order by sal) as prevsal

from emp;

: 현재 row의 1칸 밑의 sal, 마지막 row는 밑에 값이 없을 때, null을 return하므로 숫자를 넣을 수 있다.

7. First Value , Last Value

① 조건의 처음의 값과 마지막 값을 return

② Last Value는 버그가 발생 이에 관한 자료 참고

http://blog.naver.com/gseducation?Redirect=Log&logNo=20095625332

- last_value 대신 first_value를 사용하여 last_value의 결과값을 구할 수 있다.

select deptno, empno, ename, first_value(sal) over(partition by deptno
order by sal) as dept_min_sal
from emp;

>>

select deptno, empno, ename, first_value(sal) over(partition by deptno
order by sal desc) as dept_min_sal
from emp;

: last_value대신 first_value에서 order by의 desc를 사용하여 구할 수 있다.

>>


'먹고살기 > Oracle' 카테고리의 다른 글

window 오라클 삭제하는 방법(oracle 10g)  (0) 2011.08.10
Analytic Function  (0) 2011.08.10
GROUP BY의 고급 응용  (0) 2011.08.10
DECODE와 CASE WHEN ...  (0) 2011.08.10
Oracle case문  (0) 2011.08.10

GROUP BY는 모르는 사람이 없을 정도로 많이 애용되는 문입니다.

그렇다면, 이 GROUP BY는 서버의 수많은 내부 장비중에서 무엇을 주로 사용하는 것일까요?

GROUP BY는 GROUP BY다음에 나오는 컬럼을 기준으로 데이터를 묶어서 보여줄 때 사용합니다.

그렇다면, 데이터를 묶는다는 말의 의미는 무엇일까요?

일단 묶어내기 위해서는 어떤 방식으로 일을해서 묶어놓을 수가 있을까요?

제일 쉬는 방법은 묶어야 되는 컬럼을 기준으로 데이터를 줄을 세우는 방법입니다.

줄세워놓고 틀려지면 한개씩 모은담에 보여주면 그게 제일 편한 답이죠.

그러면 또 다른 방법은 뭐가 있을까요?

모든 데이터를 읽어 들이면서 종류가 다른것이 하나씩 나올 때 마다 체크해 두었다가 보여주는

방법도 있습니다. 이 두가지 방법에는 어떤 장.단점이 있을까요?

어차피 보여주는 결과에는 차이가 없지만 어찌 됐던 달라지는 데이터를 관리 해야 한다는 측면에서는

서로 다를 바가 없죠? 대신 나타날 수 있는 큰 차이를 알아 봅시다.

먼저, 나타나는 데이터의 순서를 생각해 봅시다.

처음 방법을 선택하면 묶여진 데이터가 줄을서서 그 값을 나타내어 주게 됩니다.

두번째 방법을 선택하면, 묶여진 데이터가 줄을 서있는 것이 아닌 먼저 나타는 종류별 데이터의

순서대로 그 값을 나타내어 주게 되겠네요.

이것이 오라클에서 데이터를 GROUP BY할떄와 MS-SQL에서 GROUP BY할때의 차이일 것입니다.

(물론 둘다 제가 소스를 본것도 아니기 때문에 더 복잡한 뭔가가 있을지도 모르겟습니다만,

상식적인 수준에서 생각한다면 이런 차이일 것입니다 ㅡ_ㅡa)

다음으로, 내부적인 처리에 대해서 알아봅시다. 처음방법을 선택하는 경우 GROUP BY해서

쌓여지는 데이터는 이미 줄을 서 있기 때문에 그 데이터를 굳이 확인해 볼 필요가 없습니다.

아래의 그림을 보시죠. 먼저, 우리가 실행하는 쿼리는 아래와 같다고 가정합니다.


EMP테이블에서 실제 일어나는 데이터만 살짝 살펴보죠.

이미지를 클릭하시면 창이 닫힙니다

위와 같이 줄을 세워버리고 나서 데이터를 하나씩 뽑는 것은 deptno가 바뀔때만 하나씩 더해서

저장만 해주면 되기 때문에 이후 데이터를 만들어내는데 걸리는 시간이 단축됩니다.

즉,출력하는 데이터를 뽑아내는 순간에는 시간이 절약된다는 말입니다.

그러나, 이렇게 줄을 세우는 시간이 많이 걸리게 되겠지요(비교해서의 이야기입니다). 다음방법으로,

줄을 세우지 않고 뽑아내는 경우를 생각해봅시다.

이 경우는 줄을 세울 필요가 없습니다. 따라서, 줄을 세우는 작업을 하는 시간이 없어지는 대신,

출력하는 데이터를 만들어내는 과정에서 매번 지금 읽어내는 가공전 데이터가 출력하는데이터에

있나, 없나를 살펴봐야하겠지요. 따라서, 읽어내야하는 데이터도 많고 출력하는 데이터.

즉,GROUPING된 데이터도 많다면 오히려 더 느릴수도있을 것입니다.

자세한 액세스COST등은 언급하지 않겟습니다(사실 잘 모릅니다. ㅡ0-;)

자, 잠시 이야기가 옆으로 세버렸는데 이 두가지 차이에 의해서 DBMS의 GROUPING함수가 어떤 경우는

줄을 세워서 보여주고, 어떤 경우는 줄을 세우지 않고 보여준다는 것을 이해하셨을 겁니다.

위의 과정을 살펴 보셧다면 GROUP BY를 실행하면 당연히 있는지를 체크를 하기 위해서건

줄을 세우기 위해서건 CPU를 많이 사용해야만 한다는 것을 파악하셨을 것입니다.

즉 GROUP BY는 CPU를 많이 사용하는 문입니다.

이제부터는 약간의 사례를 보면서 GROUP BY를 이용하는 방법에 대해서 좀 살펴봅시다.

먼저,GROUP BY의 뒤에는 어떤 것들이 올 수 잇는지부터 살펴보죠. 제일 쉬운건 컬럼입니다.

뭐.. 이건 넘어가겠습니다. 그 다음 DECODE,CASE도 다 올수 있습니다. 아래를 보시죠.

위와 같이 데이터가 등록되어 있는 테이블이 있습니다.

이 데이터를 2003년 데이터만을 가지고 어떤 부서들이 요청을 했는지 요청건수를 알아보고싶다면,

그 쿼리는 우 단순할 것입니다.

그런데 요구사항이 점점 복잡해 지게 됩니다. 각 부서별로 집계 자료를 뽑았다면,

이제 요구를 제일 많이 한 부서 부터 순서대로 등수를 만들어서 보여달라고 요청하겠지요.

이정도는 기본입니다.

"뭐 그까이꺼 이정도야~" 하면서 만들어 줬습니다. 제가 오라클을 쓰기땜시 ANALYTIC FUNCTION을

쓴것이구요, 오라클이 아니라도 충분히 만들 수 있습니다. 그건 나중에 응용에서 보기로 하고,

자 이제 새로운 요구사항이 나왔습니다.

1등부터 7등까지는 그냥 부서별로 몇건 했는지 보여주면 좋겟고, 8등이상의 부서는 기타로 몰아서

몇개의 부서가 몇건을 요청했는지도 함께 보여주게.라고 하는 순간 머리에 쥐납니다. (-"-;)

일단, GROUPING하는 대상이 이제 더이상 컬럼이 아니죠.

거기다가 더하는 것도 기타부서 다 모아서 더해야합니다. 이걸 어떻게....

이제 머리가 더이상 안움직이고 정신이 없죠....

뭐.. 그렇다면 7등이내의 부서를 뽑아서 그거에 해당하지 않는 부서의 리스트를 만들어서 붙인 다음에..

어쩌고.. 어쩌고... 그럼 큰일납니다.-0-; 읽은 데이터를 최소한 3번은 읽어야 답이 나오겠네요.

그렇게 안하고 더 효율적으로 할 수 있는 방법을 찾아봅시다..

일단 1등~7등은 그냥 RANK함수가 만들어주는거 그대로 자알 쓰면 됩니다.

그럼, 8등이 넘는 부서는?... 여기서 개념이 바뀝니다. 부서별로 카운트까지는 잘했습니다.

거기에 등수도 잘 골라줬구요. 자 그럼 그게 끝일까요?... 아닙니다 아직 멀었습니다.

이번에는 이 데이터를 가지고, 등수별로 다시 GROUPING을해야 합니다.

단, 8등부터는 부서명을 기타로 바꿔놓고 해야겟죠? 두둥~ 이 쿼리를 한번 봅시다.

이 쿼리는 8위가 넘는 등수의 부서는 이제 부서별로 모아줍니다. 부서별 계도 당연하고,

이제 원하는 결과를 제대로 얻어냈습니다.

이런 형태를 이용하면 데이터를 읽어내는 횟수도 줄이고 속도 역시 향상 시킬 수 있을 겁니다.


'먹고살기 > Oracle' 카테고리의 다른 글

Analytic Function  (0) 2011.08.10
SQL -- Analytic Function  (0) 2011.08.10
DECODE와 CASE WHEN ...  (0) 2011.08.10
Oracle case문  (0) 2011.08.10
oracle case when ~ then~ else end  (0) 2011.08.10

DECODE는 오라클에서 지원하는 내장 함수입니다. 일반 어플리케이션의 IF... ELSE와 비슷한 문법구조를

가지나, 훨씬 간결하고 표현이 용이한 장점이 있습니다. 또한, 기호를 잘 활용하면 가독성도 용이해집니다.

간단하게 DECODE를 살펴보겠습니다.


DECODE(DEPRCD, 1, '영업부','기타부서')

위의 DECODE는 실제 테이블에 데이터가 1,2,3,4,5.... 이렇게 다양한 부서가 있을 경우에 우리가

조회하고 싶은 데이터는 1인 영업부만을 부서로, 나머지는 기타로 몰아서 데이터를 보고 싶다는 의미입니다.

실제, 이 DECODE문장은 SELECT/WHERE/GROUP BY/HAVING/ORDER BY등 모든 절에 다 올수 있지만,

그 사례는 다음장에서 보기로 하고 일단, 기능에 대해서만 살펴보기로 하죠. 위 문장을 글자로 풀어내면

"부서코드가 1이면 영업부로, 그외의 데이터는 기타로 몰아달라는 것입니다.

설령 부서코드가 NULL이라 하더라도 기타로 들어가게 됩니다. 이후, NULL에 대한 강좌에서

NULL값에 대한 의미를 다시한번 확인하기로 하고, 위의 경우처럼 코드를 기준으로 가지고 데이터를

표현하는 경우에 DECODE는 짧고 간결하게 그 값을 나타낼 수 있습니다. 그렇다면, 이런 경우는 어떨까요?

관리부장님의 요구사항이 들어왔습니다.

"우리 회사에서 근무하는 직원들의 월급을 3단계로 나누어서 보고 싶다네...

100만원 미만이면 하,100~200만원이면 중,200만원이상은 상 이렇게 데이터를 보여주게나"

라고 말하고 가십니다. DECODE는 EQUAL처리 밖에 할수가 없기 때문에,

개발자는 고민에 빠지게 될 것입니다. 물론 SQL을 많이 사용해본 개발자라면 SIGN이라는함수가 있다는

것을 알기 때문에 다음과 같이 그 해답을 찾게될 것입니다.

DECODE(FLOOR(SALARY/1000000),0,'하',1,'중',2,'상')

그러나, 위와 같이 DECODE문장을 사용할 경우는 중요한 데이터를 놓칠 가능성이 있습니다.

만일, 월급이 3백만원을 넘는 직원이 존재하는 경우는 어떻게 해야할까요..

물론, DECODE(FLOOR(900000/1000000),0,'하',1,'중',2,'상',3,'상') 라는 방법으로 상중하를 나누어

줄 수는 있습니다. 그러나, 4백만원을 넘는 직원이 나오면 또 붙여야 할까요? 이것은 문제가 있습니다. 그래서,

다음과 같이 SQL을 다시 개량하게 됩니다.

DECODE(SIGN(FLOOR((SALARY-1000000)/1000000)),-1,'',0,'',1,')

이제 2백만원을 넘는 직원에 대해서는 그 급여가 얼마이든 상으로 표현할 수 있게 되겠네요.

하지만 살펴봅시다. 한개의 직원 데이터 급여의 상중하를 구분해 내기 위해서, FLOOR와 SIGN,DECODE를

각각 수행해 봐야 하는 상황이 발생합니다. 더구나 빼고 더하고 산수기호 처럼 햇갈리죠...

거기다 산술연산의 우선순위(굳이 언급 하지는 않겠습니다)까지 고려해야 한다면 복잡한 쿼리일 경우

오히려 가독성이 떨어질 수 있습니다. 이러한 이유로 ANSI-92에서 제시된 새로운 표현방법이 CASE문입니다.

CASE문에서는 위와 같은 문장을 아래와 같이 해소할 수 있습니다.


CASE  WHEN SALARY < 1000000                                  THEN '하'
          WHEN SALARY >= 1000000 AND SALARY < 2000000 THEN '중'
          WHEN SALARY < 2000000                                  THEN '상'
  END

이제 복잡한 덧셈뺄샘도, 나누기와 몫을 구하는 함수도 기억할 필요가 없습니다. 다만,

CASE문장을 어떻게 쓰는지는 기억해야 합니다. 그러나, CASE문장이 DECODE문장 보다는

훨씬 그 표현해야 하는 내용이 길어졌죠? 그게 CASE문의 단점입니다.

따라서, EQUAL에 의한 데이터처리가 가능한 경우에는 DECODE가, RANGE에 의한 데이터 분류가

필요한 경우는 CASE가 유리할수 있겠습니다. 이런 방식으로 나누어서 사용하신다면 훨씬 더 효과적이겟죠?



'먹고살기 > Oracle' 카테고리의 다른 글

SQL -- Analytic Function  (0) 2011.08.10
GROUP BY의 고급 응용  (0) 2011.08.10
Oracle case문  (0) 2011.08.10
oracle case when ~ then~ else end  (0) 2011.08.10
Oracle Sql Case 사용법  (0) 2011.08.10