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

SQL별 숫자 극한값 구하기.

주로 ORDER BY 에서 최우선순위를 주거나 아예 밀 때 요긴할 SQL 구문이니
잘 쓰도록.

MySQL(MariaDB)

출처: StackOverflow – In SQL how do I get the maximum value for an integer?
MIN은 현재 방법 없다고 한다. 그냥 타입에 알맞는 상수 써라.

SELECT ~0 as max_bigint_unsigned
,      ~0 >> 32 as max_int_unsigned
,      ~0 >> 40 as max_mediumint_unsigned
,      ~0 >> 48 as max_smallint_unsigned
,      ~0 >> 56 as max_tinyint_unsigned
,      ~0 >> 1  as max_bigint_signed
,      ~0 >> 33 as max_int_signed
,      ~0 >> 41 as max_mediumint_signed
,      ~0 >> 49 as max_smallint_signed
,      ~0 >> 57 as max_tinyint_signed
\G

*************************** 1. row ***************************
   max_bigint_unsigned: 18446744073709551615
      max_int_unsigned: 4294967295
max_mediumint_unsigned: 16777215
 max_smallint_unsigned: 65535
  max_tinyint_unsigned: 255
     max_bigint_signed: 9223372036854775807
        max_int_signed: 2147483647
  max_mediumint_signed: 8388607
   max_smallint_signed: 32767
    max_tinyint_signed: 127
1 row in set (0.00 sec)

MSSQL

방법 없다. 아래 링크가서 타입에 알맞은 상수 갖다 써라. 아니면 아래 상수를 스칼라 함수 꾸며 써도 된다. 아니면 “닷넷 어셈블리” 간단히 만들어서 써도 된다.
MSDN – int, bigint, smallint, and tinyint (Transact-SQL)

MSSQL 2012 이상

Maximum Limit Value For Integer Data Type in SQL Server 2012

Select Power(cast(2 as varchar),(64) -1) as 'Bigint max range'  from sys.types Where name = 'BIGInt' 
Select Power(cast(2 as varchar),(32) -1) as 'int max range'  from sys.types Where name = 'Int' 
Select Power(cast(2 as varchar),(16) -1) as 'Smallint max range'  from sys.types Where name = 'SMALLInt' 

ORACLE

역시 방법 없다. 오라클은 여타 SQL과는 달리 INTEGER가 NUMERIC(11)의 ALIAS다.
일단 알려진 방법은 아래 함수가 있다. INTEGER MAX 구하는 함수이다.
Is there a way to get the PL/SQL maximum pls_integer?

CREATE FUNCTION MAX_PLS_INTEGER_SIZE RETURN PLS_INTEGER AS 
  p PLS_INTEGER;
  b NUMBER;
BEGIN
  b := 0;
  WHILE TRUE LOOP
    BEGIN
      p := POWER(2, b)-1;

      b := b + 1;
      EXCEPTION WHEN OTHERS THEN
        EXIT;
    END; 
  END LOOP;

  RETURN p;
end;
\
SELECT MAX_PLS_INTEGER_SIZE FROM DUAL;

PostgreSQL

얘도 오라클과 마찬가지로 NUMERIC이 모든 숫자 타입을 담당한다.
상수 값은 공식 문서 참조.
다행히도 간단한 방법이 있는데, pg_column_size라는 함수가 있다.
여기에 원하는 타입과 타입에 맞는 바이트 길이를(예: int는 4) 대입하여 처리하면 된다.
Postgres maximum value for BIGINT

select  (2^(8*pg_column_size(1::bigint)-2))::bigint << 1 as min_bigint_value;
select  -(((2^(8*pg_column_size(1::bigint)-2))::bigint << 1)+1) as max_bigint_value;

일단 많이 쓰는 SQL 기반으로 작성하였다. 추가할 거 있다면 서로 공유하면 웃음꽃이 피어날 것이다.

composite / 2016년 5월 11일 / Piss Development / 0 Comments

SQL을 갖고놀자! SQLFiddle!

자바스크립트 테스트라던가 갖고논다면 아마 JSFiddle 이나 JSBin 을 많이 이용하실 겁니다.

또한 자바나 C# 등의 서버단 언어를 갖고 놀거나 테스트하기 위해 IDEOne 사이트도 이용하실 겁니다.

그렇다면, DB를 갖고 노는 사이트는 있습니까? 있습니다!

바로 SQLFiddle 입니다!

http://sqlfiddle.com/

여기서는 여러분이 DB의 CRUD를 테스트해주는 재대로된 환경을 제공합니다!

<

p style=”text-align: center; clear: none; float: none;”>

광고는 애교로 봐줍시다. 돈들여서 AWS 사고 서버 들이고 호스팅 하고 하는데.. 가치가 있다면 Donate도 할수도 있죠.

어쨌든, 이녀석은 왼쪽에는 스키마 DDL을 작성하여, 옆에서 CRUD 작업을 할 수 있는 환경을 제공합니다.

제공하는 DB 종류는 MySQL을 비롯하여 오라클 11g, MSSQL 2008 및 2012, PostgreSQL, SQLite 등을 제공합니다!

만약 당신의 업무 중에 중요한 데이터를 변경할 일이 생기셨습니까?

그러면 먼저 왼쪽 편집기에 CREATE TABLE 로 테이블을 만들고, INSERT 문으로 대표적인 몇 개의 데이터를 넣으세요!

그런 다음, 오른쪽 편집기에서 CRUD 작업을 실시하세요.

많은 작업을 하기에는 무리기 때문에 샘플로 몇개 뽑아서 테스트하기에 적합합니다.

만약 새로운 질의문 테스트를 원하신다면, 주저 말고 여기서 작업하세요.

여기서는 실행 계획(Execution Plan)과 오류도 자세하게 볼 수 있습니다.

이제 DB 관리 및 개발에 대한 부담. 여기서 확 줄이세요. 여러분에 컴터에 무겁게 DB깔 필요도 없이 어떤 CRUD 작업은 여기에 맡기세요.

대세는 클라우드라는게 어떤건지 이제 감이 오십니까? 이것이 바로 아이디어이고 혁명입니다.

composite / 2012년 12월 12일 / 미분류 / 0 Comments