SQLite 행번호(행넘버, row number) 조회하고 UPDATE하기

기존테이블에는 아이디와 네임, 같은 기본 정보들만 존재하는데 순번이 추가되어야 하는 상황이다.
이 순번은 서로 바뀔 수도 있기 때문에 꼭 칼럼을 추가해야 한다.
순번 칼럼을 추가하면, 기존에 있는 데이터는 순번 데이터가 NULL이 되기 때문에 기존 데이터는 등록일 기준으로 순번 데이터를 업데이트 하려고 한다.
 
SEQ_NO 칼럼을 추가 한 직후의 테이블을 조회하면 아래와 같다.

순번 칼럼을 추가 하고 테이블 조회 예시

 

1. 순번 조회하기

SQLite에는 순번을 쉽게 구할 수 있는 내장 함수를 지원한다. (3.8.3 이상)
대표적으로  RANK와 ROW_NUMBER가 있다.
RANK와 ROW_NUMBER는 사용하는 방법은 동일하지만 결과는 조금 다르다.

1) RANK()

SELECT
  RANK() OVER (ORDER BY CREATE_DATE) AS SEQ_NO,
  *
FROM MY_TABLE;

RANK() 함수 결과

 

2) ROW_NUMBER()

SELECT
    ROW_NUMBER() OVER (ORDER BY CREATE_DATE) AS SEQ_NO,
    KEY
FROM MY_TABLE;

ROW_NUMBER() 함수 결과

 
위의 결과 이미지를 통해 알 수 있듯이 RANK() 함수는 동률 처리를, 동률인 경우 같은 순위를 부여하고 그 다음 순위는 빈칸이 되어 그 다음 다음 순위는 빈칸만큼 더한 순번을 가지게 된다. 반면에 ROW_NUMBER() 함수는 동률인 경우 다음 순위에 1씩 더하여 순위를 매긴다.
 
내가 원하는 결과는 ROW_NUMBER() 함수를 써야 얻을 수 있으므로 이걸 이용해서 순번 데이터를 업데이트하자.

2. 업데이트 하기

UPDATE MY_TABLE
SET SEQ_NO = (
  SELECT SEQ_NO
  FROM (
    SELECT
      ROW_NUMBER() OVER (ORDER BY CREATE_DATE) AS SEQ_NO,
      TABLE_ID
    FROM MY_TABLE
  ) AS t
  WHERE t.TABLE_ID = MY_TABLE.TABLE_ID
);

이렇게 하면 ID를 기준으로 SEQ_NO에 순번 데이터를 업데이트할 수 있다.