MSSQL2005 row_number() 함수로 게시판 페이지 불러오기
개인적으로 MSSQL을 2005로 업그레이드 하고 나서 가장 크게 감명받은 기능이 있다면
1. 온라인인덱싱
2. 로그백업/복원
3. row_number() 함수다.
오늘은 이중 row_number()함수의 활용을 해보고자 한다.
-- 페이지 출력하기~~~~~~~~~
실제 개발에서 가장 많이 사용할 수 있는 부분은 무엇보다 게시판 출력시 페이지별로 불러오는 것이다.
기존 MSSQL에서는 게시판 페이지 불러오기란.......참..거시기 했다.
order by 와 not in 을 이용해서 매우 지저분하고 복잡한 쿼리문을 이용했었다.
하지만 row_number()를 이용하면 매우 깔끔해진다.
아래는 실제 소스를 복사한 것이다.
-- 쿼리문
declare @sql varchar(max)
declare @where varchar(max)
declare @con varchar(max)
declare @page int --찾으려는 페이지
declare @cnt int --페이지당 갯수
declare @s_num int --페이지 시작번호
declare @e_num int --페이지 종료번호
declare @table varchar(50) --사용될 테이블
declare @orderby varchar(300) --리스트의 정렬
declare @part varchar(300) --장르 또는 구분될 내용
set @sql = '';
set @where = '';
set @page = 11;
set @cnt = 200;
set @s_num = (@page*@cnt)-(@cnt-1);
set @e_num = (@page*@cnt);
set @table = 'test';
set @orderby = 'title asc';
set @part = 'name';
set @con = 'test';
set @where = ' where '+@part+'= '''+@con+''' ' ;
set @where = @where + ' and rownum between '+convert(varchar(10),@s_num)+' and '+convert(varchar(10),@e_num)+' ';
set @sql = @sql + ' select * from ('
set @sql = @sql + ' select row_number()over(partition by '+@part+' order by '+@orderby+') as rownum, * from '+@table+' '
set @sql = @sql + ' ) tmp ' + @where
print (@sql);
exec (@sql);
위 소스를 그대로 긁어서 확인 할 수도 있지만 부연설명을 하자면...
찾으려는 페이지수 와 페이지 갯수만 알게 되면 출력은 간단해진다.
먼저 데이터를 뽑으려는 테이블의 내역을 select 하는데 중요한 것은
row_number()over(partition by 파티션구분 order by 정렬구분) as rownum 의 값을 뽑아서 출력하는 것이다.
파티션은 말그대로 그 테이블 내에서 순위를 뽑아내고 싶은 기준 파티션을 말한다.
예를들어 상품정보 테이블이라면 상품의 등록날짜를 기준으로 순위를 뽑을지...이름을 기준으로 순위를 뽑을지..말이다.
정렬기준은 당연히 어떤것을 기준으로 정렬을 하는지에 대한 것이다.
(위 쿼리문은 동률없는 순위를 뽑는 방식이다. 다른 출력 방법은?
row_num() over(~~~) : 무조건 순차대로
rank() over(~~~) : 동률있으면 같은 순위로 나옴, 그리고 다음 순위는 +1 (1,2,3,3,3,4)
dense_rank() over(~~~) : 동률있으면 같은순위, 다음순위는 뛰어넘음(1,2,3,3,3,6)
ntile(숫자) over(~~~) : 숫자만큼 %별로 순위를 선정함. 해당% 이내는 같은 순위임.)
-- 특별한 조건들 뽑아내기~~~~~~~~~
예를들어 A상품을 구매한 고객 중 두번째로 많이 구매한 고객을 뽑아라?
한달의 매출 중 가장 매출액이 큰 상품을 출력하라?
한달 기간동안 하루 단위로 가장 많이 팔린 또는 적게 팔린 상품을 뽑아라?
등등 특정한 값을 출력할 때 위 함수는 큰 힘을 발휘한다. 가상테이블이나 임시로 테이블을 만들어서 저장후 사용할 필요같은 것 없이 바로 출력이 가능하다.
정말이지 이런 순위함수는 그 쓰임새가 생각할 수록 엄청나다....그만큼 생각도 많이 하지만 분명 코딩을 짧게 하는데 큰 도움을 줄 것이다.