23.10.16. Oracle SQL PIVOT, UNPIVOT, 다중열 서브쿼리(비쌍비교 VS 쌍비교), SCALAR SUBQUERY(스칼라 서브쿼리)

Data Base/Oracle SQL

23.10.16. Oracle SQL PIVOT, UNPIVOT, 다중열 서브쿼리(비쌍비교 VS 쌍비교), SCALAR SUBQUERY(스칼라 서브쿼리)

잇꼬 2023. 10. 16. 17:29

728x90

SMALL

■ PIVOT (그룹함수 for 컬럼명 in (컬럼명1, 컬럼명2, 컬럼명3, ... 컬럼명n as "별칭"))
a. 행(세로) 데이터를 열(가로)로 변경하는 함수

SELECT department_id, COUNT(*) cnt
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 "소속사원이 없는 부서") );

SELECT department_id, SUM(salary)
FROM hr.employees
GROUP BY department_id;

(방법1)

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 "소속사원이 없는 부서") );

(방법2)

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 "소속사원이 없는 부서") );

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

(방법1)

SELECT *
FROM ( SELECT to_char(hire_date, 'yyyy') as year -- 별칭사용
       FROM hr.employees )
PIVOT ( count(*) FOR year IN ('2001' "2001년", '2002' as "2002년", '2003' as "2003년", '2004' as "2004년", '2005' as "2005년", '2006' as "2006년", '2007' as "2007년", '2008' as "2008년") ) ,
      ( SELECT count(*) "총 인원수" FROM hr.employees ), 
      ( SELECT sum(salary) "총 급여" FROM hr.employees );

(방법2)

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

금액을 콤마로 표현할 때

to_char(SUM(salary), '9,999,999') 로 표현한다/

[문제]요일별 입사 인원수를 가로방향으로 출력해주세요.
(방법1)

SELECT *
FROM ( SELECT to_char(hire_date, 'day') as day
       FROM hr.employees )
PIVOT ( count(*) FOR day IN ('일요일' as "일요일", '월요일' as "월요일", '화요일' as "화요일", '수요일' as "수요일", '목요일' as "목요일", '금요일' as "금요일", '토요일' as "토요일") ) ;

(방법2)

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

■ UNPIVOT( 컬럼명(열이름) FOR 컬럼명(열이름) IN (별칭1, 별칭2, 별칭3, ... 별칭n) )
a. 열(가로)을 행(세로)으로 변환하는 함수

SELECT 요일||'요일' as 요일, 인원수 
FROM ( SELECT *
       FROM ( SELECT to_char(hire_date, 'day') as day
              FROM hr.employees )
      PIVOT ( COUNT(*) FOR day IN ('일요일' as "일", '월요일' as "월", '화요일' as "화", '수요일' as "수", '목요일' as "목", '금요일' as "금", '토요일' as "토") ) 
)
    UNPIVOT( 인원수 FOR 요일 IN (일, 월, 화, 수, 목, 금, 토) );

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

1) 년도별 총액

SELECT to_char(hire_date, 'yyyy') 년도, SUM(salary) 총액
FROM hr.employees
GROUP BY to_char(hire_date, 'yyyy');

2) 분기별 총액

SELECT to_char(hire_date, 'q') 분기, SUM(salary) 총액
FROM hr.employees
GROUP BY to_char(hire_date, 'q');

3) 년도, 분기별 총액

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 AS "1분기", 2 "2분기", 3 "3분기", 4 "4분기") )
ORDER BY 1;

[문제] 1분기 데이터만 출력해주세요.

SELECT "1분기"
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 AS "1분기", 2 "2분기", 3 "3분기", 4 "4분기") )
ORDER BY 년도;

■ 다중열 서브쿼리

a. 비쌍 비교 쿼리문

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'); /* 총 23건 */

b. 쌍비교 쿼리문

SELECT * 
FROM hr.employees
WHERE (manager_id, department_id) IN ( SELECT manager_id, department_id
                                       FROM hr.employees
                                       WHERE first_name = 'John' ); /* 총 18건 */

예시)

manager_id	department_id	서브쿼리에서 추출한 값
100	10	100	10
200	20	200	20
300	30	300	30
300	10

        1) 비쌍비교                 2) 쌍비교
        A → A   B → A               (A, B) → (A´, B´)
        A → B   B → B   ...         (C, D) → (C´, D´)
        A → C   B → C               (E, F) → (E´, F´)

[문제] commission_pct가 NULL 이 아닌 사원들의 department_id, salary와 일치하는 사원들의 정보를 출력해주세요.
1) 비쌍비교 쿼리문

SELECT count(*)
FROM hr.employees
WHERE commission_pct IS NOT null;

-- null 값 존재
nvl(department_id,0) 으로 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)
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);

JOIN의 비효율성: 동일한 데이터가 있어도 한번더 조회를 하게 된다

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;

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

SELECT 
    employee_id, 
    last_name, 
    department_id, 
    ( SELECT department_name
      FROM hr.departments 
      WHERE department_id = e.department_id ) as department_name 
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 ) as department_name 
FROM hr.employees e 
ORDER BY 3;

## 여러 열값을 하나의 열로 수정해서 수행할 수 잆다.

A. 연결연산자

SELECT 
    employee_id, 
    last_name, 
    department_id, 
    ( SELECT department_name ||', '||manager_id
      FROM hr.departments
      WHERE department_id = e.department_id ) as department_name
FROM hr.employees e 
ORDER BY 3;

B. 별도의 컬럼 생성

SELECT 
    employee_id, 
    last_name, 
    department_id, 
    ( SELECT department_name
      FROM hr.departments 
      WHERE department_id = e.department_id ) as department_name , 
    ( SELECT manager_id  
      FROM hr.departments 
      WHERE department_id = e.department_id ) as manager_id
FROM hr.employees e 
ORDER BY 3;

[문제] 부서이름별 급여의 총액, 평균을 출력해주세요.
1) 일반적인 형식(JOIN)

SELECT department_name, 
       to_char(sum(e.salary), '999,999') sumsal, 
       to_char(round(avg(e.salary)), '999,999') avgsal
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
GROUP BY department_name;

- INLINE VIEW 을 이용해서 조인의 일량을 줄이는 쿼리문을 작성

SELECT d.department_name, sumsal, avgsal
FROM  ( SELECT department_id, 
            to_char(sum(salary), '999,999') sumsal, 
            to_char(round(avg(salary)), '999,999') avgsal
        FROM hr.employees 
        GROUP BY department_id ) e, hr.departments d
WHERE e.department_id = d.department_id ;

- OUTER JOIN(오라클 버젼)

SELECT d.department_name, sumsal, avgsal
FROM  ( SELECT department_id, 
                to_char(sum(salary), '999,999') sumsal, 
                to_char(round(avg(salary)), '999,999') avgsal
        FROM hr.employees 
        GROUP BY department_id ) e, hr.departments d
WHERE e.department_id = d.department_id(+) ;

- OUTER JOIN(ANSI 버전)

SELECT d.department_name, sumsal, avgsal
FROM  ( SELECT department_id, 
                to_char(sum(salary), '999,999') sumsal, 
                to_char(round(avg(salary)), '999,999') avgsal
        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;

# 이렇게 작성하게 되면, 단일행 쿼리문으로 선능적으로 떨어지게 된다.

방법1)

SELECT department_name,
                        ( SELECT '총액: '||SUM(salary)||', 평균:'||round(AVG(salary))
                          FROM hr.employees
                          WHERE department_id = d.department_id ) agg_sal
FROM hr.departments d;

# 이렇게 보고서 작성후 제출하면.. 각각 나오게 해달라는 요청이 들어온다고 한다!

#1) 먼저, LPAD() 를 이용해서 공백을 만들어준다.

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;

#2) 다음에 전체적으로 SELECT 문으로 묶고,

SELECT department_name, substr(agg_sal, 1, 10) sum_sal, substr(agg_sal, 11) avg_sal
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 );

#3) 출력했을 때 나와야 하는 컬럼명을 SUBSTR() 로 공간을 확보해 출력해 준다.

#4) 그럼 원하는 모습으로 출력할 수 있다!

728x90

LIST

저작자표시 비영리 동일조건

'Data Base > Oracle SQL' 카테고리의 다른 글

23.10.17. Oracle SQL 계층 검색(hierarchical query) START WITH-CONNECT BY PRIOR (0)	2023.10.17
23.10.17. Oracle SQL 집합연산자 UNION(합집합), UNION ALL(합집합), INTERSECT(교집합), MINUS(차집합) (0)	2023.10.17
23.10.13. Oracle SQL IN, ANY, ALL (0)	2023.10.13
23.10.13. Oracle SQL SUBQUERY(서브쿼리) (0)	2023.10.13
23.10.12. Oracle SQL JOIN ② (0)	2023.10.12

현재글23.10.16. Oracle SQL PIVOT, UNPIVOT, 다중열 서브쿼리(비쌍비교 VS 쌍비교), SCALAR SUBQUERY(스칼라 서브쿼리)

250x250

안녕? 나야 :)