오라클 페이징 쿼리, 오라클 paging 방법 - 삽질중인 개발자

- 오라클 페이징 쿼리 정리 -

 

쿼리를 작성하다 보면 SELECT 된 결과에서 순번을 매겨야 하는 경우가 있다. ex) paging

이러한 경우 오라클에서는 ROWNUM , ROW_NUMBER() OVER() , RANK OVER(), DENSE_RANK() OVER 등을 지원하고 있다.

 

 

1. 테이블 생성 정보와 데이터

--사용 중인 DB는 oracle 11g 
-- 테스트용 쿼리 문
create table tbl_student_info(
    seq number primary key,
    height number,
    weight number,
    name varchar2(30),
    etc varchar2(500)

);

--인서트문
insert into tbl_student_info(seq, height,weight, name, etc) values(1,170,70,'홍길동','금수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(2,180,60,'아무개','흙수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(3,160,80,'개똥이','금수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(4,180,80,'아저씨','흙수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(5,180,75,'원빈','다이아수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(6,163,72,'현빈','은수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(7,171,73,'박원승','동수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(8,175,65,'홍길순','동수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(9,172,66,'박내림','동수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(10,181,67,'박순동','금수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(11,163,70,'박길동','다이아수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(12,165,70,'고길동','수저없음');
insert into tbl_student_info(seq, height,weight, name, etc) values(13,190,90,'이길','흙수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(14,159,57,'홍동','흙수저');
insert into tbl_student_info(seq, height,weight, name, etc) values(15,169,70,'홍순희','수저없음');
commit;

 

 

요구사항 : 키가 큰 순서대로 같은 키면 이름이 빠른 순서로 순번이 필요

 

1. ROWNUM 을 이용한 순번 매기는 방법

ROWNUM 은 오라클에서 지원하는 가상 컬럼으로 쿼리의 결과에서 1부터 하나씩 증가해서 만들어지는 컬럼

 

-- 기본 구문
SELECT 컬럼1, 컬럼2, 컬럼3, ROWNUM AS 별칭 
FROM 테이블 [ORDER BY 정렬 컬럼 DESC[ASC]]

 

사용할 때 주의사항

1. * 을 사용하면 안된다. 테이블.* 과 함께 사용하던가 모든 컬럼을 다 써놔야 한다. ex) select 컬럼1,컬럼2,컬럼3 ,rownum from 테이블

2. ROWNUM 은 ORDER BY 전에 만들어진다. 그래서 ORDER BY 절이 있으면 ROWNUM까지 정렬이 된다.

​

​

EX) 다음과 같은 쿼리를 사용하게 되면 결과가 아래와 같이 요구 사항과는 다른 결과가 나온다.

--예를 들면 
SELECT a.*, ROWNUM FROM tbl_student_info a ORDER BY height DESC,name ASC;   --키크면서 이름이 빠른 순

 

따라서 다음과 같이 ORDER BY 된 테이블을 한번 감싸서 사용해야지 원하는 대로 결과가 나온다.

SELECT a.*, ROWNUM as rnum FROM (
        SELECT * FROM tbl_student_info ORDER BY height DESC,name ASC
) a;   --키크면서 이름이 빠른 순

 

ROWNUM을 이용한 페이징 방법

--이런 식으로 해도 괜찮지만
SELECT a.*, ROWNUM as rnum FROM (
      SELECT * FROM tbl_student_info ORDER BY height DESC, name ASC
      ) a
 WHERE ROWNUM >=1 AND ROWNUM <=3;   --키크면서 이름이 빠른 순


-- 한번 더 감싸서 하는걸 추천
SELECT * FROM (
        SELECT a.*, ROWNUM as rnum FROM (
               SELECT * FROM tbl_student_info ORDER BY height DESC,name ASC
               )
        a)  WHERE rnum >= 1 and rnum <=3       -- 1번부터 3번까지 조회

 

 

2. ROW_NUMBER() OVER() 을 이용한 순번 매기는 방법

-- 기본 사용 법
SELECT 컬럼1, 컬럼2 ,ROW_NUMBER() OVER([PARTITION BY 그룹핑 컬럼] [ORDER BY 정렬 컬럼 DESC]) AS 별칭 
FROM 테이블

 

사용할 때 주의사항

1. * 을 사용하면 안된다. 테이블.* 과 함께 사용하던가 모든 컬럼을 다 써놔야 한다. ex) select 컬럼1,컬럼2,컬럼3 ,ROW_NUMBER() OVER(ORDER BY 컬럼) from 테이블

 

SELECT a.*,ROW_NUMBER() OVER(ORDER BY height DESC,name ASC) AS rnum FROM tbl_student_info a; -- 키크면서 이름이 빠른 순

OVER 안에 ORDER BY 절에 때문에 정렬된 값에서 등수가 정해진다.

 

 

위에 ROWNUM 과 같은 결과가 나옴.

​

ROW_NUMBER() OVER() 페이징 방법

SELECT * FROM (
           SELECT a.*,ROW_NUMBER() OVER(ORDER BY height DESC,name ASC) AS rnum FROM tbl_student_info a
        ) WHERE rnum >=1 and rnum<=3; -- 키크면서 이름이 빠른 1~3등까지

ROWNUM 보다 편하다.

​

추가 요구 사항 : 수저 별로 키큰 사람 순 이름 순으로 순위 매기기

SELECT a.*,ROW_NUMBER() OVER(PARTITION BY etc ORDER BY height DESC,name ASC) AS rnum FROM tbl_student_info a; --수저별로 키큰사람, 이름 순으로 정렬

이런식으로 PARTITION BY를 사용할 수도 있다.

수저 별 키,이름 순위

 

3. RANK() OVER()

​

ROWNUM 이나 ROW_NUMBER() OVER() 과는 다르게 같은 조건이면 같은 순위를 매긴다.

​

사용할 때 주의사항

1. * 을 사용하면 안된다. 테이블.* 과 함께 사용하던가 모든 컬럼을 다 써놔야 한다. ex) select 컬럼1,컬럼2,컬럼3,RANK() OVER(ORDER BY 컬럼) from 테이블

 

SELECT 컬럼1, 컬럼2, RANK() OVER([PARTITION BY 그룹핑 컬럼] [ORDER BY 정렬 컬럼 DESC]) AS 별칭 
FROM 대상 테이블

OVER 안에 ORDER BY 절에 때문에 정렬된 값에서 등수가 정해진다. 같은 순위면 같은 등수로 나온다 그 다음 순번은 같은 순위 개수만큼 더한 후 나옴. ex) 키가 180인 사람이 3명이면 180 인 사람은 1 이지만 바로 다음에 나오는 179인 사람은 4 가 된다.

 

SELECT a.*, RANK() OVER(ORDER BY height DESC) AS rnum FROM tbl_student_info a; --키 순으로 정렬

키가 180인 사람은 전부 같은 순위

* RANK() OVER() 을 이용해서 마찬가지로 페이징을 할 수 있지만 RANK() OVER()은 동일 조건이면 같은 순번으로 나오기에 조건에 따라 조회되는 개수가 달라질 수 있다.

 

 

SELECT * FROM
    (SELECT a.*, RANK() OVER(ORDER BY height DESC) AS rnum FROM tbl_student_info a)
WHERE rnum >=1 and rnum<=4;   --RANK() OVER()을 이용한 페이징

3개를 원했지만 5개나 나온 모습.

4번이 없어서 안나온다.

 

 

4. DENSE_RANK() OVER()

​

RANK() OVER() 과는 비슷하게 동일 조건이면 같은 순번으로 번호가 매겨지는데 같은 조건이면 같은 등수로 나오고 그 다음 조건도 바로 다음 순번으로 나온다. ex) 키가 180인 사람이 3명이면 180 인 사람은 1 이지만 바로 다음에 나오는 179인 사람은 2 가 된다.

​

사용할 때 주의사항

1. * 을 사용하면 안된다. 테이블.* 과 함께 사용하던가 모든 컬럼을 다 써놔야 한다. ex) select 컬럼1,컬럼2,컬럼3,RANK() OVER(ORDER BY 컬럼) from 테이블

 

SELECT 컬럼1, 컬럼2, 컬럼3, DENSE_RANK() OVER ([PARTITION BY 그룹핑 컬럼] [ORDER BY 정렬컬럼 DESC]) AS 별명 
FROM 테이블;

 

SELECT a.*, DENSE_RANK() OVER(ORDER BY a.height DESC) AS rnum FROM tbl_student_info a; -- 키순으로 순번 정하기

 

180 다음인 175는 4번으로 나온다.

페이징 방법은 다른 것과 마찬가지.

SELECT * FROM
    (SELECT a.*, DENSE_RANK() OVER(ORDER BY a.height DESC) AS rnum FROM tbl_student_info a)
WHERE rnum >=1 and rnum<=4 ;

 

4번이 있어서 조회가 된다.

 

 

이상 자주 쓰는 오라클 페이징 방법에 대해서 정리한 글 입니다.

이 외에도 괜찮은 방법이 있으면 댓글로 알려주세요