[SQLD]「2」(2.8) ROWNUM, 셀프 조인, 계층 쿼리

[SQLD]「2」(2.8) ROWNUM, 셀프 조인, 계층 쿼리

「1」 데이터 모델링의 이해

> 「2」 SQL 기본 / 활용

「3」 관리 구문

 

1. 서브쿼리

2. 뷰

3. 집합 연산자

4. 소계(총계) 함수

5. 윈도우 함수

> 6. Top-N 쿼리

> 7. 셀프 조인

> 8. 계층 쿼리

 

6. Top-N 쿼리

Top-N 쿼리

⸰ROWNUM: 순위 함수가 아닌, 각각의 칼럼에 순서대로 번호를 부여한 슈도 칼럼(존재하지 않는 가짜 칼럼)

√. 항상 WHERE 절에서 < 조건이나 <= 조건으로 사용해야 함

√. ORDER BY 절을 사용하려면, 서브 쿼리로 안에서 정렬 후, 밖에서 WHERE 절을 사용해야 함

(WHERE 절, ORDER BY 절이 있으면, ORDER BY 절이 나중에 수행되기 때문에  ROWNUM이 섞여서 출력됨)

SELECT ROWNUM, 칼럼명1, 칼럼명2 FROM 테이블명;

ROWNUM	칼럼명1	칼럼명2
1	데이터1	데이터2
2	데이터1	데이터2
3	데이터1	데이터2
4	데이터1	데이터2

 

⸰ 순위 함수를 이용한 Top-N 쿼리

SELECT * FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY 칼럼명1 [ASC|DESC], 칼럼명2 [ASC|DESC]) AS RNUM,
           칼럼명1,
           칼럼명2,
      FROM 테이블명1)
WHERE RNUM <= 숫자;

SELECT * FROM (
    SELECT RANK() OVER(ORDER BY 칼럼명1 [ASC|DESC], 칼럼명2 [ASC|DESC]) AS RANK,
           칼럼명1,
           칼럼명2,
      FROM 테이블명1)
WHERE RNAK <= 숫자;

SELECT * FROM (
    SELECT DENSE_RANK() OVER(ORDER BY 칼럼명1 [ASC|DESC], 칼럼명2 [ASC|DESC]) AS DR,
           칼럼명1,
           칼럼명2,
      FROM 테이블명1)
WHERE DR <= 숫자;

 

⸰ROW_NUMBER( )과ROWNUM의 차이

  -ROW_NUMBER( ): OVER 를 통해 범위를 지정해 번호 부여

  -ROWNUM: 단순히 데이터가 입력한 순서대로 번호 부여

 

7. 셀프 조인

셀프 조인

⸰ 카테고리 대-중-소(-세) 의 구조를 가진 데이터

⸰ FROM 절에 같은 테이블이 두 번 등장하기 때문에, ALIAS 반드시 표기

⸰ 계층 쿼리를 이용하여 더 간단하게 작성 가능

SELECT A.칼럼명1,
       A.칼럼명2,
       B.칼럼명1,
       B.칼럼명2,
       C.칼럼명1,
       C.칼럼명2,
  FROM 테이블명1 A,
       테이블명1 B,
       테이블명1 C,
 WHERE A.동일칼럼명 = B.동일칼럼명
   AND B.동일칼럼명 = C.동일칼럼명;

 

8. 계층 쿼리

계층 쿼리

⸰ 테이블에 계층 구조를 이루는 칼럼이 존재하는 경우 계층 쿼리를 이용하여 데이터 출력 가능

  -LEVEL: 현재 depth를 반환, 루트 노드는 1

  -SYS_CONNECT_BY_PATH(칼럼, 구분자): 루트 노드부터 현재 노드까지의 경로 출력

  -START WITH: 경로가 시작되는 루트 노드를 생성

  -CONNECT BY: 루트로부터 자식 노드를 생성, 조건에 만족하는 데이터가 없을 때까지 노드 생성

  -PRIOR: 바로 앞에 있는 부모 노드의 값 반환

  -CONNECT_BY_ROOT 칼럼: 루트 노드의 주어진 칼럼 값을 반환

  -CONNECT_BY_ISLEAF: 가장 하위 노드인 경우 1, 그 외에는 0 반환

 

  -ORDER SIBLINGS BY 칼럼: 같은 레벨끼리 정렬

SELECT LEVEL,
       카테고리타입명 AS TYPE,
       카테고리명 AS NAME,
       부모카테고리명 AS PARENT,
       SYS_CONNECT_BY_PATH('['||카테고리타입명||']'||카테고리명, '-') AS PATH
  FROM 테이블명
 START WITH 부모카테고리명 IS NULL
 CONNECT BY PRIOR 카테고리명 = 부모카테고리명;

LEVEL	TYPE	NAME	PARENT	PATH
1	대	AB	NULL	-[대]AB
2	중	A	AB	-[대]AB-[중]A
2	중	B	AB	-[대]B-[중]B
3	소	AA	A	-[대]AB-[중]A-[소]AA
3	소	AA	A	-[대]AB-[중]A-[소]AA
3	소	BB	B	-[대]AB-[중]B-[소]BB
3	소	BB	B	-[대]AB-[중]B-[소]BB

 

⸰순방향: 루트(상위) > 리프(하위) 전개 방향

⸰역방향: 리프(하위) > 루트(상위) 전개 방향

 

SELECT LEVEL,
       카테고리타입명 AS TYPE,
       카테고리명 AS NAME,
       부모카테고리명 AS PARENT,
       SYS_CONNECT_BY_PATH('['||카테고리타입명||']'||카테고리명, '-') AS PATH
  FROM 테이블명
 START WITH 카테고리타입명 = '소'
 CONNECT BY 카테고리명 = PRIOR 부모카테고리명;

LEVEL	TYPE	NAME	PARENT	PATH
1	소	A	A	-[대]AB-[중]A-[소]AA
1	소	AA	A	-[대]AB-[중]A-[소]AA
1	소	BB	B	-[대]AB-[중]B-[소]BB
1	소	BB	B	-[대]AB-[중]B-[소]BB
2	중	A	AB	-[대]AB-[중]A
2	중	B	AB	-[대]AB-[중]B
3	대	AB	NULL	-[대]AB

 

 

 

 

 

 

 

유선배 SQLD 과외노트