고급 쿼리 / 계층적 쿼리 / PIVOT

계층적 쿼리

쉽게 얘기하자면 결과 값들을 계단과 같이 층을 지어서 보여준다.

(눈으로 봤을때 더 알아보기 쉽게)

-- soft 테이블

CREATE  TABLE  soft (
     num  NUMBER  PRIMARY KEY
     ,subject  VARCHAR2(100) NOT NULL
     ,parent  NUMBER
);

INSERT INTO soft(num, subject, parent) VALUES (1, '소프트웨어', NULL);

INSERT INTO soft(num, subject, parent) VALUES (2, '프로그래밍', 1);
INSERT INTO soft(num, subject, parent) VALUES (3, '데이터베이스', 1);
INSERT INTO soft(num, subject, parent) VALUES (4, '웹프로그래밍', 1);

INSERT INTO soft(num, subject, parent) VALUES (5, '자바', 2);
INSERT INTO soft(num, subject, parent) VALUES (6, 'C/C++', 2);
INSERT INTO soft(num, subject, parent) VALUES (7, 'C#', 2);

INSERT INTO soft(num, subject, parent) VALUES (8, 'C 언어', 6);
INSERT INTO soft(num, subject, parent) VALUES (9, 'C++ 언어', 6);

INSERT INTO soft(num, subject, parent) VALUES (10, '오라클', 3);
INSERT INTO soft(num, subject, parent) VALUES (11, 'MS-SQL', 3);
INSERT INTO soft(num, subject, parent) VALUES (12, 'MYSQL', 3);

INSERT INTO soft(num, subject, parent) VALUES (13, '오라클 SQL', 10);
INSERT INTO soft(num, subject, parent) VALUES (14, '오라클 튜닝', 10);
INSERT INTO soft(num, subject, parent) VALUES (15, '오라클 PL/SQL', 10);

INSERT INTO soft(num, subject, parent) VALUES (16, '자바스크립트', 4);
INSERT INTO soft(num, subject, parent) VALUES (17, 'CSS 3', 4);
INSERT INTO soft(num, subject, parent) VALUES (18, 'HTML 5', 4);
INSERT INTO soft(num, subject, parent) VALUES (19, 'JSP/Servlet', 4);
INSERT INTO soft(num, subject, parent) VALUES (20, 'ASP.NET', 4);

COMMIT;

먼저 soft 테이블 만들고 값을 추가해 주자.

 

     SELECT num, subject, LEVEL, parent
     FROM soft
     START WITH num = 1             -- 출력할 시작 행
     CONNECT BY PRIOR  num = parent;
             -- 계층관계 지정
             -- 이전 행의 num은 parent
             -- 나(num)을 부모(parent)로 사용하는 행(부하 직원) 검색
             -- parent 컬럼: 상위 정보를 가진 컬럼
             -- LEVEL : 단계, 처음(루트)이 1단계, 하위로 갈수록 1씩 증가
             -- LEVEL은 CONNECT BY 절이 있는 경우만 사용 가능

     SELECT num, LPAD(' ', (LEVEL-1)*4) || subject subject, LEVEL, parent
     FROM soft
     START WITH num = 1
     CONNECT BY PRIOR  num = parent;

     SELECT num, LPAD('*', (LEVEL-1)*4, '*') || subject subject, LEVEL, parent
     FROM soft
     START WITH num = 3
     CONNECT BY PRIOR  num = parent;
     
     -- vsCode 전용

     -- 계층구조를 유지하면서 같은 레벨 끼리 정렬
     SELECT num, subject, LEVEL, parent
     FROM soft
     START WITH num = 1
     CONNECT BY PRIOR  num = parent
     ORDER  SIBLINGS  BY subject;

 

 

PIVOT과 UNPIVOT

행과 열을 바꾸는 개념

 

PIVOT

      WITH temp_table AS (
         SELECT 1 cnt, 1000 price FROM DUAL UNION ALL
         SELECT 2 cnt, 1050 price FROM DUAL UNION ALL
         SELECT 3 cnt, 2100 price FROM DUAL UNION ALL
         SELECT 1 cnt, 5500 price FROM DUAL UNION ALL
         SELECT 2 cnt, 7000 price FROM DUAL UNION ALL
         SELECT 3 cnt, 7000 price FROM DUAL
     )
     SELECT cnt, SUM(price) price
     FROM temp_table
     GROUP BY cnt;

위 결과 값의 행과 열을 바꿔 보자

 

WITH temp_table AS (
         SELECT 1 cnt, 1000 price FROM DUAL UNION ALL
         SELECT 2 cnt, 1050 price FROM DUAL UNION ALL
         SELECT 3 cnt, 2100 price FROM DUAL UNION ALL
         SELECT 1 cnt, 5500 price FROM DUAL UNION ALL
         SELECT 2 cnt, 7000 price FROM DUAL UNION ALL
         SELECT 3 cnt, 7000 price FROM DUAL
)
SELECT * FROM (
       SELECT cnt,price FROM temp_table
)
PIVOT(
       SUM(price) FOR cnt IN(1,2,3)
);

행과 열이 바뀌었다.

 

 

예제)

SELECT dept, city, COUNT(*) FROM emp
GROUP BY dept, city
ORDER BY dept;

     SELECT * FROM (
          SELECT city, dept --열
          FROM emp
     ) 
     PIVOT (
           COUNT(dept) -- 값
           FOR dept IN ( -- 행
                  '개발부' AS "개발부",
                  '영업부' AS "영업부", 
                  '기획부' AS "기획부", 
                  '인사부' AS "인사부", 
                  '자재부' AS "자재부", 
                  '총무부' AS "총무부", 
                  '홍보부' AS "홍보부"
           )
     );

 

UNPIVOT

    CREATE TABLE  tcity  AS
     SELECT * FROM (
          SELECT city, dept
          FROM emp
     ) 
     PIVOT (
           COUNT(dept)
           FOR dept IN (
                  '개발부' AS "개발부",
                  '영업부' AS "영업부", 
                  '기획부' AS "기획부", 
                  '인사부' AS "인사부", 
                  '자재부' AS "자재부", 
                  '총무부' AS "총무부", 
                  '홍보부' AS "홍보부"
           )
     );
    SELECT * FROM tcity;

    SELECT * FROM tcity
    UNPIVOT
    (
         인원수
         FOR  dept IN (개발부, 영업부, 기획부, 인사부, 자재부, 총무부, 홍보부)
    );

    DROP TABLE tcity PURGE;

결과 테이블로 저장하고

테이블을 이용해서 UNPIVOT한다.

인원수 라는 새로운 행을 만들어서 값을 넣는다 (COUNT 값)