개요
데이터베이스에서 결과 셋의 행대로 번호를 매겨야 하는데 다음과 같은 에러가 출력되었다.
select row_number() over(order by 1), username, address from testTable
- 에러 메시지
Msg 5308, Level 16, State 1, Line 22
기간 이동 함수, 집계 및 NEXT VALUE FOR 함수는 ORDER BY 절 식으로 정수 인덱스를 지원하지 않습니다.
이 에러가 무슨 뜻일까?
궁금증을 해결할 겸 ROW_NUMBER에 대해 알아보기로 했다.
ROW_NUMBER
ROW_NUMBER 이란?
결과 집합의 출력 번호를 지정한다.
즉, 모든 행의 번호를 순차적으로 지정하는 기능을 한다.
ROW_NUMBER은 번호를 매기는 점에 있어서는 RANK와 유사하다.
ROW_NUMBER VS RANK (ROW_NUMBER과 RANK의 차이점)
ROW_NUMBER과 RANK의 공통점은 번호를 매긴다는 것이다.
완전히 같은 기능을 하는 것은 아니고 차이점이 존재한다.
RANK는 순위가 같을 경우에 대해 동일 값을 제공하지만,
ROW_NUMBER는 순위가 같을 경우에도 서로 다른 값(고윳값)을 제공한다.
구문
ROW_NUMBER ( )
OVER ( PARTITION BY 값 ORDER BY 값 )- PARTITION BY 절은 조회된 결과 집합을 그룹별로 분할한다.
만약 PARTITION BY를 지정하지 않은 경우는 쿼리 결과 집합의 모든 행을 단일 그룹으로 취급한다. - ORDER BY 절은 행의 고유 ROW_NUMBER가 할당되는 순서를 결정한다.
ROW_NUMBER 를 이용한 번호 매기기 예제
테스트를 위한 테이블
select * from testTable
특정 컬럼의 순서에 따라 번호를 매길 경우
특정 컬럼의 순서에 따라 번호를 매기려 할 때에는
평소의 SQL 쿼리문에서 사용하던 ORDER BY 절의 양식과 동일하다.
ORDER BY 절에 컬럼의 이름, 오름차순과 내림차순 여부를 설정하면 된다.
예를 들어, 유저 이름별로 내림차순 순서대로 번호를 매겨야 한다면,
ORDER BY 절에 컬럼(username)을 기입하고 내림차순(DESC)을 설정해 주면 된다.
select
row_number() over(order by username desc) as 'order',
username,
address
from
testTable
컬럼의 순서 없이 출력되는 결과 셋을 순차적으로 번호를 매길 경우
특정 컬럼의 순서와 상관없이 SELECT로 조회되는 결과 그대로를 번호를 매겨야 할 때가 있다.
그럴 때에는 컬럼 대신 다른 일정한 값을 넣어주면 된다.
이 말을 풀어 말하자면,
ORDER BY 절을 기준으로 순서를 매기지만 ORDER BY 절의 주어진 값이 모두 일정하다면
조회되는 결과값 그대로를 순차적으로 번호를 매길 수 있다는 소리이다.
동일한 값을 주는 방법은 변수를 이용하면 된다.
/* 동일한 값으로 번호를 매기기 위한 변수 선언 및 값 초기화
동일한 값을 필요로 하기 때문에 꼭 정수형의 숫자일 필요는 없다.
마찬 가지로 아래와 같이 선언해도 번호는 동일하게 지정된다.
declare @rowOrderNumber int = 1 -> 1, 2, 3, 4, ...
declare @rowOrderNumber nvarchar(1) = '1' -> 1, 2, 3, 4, ...
*/
declare @rowOrderNumber int = 1
select
row_number() over(order by @rowOrderNumber) as 'order',
username,
address
from
testTable
특정 번호대부터 순서를 매겨야 할 경우
ROW_NUMBER의 계산된 값에 특정 번호대를 더해주면 된다.
예를 들어, 순서를 천 번대부터 시작하게 하려면 연산된 값 뒤에 +1000을 해주면 된다.
- ROW_NUMBER() OVER(ORDER BY 값) + 1000
declare @rowOrderNumber nvarchar(8) = 'eunbyeol'
select
row_number() over(order by @rowOrderNumber) + 1000 as 'order',
username,
address
from
testTable
특정 그룹별로 번호를 매겨야 할 경우
PARTITION BY 절을 이용하면 된다.
PARTITION BY 절은 쉽게 말하면 우리가 알고 있는 SQL 쿼리문의 GROUP BY와 같다.
예를 들어, 지역별로 번호를 부여해야 한다면 PARTITION BY 절에 그룹을 형성할 컬럼명을 넣어주면 된다.
- ROW_NUMBER() OVER(PARTITION BY 지역컬럼 ORDER BY 값)
declare @rowOrderNumber nvarchar(8) = 'eunbyeol'
select
row_number() over(partition by address order by @rowOrderNumber) as 'order',
username,
address
from
testTable
( 참고 )
지역 그룹별로 번호가 따로 부여되었다.
테스트 테이블에서는 값이 두 개만 존재하므로 둘 다 모두 order 컬럼의 값이 시작값인 1로 출력되었다.
개요에서의 에러 해결 방법
말이 어려웠을 뿐이지, 사실 에러 메시지에 모든 답이 있다.
'ORDER BY 절 식으로 정수 인덱스를 지원하지 않습니다.'
내가 작성한 쿼리문은 row_number() over(order by 1) 이다.
ORDER BY 절에 정수 인덱스를 지원하지 않으므로 변수를 하나 선언해 주어서 넣어주면 된다.
변수의 타입은 상관없다.
이유는 위에서 설명한 '컬럼의 순서 없이 출력되는 결과 셋을 순차적으로 번호를 매길 경우' 챕터에서 확인할 수 있다.
/* 오류가 발생한 구문 */
select
row_number() over(order by 1) as 'order',
username,
address
from
testTable
/* 올바르게 수정한 구문 */
declare @rowOrderNumber nvarchar(8) = 'eunbyeol'
select
row_number() over(order by @rowOrderNumber) as 'order',
username,
address
from
testTable