[database] Advanced SQL - nvl, decode, case, pivot, rollup, cube, rank, 상관 쿼리

1. NVL 함수

• 컬럼의 값이 null일 때 어떤 값을 보이게 할 것인지를 지정한다
• NVL 함수는 모든 데이터 타입에 적용할 수 있다
• NVL 함수를 사용할 때 변경되는 값의 데이터 타입을 일치시켜야 한다

SELECT name, salary, nvl(salary, 0) modified_salary -- NVL()
FROM temp;

SELECT name, salary, nvl2(salary, 100000, 0)    -- NVL2(값, 지정값1, **지정값2**)
FROM temp;

2. DECODE 함수

• n항연산자, switch 문
• 컬럼 값에 따라 출력 값을 지정한다
• else 조건을 명시하지 않을 경우 기본 값으로 null

SELECT name, salary, DECODE(TRUNC(salary/1000), null, null, 0, 'E', 1, 'D', 2, 'C', 3, 'B', 'A') salgrade
FROM s_emp;

3. CASE 문

• 컬럼의 값을 경우에 따라 다르게 지정한다

DROP VIEW vw_case_sal;
CREATE VIEW vw_case_sal AS
SELECT id, salary, name,
    CASE 
        WHEN salary BETWEEN 1000 AND 2000 THEN 'FIR'
        WHEN salary BETWEEN 2100 AND 3000 THEN 'SEC'
        WHEN salary BETWEEN 3100 AND 4000 THEN 'THIRD'
        WHEN salary BETWEEN 4100 AND 5000 THEN 'FOURTH'
        ELSE 'COUNTLESS'
    END 급여등급
FROM s_emp;
SELECT * FROM vw_case_sal;

4. PIVOT 함수

• 다차원 분석
• row를 column으로 펴서 확인할 수 있다
• DECODE 함수로도 변환할 수 있다

SELECT *
FROM vw_case_sal
PIVOT(
    COUNT(*)
    FOR "급여등급" IN ('COUNTLESS', 'FIR', 'SEC', 'THIRD', 'FOURTH')
) 
ORDER BY salary asc;

SELECT *
FROM (SELECT dept_id, title FROM s_emp)
PIVOT (
    COUNT(*)
    FOR title IN ('사원', '과장', '부장', '이사', '사장')
)
ORDER BY dept_id;

5. ROLLUP 함수

• 그룹별 합계
• java의 for/if 문으로 수행해야 하는 것을 ROLLUP 함수로 대신 사용

SELECT dept_id, title, count(*)
FROM s_emp
WHERE dept_id IN (106, 112, 113)
GROUP BY ROLLUP(dept_id, title)
ORDER BY dept_id;

6. CUBE 함수

• 그룹별 합계, 소계
• 컬럼 단위의 합계도 포함

SELECT dept_id, title, count(*) 
FROM s_emp
WHERE dept_id IN(106, 112, 113)
GROUP BY CUBE(dept_id, title)
ORDER BY dept_id;

7. RANK 함수

• 행별 순위를 계산해서 보여준다
• 그룹별 순위를 계산해서 보여줄 수 있다

-- over (order by 기준) 명시
SELECT id, name, salary,
    rank() over (order by salary DESC) as rank
FROM s_emp;

SELECT dept_id, name, salary,
    rank() over (
    partition by dept_id -- 그룹별 순위 원할 때
    order by salary DESC) as rank
FROM s_emp;

8. 상관 쿼리

• 서브 쿼리를 먼저 실행하는 것이 아니라, 외부 쿼리의 row를 먼저 실행하면서 서브 쿼리와 비교
• outer 쿼리의 candidate row가 더 이상 남지 않을 때까지 반복

-- 8. 상관 쿼리 
-- outer에서 inner를 사용
-- outer query의 candidate row가 더 이상 남지 않을 때까지 반복

-- 자신의 급여가 자신이 속한 부서의 평균 급여보다 적은 직원에 대해 이름, 급여, 부서번호를 출력하시오
SELECT name, salary, dept_id
FROM s_emp e
WHERE e.salary < (
    SELECT AVG(salary) FROM s_emp WHERE dept_id = e.dept_id
);

-- 본인의 급여가 각 부서별 평균 급여 중 어느 한 부서의 평균 급여보다 적은 급여를 받는 직원에 대해
-- 이름, 급여, 부서번호를 출력하시오
SELECT name, salary, dept_id
FROM s_emp
WHERE salary < ANY(
    SELECT AVG(salary)
    FROM s_emp
    GROUP BY dept_id
);

-- EXISTS 서브 쿼리
-- EXISTS 서브 쿼리는 단순히 T/F를 반환하기 때문에 SELECT 절에 뭐를 쓰든 관계 없음
-- 하지만 비교하는 원본 컬럼을 명시하면 논리적으로 더 깔끔한 쿼리가 됨
-- 본인이 다른 사람의 관리자(manager_id)로 되어 있는 직원의 사번, 이름, 직책, 부서번호를 나타내시오

SELECT id, name, title, dept_id
FROM s_emp outer
WHERE EXISTS (
    SELECT id FROM s_emp WHERE manager_id = outer.id
);

sql.txt

0.03MB