업무에 바로 쓰는 SQL 활용 실습 - 3일차

<조건 처리 함수>

DECODE(VALUE, IF1, THEN1, IF2, THEN2 ...)

CASE WHEN 비교조건1 THEN 처리1
        WHEN 비교조건2 THEN 처리2

        WHEN 비교조건N THEN 처리N

        ELSE 디폴트처리

END

(CASE문이 ANCI 표준으로 선택되었으며, 다량의 데이터를 처리할 때에는 DECODE보다 성능이 우수하다)

 

▲위에서 쓴 DECODE와 아래에서 쓴 CASE문장은 같은 결과를 보여준다.

 

더 다양한 조건 IF문과 같이 대소를 비교해야 하는 경우에는 CASE문을 쓰는 게 더 간편하다.

(DECODE를 쓸 시에는 2일 차에 배운 SIGN함수를 써서 작으면 -1, 같으면 0, 크면 1로 처리를 해야 한다)

 

▲CASE문을 쓰면 더 간단하게 대소비교를 하여 결과를 볼 수 있다.

 

 

<그룹 함수>

COUNT(*) - NULL 값을 포함한 행의 수를 출력(유일하게 GROUP 함수 중에서 NULL을 포함한다)

COUNT(표현식) - 표현식의 값이 NULL 값인 것을 제외한 행의 수를 출력

MAX - 최대값 (문자, 날짜 데이터 타입도 사용 가능)

MIN - 최소값 (문자, 날짜 데이터 타입도 사용 가능)

 

SUM - N의 합계 (숫자 데이터만 사용 가능)

AVG - N행의 평균값 (숫자 데이터만 사용 가능)

STDDEV - N의 표준편차 (숫자 데이터만 사용 가능)

VARIANCE - N의 분산 (숫자 데이터만 사용 가능)

• SUM(NVL(A, 0)) - X
• SUM(A) - O

(두 문장은 같은 결과를 가져오지만 애초에 COUNT(*)이 아니면 NULL을 집계하지 않기 때문에 SUM(A)가 낫다)

 

▲위의 경우에는 값이 다르게 나오는것을 인지해야한다.

A칼럼에 7개의 항목이 있고 3개의 항목이 NULL이라고 했을 때

• AVG(A) - NULL이 아닌 4개의 항목들을 더하고 / 4
• AVG(NVL(A,0)) - NULL이 아닌 항목들을 0으로 바꿔서 모두 더하고 / 7

 

▲GROUP BY절로 DISTINCT를 구현할 수 있다.

 

SQL의 실행 순서

1. FROM  2. WHERE  3. GROUP BY  4. HAVING  5. GROUP함수

▲GROUP에서 집계가 되기 전에 WHERE에서 썼기에 오류

▲HAVING에는 GROUP BY에서 원하는 조건을 적는다

(HAVING과 GROUP BY의 순서는 바뀌어도 상관없다)

 

 

▲둘 문장 모두 결과는 같다

아래가 더 잘 짜여 있는 SQL이다. 되도록이면 거를 수 있는 것들은 미리 걸러서 처리되는 데이터의 양을 줄여야 한다.

WHERE가 먼저 실행이 되기 때문에 DEPTNO IN (20, 30)은 WHERE에서 먼저 처리하는 게 좋다.

=> 결론적으로 HAVING절에는 GROUP함수 조건식만 적는 게 제일 효과적이다.

 

 

<분석함수>

LISTAGG - 집계에 포함된 목록을 보고자 할 때 쓰는 함수

WITHIN GROUP - LISTAGG함수를 쓸 때 뒤에 항상 있어야 한다

 

▲회사에서 부서별 연봉을 합친 데이터를 보는것과 동시에 해당 부서에 소속되어 있는 직원을 조회하는 쿼리문

<소계>

ROLLUP(순서가 중요) - 개수: 요소갯수 + 1

CUBE(순서 상관 없다) - 개수: 2^(요소갯수)

GROUPING SETS

위의 함수를 활용하면 소계 항목을 추가할 수 있다.

▲ROLLUP 함수를 활용하면 위처럼 소계 항목을 생성할 수 있다.

 

 

<PIVOT 함수 활용>

PIVOT - 행단위 데이터를 컬럼 단위 데이터로 표현

(PIVOT 함수는 ORACLE 11G 버전 이후부터 활용이 가능하다)

 

PIVOT 함수가 없을 때는 SUM, DECODE를 이용해서 데이터를 회전 시켰다.

 

▲FROM에서 사용할 데이터를 정의하고 아래에 PIVOT 함수를 이용해서 회전시킨다.

▲PIVOT 함수를 응용하면 위처럼 통계로 활용할 수 있는 자료를 만들 수 있다.

 

 

<조인>

JOIN - 여러 테이블의 데이터를 합쳐서 데이터를 가져오는 방법

종류로는 EQUIJOIN, NON-EQUIJOIN, OUTER JOIN, SELF JOIN 4가지가 존재한다.

 

EQUIJOIN는 WHERE절에서 '='를 이용해서 JOIN하는 방법이다.

▲이처럼 '='를 활용해서 JOIN하여서 다른 테이블에 있는 데이터를 가져올 수 있다.

 

NON-EQUIJOIN는 '=' 연산자가 아닌 다른 연산자를 사용하여 JOIN을 하는 방법이다.

▲BETWEEN 함수를 이용해서 범위를 지정해서 GRADE를 가져온 결과

 

OUTERJOIN는 '(+)'가 WHERE절에 있는 JOIN 방식이다. '(+)'가 있는 테이블은 데이터가 없다하더라도 보여준다.

OUTERJOIN는 기준테이블을 설정하는게 중요하다. 조인을 하는 테이블에 일치하는 데이터가 없으면 데이터가 누락된다.

(WHERE에서 '(+)'를 써서 OUTERJOIN을 걸었는데 추가 조건으로 기준이 아닌 테이블을 상수조건으로 걸게되면 '(+)'를 걸어줘야한다)

▲DEPT테이블이 기준이며 EMP테이블에 40, 50 부서인 사람은 없지만 회사에 있는 모든 부서를 보여주기 위해서 OUTERJOIN을 이용함

 

▲추가 조건에 '(+)' 추가 안 할시에는 기준이 되는 테이블에서 출력하려는 데이터 출력 안됨

▲추가 조건에 '(+)' 추가해주면 원하는대로 모든 데이터가 조회된다

 

SELFJOIN은 같은 테이블을 두번 사용해서 JOIN을 한다.