07 PIVOT, UNPIVOT, SCALAR SUBQUERY

[문제] 부서별로 인원수를 출력.

10	20	...	110	부서가 없는 사원
1	2	...	2	1

SELECT
    count(decode(department_id, 10, 'x')) "10",
    -- count는 개수를 세는용도이니 리턴값을 'x'문자나 숫자1 등으로 활용
    count(decode(department_id, 20, 'x')) "20",
    count(decode(department_id, 30, 'x')) "30",
    count(decode(department_id, 40, 'x')) "40",
    count(decode(department_id, 50, 'x')) "50",
    count(decode(department_id, 60, 'x')) "60",
    count(decode(department_id, 70, 'x')) "70",
    count(decode(department_id, 80, 'x')) "80",
    count(decode(department_id, 90, 'x')) "90",
    count(decode(department_id, 100, 'x')) "100",
    
    count(CASE WHEN department_id = 110 THEN 'x' END) "110",
    count(CASE WHEN department_id IS NULL THEN 'x' END) "부서가 없는 사람"
FROM hr.employees;

>> rows 개수만큼 decode 를 각 반복 체크 > 107행 * decode 12개 반복 > 속도 저하 > 악성코드

↓

SELECT department_id, count(*)
FROM hr.employees
GROUP BY department_id;
>> 부서별 카운트
↓
SELECT
    max(decode(department_id, 10, cnt)) "10",
    max(decode(department_id, 20, cnt)) "20",
    max(decode(department_id, 30, cnt)) "30",
    max(decode(department_id, 40, cnt)) "40",
    max(decode(department_id, 50, cnt)) "50",
    max(decode(department_id, 60, cnt)) "60",
    max(decode(department_id, 70, cnt)) "70",
    max(decode(department_id, 80, cnt)) "80",
    max(decode(department_id, 90, cnt)) "90",
    max(decode(department_id, 100, cnt)) "100",
    max(decode(department_id, 110, cnt)) "110",
    max(decode(department_id, NULL, cnt)) "부서가 없는 사원"
FROM (SELECT department_id, count(*) cnt
        FROM hr.employees
        GROUP BY department_id);

>> 부서별카운트 12행 x decode 12개 반복

↓

■ PIVOT
- 행(세로) 데이터를 열(가로)로 변경하는 함수
    ★ PIVOT(그룹함수 FOR 적용칼럼 IN (InlineView에 해당하는 값을 칼럼명으로 사용)
                 └ 함수사용 X                   ㄴ'문자' as "칼럼명"

SELECT department_id, count(*)
FROM hr.employees
GROUP BY department_id;
>> 부서별 카운트
↓ 
SELECT *
FROM (SELECT department_id
        FROM hr.employees)
PIVOT(count(*) FOR department_id IN (10,20,30,40,50,60,70,80,90,100,
                                        110,null as "부서가 없는 사원"));
||
SELECT *
FROM (SELECT department_id, count(*) cnt
        FROM hr.employees
        GROUP BY department_id)
PIVOT(max(cnt) FOR department_id IN (10,20,30,40,50,60,70,80,90,100,
                                        110,null as "부서가 없는 사원"));

>> 결과집합 cnt가 이미 사용되어 max,min,sum 등을 사용하여 그룹함수를 적용

 

SELECT department_id, sum(salary)
FROM hr.employees
GROUP BY department_id
↓
SELECT *
FROM (SELECT department_id, salary
        FROM hr.employees)
PIVOT(sum(salary) FOR department_id IN (10,20,30,40,50,60,70,80,90,100,
                                        110,null as "부서가 없는 사원"));
||
SELECT *
FROM (SELECT department_id, sum(salary) sum_sal
        FROM hr.employees
        GROUP BY department_id)
PIVOT(max(sum_sal) FOR department_id IN (10,20,30,40,50,60,70,80,90,100,
                                        110,null as "부서가 없는 사원"));

 

[문제] 년도별 입사 인원수를 가로방향으로 출력.

SELECT *
FROM (SELECT to_char(hire_date,'yyyy') year, count(*) cnt
        FROM hr.employees
        GROUP BY to_char(hire_date,'yyyy'))
PIVOT (max(cnt) FOR year IN ('2001' "2001",'2002' "2002",'2003' "2003",
                                '2004' as "2004",'2005' as "2005",
                                '2006' "2006",'2007' "2007",'2008' "2008")),
        (SELECT count(*) 총인원수 FROM hr.employees),
        (SELECT to_char(sum(salary),'9,999,999') 총급여 FROM hr.employees);
||
SELECT *
FROM (SELECT to_char(hire_date,'yyyy') year
        FROM hr.employees)
PIVOT (count(*) FOR year IN ('2001' "2001",'2002' "2002",'2003' "2003",
                                '2004' as "2004",'2005' as "2005",
                                '2006' "2006",'2007' "2007",'2008' "2008")),
        (SELECT count(*) 총인원수 FROM hr.employees),
        (SELECT to_char(sum(salary),'9,999,999') 총급여 FROM hr.employees);

 

[문제] 요일별 입사 인원수를 가로방향으로 출력.

SELECT *
FROM (SELECT to_char(hire_date,'dy') day, count(*) cnt
        FROM hr.employees
        GROUP BY to_char(hire_date,'dy'))
PIVOT (max(cnt) FOR day IN ('월' "월요일", '화' "화요일", '수' "수요일",
                                '목' "목요일", '금' "금요일", '토' "토요일",
                                '일' "일요일"));
||
SELECT *
FROM (SELECT to_char(hire_date,'dy') day
        FROM hr.employees)
PIVOT (count(*) FOR day IN ('월' "월요일", '화' "화요일", '수' "수요일",
                                '목' "목요일", '금' "금요일", '토' "토요일",
                                '일' "일요일"));

 

---

 

■ UNPIVOT
- 열(가로)을 행(세로)으로 변환하는 함수

SELECT *
FROM (SELECT *
        FROM (SELECT to_char(hire_date,'dy') day
                FROM hr.employees)
        PIVOT (count(*) FOR day IN ('월' "월요일", '화' "화요일", '수' "수요일",
                                    '목' "목요일", '금' "금요일", '토' "토요일",
                                    '일' "일요일")))
UNPIVOT (인원수 FOR 요일 in(월요일,화요일,수요일,목요일,금요일,토요일,일요일));
                            ㄴ데이터명
||
SELECT 요일||'요일' as 요일, 인원수
FROM (SELECT *
        FROM (SELECT to_char(hire_date,'dy') day
                FROM hr.employees)
        PIVOT (count(*) FOR day IN ('월' "월", '화' "화", '수' "수",
                                    '목' "목", '금' "금", '토' "토",
                                    '일' "일")))
UNPIVOT (인원수 FOR 요일 in(월,화,수,목,금,토,일));

 

[문제] 년도, 분기별 급여의 총액을 구하세요.

SELECT to_char(hire_date,'yyyy') 년도, to_char(hire_date, 'q') 분기,
        sum(salary) 총액
FROM hr.employees
GROUP BY to_char(hire_date,'yyyy'), to_char(hire_date, 'q');
↓
SELECT *
FROM (SELECT to_char(hire_date,'yyyy') 년도, to_char(hire_date, 'q') 분기,
                sum(salary) 총액
        FROM hr.employees
        GROUP BY to_char(hire_date,'yyyy'), to_char(hire_date, 'q'))
PIVOT (max(총액) FOR 분기 IN (1 "1분기", 2 "2분기", 3 "3분기", 4 "4분기"))
ORDER BY 1;

 

---

■ 다중열 서브쿼리

- 비쌍비교

SELECT *
FROM hr.employees
WHERE manager_id IN (SELECT manager_id
                        FROM hr.employees
                        WHERE first_name = 'John')
AND department_id IN (SELECT department_id
                        FROM hr.employees
                        WHERE first_name = 'John');
예시)
manager_id  department_id       서브쿼리에서 추출한 값
----------  --------------      ----------------------
100                 10 (O)        100 10
200                 20 (O)        100 20
300                 30 (O)        100 30
300                 10 (O)        200 10
...                 ...           200 20
                                  200 30
                                  300 10
                                  300 20
                                  300 30
                                  ...

 

- 쌍비교

SELECT *
FROM hr.employees
WHERE (manager_id, department_id) IN (SELECT manager_id, department_id
                                        FROM hr.employees
                                        WHERE first_name = 'John');
예시)
manager_id  department_id       서브쿼리에서 추출한 값
----------  -------------       ---------------------
100                 10 (O)        100 10
200                 20 (O)        200 20
300                 30 (O)        300 30
300                 10            ...
...                 ...

 

[문제] commission_pct가 null이 아닌 사원들의 department_id, salary와 일치하는
	사원들의 정보를 출력해주세요.

1) 비쌍비교
SELECT *
FROM hr.employees
WHERE department_id IN (SELECT department_id
                        FROM hr.employees
                        WHERE commission_pct IS NOT NULL)
--     └ department_id에 null은 비교대상 제외된다.
AND salary IN (SELECT salary
                FROM hr.employees
                WHERE commission_pct IS NOT NULL);
↓
SELECT *
FROM hr.employees
WHERE nvl(department_id,0) IN (SELECT nvl(department_id,0)
                        FROM hr.employees
                        WHERE commission_pct IS NOT NULL)
--     └ department_id에 있는 null을 0으로 대체하여 출력
AND salary IN (SELECT salary
                FROM hr.employees
                WHERE commission_pct IS NOT NULL);

2) 쌍비교
SELECT *
FROM hr.employees
WHERE (nvl(department_id,0), salary) IN (SELECT nvl(department_id,0), salary
                                            FROM hr.employees
                                            WHERE commission_pct IS NOT NULL);

★ NULL값의 비교대상여부, 표시여부 체크  ★

 

---

 

SELECT e.employee_id, e.last_name, e.department_id, 
        d.department_id, d.department_name 
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
ORDER BY 3, 4;

>> e.department_id 107행 X d.department_id 27행
>> JOIN의 비효율성

↓

■ SCALAR SUBQUERY
- SELECT절에 SUBQUERY를 SCALAR SUBQUERY라고 한다.
- 한행에 정확히 하나의 열값만 반환하는 서브쿼리를 의미한다.
- 동일한 입력값이 들어오면 수행 횟주를 최소화 할 수 있는 기능이 되어 있다.
- query execution cache 기능이 수행된다.
- 키값이 없는 데이터가 입력되면 null값으로 리턴한다.(OUTER JOIN 기법처럼 출력된다.)

SELECT employee_id, last_name, department_id,
        (SELECT department_name                     -- 한개값만 반환
            FROM hr.departments
            WHERE department_id = e.department_id)  -- cash 기능
FROM hr.employees e
ORDER BY 3;


SELECT employee_id, last_name, department_id,
        (SELECT department_name, manager_id         -- 오류
            FROM hr.departments
            WHERE department_id = e.department_id)
FROM hr.employees e
ORDER BY 3;
>> 여러 열값으로 출력 할 수 없다.

SELECT employee_id, last_name, department_id,
        (SELECT department_name||', '||manager_id   -- 가능
            FROM hr.departments
            WHERE department_id = e.department_id)
FROM hr.employees e
ORDER BY 3;
>> 여러 열값을 하나의 열로 수정해서 수행할 수 있다.

 

[문제] 부서이름별 급여의 총액, 평균을 출력해주세요.

1) 일반적형식(JOIN)
SELECT d.department_name, sum(e.salary), round(avg(e.salary))
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

## INLINE VIEW를 이용해서 조인의 일량을 줄이는 쿼리를 사용
SELECT department_id, sum(salary), round(avg(salary))
FROM hr.employees
GROUP BY department_id;
>> 부서ID로 검색결과 추출
↓
SELECT d.department_name, sum_sal, avg_sal
FROM (SELECT department_id, sum(salary) sum_sal, round(avg(salary)) avg_sal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id(+);
||
SELECT d.department_name, sum_sal, avg_sal
FROM (SELECT department_id, sum(salary) sum_sal, round(avg(salary)) avg_sal
        FROM hr.employees
        GROUP BY department_id) e LEFT OUTER JOIN hr.departments d
ON e.department_id = d.department_id;


2) SCALAR SUBQUERY
SELECT department_name,
        (SELECT sum(salary)
            FROM hr.employees
            WHERE department_id = d.department_id) sumsal,
        (SELECT round(avg(salary))
            FROM hr.employees
            WHERE department_id = d.department_id) avgsal
FROM hr.departments d;
>> 단일행, 단일값 출력을 위해 각각 만들었는데 hr.employees 테이블을 반복작업
↓
SELECT department_name,
        (SELECT sum(salary)||round(avg(salary))
            FROM hr.employees
            WHERE department_id = d.department_id) agg_sal
FROM hr.departments d;
>> scalar subquery로 한번에 계산하지만 sum과 avg 구분불가
↓
SELECT department_name, substr(agg_sal,1,10) sumsal,
        substr(agg_sal,11) avgsal
FROM (SELECT department_name,
            (SELECT lpad(sum(salary),10)||lpad(round(avg(salary)),10)
                FROM hr.employees
                WHERE department_id = d.department_id) agg_sal
        FROM hr.departments d);
>> lpad, substr 활용하여 분리