SQLD: TOP-N, 계층쿼리

TOP-N

ROWNUM

ROWNUM은 테이블의 행에 임시로 부여되는 일련번호이다.

SELECT ROWNUM AS RN --가상컬럼 : 실제 테이블에 존재하지는 않지만 특수한 기능을 가진 컬럼
    , 직원ID 
    , 이름
    , 연봉 
    , 부서ID
FROM 직원; 

WHERE 조건에 ROWNUM을 사용하면 출력되는 행의 개수를 제한할 수 있다.

SELECT ROWNUM AS RN --가상컬럼 : 실제 테이블에 존재하지는 않지만 특수한 기능을 가진 컬럼
    , 직원ID 
    , 이름
FROM 직원
WHERE ROWNUM <= 3; 

일련번호 1부터 3까지만 가져오기

ROWNUM은 1의 값을 먼저 사용해야 2의 값을 사용할 수 있다. 가상의 값이기 때문에 이전의 기준점을 잡아주어야 한다.

이럴 때, ROWNUM = 2인 대상만 뽑기 위해 인라인뷰를 활용한다.

위 문제에 따라 테이블을 만들려면 다음과 같이 쿼리를 작성하면 된다:

SELECT *  
FROM 직원정보 
WHERE 연봉 IS NOT NULL
ORDER BY 연봉 DESC;

여기서 추가로 연봉이 가장 높은 세명을 뽑고 싶어 WHERE절 아래에 AND RN <= 3 을 한다면 잘못된 정보가 나온다. 실행 순서의 문제 때문인데, WHERE가 ORDER BY보다 먼저 실행되기 때문이다.

이에 대한 해결 방안은 ORDER BY를 먼저 실행되게 하도록 작성하면 되는데, 이를 위해서는 인라인뷰를 활용하면 된다.

SELECT *  
FROM (SELECT *
    FROM 직원정보
    WHERE 연봉 IS NOT NULL
    ORDER BY 연봉 DESC) 
WHERE RN <= 3;

---

계층형 질의와 셀프 조인

계층형 질의는 메뉴 정보 혹은 직원 정보 등에 사용이 되는데, 이 계층형 질의라는 것은 동일한 테이블 내에 계층 관계가 있는 컬럼이 존재하고, 이를 이용해 계층 관계를 표현하는 질의(쿼리)이다.

메뉴ID와 상위메뉴ID가 계층형 관계인 테이블이다.

SELECT MENU_ID
    , UPPER_MENU_ID
    , MENU_NAME
    , LEVEL -- 가상의 컬럼 (LEVEL: 계층레벨)
    , LPAD(' ', (LEVEL - 1 ) * 2, ' ') || MENU_NAME 
FROM MENU
WHERE 1 = 1
START WITH UPPER_MENU_ID IS NULL -- START WITH: 계층을 전개할 시작점
CONNECT BY NOCYCLE UPPER_MENU_ID = PRIOR MENU_ID -- CONNECT BY: 계층을 전개할 자식 데이터 조건 
                                                -- NOCYCLE: CYCLE  발생 시 데이터 전개 멈춤 
ORDER SIBLINGS BY MENU_ID; -- 정렬을 하되, 형제노드끼리 연결  

CONNECT_BY_ISLEAF = 단말유무, 리프노드일 시 1, 아닐 시 0
CONNECT_BY_ISCYCLE = 사이클유무, 있을 시 1, 없을 시 0

필터링을 하기 전 계층형 질의를 모두 수행한다

순방향? 역방향?

부모 = PRIOR 자식 : 순방향
PRIOR 자식 = 부모 : 순방향
자식 = PRIOR 부모 : 역방향
PRIOR 부모 = 자식 : 역방향

---

SELF JOIN

셀프 조인이란 동일한 테이블끼리 조인하는 것을 의미하며, 계층질의가 구현되지 않았던 시절에는 이 방식으로 계층 질의를 수행했다.

---