SQL 활용

 

본 게시물은 ‘정미나, ⌜2022 유선배 SQL개발자(SQLD)⌟, 시대고시기획, 2022’ 을 인용하였습니다.

 

1. 서브쿼리(Subquery)

• 하나의 쿼리 안에 존재하는 또 다른 쿼리
• 서브쿼리 위치에 따른 구분
  • SELECT 절 : 스칼라 서브쿼리(Scalar Subquery)
  • FROM 절 : 인라인 뷰(Inline View)
  • WHERE 절, HAVING 절 : 중첩 서브쿼리(Nested Subquery)

1-1. 스칼라 서브쿼리(Scalar Subquery)

• 주로 SELECT 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용O
• 컬럼 대신 사용되므로 반드시 하나의 값만 반환해야 함(그렇지 않다면, 에러 발생)
• 스칼라 서브쿼리 예시

SELECT M.PRODUCT_CODE,
       (SELECT S.PRODUCT_NAME
          FROM PRODUCT S
         WHERE S.PRODUCT_CODE = M.PRODCUT_CODE) AS PRODUCT_NAME,
        M.MEMVER_ID,
        M.CONTENT
	FROM PRODUCT_REVIEW M;

 

1-2. 인라인 뷰(Inline View)

• FROM 절 등 테이블명이 올 수 있는 위치에 사용O
• 인라인 뷰 예시

SELECT M.PRODUCT_CODE,
       S.PRODUCT_NAME,
       S.PRICE,
       M.MEMBER_ID,
       M.CONTENT
	FROM PRODUCT_REVIEW M,
    	  (SELECT PRODUCT_CODE,
                  PRODUCT_NAME,
                  PRICE
             FROM PRODUCT) S
 WHERE M.PRODUCT_CODE = S.PRODUCT_CODE;

 

1-3. 중첩 서브쿼리(Nested Subquery)

1. WHERE 절, HAVING 절에 사용O

• 메인 쿼리 관계에 따른 중첩 서브쿼리 구분

① 비연관 서브쿼리(Uncorrelated Subquery) : 메인 쿼리와 관계 맺음X

② 연관 서브쿼리(Correlated Subquery) : 메인 쿼리와 관계 맺음O

• 비연관 서브쿼리(Un-Correlated Subquery) : 서브쿼리 내 메인 쿼리 컬럼 존재X
  • 예시

SELECT * FROM ANGENCY;

SELECT * FROM ENTERTAINER;

 

SELECT NAME, JOB, BIRTHDAY, AGENCY_CODE
	FROM ENTERTAINER
WHERE AGENCY_CODE = (SELECT AGENCY_CODE
                       FROM AGENCY
                      WHERE AGENCY_NAME = "EDAM엔터테인먼트");

 

• 연관 서브쿼리(Correlated Subquery) : 서브쿼리 내 메인 쿼리 컬럼O
  • 예시

SELECT * FROM CAFE_ORDER;

 

# 음료별 가장 많은 주문수량 가진 주문번호 조회 쿼리

SELECT ORDER_NO,
       DRINK_CODE,
       ORDER_CNT
  FROM CAFE_ORDER A
 WHERE ORDER_CNT = (SELECT MAX(ORDER_CNT)
                      FROM CAFE_ORDER B
                     WHERE B.DRINK_CODE = A.DRINK_CODE);

 

▶ cf. WHERE B.DRIKN_CODE = A.DRINK CODE 존재 이유(연관 서브쿼리)

- 메인 쿼리 테이블 : CAFE_ORDER, 서브쿼리 별칭은 B 메인쿼리 별칭은 A

- 서브쿼리가 메인쿼리의 테이블과 관계 설정하기 위함(서브쿼리 결과가 메인쿼리 특정 행과 관련 있음)

 

 

2. 반환하는 형태에 따른 중첩 서브쿼리 구분

① 단일 행(Single Row) 서브쿼리

• 서브쿼리가 1건 이하의 데이터 반환
• 단일 행 비교 연산자와 함께 사용
• ex. =, <, >, <=, >=, <>

② 다중 행(Multi Row) 서브쿼리

• 서브쿼리가 여러 건의 데이터 반환
• 다중 행 비교 연산자와 함께 사용
• ex. IN, ALL, ANY, SOME, EXISTS

③ 다중 컬럼(Multi Column) 서브쿼리

• 서브쿼리가 여러 컬럼의 데이터 반환

 

• 단일 행 서브쿼리(Single Row Subquery) : 항상 1건 이하 결과만 반환

SELECT * FROM PRODUCT;

 

# 가장 가격 높은 상품 정보 조회 쿼리

SELECT * FROM PRODUCT
  WHERE PRICE = (SELECT MAX(PRICE) FROM PRODUCT);

 

• 다중 행 서브쿼리(Multi Row Subquery) : 2건 이상 행 반환

SELECT * FROM PRODUCT;

SELECT * FROM PRODUCT_REVIEW;

 

# 리뷰 존재하는 상품 정보 조회 쿼리

SELECT * FROM PRODUCT
  WEHRE PRODUCT_CODE IN (SELECT PRODUCT_CODE FROM PRODUCT_REVIEW);

 

• 다중 컬럼 서브쿼리(Multi Column Subquery)

SELECT * FROM JOBS;

SELECT * FROM EMPLOYEES;

 

# MAX_SALARY=10,000인 직업을 갖고 실제 SALARY==MAX_SALARY인 직원 정보 조회 쿼리

SELECT *
	FROM EMPLOYEES
  WEHRE (JOB_ID, SALARY) IN (SELECT JOB_ID, MAX_SALARY
                               FROM JOBS
                              WHERE MAX_SALARY = 10000);

 

 

 

2. 뷰(View)

• 특정 SELECT 문에 이름을 붙여 재사용이 가능하도록 저장해놓은 오브젝트
• SQL에서 테이블처럼 사용O
• ex. 인라인 뷰를 뷰로 정의한다면, 쿼리 작성 시 인라인 뷰가 들어가는 위치에 뷰 이름만 기술하게 됨

CREATE OR REPLACE VIEW DEPT_MEMBER AS
    SELECT A.DEPARTMENT_ID,
           A.DEPARTMENT_NAME,
           B.FIRST_NAME,
           B.LAST_NAME
      FROM DEPARTMENTS A
      LEFT OUTER JOIN EMPLOYEES B
        ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

• 뷰는 가상 테이블!
  • 실제 데이터 저장X, 해당 데이터를 조회해오는 SELECT 문만 갖고 있음
• 뷰 예시

SELECT * FROM DEPT_MEMBER EWHERE DEPARTMENT_NAME = 'IT';

 

# 부서별 인원을 카운트해 인원 많은 부서부터 정렬 쿼리

SELECT DEPARTMENT_NAME, COUNT(*)
  FROM DEPT_MEMBER
 GROUP BY DEPARTMENT_NAME
 ORDER BY COUNT(*) DESC;

 

 

- cf. 뷰 삭제 : DROP VIEW DEPR_MEMBER;

 

 

3. 집합 연산자

• 각 쿼리 결과 집합으로 연산하는 명령어
• 집합 연산자 종류
  • UNION ALL : 각 쿼리 결과 집합이 합집합. 중복된 행 그대로 출력
  • UNION : 각 쿼리 결과 집합이 합집합. 중복된 행 한 줄 출력
  • INTERSECT : 각 쿼리 결과 집합이 교집합. 중복된 행 한 줄 출력
  • MINUS/EXCEPT : 앞에 있는 쿼리 결과 집합에서 뒤에 있는 쿼리 결과 집합을 뺀 차집합. 중복된 행 한 줄 출력

3-1. UNION ALL / UNION

1. UNION ALL

• QUERY1, QUERY2 결과를 그대로 합하는 것으로 중복된 행도 그대로 출력
• UNION ALL 예시

SELECT * FROM RUNNING_MAN;

SELECT * FROM INFINITE_CHALLENGE;

 

SELECT * FROM RUNNING_MAN
UNION ALL
SELECT * FROM INFINITE_CHALLENGE;

 

 

2. UNION

• QUERY1, QUERY2 결과를 합한 후 중복 제거해 출력
• UNION 예시

SELECT * FROM RUNNING_MAN
UNION
SELECT * FROM INFINITE_CHALLENGE;

 

 

3. 각 쿼리 결과 집합이 합집합에 중복된 행 없을 때 UNION ALL/UNION 모두 같은 결과 도출하지만,

UNION 사용 시 데이터베이스 내부적으로 중복된 행을 제거하는 과정을 거쳐야 해 성능상 불리할 수 있음

3-2. INTERSECT

• QUERY1, QUERY2 결과에서 공통된 부분만 중복 제거해 출력
• INTERSECT 예시

SELECT * FROM RUNNING_MAN
INTERSECT
SELECT * FROM INFINITE_CHALLENGE;

 

3-3. MINUS / EXCEPT

• QUERY1 결과에서 QUERY2 결과 제거 후 출력
• MINUS / EXCEPT 예시

SELECT * FROM RUNNING_MAN
MINUS
SELECT * FROM INFINITE_CHALLENGE;

 

 

 

4. 그룹 함수

• 데이터를 GROUP BY 하여 나타낼 수 있는 데이터를 구하는 함수
• 역할에 따른 그룹 함수 구분
  • 집계 함수 : COUNT, SUM, AVG, MAX, MIN 등
  • 소계(총계) 함수 : ROLLUP, CUBE, GROUPING SETS 등

4-1. ROLLUP

소그룹 간의 소계/총계를 계산하는 함수

• ROLLUP(A)
  • A로 그룹핑
  • 총합계
• ROLLUP(A, B)
  • A, B로 그룹핑
  • A로 그룹핑
  • 총합계
• ROLLUP(A, B, C)
  • A, B, C로 그룹핑
  • A, B로 그룹핑
  • A로 그룹핑
  • 총합계

SELECT * FROM STARBUCKS_ORDER;

 

# 주문 수량을 날짜별로 그룹핑하여 카운트한 쿼리

SELECT ORDER_DT, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT
 ORDER BY ORDER_DT;

 

# ROLLUP 함수를 적용하여 합계 구하기

SELECT ORDER_DT, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT)
 ORDER BY ORDER_DT;

 

날짜별로 그룹핑 + 총합계 결과

# 주문 수량을 날짜/주문음료별 그룹핑하여 카운트한 쿼리

SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT, ORDER_ITEM
 ORDER BY ORDER_DT;

 

# ROLLUP 함수 적용해 소계/총계 구하기

SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT, ORDER_ITEM)
 ORDER BY ORDER_DT;

 

날짜별/주문음료별 그룹핑 + 날짜별 그룹핑 + 총합계 결과

# 주문 수량을 날짜/주문음료/판매사원별로 그룹핑하여 카운트한 쿼리

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT, ORDER_ITEM, REG_NAME
 ORDER BY ORDER_DT;

 

# ROLLUP 함수 적용하여 소계/총계 구하기

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT, ORDER_ITEM, REG_NAME)
 ORDER BY ORDER_DT;

 

날짜별/주문음료별/판매사원별로 그룹핑 + 날짜별/주문음료별로 그룹핑 + 날짜별 그룹핑 + 총합계

• () 추가해보기

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP((ORDER_DT, ORDER_ITEM), REG_NAME)
 ORDER BY ORDER_DT;

 

날짜별, 주문음료별, 판매사원별로 그룹핑 + 날짜별/음료별 그룹핑 + 총합계 결과(날짜별 그룹핑 Row 빠짐)

 

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT, (ORDER_ITEM, REG_NAME))
 ORDER BY ORDER_DT;

 

 

날짜별/주문음료별/판매사원별 그룹핑 + 날짜별 그룹핑 + 총합계(날짜별/주문음료별 그룹핑 Row 빠짐)

4-2. CUBE

• 소그룹 간 소계/총계를 다차원적으로 계산할 수 있는 함수
• GROUP BY가 일방향으로 그룹핑하여 소계를 구한다면, CUBE는 조합할 수 있는 모든 그룹 대한 소계 집계
• CUBE
  • CUBE (A)
    • A로 그룹핑
    • 총합계
  • CUBE (A, B)
    • A, B로 그룹핑
    • A로 그룹핑
    • B로 그룹핑
    • 총합계
  • CUBE (A, B, C)
    • A, B, C로 그룹핑
    • A, B로 그룹핑
    • A, C로 그룹핑
    • B, C로 그룹핑
    • A로 그룹핑
    • B로 그룹핑
    • C로 그룹핑
    • 총합계

# 주문수량을 날짜별로 그룹핑하여 카운트한 쿼리

SELECT ORDER_DT, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT
 ORDER BY ORDER_DT;

 

날짜별로 그룹핑 + 총합계(ROLLUP 함수와 결과 같음)

# 주문수량을 날짜/주문음료별로 그룹핑하여 카운트한 쿼리

SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT, ORDER_ITEM
 ORDER BY ORDER_DT;

 

# 위 데이터에 CUBE 함수 적용해 소계/총계 구하기

SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY CUBE(ORDER_DT, ORDER_ITEM)
 ORDER BY ORDER_DT;

 

날짜별/주문음료별 그룹핑 + 날짜별 그룹핑 + 주문음료별 그룹핑 + 총합계

 

아래 쿼리와 같은 의미

SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT, ORDER_ITEM
UNION ALL
SELECT ORDER_DT, NULL, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT
UNION ALL
SELECT NULL, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_ITEM
UNION ALL
SELECT NULL, NULL, COUNT(*)
  FROM STARBUCKS_ORDER
 ORDER BY 1, 2;

 

# 주문 수량을 날짜/주문음료/판매사원 별로 그룹핑하여 카운트한 쿼리

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT, ORDER_ITEM, REG_NAME
 ORDER BY ORDER_DT;

 

# 위 데이터에 CUBE 함수 적용하여 소계/총계 구하기

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY CUBE(ORDER_DT, ORDER_ITEM, REG_NAME)
 ORDER BY OREDER_DT;

 

날짜별/주문음료별/판매사원별 그룹핑 + 날짜별/주문음료별 그룹핑 + 날짜별/판매사원별 그룹핑 + 주문음료별/판매사원별 그룹핑 + 날짜별 그룹핑 + 주문음료별 그룹핑 + 판매사원별 그룹핑 + 총합계

• 위 데이터에 () 추가

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY CUBE((ORDER_DT, ORDER_ITEM), REG_NAME)
 ORDER BY OREDER_DT;

 

날짜별/주문음료별/판매사원별 그룹핑 + 날짜별/주문음료별 그룹핑 + 판매사원별 그룹핑 + 총합계 결과(날짜별/주문음료별 그룹핑 더해짐)

 

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY CUBE(ORDER_DT, (ORDER_ITEM, REG_NAME))
 ORDER BY OREDER_DT;

 

4-3. GROUPING SETS

• 특정 항목 대한 소계 계산하는 함수
• 인자값으로 ROLLUP, CUBE 사용O
• GROUPING SETS
  • GROUPING SETS (A, B)
    • A로 그룹핑
    • B로 그룹핑
  • GROUPING SETS (A, B, ())
    • A로 그룹핑
    • B로 그룹핑
    • 총합계
  • GROUPING SETS (A, ROLLUP(B))
    • A로 그룹핑
    • B로 그룹핑
    • 총합계
  • GROUPING SETS(A, ROLLUP(B, C))
    • A로 그룹핑
    • B, C로 그룹핑
    • B로 그룹핑
    • 총합계
  • GROUPING SETS (A, B, ROLLUP(C)) 
    • A로 그룹핑
    • B로 그룹핑
    • C로 그룹핑
    • 총합계

# 주문수량을 날짜/주문음료별로 그룹핑하여 카운트한 쿼리

SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 ORDER BY ORDER_DT, ORDER_ITEM
 ORDER BY ORDER_DT;

 

# 위 데이터에 GROUPING SETS 함수 적용하여 소계/총계 구하기

SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY GROUPING SETS(ORDER_DT, ORDER_ITEM)
 ORDER BY ORDER_DT;

 

날짜별 그룹핑 + 주문음료별 그룹핑 결과(총계 구하려면, 인자값에 () 추가 혹은 ROLLUP 적용)

• 인자값에 () 추가해 총계 구한 쿼리

SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY GROUPING SETS(ORDER_DT, ORDER_ITEM, ())
 ORDER BY ORDER_DT, ORDER_ITEM;

 

• 인자값에 ROLLUP 적용하여 총계 구한 쿼리
  • ROLLUP 함수를 ORDER_DT 컬럼에 적용하여도 같은 결과

SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY GROUPING SETS(ORDER_DT, ROLLUP(ORDER_ITEM))
 ORDER BY ORDER_DT, ORDER_ITEM;

 

• 위 데이터에 GROUPING SETS 함수 적용하여 소계/총계 구하기

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY GROUPING SETS(ORDER_DT, ORDER_ITEM, REG_NAME)
 ORDER BY ORDER_DT, ORDER_ITEM;

 

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY GROUPING SETS(ORDER_DT, ORDER_ITEM, ROLLUP(REG_NAME))
 ORDER BY ORDER_DT, ORDER_ITEM;

 

• ROLLUP 함수를 2개 컬럼에 적용

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY GROUPING SETS(ORDER_DT, ROLLUP(ORDER_ITEM, REG_NAME))
 ORDER BY ORDER_DT, ORDER_ITEM;

 

날짜별 그룹핑 + 주문음료별/판매사원별 그룹핑 + 주문음료별 그룹핑 + 총합계 결과

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY GROUPING SETS(ROLLUP(ORDER_DT, ORDER_ITEM), REG_NAME)
 ORDER BY ORDER_DT, ORDER_ITEM, REG_NAME;

 

날짜별/주문음료별 그룹핑 + 날짜별 그룹핑 + 판매사원별 그룹핑 + 총합계 결과

4-4. GROUPING

• ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이고 소계를 나타내는 Row를 구분할 수 있게 해줌
• 앞선 예제는 소계 나타내는 Row에서 그룹핑 기준 되는 컬럼 제외 모두 NULL 표현,
  GROUPING 함수 이용 시 원하는 위치에 원하는 텍스트 출력O

SELECT ORDER_DT,
       GROUPING(ORDER_DT),
       COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT)
 ORDER BY ORDER_DT;

 

데이터에서 소계가 계산된 Row에서는 GROUPING 함수 결과값 1이되고 나머지 Row에서는 0이 되는 것 확인

• 결과값으로 CASE문을 이용해 원하는 텍스트 출력하기

SELECT CASE GROUPING(ORDER_DT)
            WHEN 1 THEN 'TOTAL' ELSE ORDER_DT
       END AS ORDER_DT
       COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT)
 ORDER BY ORDER_DT;

 

• Oracle 경우, DECODE 문으로 CASE 문 대체O

SELECT DECODE(GROUPING(ORDER_DT), 1, 'TOTAL', ORDER_DT) AS ORDER_DT, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT)
 ORDER BY ORDER_DT;

 

• 그룹핑하는 컬럼이 2개일 때도 동일한 방법으로 쿼리 작성O

SELECT ORDER_DT,
       GROUPING(ORDER_DT),
       ORDER_ITEM,
       GROUPING(ORDER_ITEM),
       COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT, ORDER_ITEM)
 ORDER BY ORDER_DT;

 

 

 

5. 윈도우 함수

• OVER 키워드와 함께 사용됨
• 역할에 따른 윈도우 함수 구분
  • 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER
  • 집계 함수 : SUM, MAX, MIN, AVG, COUNT
  • 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
  • 비율 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

5-1. 순위 함수

• RANK : 1, 2, 2, 4, 5, 5, 7 ...
• DENSE_RANK : 1, 2, 2, 3, 4, 4, 5 ...
• ROW_NUMBER : 1, 2, 3, 4, 5, 6 ,7 ...

1. RANK

• 순위를 매기며 같은 순위 존재 시, 존재하는 수 만큼 다음 순위 건너뜀
• 날짜 별 주문 건수 카운트 해 순위 매긴 쿼리

SELECT ORDER_DT,
       COUNT(*),
       RANK() OVER(ORDER BY COUNT(*) DESC) AS RANK
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT;

 

• 부서 별 급여 높은 사원부터 순위 매긴 쿼리

SELECT FIRST_NAME,
       LAST_NAME,
       DEPARTMENT_ID,
       SALARY,
       RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RANK
   FROM EMPLOYEES;

 

 

2. DENSE_RANK

• 순위를 매기며 같은 순위가 존재해도 다음 순위를 건너뛰지 않고 이어서 매김('순위가 밀집되어 있다')
• 날짜별로 주문 건수 카운트해 순위 매긴 쿼리

SELECT ORDER_DT,
       COUNT(*),
       DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS DENSE_RANK
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT;

 

• 부서 별 급여 높은 사원부터 순위 매긴 쿼리

SELECT FIRST_NAME,
       LAST_NAME,
       DEPARTMENT_ID,
       SALARY,
       DENSE_RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS DENSE_RANK
  FROM EMPLOYEES;

 

 

3. ROW_NUMBER

• 순위를 매기며 동일한 값이라도 각기 다른 순위 부여
• 날짜별 주문건수 카운트 해 순위 매긴 쿼리

SELECT ORDER_DT,
       COUNT(*),
       ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS ROW_NUMBER
   FROM STARBUCKS_ORDER
  GROUP BY ORDER_DT;

 

• 부서별로 급여 높은 사원부터 순위 매긴 쿼리

SELECT FIRST_NAME,
       LAST_NAME,
       DEPARTMENT_ID,
       SALARY,
       ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS ROW_NUMBER
   FROM EMPLOYEES;

 

5-2. 집계 함수

1. SUM : 데이터 합계 구하는 함수로 인자값은 숫자형만O

SELECT * FROM SQLD;

 

 

# SQLD 시험 성적 데이터에 SUM 함수 적용
# 결과 : 모든 사람들의 점수 합

SELECT SUM(SCORE) AS TOTAL_SCORE FROM SQLD;

 

# 개인별 총점수 구하는 쿼리

SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       SUM(SCORE) OVER(PARTITION BY STUDENT_NAME) AS TOTAL_SCORE
  FROM SQLD;

 

• Oracle 경우, OVER 절 내에 ORDER BY 절 사용해 데이터 누적값 구할 수 있음

SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       SUM(SCORE) OVER(PARTITION BY STUDENT_NAME
                           ORDER BY SUBJECT DESC
                           RANGE UNBOUNDED PRECEDING) AS TOTAL_SCORE
   FROM SQLD;

 

• SUM 하는 컬럼을 OVER 절에서 ORDER BY 절에 명시해주면, RANGE UNBOUNDED PRECEDING 구문 없어도 누적합이 집계됨

SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       SUM(SCORE) OVER(ORDER BY SCORE DESC) AS SUM_SCORE
  FROM SQLD
 WHERE SUBKECT = 'SQL 기본 및 활용';

 

 

2. MAX : 데이터 최댓값 구하는 함수

SELECT * FROM SQLD;

 

# SQLD 시험 성적에 MAX 함수 적용(모든 점수 대상으로 최댓값 출력)

SELECT MAX(SCORE) AS MAX_SCORE FROM SQLD;

 

# 과목별 최대 점수 구하는 쿼리

SELECT STUDENT_NAME,
       SUBJECT,
       SCORE
   FROM (
     SELECT STUDENT_NAME,
            SUBJECT,
            SCORE,
            MAX(SCORE) OVER(PARTITION BY SUBJECT) AS MAX_SCORE
       FROM SQLD )
 WHERE SCORE = MAX_SCORE;

 

 

3. MIN : 데이터 최솟값 구하는 함수

SELECT * FROM SQLD;

 

# SQLD 시험 성적에 MIN 함수 적용(모든 점수 대상 최솟값 출력)

SELECT MIN(SCORE) AS MIN_SCORE FROM SQLD;

 

# 과목별 최소 점수 구하는 쿼리

SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       MIN(SCORE) OVER(PARTITION BY SUBJECT) AS MIN_SCORE
  FROM SQLD;

 

# 과목별 최소 점수 받은 사람만 출력하는 쿼리

SELECT STUDENT_NAME,
       SUBJECT,
       SCORE
   FROM (
     SELECT STUDENT_NAME,
            SUBJECT,
            SCORE,
            MIN(SCORE) OVER(PARTITION BY SUBJECT) AS MIN_SCORE
        FROM SQLD )
 WHERE SCORE = MIN_SCORE;

 

 

4. AVG : 데이터 평균을 구하는 함수

SELECT * FROM SQLD;

 

 

# SQLD 시험 성적 데이터에 AVG 함수 적용(모든 점수 대상 평균값 출력)

SELECT AVG(SCORE) AS AVG_SOCRE FROM SQLD;

 

SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       ROUND(AVG(SCORE) OVER(PARTITION BY SUBJECT)) AS AVG_SCORE
  FROM SQLD;

 

# 과목별 평균 점수 이상 받은 사람만 출력하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE
  FROM (
    SELECT STUDENT_NAME, SUBJECT, SCORE,
           ROUND(AVG(SCORE) OVER(PARTITION BY SUBJECT)) AS AVG_SCORE
      FROM SQLD )
  WHERE SCORE >= AVG_SCORE;

 

• cf. 윈도우 함수 사용 옵션
  • WINDOWING 절을 이용해 집계하려는 데이터 범위 지정O

 

5. COUNT : 데이터 건 수 구하는 함수

SELECT * FROM SQLD;

 

# SQLD 시험 성적 데이터에 COUNT 함수 적용(모든 점수 대상 건수 출력)

SELECT COUNT(*) AS SCORE_COUNT FROM SQLD;

 

# 과목별로 PASS한 건 수 구하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       COUNT(*) OVER(PARTITION BY SUBJECT) AS PASS_COUNT
  FROM SQLD
 WHERE RESULT = 'PASS';

 

# COUNT 함수는 WINDOWING 절을 이용해 원하는 범위에 해당하는 데이터에 대한 통계값을 구할 수 있음
# 과목별로 본인보다 점수 높거나 같은 건수를 카운트하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SOCRE,
       COUNT(*) OVER(PARTITION BY SUBJECT
                        ORDER BY SCORE DESC
                        RANGE UNBOUNDED PRECEDING ) AS HIGER_COUNT
  FROM SQLD;

 

# 과목별로 본인 점수와 5점 이하로 차이나거나 같은 건수 카운트하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       COUNT(*) OVER(PARTITION BY SUBJECT
                         ORDER BY SCORE DESC
                         RANGE BETWEEN 5 PRECEDING AND 5 FOLLOFWING) AS SIMILAR_COUNT
  FROM SQLD;

 

5-3. 행 순서 함수

1. FIRST_VALUE : 파티션 별 가장 선두에 위치한 데이터 구하는 함수(cf. SQL Server(MSSQL)는 지원X)

SELECT * FROM SQLD;

 

# SQLD 시험 성적 데이터에 FIRST_VALUE 함수 적용
# 모든 점수 대상으로 오름차순 한 다음 첫 번째에 위치하게 되는 점수 출력

SELECT STUDENT_NAME, SUBJECT, SCORE
       FIRST_VALUE(SCORE) OVER(ORDER BY SCORE) AS FIRST_VALUE
  FROM SQLD;

 

# 과목별로 가장 높은 점수 구하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       FIRST_VALUE(SCORE) OVER(PARTITION BY SUBJECT
                                   ORDER BY SCORE DESC) AS FIRST_VALUE
  FROM SQLD;

 

 

2. LAST_VALUE : 파티션 별 가장 끝에 위치한 데이터 구하는 함수(cf. SQL Server(MSSQL)는 지원X)

SELECT * FROM SQLD;

 

# SQLD 시험 성적 데이터에 LAST_VALUE 함수 적용
# 모든 점수 대상 오름차순 한 후, 마지막에 위치하는 점수 출력

SELECT STUDENT_NAME, SUBJECT, SCORE,
       LAST_VALUE(SOCRE) OVER(ORDER BY SCORE) AS LAST_VALUE
  FROM SQLD;

 

- 오름차순 후 마지막 점수는 모두 77이 출력되어야 할 것 같으나, 실제로는 각 Row의 SCORE 값과 동일한 값 출력됨

- WINDOWING 절의 default가 RANGE UNBOUNDED PRECEDING 이어서 파티션의 범위가 맨 위 끝 행부터 현재 행까지로 지정됨

# 오름차순 후 마지막 점수 모두 77이 출력되는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       LAST_VALUE(SCORE) OVER(ORDER BY SCORE
                              RANGE BETWEEN UNBOUNDED PRECEDING
                                AND UNBOUNDED FOLLOWING) AS LAST_VALUE
  FROM SQLD;

 

# 과목별로 가장 높은 점수 구하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       LAST_VALUE(SCORE) OVER(PARTITION BY SUBJECT
                                  ORDER BY SCORE
                                  RANGE BETWEEN UNBOUNDED PRECEDING
                                    AND UNBOUNDED FOLLOWING) AS LAST_VALUE
  FROM SQLD;

 

 

3. LAG : 파티션 별로 특정 수 만큼 앞선 데이터 구하는 함수(cf. SQL Server(MSSQL)는 지원X)

SELECT * FROM SQLD;

 

# SQLD 시험 성적 데이터에 LAG 함수 적용

SELECT STUDENT_NAME, SUBJECT, SCORE,
       LAG(SCORE, 3) OVER(ORDER BY SCORE) AS LAG
  FROM SQLD;

 

 

# LAG 함수 두 번째 인자값 생략 시, default는 1

SELECT STUDENT_NAME, SUBJECT, SCORE,
       LAG(SCORE) OVER(ORDER BY SCORE) AS LAG
  FROM SQLD;

 

# 과목별로 본인보다 2만큼 앞에 있는(높은) 점수 구하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       LAG(SCORE, 2) OVER(PARTITION BY SUBJECT
                              ORDER BY SCORE DESC) AS LAG
  FROM SQLD;

 

 

4. LEAD : 파티션 별 특정 수 만큼 뒤에 있는 데이터 구하는 함수(cf. SQL Server(MSSQL)는 지원X)

SELECT * FROM SQLD;

 

# SQLD 시험 성적 데이터에 LEAD 함수 적용

SELECT STUDENT_NAME, SUBJECT, SCORE,
       LEAD(SCORE, 3) OVER(ORDER BY SCORE) AS LEAD
  FROM SQLD;

 

# LEAD 함수 두 번째 인자값 생략 시 default는 1
SELECT STUDENT_NAME, SUBJECT, SCORE
       LEAD(SCORE) OVER(ORDER BY SCORE) AS LEAD
  FROM SQLD;

 

# 과목별로 본인보다 2만큼 뒤에 있는(낮은) 점수 구하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       LEAD(SCORE, 2) OVER(PARTITION BY SUBJECT
                               ORDER BY SCORE DESC) AS LEAD
  FROM SQLD;

 

5-4. 비율 함수

1. PARTIO_TO_REPORT : 파티션 별 합계에서 차지하는 비율 구하는 함수(cf. SQL Server(MSSQL)는 지원X)

SELECT * FROM SQLD;

 

# SQLD 시험 성적 데이터에 RATIO_TO_REPORT 함수 적용
# SCORE 총합에서 본인 SCORE 나눈 값과 동일

SELECT STUDENT_NAME, SUBJECT, SCORE,
       SUM(SCORE) OVER() AS SUM,
       SCORE/SUM(SCORE) OVER() AS "SCORE/SUM",
       RATIO_TO_REPORT(SCORE) OVER() AS PRTIO_TO_REPORT
  FROM SQLD;

 

# 과목별 SCORE 합계에서 차지하는 비율 구하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       SUM(SCORE) OVER(PARTITION BY SUBJECT) AS SUM,
       SCORE/SUM(SCORE) OVER(PARTITION BY SUBJECT) AS "SCORE/SUM"
       RATION_TO_REPORT(SCORE) OVER(PARTITION BY SUBJECT) AS RATIO_TO_REPORT
  FROM SQLD;

 

 

2. PERCENT_RANK : 해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수(cf. SQL Server(MSSQL)는 지원X)

SELECT * FROM SQLD;

 

# SQLD 시험 성적 데이터에 PERCENT_RANK 함수 적용
# PERCENCT_RANK 함수 결과가 RANK 순위 값에서 1 뺀 값을 총 COUNT에서 1 뺀 값으로 나눈 값과 동일 

SELECT STUDENT_NAME, SUBJECT, SCORE,
       RANK() OVER(ORDER BY SCORE) AS RANK,
       COUNT(*) OVER() AS COUNT,
       (RANK() OVER(ORDER BY SCORE)-1)/COUNT(*) OVER()-1) AS "(RANK-1)/(COUNT-1)",
       PERCENT_RANK() OVER(ORDER BY SCORE) AS PERCENT_RANK
  FROM SQLD;

 

# 과목별로 나눈 파티션에서 해당 SCORE가 차지하는 백분위 순위 구하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       RANK() OVER(PARTITION BY SUBJECT ORDER BY SCORE) AS RANK,
       COUNT(*) OVER(PARTITION BY SUBJECT) AS COUNT,
       (RANK() OVER(PARTITION BY SUBJECT ORDER BY SCORE)-1)/
       (COUNT(*) OVER(PARTITION BY SUBJECT)-1) AS "(RANK-1)/(COUNT-1)",
       PERCENT_RANK() OVER(PARTITION BY SUBJECT ORDER BY SCORE) AS PERCENT_RANK
  FROM SQLD;

 

 

3. CUME_DIST : 해당 파티션에서 누적 백분율 구하는 함수로 결과값은 0보다 크고 1보다 작거나 같은 값 가짐(cf. SQL Server(MSSQL)는 지원X)

SELECT * FROM SQLD;

 

# CUME_DIST 함수 적용
# CUME_DIST 함수 결과가 현재 행까지의 누적 건수에서 전체 건주로 나눈 값과 동일함

SELECT STUDENT_NAME, SUBJECT, SCORE,
       COUNT(*) OVER(ORDER BY SCORE) AS COUNT,
       COUNT(*) OVER() AS TOTAL_COUNT,
       COUNT(*) OVER(ORDER BY SCORE)/COUNT(*) OVER() AS "COUNT/TOTAL_COUNT",
       CUME_DIST() OVER(ORDER BY SCORE) AS CUME_DIST
  FROM SQLD;

 

# 과목별로 나눈 파티션에서 해당 SCORE에 해당하는 누적 백분율 구하는 쿼리

SELECT STUDENT_NAME, SUBJECT, SCORE,
       COUNT(*) OVER(PARTITION BY SUBJECT ORDER BY SCORE) AS COUNT,
       COUNT(*) OVER(PARTITION BY SUBJECT) AS TOTAL_COUNT,
       COUNT(*) OVER(PARTITION BY SUBJECT ORDER BY SCORE)/
       COUNT(*) OVER(PRATITION BY SUBJECT) AS "COUNT/TOTAL_COUNT",
       CUME_DIST() OVER(PARTITION BY SUBJECT ORDER BY SCORE) AS CUME_DIST
  FROM SQLD;

 

 

4. NTILE : 주어진 수 만큼 행 들을 n등분 한 후 현재 행에 해당하는 등급 구하는 함수

SELECT * FROM SQLD;

 

# NTILE 함수 적용
# SCORE 데이터가 주어진 인수만큼 그룹으로 분리된 후 동등하게 순서 부여됨
# 단, NTILE(3)은 할당할 행 남았을 경우 맨 앞의 그룹부터 하나씩 더 채워짐

SELECT STUDENT_NAME, SUBJECT, SCORE,
       NTILE(1) OVER(ORDER BY SCORE DESC) AS NTILE1,
       NTILE(3) OVER(ORDER BY SCORE DESC) AS NTILE3,
       NTILE(5) OVER(ORDER BY SCORE DESC) AS NTILE5,
  FROM SQDL;

 

# 과목별로 SCORE 등급 나누기

SELECT STUDENT_NAME, SUBJECT, SCORE,
       NTILE(1) OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC) AS NTILE1,
       NTILE(3) OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC) AS NTILE3,
       NTILE(5) OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC) AS NTILE5,
  FROM SQDL;

 

 

 

6. Top-N 쿼리

6-1. ROMNUM

• Oracle의 ROWNUM은 Pseudo Column
  • Pseudo : 사전적 의미로 '가짜'
• ROWNUM은 실제로 존재하지 않는 가짜 컬럼
• ex. 엑셀 작성 시 순번이 필요한 경우 보통 맨 앞에 별도의 열을 만들어 엑셀 자동번호 매기는 경우
  • Oracle에서는 SELECT 절에 ROWNUM 컬럼 하나 추가하면 됨

SELECT ROWMUM, 이름, 국어, 영어, 수학
  FROM EXAM_SCORE;

 

• 엑셀 자동 순번 매기기가 위에 있는 행에서 +1이 되는 것처럼 ROWNUM도 행이 반환될 때마다 순번이 1씩 정가해 WHERE ROWNUM = 5같은 건너뛰기 조건 성립X
• ROWNUM은 항상 <, <=조건으로 사용해야 함

SELECT ROWNUM, 이름, 국어, 영어, 수학
  FROM (
    SELECT 이름, 국어, 영어, 수학
      FROM EXAM_SCORE
     ORDER BY 국어 DESC, 영어 DESC, 수학 DESC )
 WHERE ROWNUM <= 5;

 

# 주의! 이와 같이 작성하면 안됨
# SELECT 절에서 논리적으로 ORDER BY 절이 WHERE 절보다 나중에 수행됨 
# 아래 쿼리는 데이터를 랜덤으로 5개 뽑은 후 그걸 가지고 순위를 매기는 격

SELECT ROWNUM, 이름, 국어, 영어, 수학
  FROM EXAM_SCORE
 WHERE ROWNUM <= 5
 ORDER BY 국어 DESC, 영어 DESC, 수학 DESC;

 

6-2. 윈도우 함수의 순위 함수

# ROW_NUMBER 함수 이용해 Top-N 쿼리 작성

SELECT * FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY 국어 DESC, 영어 DESC, 수학 DESC)
           AS RNUM, 이름, 국어, 영어, 수학
      FROM EXAM_SCORE)
 WHERE RNUM <= 5;

 

# RANK 함수 이용해 Top-N 쿼리 작성

SELECT * FROM (
    SELECT RANK() OVER(ORDER BY 국어 DESC, 영어 DESC, 수학 DESC)
           AS RANK, 이름, 국어, 영어, 수학
      FROM EXAM_SCORE)
 WHERE RANK <= 5;

 

# DENSE_RANK 함수 이용해 Top-N 쿼리 작성

SELECT * FROM (
         SELECT DENSE_RANK() OVER(ORDER BY 국어 DSEC, 영어 DESC, 수학 DESC)
                AS DR, 이름, 국어, 영어, 수학
           FROM EXAM_SCORE)
 WHERE DR <= 5;

 

 

 

7. 셀프 조인(Self Join)

• 나 자신과의 조인
• FROM 절에 같은 테이블이 2번 이상 등장해 혼란 막기 위해 ALIAS 표기 필수
• ex. 쇼핑몰에서 상품이 속한 카데고리

SELECT * FROM CATEGORY;

 

# 셀프 조인
# 대분류&중분류 함께 출력한 쿼리

SELECT A.CATEGORY_TYPE, A.CATEGORY_NAME,
       B.CATEGORY_TYPE, B.CATEGORY_NAME
  FROM CATEGORY A,
       CATEGORY B
 WHERE A.CATEGORY_NAME = B.PARENT_CATEGORY
   AND A.CATEGORY_TYPE = '대';

 

# 대분류, 중분류, 소분류 함께 출력
# 카데고리 Depth 깊어질수록 셀프 조인 반복됨

SELECT A.CATEGORY_TYPE, A.CATEGORY_NAME,
       B.CATEGORY_TYPE, B.CATEGORY_NAME,
       C.CATEGORY_TYPE, C.CATEGORY_NAME
  FROM CATEGORY A,
       CATEGORY B
       CATEGORY C
 WHERE A.CATEGORY_NAME = B.PARENT_CATEGORY
   AND B.CATEGORY_TYPE = C.PARENT_CATEGORY;

 

 

 

8. 계층 쿼리

• 테이블에 계층 구조 이루는 컬럼 존재 경우, 계층 쿼리 이용해 데이터 출력O

# 앞의 셀프 조인 쿼리 => 계층 쿼리로 변환

SELECT LEVEL,
       SYS_CONNECT_BY_PATH('['||CATEGORY_TYPE||']' CATEGORY_NAME, '-') AS PATH
  FROM CATEGORY
 START WITH PARENT_CATEGORY IS NULL
 CONNECT BY PRIOR CATEGORY_NAME = PARENT_CATEGORY;

 

별다른 JOIN 없이도 계층 구조 출력됨

• 쿼리 해석
  • LEVEL : 현재 DEPTH 반환. 루트 노드는 1이됨
  • SYS_CONNECT_BY_PATH(컬럼, 구분자) : 루트 노드부터 현재 노드까지 경로를 출력해주는 함수
  • START WITH : 경로 시작되는 루트 노드를 생성해주는 절
  • CONNECT BY : 루트 노드로부터 자식 노드 생성해주는 절. 조건에 만족하는 데이터 없을 때까지 노드 생성
  • PRIOR : 바로 앞의 부모 노드 값 반환
• 계층 이루는 단계별 순서

① START WITH PARENT_CATEGORY IS NULL

② CONNECT BY PRIOR CATEGORY_NAME=PARENT_CATEGORY

-> 컴퓨터/디지털/가전 = PARENT_CATEGORY

③ CONNECT BY PRIOR CATEGORY_NAME = PARENT_CATEGORY

-> 컴퓨터 = PARENT_CATEGORY

-> 디지털 = PARENT_CATEGORY

-> 가전 = PARENT_CATEGORY

# 위 단계 걸친 후 결과

SELECT LEVEL,
       CATEGORY_TYPE AS TYPE,
       CATEGORY_NAME AS NAME,
       PARENT_CATEGORY AS PARENT,
       SYS_CONNECT_BY_PATH('['||CATEGORY_TYPE||']' CATEGORY_NAME, '-') AS PATH
  FROM CATEGORY
 START WITH PARENT_CATEGORY IS NULL
 CONNECT BY PRIOR CATEGORY_NAME = PARENT_CATEGORY;

 

• 그 밖의 계층 쿼리에서 사용할 수 있는 항목
  • CONNECT_BY_ROOT 컬럼 : 루트 노드의 주어진 컬럼 값 반환
  • CONNECT_BY_ISLEAF : 가장 하위 노드인 1 반환, 그 외 0 반환

SELECT LEVEL,
       CATEGORY_TYPE,
       CATEGORY_NAME,
       PARENT_CATEGORY,
       CONNECT_BY_ROOT CATEGORY_NAME AS ROOT_INFO,
       CONNECT_BY_ISLEAF AS LEAF_INFO
  FROM CATEGORY
 START WITH PARENT_CATEGORY IS NULL
 CONNECT BY PRIOR CATEGORY_NAME = PARENT_CATEGORY;

 

지금까지 계층 쿼리는 순방향(루트(상위) -> 리프(하위))

• 역방향 전개 리프(하위) -> 루트(상위)

SELECT LEVEL,
       CATEGORY_TYPE AS TYPE,
       CATEGORY_NAME AS NAME,
       PARENT_CATEGORY AS PARENT,
       SYS_CONNECT_BY_PATH('['||CATEGORY_TYPE||']' || CATEGORY_NAME, '-') AS PATH
  FROM CATEGORY
 START WITH CATEGORY_TYPE = '소'
 CONNECT BY CATEGORY_NAME = PRIOR PARENT_CATEGORY;

 

- START WITH 절을 하위 노드로 잡아주고 CONNECT BY 절에서 부모 노드의 PARENT_CATEGORY가 자식 노드의 CATEGORY_NAME이 되도록 작성하면 역방향 전개의 계층 쿼리 됨

- START WITH 절은 CATEGORY_TYPE이 아닌, CATEGORY_NAME으로 잡아줄 수 있음

SELECT LEVEL,
       CATEGORY_TYPE AS TYPE,
       CATEGORY_NAME AS NAME,
       PARENT_CATEGORY AS PARENT,
       SYS_CONNECT_BY_PATH('['||CATEGORY_TYPE||']' || CATEGORY_NAME, '-') AS PATH
  FROM CATEGORY
 START WITH CATEGORY_TYPE = '노트북/PC'
 CONNECT BY CATEGORY_NAME = PRIOR PARENT_CATEGORY;

 

• 계층 쿼리에서 ORDER BY 절 사용하는 경우

SELECT LEVEL,
       CATEGORY_TYPE AS TYPE,
       CATEGORY_NAME AS NAME,
       PARENT_CATEGORY AS PARENT,
       SYS_CONNECT_BY_PATH('['||CATEGORY_TYPE||']' || CATEGORY_NAME, '-') AS PATH
  FROM CATEGORY
 START WITH PARENT_CATEGORY IS NULL
 CONNECT BY PRIOR CATEGORY_NAME = PARENT_CATEGORY
 ORDER BY NAME;

 

- 계층 구조와 상관 없이 정렬됨

- ORDER SIBLINGS BY 절 이용해 보완O

• ORDER SIBLINGS BY 절 사용해 같은 레벨끼리 정렬되도록 작성한 쿼리

SELECT LEVEL,
       CATEGORY_TYPE AS TYPE,
       CATEGORY_NAME AS NAME,
       PARENT_CATEGORY AS PARENT,
       SYS_CONNECT_BY_PATH('['||CATEGORY_TYPE||']' || CATEGORY_NAME, '-') AS PATH
  FROM CATEGORY
 START WITH PARENT_CATEGORY IS NULL
 CONNECT BY PRIOR CATEGORY_NAME = PARENT_CATEGORY
 ORDER SIBLINGS BY NAME;