'함수'에 해당되는 글 3건

  1. 2011.05.16 실전 데이터모델링(비트컬럼으로 컬럼레벨 데이터통합) 기법 및 오라클 비트연산 가이드 (2)
  2. 2010.08.27 SQLite User Guide for Android, iPhone - DateTime 함수, DateTime Formatting (1)
  3. 2010.08.27 SQLite User Guide for Android, iPhone - 함수. Function (Core, Aggregation) (1)
2011.05.16 17:10

실전 데이터모델링(비트컬럼으로 컬럼레벨 데이터통합) 기법 및 오라클 비트연산 가이드




비트컬럼으로 속성레벨의 데이터 통합

이번 포스트에서는 비트값으로 구성된 컬럼으로 컬럼 수준의 데이터 통합에 대한 가이드 및 비트 컬럼을 SQL로 조작하는 방안에 대해 살펴볼까 합니다.

먼저, 일반적인 샘플 ERD를 하나 보겠습니다.


좌측은 Logical 엔터티이며 우측은 Physical 테이블 설계안 입니다.

지극히 간단한 테이블입니다. 웹 화면을 구성하는 메뉴가 있고, 메뉴의 속성으로는 ID, 명, 그리고 화면을 제어하는 4개의 여부 컬럼이 있습니다. 간단하지만 전혀 문제가 없습니다. 요구사항도 명확히 반영했다고 한다면 정말 문제가 없는 모델링 입니다.

프로젝트팀이 프로젝트를 완료하고 난 후 운영팀에서 운영중에 새로운 고객 요구사항들이 나오기시작합니다. 메뉴ID를 화면에 무조건 보여주지 말고 사용자가 보여줄지 말지를 선택하게 해달라는 군요. 그러면 컬럼을 추가하면 됩니다 “메뉴ID디스플레이여부” 란 컬럼이 추가됩니다.

또 다른 고객이 이와 유사한 요구를 해 옵니다.
얼마 지나지 않아 “~여부” 컬럼만 6개나 추가되었군요. 그래도 컬럼만 추가하고 프로그램 코드를 수정하면 요구사항을 반영하는 데에는 큰 문제는 없습니다.

하지만 일 하기 싫은 DBA가 고민합니다.(바로 저 입니다.)
“이런 식의 고객 요구사항이 들어와도 컬럼 수정, 테이블 수정 없이 처리할 순 없을까?”

방법은 아래 그림과 같이 모든 여부 컬럼을 비트값의 합으로 표현하여 하나의 컬럼으로 통합해버리면 됩니다.


4개의 여부 컬럼을 “디스플레이비트값” NUMBER 하나의 컬럼으로 통합했습니다. 기존 모델의 각각의 컬럼은 2진수로 표현했을 때 하나의 비트값에 대응됩니다.

예를 들어
“배너디스플레이여부” 컬럼은 2^0 = 1로 설정하면 되며,
“메뉴이미지사용여부” 컬럼은 2^1 = 2,
“컨텐츠표시여부” 2^2 = 4,
“Hybrid메뉴여부” 2^3 = 8 이 됩니다.

그리고 실제 디스플레이비트값은 각각의 값을 BITWISE OR 연산을 하여 저장을 하게 됩니다.

예를 들어
“배너디스플레이여부” = “Y” ,
“메뉴이미지사용여부”= “N”,
“컨텐츠표시여부” = “Y”,
“Hybrid메뉴여부” = “Y” 값을 갖는 다면 실제 디스플레이비트값 컬럼에 저장되는 값은
1(2^0) + 4(2^2) + 8(2^3) = 13이 됩니다.

각각의 통합전 컬럼에 해당 되는 비트값을 부여할 때 2의 승수로만 부여하기 때문에 실제 bitwise or 연산의 결과나 단순 더하기 결과나 결과는 동일해 집니다.

0001(1) + 0010(2) + 1000(8) = 1011(13)

이 모델의 장점은 고객의 새로운 요구사항이 있을 때 컬럼의 추가 없이 프로그램만 수정하면 됩니다. 좀 있어보이는 말로는 “시스템의 유연성을 높이는 설계 방법” 이라고 하죠. 쉽게 말해 DBA가 편해지는 군요. 제가 바라는 바입니다.

제가 운영하는 시스템의 사례를 얘기할까 합니다.

제가 운영하는 시스템은 마케팅 사이트로 Global 시스템이지만 국내 한 서버에서 전세계 사이트를 모두 서비스 합니다. 시스템 사양에 비해서 Request가 많기 때문에 서버는 항상 바쁩니다. 글로벌 사이트라서 고객은 일년의 단 10분도 시스템이 정지되는걸 원하지 않습니다.

그럼에도 불구하고 위의 사례처럼 자잘 하지만 “~여부” 같은 컬럼을 추가해야만 처리될 수 있는 요구를 합니다. 컬럼 추가하고 개발하면 되겠죠. 하지만 문제가 있습니다. 대부분의 테이블은 문제가 없지만 시스템의 Main Entity인 2~3개의 테이블은 거의 모든 SQL이 집중되고, 수많은 PL/SQL이 참조하고 있어서 컬럼을 추가하는 순간 library cache pin 및 lock 등의 waiting이 걸리면서 오라클이 처리를 빨리 하지 못하게 되며, WAS에서는 DB 요청에 대한 리턴을 빠르게 받지 못하게 되니 계속계속 새로운 요청마다 새로운 Oracle Connection을 맺다가 Connection Pool Max size를 넘어서게 될 때 시스템이 거의 죽어버린답니다.

이런 상황에서는 컬럼 추가 작업 자체가 online에서 쉽게 alter table 명령어로 처리할 수 없으며, 컬럼을 추가하기 위해서는 수많은 결재와 보고를 해야 하고 휴일에 특정 시간대에 시스템을 내리고 컬럼 추가하고 다시 시스템을 기동하는 식으로 처리를 합니다.

어휴~ 컬럼 추가 작업이 장난이 아닙니다. 그러니, 제가 컬럼 추가 안하고 설계할 수 있는 방안을 고민하지 않을 수 없습니다.

하지만 이 모델의 단점도 만만치 않습니다.
단점은 첫째, 가독성이 떨어집니다. DA#으로 모델링을 하면 통합전 컬럼의 비트값을 서브타입으로 표현할 수 있어서 그나마 낫지만 Erwin 같은 툴을 쓰면 속성설명 정도에만 표현할 수 밖에 없어 모델의 가독성이 떨어져 문제가 됩니다.
두번째 단점은 SQL이 복잡해 집니다. 초급 개발자들은 설명을 해줘도 잘 이해를 못하더군요.

간단하지만 위 두 모델에 대해 무엇이 좋고 나쁘다라고는 말할 수 없을 것 같습니다. 다만, 비트값으로 속성을 표현하여 통합할 수 있는 방법도 있다라는 것을 알고, 시스템의 상황에 맞게 가장 적합한 방법을 사용하시면 됩니다. 저 같은 경우는 위의 컬럼 추가가 문제가 되는 테이블, 그리고 자주 업무 요건이 변경되는 테이블은 비트값으로 컬럼을 통합하는 방식을 많이 사용하며, 그렇지 않는 테이블은 사용하지 않습니다.

비트연산 기초

비트값 형식으로 속성을 모델링 했을 때 화면 UI와 이 UI를 처리하는 DML에 대해 살펴보도록 하겠습니다. 그전에 기초로 돌아가서 비트 연산에 대해 알아보겠습니다. 요즘 개발자 분들이 처음부터 웹 개발을 하신 분들은 비트 연산을 할 일이 없어서인지 비트연산을 헷갈려 하더군요.

비트 연산(Bitwise operation)은 한 개 혹은 두 개의 이진수에 대해 비트 단위로 적용되는 연산입니다.

NOT : NOT 연산은 각 자릿수의 값을 반대로 바꾸는 연산입니다.

NOT 0111
= 1000

OR : OR 연산은 두 값의 각 자릿수를 비교해, 둘 중 하나라도 1이 있다면 1을, 아니면 0으로 계산됩니다.

0101
OR 0011
= 0111

XOR : XOR 연산은 두 값의 각 자릿수를 비교해, 값이 같으면 0, 다르면 1으로 계산 합니다.

0101
XOR 0011
= 0110

AND : AND 연산은 두 값의 각 자릿수를 비교해, 두 값 모두에 1이 있을 때에만 1을, 나머지 경우에는 0으로 계산합니다

0101
AND 0011
= 0001


예전에 공부했던 게 기억이 나십니까?

비트속성 컬럼값 DML 가이드

화면 UI에서 비트컬럼값을 처리하는 DML 가이드입니다.



다음과 같은 화면이 있다고 가정합니다. 위 메뉴 테이블을 관리하는 화면입니다.

각 비트값에 해당되는 항목마다 라디오 버튼을 가진 하나의 항목으로 UI는 구성될 것입니다. 물론 다른 방식으로도 가능하겠지만요. 일단 이렇게 된다고 가정합니다.

이 화면의 특징은 한 화면에서 비트컬럼값이 사용하는 모든 비트값을 다 관리를 한다는게 특징입니다.

이런 케이스에 대해서는 처리 로직은 간단합니다.

먼저, 화면을 구성하기 위한 SELECT 문을 보겠습니다.

SELECT MENU_ID
            ,MENU_NM
            ,DECODE(BITAND(DISPLAY_BV,1),1,'Y','N') AS "배너디스플레이여부"
            ,DECODE(BITAND(DISPLAY_BV,2),2,'Y','N') AS "메뉴이미지사용여부"
            ,DECODE(BITAND(DISPLAY_BV,4),4,'Y','N') AS "컨텐츠표시여부"
            ,DECODE(BITAND(DISPLAY_BV,8),8,'Y','N') AS "Hybrid메뉴여부"
FROM MENU2;

오라클에서 제공하는 BITAND 함수를 가지고 해당비트값이 1인지를 판단할 수 있습니다.

이 경우 값을 INSERT, UPDATE 할 때는 각각의 항목에 대한 값(Y/N)을 판단해서 아래와 같이 처리할 수 있습니다.

UPDATE MENU2
      SET DISPLAY_BV = DECODE('배너디스플레이여부','Y',1,0)
                                + DECODE('메뉴이미지사용여부','Y',2,0)
                                + DECODE('컨텐츠표시여부','Y',4,0)
                                + DECODE('Hybrid메뉴여부','Y',8,0)
 WHERE MENU_ID = :MENU_ID;

하지만, 위의 경우처럼 Y/N으로 INPUT TYPE=”RADIO”의 값을 구성하는 것 보다는 VALUE로 각각의 컬럼이 갖는 비트값으로 바로 지정하는게 효율적일 수 있습니다. 웹으로 구성한다면 value >0 면 checked를 지정하면 될 것 같군요.


SELECT MENU_ID
            ,MENU_NM
            ,DECODE(BITAND(DISPLAY_BV,1),1,1,0) AS "배너디스플레이여부"
            ,DECODE(BITAND(DISPLAY_BV,2),2,2,0) AS "메뉴이미지사용여부"
            ,DECODE(BITAND(DISPLAY_BV,4),4,4,0) AS "컨텐츠표시여부"
            ,DECODE(BITAND(DISPLAY_BV,8),8,8,0) AS "Hybrid메뉴여부"
  FROM MENU2;

INSERT, UPDATE문은 각각의 항목값을 모두 더해서 처리하면 됩니다.


UPDATE MENU2
      SET DISPLAY_BV = 1 + 4 + 0 + 8
 WHERE MENU_ID = :MENU_ID;

이번에는 좀 더 난이도가 높은 UI를 보겠습니다.

원칙적으로는 비트속성값 모두를 한 화면에서 핸들링 해야 하지만, 업무요건상 그렇게 될 수 없다고 가정합니다. 아래 그림을 보시면 메뉴관리 화면에서는 Menu Image Use 항목과 Is Hybrid menu 항목은 메뉴관리 화면에서 관리하고, 나머지 2개의 컬럼은 다른 화면에서 관리한다고 가정합니다.



이 경우에는 2^1(2) 과 2^3(8)만 화면에서 관리하므로 다른 비트값 2^0(1), 2^2(4) 값은 이 화면에서 핸들링하면 안됩니다.

SELECT문과 INSERT문은 이렇게 화면을 구성하더라도 기존과 동일하게 처리하면 된다. 하지만 UPDATE문은 다른쪽 화면에서 나머지 값에 대한 핸들링을 하므로 영향을 받지 않도록 다르게 처리해야 합니다.

현재 DIPLAY_BV 값을 7 (2^0(1) + 2^1(2) + 2^2(4))이라고 가정하겠습니다.

즉, 배너디스플레이여부=Y, 메뉴이미지사용여부=Y, 컨텐츠표시여부=Y, Hybrid메뉴여부=N인 상태입니다.
위의 메뉴화면에서 사용자가 Save 버튼 클릭시 값이 메뉴이미지사용여부=N, Hybrid메뉴여부=Y로 상태값이 변경되었다고 하면


현재값:                                      0111 (7)
화면에서 관리안하는 비트값의 합 : 0101 (5)
----------------------------------------------
두값의 BITAND 값                     : 0101 (5)

현재 설정값(비관리항목은0으로)  : 1000 (8)
----------------------------------------------
두값의 BITOR                           : 1101 (13)

먼저 현재값과 화면에서 관리하지 않는 항목에 해당되는 비트값의 BITWISE OR 값 (단순 더하기)을 구해서 두 값을 BITAND 합니다.
즉, 현재값인 7인 값과 화면에서 관리하지 않는 항목의 값 (배너디스플레이여부 2^0(1) + 컨텐츠표시여부 2^2(4))을 더한 값과 서로 BITAND 연산을 합니다. 결과가 0101 (5)가 나오는군요.
이 값과 현재화면에서 설정값(단, 관리안하는 UI항목에 대해서는 전부 0으로 비트값처리, 메뉴이미지사용여부=N이므로 0, Hybrid메뉴여부=Y이므로 8)의 합을 서로 BITOR 연산합니다. 5 BITWISE OR 8 = 13가 나오는군요.
13값을 저장하면 됩니다.

다음과 같은 UPDATE문이 나올 수 있습니다.


UPDATE MENU2
   SET DISPLAY_BV = PKG_UTILS.BITOR(BITAND(DISPLAY_BV,5),DECODE('메뉴이미지사용여부','Y',2,0) + DECODE('Hybrid메뉴여부','Y',8,0))
 WHERE MENU_ID = 1;

* DECODE 부분은 UI 값을 대응되는 비트값으로 구성했다면 DECODE없이 바로 더하면 됩니다.

 BITOR 함수는 오라클에서는 DEFAULT로 제공하지 않습니다. 그래서 아래와 같은 PL/SQL 패키지에서 BIT관련 함수를 구현했습니다.

그리고 패키지 주석을 보시면 이해하시겠지만, 특정 비트자리수의 값을 0으로 혹은 1로 설정할 수 있는 BIT_SET 함수도 만들어 두었습니다.
이 함수를 위 사례에 적용해서 만일 현재값과 상관없이 Hybrid메뉴여부(2^3=8) 항목을 CHECKED로 만들려고 하면 BIT_SET(컬럼값, 8, 1)로 함수를 호출하면 됩니다. 마지막 인자값은 두번째 인자값에 해당되는 비트자리수의 값을 1 혹은 0으로 설정하도록 하는 값(1/0)이 인자로 넘어갑니다.

좀 전에 사용했던 UPDATE문을 동일하게 BIT_SET 함수로 처리할 수 있습니다.

UPDATE MENU2
      SET DISPLAY_BV = PKG_UTILS.BIT_SET(PKG_UTILS.BIT_SET(DISPLAY_BV,2,0), 8,1)
 WHERE MENU_ID = 1;


BITWISE 관련 오라클 유저 함수 (패키지)

다음의 패키지(스크립트)는 오라클에서 BITAND 함수만을 제공하기 때문에 원할한 BIT연산을 위해서 관련 함수를 구현한 코드입니다. 참고하시기 바랍니다.


CREATE OR REPLACE PACKAGE PKG_UTILS AS
/******************************************************************************
   NAME        : PKG_UTILS
   PURPOSE     : 시스템 공통으로 사용되는 함수, 프로시저 등을 제공하는 패키지
   DESCRIPTION : UTILITY OPERATION PACKAGE
   REVISIONS   : 1.0
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2011-04-01  Jinook,lee       1. Created this package.
******************************************************************************/
 
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT OR 연산한다.
    -- OR 연산은 두 값의 각 자릿수를 비교해, 둘 중 하나라도 1이 있다면 1을, 아니면 0을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : bit or 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT XOR 연산한다.
    -- XOR 연산은 두 값의 각 자릿수를 비교해, 값이 같으면 0, 다르면 1을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : BIT XOR 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITXOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : X 값을 BIT NOT 연산한다.
    -- NOT 연산은 각 자릿수의 값을 반대로 바꾸는 연산이다.
    -- x : 비트 연산할 숫자
    -- return   : BIT NOT 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITNOT(x NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 0으로 만드는 함수이다.
    -- 내부적으로 X & ^Y 로 연산한다.
    -- x, y : 비트 연산할 숫자
    -- return   : BIT 마스크 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_MASK(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 주어진 값으로 만드는 함수이다.
    -- x,y : 비트 연산할 숫자
    -- z : x값의 y자리수를 값을 설정할 값 (0 or 1)
    -- return   : BIT SET 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_SET(x NUMBER, y NUMBER, Z NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
END PKG_UTILS;
/


CREATE OR REPLACE PACKAGE BODY PKG_UTILS AS
/******************************************************************************
   NAME        : PKG_UTILS
   PURPOSE     : 시스템 공통으로 사용되는 함수, 프로시저 등을 제공하는 패키지
   DESCRIPTION : UTILITY OPERATION PACKAGE
   REVISIONS   : 1.0
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2011-04-01  Jinook,lee       1. Created this package.
******************************************************************************/
 
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT OR 연산한다.
    -- OR 연산은 두 값의 각 자릿수를 비교해, 둘 중 하나라도 1이 있다면 1을, 아니면 0을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : bit or 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN x + y - BITAND(x, y);
    END;
   
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT XOR 연산한다.
    -- XOR 연산은 두 값의 각 자릿수를 비교해, 값이 같으면 0, 다르면 1을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : BIT XOR 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITXOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN BITOR(x,y) - BITAND(x,y);
    END;
   
    -------------------------------------------------------
    -- DESC : X 값을 BIT NOT 연산한다.
    -- NOT 연산은 각 자릿수의 값을 반대로 바꾸는 연산이다.
    -- x : 비트 연산할 숫자
    -- return   : BIT NOT 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITNOT(x NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN (0 - x) - 1;
    END;
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 0으로 만드는 함수이다.
    -- 내부적으로 X & ^Y 로 연산한다.
    -- x, y : 비트 연산할 숫자
    -- return   : BIT 마스크 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_MASK(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN BITAND(x, BITNOT(y));
    END;
   
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 주어진 값으로 만드는 함수이다.
    -- x,y : 비트 연산할 숫자
    -- z : x값의 y자리수를 값을 설정할 값 (0 or 1)
    -- return   : BIT SET 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_SET(x NUMBER, y NUMBER, z NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        IF z = 1 THEN
            RETURN BITOR(x, y);
        ELSIF z = 0 THEN
            RETURN BITAND(x, BITNOT(y));
        ELSE
            RAISE PKG_GLOBAL.ERR_WRONG_PARAMETER;
        END IF;
    END;
END PKG_UTILS;
/

* BIT_SET 함수에서 RAISE PKG_GLOBAL.ERR_WRONG_PARAMETER; 부분은 제가 자주 사용하는 ERROR를 별도의 GLOBAL 패키지에 선언해두고 참조해서 사용할 수 있도록 구성한 부분입니다.  해당 부분 삭제하시고 패키지 컴파일 하시거나 일반적인 PL/SQL ERROR RAISE 하는 구문으로 대체하시면 됩니다.


Trackback 0 Comment 2
  1. 신.. 2011.09.09 01:09 신고 address edit & del reply

    참기발한 방법이군요.
    여부컬럼하면 11100111 혹은 YYYNNYYY 만 알고 있는데 이런 방법도 있군요.
    잘 봅니ㄴ다..

  2. 땅콩맨 2012.05.14 15:36 신고 address edit & del reply

    안녕하세요
    MSSQL에서 SQLite로 쿼리를 바꾸려고 하는데
    sql = "declare @num245(4), @num345 char(4) select @num245=autogubun from staff.dbo.ONR_TV_Station_tbl where num=245 select @num345=autogubun from staff.dbo.ONR_TV_Station_tbl where num=345 거든요. 원래 쿼리가...

    바꾸려고 하니깐 declare도 그렇고 select 문 2개 이어서 쓰는방식도 그렇고
    바꾸는 방법을 못찾겠는데, 혹시 이것에 대해서 답변좀 부탁드릴께요~

    이럴줄 알았으면 SQLite 공부좀 해둘껄 그랬어요... 홀홀홀...

2010.08.27 09:23

SQLite User Guide for Android, iPhone - DateTime 함수, DateTime Formatting



SQLite Databae에 대한 여섯번째 포스트입니다. 이번 포스트에서는 SQLite의 DateTime 관련 함수 및 Formatting 에 대해서 다뤄보도록 하겠습니다. 이 부분은 다른 DBMS와 많이 다르고 자주 사용하는 함수들이라 몇가지 표현법은 꼭 익혀야 될 점인것 같습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)


1. DateTime 관련 Keyword

먼저 SQLite에서는 DateTime관련해서 다음과 같은 세가지 키워드가 있습니다.
CURRENT_TIME : 현재 시간 (형식: 03:22:56) 다만, UTC 기준입니다.
CURRENT_DATE: 현재 날짜 (형식: 2010-08-25) UTC 기준.
CURRENT_DATETIME : 현재 날자 및 시간 (형식: 2010-08-25 03:23:37) UTC 기준.

UTC 기준 날짜 및 시간이라서 사용할때는 로컬시간으로 다시 변환작업이 필요할 듯 보입니다. DATETIME 관련 함수들이 있어 크게 유용해 보이지는 않습니다.

2. DateTime 관련 함수.

DateTime 관련 함수는 다음의 5가지가 있습니다.

date(timestring, modifier, modifier, ...)  : 날짜 함수
time(timestring, modifier, modifier, ...)  : 시간 함수
datetime(timestring, modifier, modifier, ...)  :날짜/시간 함수
julianday(timestring, modifier, modifier, ...)  : 율리우스력 함수
strftime(format, timestring, modifier, modifier, ...)  : 날짜 포맷팅 함수


위 다섯개의 함수 인자 중 timestring 파라미터에로 들어갈 수 있는 날짜 형식은 아래와 같습니다.

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

strftime 함수에 사용되는 format에 사용할 수 있는 값들은 다음과 같습니다.

%d    day of month: 00
%f     fractional seconds: SS.SSS
%H    hour: 00-24
%j     day of year: 001-366
%J     Julian day number
%m    month: 01-12
%M    minute: 00-59
%s     seconds since 1970-01-01
%S     seconds: 00-59
%w     day of week 0-6 with sunday==0
%W     week of year: 00-53
%Y     year: 0000-9999
%%    %

함수의 modifer에 들어갈 수 있는 값들은 다음과 같습니다.

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc


잘 안 와 닿는듯 합니다. 아래 샘플 코드를 보면 이해가 가실 겁니다.

--UTC 기준의 현재 날짜/시간
select datetime('now');
2010-08-25 04:01:46

-- 로컬 기준의 현재 날짜/시간
select datetime('now','localtime');
2010-08-25 13:02:30

--현재 로컬 기준 시간에서 10분 3.5초를 더한 시간.
select datetime('now','localtime','+3.5 seconds','+10 minutes');
2010-08-25 13:14:15

--현재 로컬 시간에 3.5초를 더하고 날짜는 돌아오는 화요일 (weekday == 0 이 일요일입니다.)
select datetime('now','localtime','+3.5 seconds','weekday 2');
2010-08-31 13:05:39

--현재 달의 마지막 날짜
SELECT date('now','start of month','+1 month','-1 day','localtime');
2010-08-31

--2004-01-01 02:34:56초부터 현재까지의 총 초
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
209785028

--현재날짜/시간 기준에서 올해 9번째달의 첫번째 화요일
SELECT date('now','start of year','+9 months','weekday 2');
2010-10-05

-- 날짜 포맷 스타일 변경
select strftime("%Y/%m/%d %H:%M:%S",'now','localtime');
2010/08/27 09:17:22




 

Trackback 0 Comment 1
  1. 김승배 2011.03.19 15:18 신고 address edit & del reply

    DateTime 쿼리 관련해서..
    Select date(birthday) From TableA 와 같이 테이블의 DateTime column을 넣어주면 값이 안나오던데요..
    혹시 해결 방법을 아시나요?

2010.08.27 01:24

SQLite User Guide for Android, iPhone - 함수. Function (Core, Aggregation)




SQLite Databae에 대한 다섯번째 포스트입니다. 이번 포스트에서는 SQLite의 Core 함수에 대해서 다뤄보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

1.Core Function

 coalesce(X,Y,...) coalesce() 함수는 Argument 중에서 첫번째로 Not Null인 Argument값을 리턴하는 함수입니다. 만일 모든 인자가 null이면 null을 리턴합니다.
 ifnull(X,Y) ifnull() 함수는 두 인자중에서 첫번째로 Not Null인 인자값을 리턴합니다. 만일, 둘다 null이면 null을 리턴합니다. Ifnull() 함수는 인자가 두개인 coalesce() 함수와 동일합니다.
 length(X) 길이값을 리턴하는 함수입니다. 만일 X 인자가 null이면 null을 리턴합니다.
 like(X,Y)
 like(X,Y,Z)
Like 함수는 “Y LIKE X [ESCAPE Z]”구문과 동일합니다.
 lower(x) 소문자로 변환 합니다.
 upper(X) 대문자로 변환 합니다.
 ltrimX)
 ltrim(X,Y)
ltrim(X)는 X 값 중 왼쪽편의 공백을 제거하는 함수입니다.
ltrim(X,Y)는 X 문자열중에서 Y에 나타난 값을 제일 좌측부터 제거하는 함수입니다.
 
select ltrim(" ZZZZabcZZ ", " aZ")
=> 좌측문자열에서 부터 공백,a,Z 문자열이 있으면 제거하고, 처음으로 공백,a,Z가 아닌 문자열부터 출력합니다. 즉, “bcZZ”가 출력됩니다.
ltrim(" ZZZZabcZZ ", " ") 는 ltrim(" ZZZZabcZZ ")과 동일합니다.
 rtrim(X)
 rtrim(X,Y)
rtrim(X)는 우측편 공백 제거
rtrim(X,Y)는 ltrim(X,Y)와 동일한 방식이지만 우측편부터 매칭되는 글자를 제거합니다. 예를들어 select rtrim(" ZZZZabcZZ ", " Z") 문장은 우측편부터 공백과 Z를 빼고 처음으로 공백과 Z가 아닌 글자, 즉 c 까지 글자가 나타납니다. 결과값: “ ZZZZabc”
 trim(X)
 trim(X,Y)
trim(X)는 양쪽 공백 제거
trim(X,Y) 는 Y에 해당되는 글자를 양쪽 끝에서 부터 제거하고 나머지 글자만 리턴함. 예) trim(" ZZZZabcZZ ", " Z") => “abc” trim은 ltrim과 rtrim을 각각 적용한것과 동일한 결과가 나타납니다.
 max(X,Y,..) 인자값들 중 최대값을 리턴합니다.

create table t1 (coll integer, col2 integer, col3 integer);

insert into t1 values(1,2,3);
insert into t1 values(5,3,1);

select max(col1, col2, col3) from t1;
 min(X,Y,...) 인자값들 중 최소값을 리턴합니다.
 nullif(X,Y) 두 인자가 서로 같으면 null을 리턴, 서로 다르면 X값을 리턴합니다.
nullif('x','y') => ‘x’ , nullif('x','x') => null 리턴
 quote(X) Quote()함수는 single quotation을 escape 해줍니다. ‘값을 ‘’ 로 변경합니다.
Insert나 update 시에 사용하면 유용할 듯 합니다.
select quote("girl's mouse") => 'girl''s mouse'
 random(*) -9223372036854775808 부터 +9223372036854775807 숫자 사이의 임의의 수를 리턴합니다.
 randomblob(N) N으로 지정된 bytes의 랜덤 바이너리 데이터를 생성합니다.
 hex(X) 바이너리 값을 hex 값으로 변경합니다.
select hex(randomblob(16))
 replace(X,Y,Z) X 문자열 중에서 Y문자열을 Z로 변경합니다.
select replace('1/12/2009','1','x') => "x/x2/2009"
 round(X)
 round(X,Y)
반올림 함수. Y는 소수점 자리. Y가 없으면 0으로 처리합니다.
Round(3.5) => 4, round(2.555, 2) => 2.56
 substr(X,Y)
 substr(X,Y,Z)
substr()함수는 X문자열 중에서 Y번째부터 시작해서 Z개수만큼 문자열을 가져오는 함수입니다. Z가 생략되면 Y번째 문자열부터 문자열 끝까지 리턴합니다. Y의 최소값은 1입니다. 자바에서는 0으로 시작하지만 SQLite에서는 1부터 인덱스가 시작합니다. 만일 Y가 –(마이너스)값이면 문자열 우측끝부터 카운팅을 시작합니다.
select substr("string", 1, 3) => str
select substr("string", 0, 3) => st
select substr("string", -1, 3) => g
select substr("string", -3, 3) => ing
select substr("string", 2) => tring
 typeof(X)  X 표현식에 대한 데이터 타입을 리턴합니다. 리턴값은 “null”, “integer”, “real”, “text”, “blob” 중의 하나입니다.

2. Aggregation Function (집합 함수)

avg(X) 그룹내의 Not Null값의 평균값을 리턴합니다. X 컬럼값이 문자열이나 BLOB이면 0으로 간주하고 처리합니다.
count(*)
count(X)
count(X)는 X가 Not Null값을 가진 레코드의 개수 리턴합니다.
count(*)는 그룹내의 모든 rows의 수를 리턴합니다.
group_concat(X)
group_concat(X,Y)
X가 not null인 경우 그룹내의 모든 문자열을 콤마(,)를 구분자로 해서 문자열을 합쳐서 리턴합니다. Y가 주어지면 Y값이 구분자로 처리됩니다.
max(X) 그룹내의 값들중 최대값을 리턴합니다.
min(X) 그룹내의 값들중 최소값을 리턴합니다. 모든값이 Null이면 Null을 리턴합니다.
sum(X)
total(X)
Sum과 total은 그룹내의 Not Null값의 합계를 리턴합니다. X의 모든 값이 Null인 경우 Sum()은 Null을 리턴하고 Total()은 0.0을 리턴합니다.

/* Aggregation Function Test */

create table t1 (
type integer,
id integer,
name varchar(10),
value real,
data text
);

insert into t1 values(1,1,'a',0.0,'1');
insert into t1 values(1,2,'b',3.5,NULL);
insert into t1 values(1,3,NULL,3.1,'2');
insert into t1 values(1,4,'c',3.5,'10');
insert into t1 values(1,5,'d',NULL,NULL);

-- count(*)와 count(X)의 차이 비교
select count(*), count(name), count(data)
from t1
group by type

-- result
count(*) count(name) count(data)
5 4 3

-- group_concat test query
select group_concat(id,' > ') as exp
from t1;

-- result
exp
1 > 2 > 3 > 4 > 5

select group_concat(data,' > ') exp
from t1
group by type

-- result
exp
1 > 2 > 10

/* sum(), tatal() function test */
select sum(data), total(data)
from t1
where id in (2,5)
group by type;

-- result
sum(data) total(data)
<null>    0

생각보다 SQLite에서 지원되는 내장 함수가 너무 적습니다. SQLite는 각 언어별로 확장 기능을 가지고 있긴 하지만, 쉽게 사용하는게 아니라서.. 한번의 SQL로 처리 가능한 것을 상당 부분 프로그램 코드에서 처리해야 하지 않을까 싶습니다. 그럼에도 불구하고 group_concat() 같은 기능의 함수는 Oracle에서도 11g가 되어서야 내장 함수로 제공할 정도로 대부분의 DB에서 제공하지 않는 함수인데.. SQLite에서 구현이 되어 있다는 게 놀라울 따름입니다.

이것으로 SQLite Function 부분을 마치며, 다음 포스트에서는 DateTime 및 DateTime Formatting에 대해서 알아보도록 하겠습니다.

Trackback 2 Comment 1
  1. suroMind 2011.04.06 17:00 신고 address edit & del reply

    좋은 정보 감사합니다. 출처 남기고 퍼가겠습니다.
    http://blog.suromind.com/43