70세 코딩

일반적으로 ROW_NUMBER()의 OVER 절에 다음과 같이 그룹화 시킬 컬럼을 넣어서 사용하게 된다.

SELECT 
 (ROW_NUMBER() OVER (PARTITION BY dept_code ORDER BY user_id)) as ord, * 
FROM 
 UserTable
ORDER BY 
 dept_code

그런데 만약 특정 컬럼에 대한 일련번호가 아닌 전체 row 에 대하여 처음부터 일련번호를 부여 하고자 하면 다음과 같이 

PARTITION BY 없이 ORDER BY 만 사용 하면 된다. 이때 ORDER BY 에 사용하는 컬럼값이 데이터에 중복되어 있어도 관계없다. 

ROW_NUMBER() 함수의 특성상 동일값 이라도 차례대로 일련번호를 부여하기 때문 이다.

SELECT 
 (ROW_NUMBER() OVER (ORDER BY user_id)) as ord, * 
FROM 
 UserTable
ORDER BY 
 ord

SQL서버에서 Linked Server로 연결된 Oracle서버에 곧바로 DML 을 던지면 오류가 발생 한다.

 

다음과 같이 해야 오류가 발생 하지 않는다.

INSERT openquery(linked1, 'select num, names from Temp_1') VALUES (100, '박찬호')

마찬가지로 UPDATE, DELETE도 다음과 같이 할 수 있다.

UPDATE openquery(linked1, 'select num from Temp_1 where num = 100')  SET num = 200

DELETE FROM openquery(linked1, 'select num from Temp_1 where num = 200')

SELECT 로 데이터를 가져 오면서 전체 또는 원하는 그룹단위로 일련번호를 부여 해야 할 때

ROW_NUMBER() 를 사용하면 쉽게 할 수 있다.

Table를 마이그레이션 할 때, 레거시 에서는 PK를 복잡하게 부여하였는데 새로운 시스템 에서는

일련번호로 단순화 한다거나 할 때 사용하면 유용 하다.

예를 들어 emp_code, request_ym 로 GROUP 하고 relation 으로 정렬하여 일련번호를 부여하고자 한다면

다음과 같이 할 수 있다.

ROW_NUMBER() OVER (PARTITION BY emp_code, request_ym ORDER BY relation) as seq_no

만일 역순으로 일련번호를 부여 하고자 하면 ORDER BY relation DESC 처럼 하면 된다.

* ORDER BY 에 의해 동순 인 경우에도 ROW_NUMBER()는 1, 2, 3, ... 과 같이 하는데 만약 동순 일때 동일한 순위번호를

  부여하고자 하면 ROW_NUMBER() 대신에 DENSE_RANK()를 사용하면 된다.

  또한 DENSE_RANK()는 동순에 대하여 동일한 순위번호를 매기고 나서 그 다음 일련번호를 부여(즉, 1, 2, 2, 2, 3, 4, ...