[Database] TOP N QUERY, 계층형 질의

TOP N QUERY

• 페이징 처리를 효과적으로 수행하기 위해 사용한다.
• 전체 결과에서 특정 N개를 추출함
  • ex 성적 상위자 3명

TOP-N 행 추출 방법

1. ROWNUM
2. RANK
3. FETCH
4. TOP N(SQL SERVER)

ROWNUM

• 출력된 데이터 기준으로 행 번호를 부여하는 함수다.
• 절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없다. (= 연산이 불가능하다.)
• 첫 번째 행이 증가한 이후 할당 되므로 > 연산은 사용 불가능하다.

1이 먼저 할당 되고 나서 다음 숫자들이 할당 되기 때문에 무조건 rownum에 1이 할당이 먼저 돼야한다.

 

따라서 row num > 1 의 연산인 경우엔 1이 할당이 안 되기 때문에 오류가 나고

 

<= 5 연산의 경우엔 1~5까지 출력하니 1이 할당 되서 오류가 나지 않고 출력되는 것을 볼 수 있다.

 

다음과 같이 where 절에 조건을 걸면 상위 5명의 급여를 추출할 수 없다.

 

이유는 sql 실행 순서를 확인하면 된다

 

where이 order by보다 먼저 실행되기에 이런 일이 발생한다.

 

 

다음과 같이 from 절에 인라인뷰를 통해 먼저 정렬을 시켜놓고 그 다음 rownum을 추출하면 된다.

 

RANK

FETCH 절

• 출력될 행의 수를 제한하는 절
• order by 절 뒤에 사용한다. 내부 파싱 순서도 order by 뒤다.

1. offset: 건너뛸 행의 수
2. N: 출력할 행의 수
3. first: offset을 쓰지 않았을 때 처음부터 N 행을 출력하라는 명령
4. next: offset을 사용했을 때 그 다음 N개 를 출력하라는 명령이다.
5. row/rows: 행의 수에 따라 하나일 경우 단수, 여러 값이면 복수형

offset은 없고 그냥 처음부터 5개의 행

fetch first 5 rows only로 해결했다.

 

4~5번째 행을 출력해볼 것이다.

 

offset은 3을 설정해 주면 3까지 건너뛸 것이고

 

이후 next를 사용해주면 되지만 first 나 next나 효과는 똑같기에 크게 구분하지 않는다고 한다.

 

SQL 서버에서의 TOP N QUERY

sql 서버에서는 다음과 같이 단순하게 select 절에 top n을 적어주면 된다.

 

top 2 with ties를 사용하면 동순위도 모두 출력한다.

 

 

계층형 질의

• 하나의 테이블 내 각 행끼리 관계를 가질 때, 연결 고리를 통해 행과 행 사이의 계층을 표현하는 기법이다.
  • ex: dept2 에서의 부서별 상하관계
• PRIOR 의 위치에 따라 연결하는 데이터가 달라진다.

pdept는 상위 부서 코드를 의미한다.

 

시작 조건은 pdept IS NULL 즉 사장실이 될 것이고

 

연결 조건은 decode 즉 선행의 부서 번호가 = pdept 상위 부서번호와 같은 것이 되겠다.

 

연결 조건 추가하기

다음과 같이 connect by 절에 연결 조건을 추가할 수 있다.

 

connect by는 하위 층계의 조건을 확인하는 절이다.

 

즉 connect by에 적힌 모든 조건을 만족해야만 하위 층으로 인정하는 것이기에

 

다음과 같이 서울지사인 행만 출력된 것을 볼 수 있다.

 

where 에  = '서울지사'로 조건을 걸면 사장실만 사라지고 나올 것이다.

 

계층형 질의 가상 컬럼

1. LEVEL: 각 DEPTH를 표현한다.
2. CONNECT_BY_ISLEAF: LEAF NODE 즉 최하위 노드 인지 아닌지를 출력한다. (참: 1, 거짓: 0)

계층형 질의 가상 함수

1. CONNECT_BY_ROOT 컬럼명: 루트 노드에 해당하는 행행값을 반환한다.
2. SYS_CONNECT_BY_PATH(컬럼, 구분자): 이어지는 경로를 출력한다.
3. ORDER SIBLINGS BY 컬럼: 같은 LEVEL일 경우 정렬을 수행한다.

 

 

참고 영상 및 이미지 출처: https://www.youtube.com/watch?v=hLvv0GN0rT8&t=1168s