ROW_NUMBER() OVER (정렬 하기 싫다.)

먼저, 이 글은 오라클 뿐 만 아니라 다른 관계형 데이터베이스에도 적용 가능한 팁임을 명시한다.

오라클에는 ROWNUM이라는 가상 칼럼이 있다.
결과가 나왔을 때, 해당 행의 번호를 새기는 재미있는 칼럼이다.
하지만 주의해야 할 것은 ORDER BY에 영향을 받지 않는다는 것이다.
그런 즉슨, 지정된 테이블 내의 가상 행 순서를 반환한다는 특징을 가지고 있다.
예를 들면, 철수와 영희를 순서대로 기록한 임의의 테이블에 ROWNUM을 붙이고, 거기에 ORDER BY 붙여보자.

SELECT ROWNUM, NAME
  FROM NAMES
 ORDER BY NAME;

이렇게 호출하면, 뻔하겠지만 정렬 규칙에 의해 “영희”가 먼저 행에서 나오기 때문에, 아래와 같이 나오게 된다.

RWONUM NAME
2 영희
1 철수

만약 ORDER BY에 영향가는 행 번호를 원한다면 ROWNUM 말고 ROW_NUMBER() 윈도우 함수를 써야 한다.

SELECT ROW_NUMBER() OVER (ORDER BY NAME) ROUNUMBER, NAME
  FROM NAMES
 ORDER BY NAME;

이렇게 호출하면, 정렬에 따른 행 번호를 매기게 되고, 그 결과 또한 원하는 대로 나올 것이다.

RWONUM NAME
1 영희
2 철수

여기까지가 오라클의 행번호 칼럼이다. 알다시피 다른SQL에는 ROWNUM에 준하는 기능이 없다.
그래서 등록한 순으로 행 번호를 매길 때는 어떻게 해야 하나 고민할 때가 생길 것이다.
오늘 그걸 소개하기 위해 이 글을 싸지른다.

대상: 완전 구버전이 아닌 왠만한 관계형 데이터베이스
요약: ROW_NUMBER() OVER (ORDER BY [LITERAL BUT NOT NUMBER])

ORACLE

ROWNUM이 있는데 굳이 왜냐고 물어보는 당신에게 굳이 이 팁을 바치도록 하겠다.

SELECT ROW_NUMBER() OVER (ORDER BY NULL) RN, * FROM SOME_TABLE

MSSQL

MSSQL의 경우 리터럴 표현이 조금 빡세지만 리터럴만 표현 가능하다면 충분히 가능하다.

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN, * FROM SOME_TABLE

MySQL

MySQL의 윈도우 함수 기능은 8.0 부터 등장했다. 당연히 대부분 시스템은 5.x일 터… 에휴…

SELECT 
    @i:[email protected]+1 AS rownum, 
    t.*
FROM 
    SOME_TABLE AS t,
    (SELECT @i:=0) AS foo

그럼 8.0 미만 환경에서 rownum 정렬은? 안타깝게도 서브쿼리 써야 한다…
MySQL 8.0 이상은 MSSQL과 동일하다.

PostgreSQL

허전할 정도로 심플하긴 하다.

SELECT ROW_NUMBER() OVER () RN, * FROM SOME_TABLE

DB2

PostgreSQL 방식과 동일하다.

SELECT ROW_NUMBER() OVER () RN, * FROM SOME_TABLE

왜 이 팁을 날리는가?

Cartesian Product 이라고 들어봤는가?
우리말로 카테시안 곱이라던가, 데카르트 이름을 딴 데카르트 곱 등으로 불린다. 근데 왜 아무도 곱집합이라고 안하는지는 모르겠다.
집합 이론에 기본적으로 있는 놈인데 말이다. 하긴 중딩도 고딩도 합집합 교집합 여집합 등을 배우지 곱집합은 안배웠으니 몇몇은 모를 수도 있겠다.
하지만 확률 이론의 경우의 수 이론을 배웠다면 사실 둘이 쌤쌤이다.

어쨌든, SQL에서는 곱집합에 의해서 조건 없는 JOIN 이나 CROSS JOIN 을 명시하는 등의 행위 질의를 던지면, 테이블의 각 행들에게 빠짐없이 하나씩 모두에게 동등하게 부여해 행 개수가 늘어나는 기현상을 일으킨다.
당연한 거지만, 예를 들어 5개의 행 A와 8개의 행 B를 조건 없이 조인하면, 5×8 총 40개의 행을 생성시켜 버린다.

하지만 나에게는 그냥 각 행마다 순서대로 1:1 조인을 하고 싶었다. 오라클에서.
그래서 선택한 게 ROWNUM 가상 칼럼을 조인하는 방식이었다.

WITH A AS (
  SELECT 1 A FROM DUAL UNION ALL
  SELECT 2 A FROM DUAL UNION ALL
  SELECT 3 A FROM DUAL UNION ALL
  SELECT 4 A FROM DUAL UNION ALL
  SELECT 5 A FROM DUAL),
B AS (
  SELECT 'A' B FROM DUAL UNION ALL
  SELECT 'B' B FROM DUAL UNION ALL
  SELECT 'C' B FROM DUAL UNION ALL
  SELECT 'D' B FROM DUAL UNION ALL
  SELECT 'E' B FROM DUAL)
SELECT * FROM (SELECT ROWNUM RN, A FROM A) A, (SELECT ROWNUM RN, B FROM B) B WHERE A.RN = B.RN;

실행 결과는 여기서 보면 된다.

그렇다. 이게 바로 글을 싸지른 원인이었던 것이다.
끗.

composite / 2018년 6월 7일 / Piss Development / 0 Comments