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

오라클 12C의 유용한 배열 처리기능

현재 내가 있는 프로젝트는 오라클 12c를 쓰고 있다.
하지만 치매걸린 DBA는 오라클 8i 시절의 기술을 요구한다.
예를 들면, ANSI JOIN 쓰지 말고 오라클 전용 JOIN 문 작성, 가능한 단일 쿼리로 모두 해결하라 등…
오라클이 그렇게 ANSI JOIN 써라 써라 해도 치매걸린 새끼들은 한국 IT의 하느님 오라클 말도 씹고 그러는데 뭐.

이럴 거면 시발 8i를 깔지 뭐하러 비싸게 12c 구축하고 지랄이야.
하긴 10g 이하는 더이상 판매도 하지 않는데 뭐…

뭐 다른 건 모르겠고, 한가지 국내 오라클 개발자들이 구미가 당길만한 새로운 기능을 소개하고자 글을 쓴다.
12c 나온지 좀 됐긴 하지만, 12c 구축한 곳 치고는 12c의 혜택을 보는 프로젝트는… 절래절래 할 것이다.
당장 내가 소속된 프로젝트부터 2000년대부터 치매걸린 아키새끼들이 프로젝트 설계했는데 뭘 더 바라겠냐만은…

본문으로 들어가서, 오라클의 SQL에서는 TABLE이라는 함수가 있다.
다들 알다시피, 테이블 함수를 테이블로 이끌기 위해 많이들 쓸 것이다.

SELECT * FROM TABLE(MY_TABLE_FUNC('FOO'))

하지만 그 뿐. 한계가 많았는데, 먼저 배열 타입(TABLE OF, VARRAY()) 조차도 지원 안한다. 테이블 함수와 외부 테이블 등을 지원한다.
예를 들면, 11g 까지는 이렇게 쿼리 짜면 안돌아간다.

CREATE OR REPLACE TYPE emp_typ IS OBJECT (
   emp_name VARCHAR2(16);
   emp_rank NUMBER(1);
);
CREATE OR REPLACE TYPE emp_arr IS TABLE OF my_texts;
/
DECLARE

  my_emp emp_arr;

BEGIN

  SELECT emp_name, emp_rank
  BULK COLLECT INTO my_emp
  FROM emp
  WHERE emp_rank < 10;

  FOR my_row IN (
    SELECT * FROM TABLE(my_emp)
  ) LOOP

    DBMS_OUTPUT.PUT_LINE(my_row.emp_name);

  END LOOP;

END;

테스트는 안해봤지만, 문법에 문제가 없다 해도 “cannot access rows from a non-nested table” 오류가 뜰 것이다.
이제 12c부터는 배열이 지원된다. CREATE TYPE 문을 통해 선언한 배열은 당연히 지원되고, 심지어 CREATE PACKAGE 안에 있는 배열까지 지원한다.
백문이 불여일견, 예제를 보자.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_row IS RECORD (
    empno NUMBER(4),
    ename VARCHAR2(10)
  );

  TYPE t_tab IS TABLE OF t_row
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1 (l_tab1 test_api.t_tab);
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 (l_tab1 test_api.t_tab) IS
  BEGIN
    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/


declare

tt test_api.t_tab;

begin

select 1,'a'
bulk collect into tt
from dual;

test_api.test1(tt);

end;

안타깝게도 SQLFIDDLE은 아직 오라클 11g라 테스트를 돌려볼 수 없다.
대신 오라클에서 교육 용도로 무료로 오라클 실행환경을 제공하는 Oracle Live SQL 에서 테스트 가능하다.
어찌저찌 12c에서 실행하면 잘 되는데, TABLE(l_tab1) 부분을 주목하자.
엄연히 패키지 내에 선언한 배열이고, 그 배열은 심지어 RECORD 형식이다.
보통 RECORD 형식은 PL/SQL 전용 문이기 때문에 SQL 쿼리문에서는 절대 안돌아가는 게 정석이다. 11g까지는.
하지만 패키지에 한해 RECORD 형식도 SELECT 문 같은 쿼리문이 지원되니 참 편하지 아니할 수가 없다.

하지만 이 기능도 한계가 있다. SQLPL/SQL 문은 컨텍스트가 틀리다.
당연히 데이터베이스에서 선언해야 쿼리가 돌아가는 특성상,
인라인 형식(익명 블록 내 DECLARE)에서 선언한 배열은 지원하지 않는다. 물론 거기서 선언한 RECORD 문은 말할 것도 없고.
패키지의 경우에서는 PACKAGE BODY 문에서 선언한 배열이나 RECORD 형식에서 선언하면 지원하지 않는다.
이들 모두 “cannot access rows from a non-nested table” 오류가 뜰 것이다.
보통 개발자들에게 TYPE 권한을 잘 안열고, 프로시저, 함수, 패키지에 한해 권한을 어느정도 오픈하는 환경이기 때문에, 12c가 구축된 환경에서는
쿼리를 짜는데 있어서 부담이 덜할 수 있는 좋은 기능이다.

뭐 MSSQL의 테이블 변수 기능에 흠뻑 젖은 개발자라면 여전히 불편한 건 매한가지다.

끗.

참고

composite / 2017년 11월 10일 / 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