[SQL] Top-N 쿼리, 셀프 조인, 계층 쿼리,

1. TOP-N 쿼리

 

이 쿼리를 사용하면 특정 기준에 따라 상위 N개의 데이터를 추출할 수 있다. 예를 들어 매출 순위 상위 10개 제품, 최근 가입한 회원 5명 등을 쉽게 찾을 수 있다. 이런 TOP-N의 데이터를 추출하기 위한 몇가지 방식들이 있다. 

 

1) ROWNUM

오라클의 ROWNUM은 수도 코드로, 실제로는 존재하지 않은 가짜 컬럼이라고 할 수 있다. 아래 코드처럼 select 절에 rownum을 추가하면 순번을 같이 출력할 수 있다. rownum은 where rownum=5 처럼 5위만 출력할 수는 없고, 항상 < 조건이나, <= 조건을 사용해 조절할 수는 있다. 

 

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

 

 

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

 

 

만약에 국어, 영어, 수학 점수 내림차순 순으로 정렬 후 순번을 매고 싶다면 다음과 같이 코드를 작성하면 된다. 

 

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

 

 

만약 아래와 같이 코드를 작성하면 order by 절이 where 절보다 나중에 수행되기 때문에 아래 쿼리는 데이터를 랜덤으로 5개 뽑아낸 뒤 그걸 가지고 순위를 매기는 것이 된다. 

 

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

 

 

 

2) 윈도우 함수의 순위 함수

윈도우 함수의 순위 함수를 이용해서 Top-N 쿼리를 작성할 수도 있다. 

 

<ROW_NUMBER>

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

 

윈도우 함수의 rank 함수를 사용해서도 Top-N 쿼리를 작성할 수 있다. 

 

<RANK>

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

 

DENSE_RANK 함수도 가능

 

<DENSE_RANK>

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

 

 

2. 셀프 조인

 

셀프 조인(Self Join)은 하나의 테이블 내에서 서로 관련된 데이터를 조회하기 위해 사용하는 SQL 기법.

즉, 하나의 테이블을 마치 두 개의 테이블인 것처럼 취급하여 조인을 수행하는 것이다. 이를 통해 계층적 구조나 순차적 관계를 가진 데이터를 효과적으로 조회할 수 있다.

셀프 조인을 사용할 때는 반드시 테이블에 별칭(Alias)을 부여해야 한다. 이를 통해 마치 두 개의 테이블인 것처럼 조인을 수행할 수 있다.

 

계층적 구조 예시 ) 제품명 > 제품 대분류 카테고리 > 중분류 카테고리 > 소분류 카테고리 처럼 계층 구조를 가진 데이터를 효과적으로 조회할 수 있다. 

category 테이블

 

셀프조인으로 대분류와 중분류 카테고리를 확인해보자. 

아래 쿼리로 확인할 수 있다. 

SELECT A.카테고리명,
	   A.제품명,
       B.카테고리명,
       B.제품명
FROM CATEGORY A,
	 CATEGORY B
WHERE A.제품명=B.대분류
AND A.카테고리명='대'

 

 

 

 

3. 계층 쿼리

테이블에서 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리를 이용해서 데이터를 출력할 수 있다.

 

계층 쿼리를 사용하기 위해서는 START WITH 절과 CONNECT BY 절을 사용한다.

• START WITH 절: 계층 구조의 최상위 데이터를 지정합니다.
• CONNECT BY 절: 상위 데이터와 하위 데이터의 관계를 정의합니다.
• LEVEL : 현재의 depth를 반환한다. 루트노드는 1이 된다.  
• SYS_COONNECT_BY_PATH(컬럼, 구분자): 루트노드부터 현재 노드까지의 경로를 출력해주는 함수.
• PRIOR : 바로 앞에 있는 부모 노드의 값을 반환.
• CONNECT_BY_ROOT : 루트노드의 주어진 컬럼 값을 반환
• CONNECT_BY_ISLEAF : 가장 하위 노드인 경우 1을 반환, 그 외에는 0을 반환

 

앞서 셀프 조인으로 작성했던 쿼리를 계층 쿼리로 변환해보겠다. 

category 테이블

SELECT LEVEL,
	SYS_CONNECT_BY_PATH('['||카테고리명||']' || 제품명, '-') AS PATH
FROM CATEGORY
START WITH 대분류 IS NULL
CONNECT BY PRIOR 제품명=대분류;

 

 

1) start with 대분류 is null

 

2) connect by prior 제품명=대분류