퀴즈로 배우는 SQL
목록
[퀴즈] 이진 문자열 논리 연산 0 0 99,999+

by 마농 문자열비교 행복제 이진문자열 XOR MODEL PIVOT 논리비교 UNPIVOT [2015.09.16]

이번 퀴즈로 배워보는 SQL 시간에는 0과 1로 이루어진 문자열 두 개를 논리 비교하여 AND, OR, XOR의 값을 계산하는 쿼리 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.

진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

문제

  • [리스트 1] 원본리스트(출석현황)
  • CREATE TABLE t
AS
SELECT 1 no, '000111000111' v FROM dual
UNION ALL SELECT 2, '111100001111' FROM dual;

SELECT * FROM t;

  • [표 1] 원본테이블(이진문자열)
  •     NO V
------ -----------------
     1 000111000111
     2 111100001111

  • [표 2] 결과테이블 
  •     NO V                비고
------ ---------------- ----
     1 000111000111      V1
     2 111100001111      V2
     3 000100000111      AND
     4 111111001111      OR
     5 111011001000      XOR

문제설명

<표 1>의 1과 0으로 연결된 12자리 문자열 값 V를 두 개 가지고 있습니다. 이 두 값은 키인 NO 항목으로 구별됩니다. 이 두 개 값을 서로 비교해 <표 2> 결과테이블을 출력하는 문제입니다.

NO 1과 2에 해당하는 V값을 각 자리수마다 비교하여 결과를 반환합니다. NO 3은 논리곱(AND) 연산을 표현한 것입니다.

1과 0을 참과 거짓으로 보고 이 두 개의 값을 AND 연산하는 것입니다. AND 연산은 두 값이 모두 참일 때만 참이 됩니다.

두 값이 모두 1인 4번째 자리와 10,11,12번째 자리가 참이 되고 나머지는 거짓이 됩니다. 이 결과를 그대로 합쳐서 나열하면 ‘000100000111’이 되는 것입니다.

NO 4는 논리합(OR) 연산을 표현한 것입니다. 둘 중 하나만 참이어도 참이 됩니다.

NO 5는 배타적논리합(XOR, Exclusive OR) 연산을 표현한 것입니다. 하나는 참, 하나는 거짓인 경우에만 참이 됩니다. 이렇게 3, 4, 5행의 자료를 추가로 만드는 SQL을 작성하는 문제입니다.

정답

문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.

  • [리스트 2] 정답 리스트
SELECT *
  FROM t
 MODEL
 DIMENSION BY (no)
 MEASURES (v)
 RULES ITERATE (12)
 ( v[3] = v[3] ||
        ( SUBSTR(v[1], ITERATION_NUMBER + 1, 1)
        * SUBSTR(v[2], ITERATION_NUMBER + 1, 1)
        )
 , v[4] = v[4] || SIGN
        ( SUBSTR(v[1], ITERATION_NUMBER + 1, 1)
        + SUBSTR(v[2], ITERATION_NUMBER + 1, 1)
        )
 , v[5] = v[5] || DECODE
        ( SUBSTR(v[1], ITERATION_NUMBER + 1, 1)
        , SUBSTR(v[2], ITERATION_NUMBER + 1, 1)
        , 0, 1)
 )
 ORDER BY no
;

어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

이번 문제는 문자열 비교 문제입니다.

두 개의 문자열을 한 자리씩 차례로 잘라내 비교해야 하는데요. 총 12자리이니 두 개의 값을 12번 잘라내야 하며(12*2=24회), 비교식이 3가지이므로 비교식만 총 72번(12*3*2) 사용해야 합니다.

단순 반복성 계산식 같은 경우 12개의 열을 1개열의 12개의 행으로 변환해 작업한다면 12번 반복되는 계산식을 줄일 수 있을 듯합니다.

  • [리스트 3] 행 복제
  • SELECT no, lv
     , SUBSTR(v, lv, 1) v
  FROM t
     , (SELECT LEVEL lv FROM dual
        CONNECT BY LEVEL < = 12)
 ORDER BY no, lv
;

  • [표 3] 행 복제
  •      NO         LV V
------- ---------- --------
      1          1 0
      1          2 0
      1          3 0
      1          4 1
      1          5 1
      1          6 1
      1          7 0
      1          8 0
      1          9 0
      1         10 1
      1         11 1
      1         12 1
      2          1 1
      2          2 1
      2          3 1
      2          4 1
      2          5 0
      2          6 0
      2          7 0
      2          8 0
      2          9 1
      2         10 1
      2         11 1
      2         12 1

<리스트 3>의 쿼리를 수행하여 <표 3>의 결과를 얻었습니다. 

      , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < = 12)

이 구문은 원하는 수만큼의 데이터를 간단하게 얻어내는 방법입니다.

이 쿼리를 수행하면 1부터 12까지의 수 12행이 반환됩니다. <리스트 3>에서는 이 집합을 이용하여 원본집합을 복제합니다. 12개의 똑같은 데이터가 복제되는 것입니다.

      , SUBSTR(v, lv, 1) v

lv값을 이용하여 SUBSTR 하는 구문입니다. 행 복제 방법을 이용하여 SUBSTR 24번 기술해 줘야하는 번거로움이 사라지고 단 한번만 기술하게 되었습니다.

이제 잘라낸 데이터를 서로 비교해야 하는데요. 같은 행에 있는 다른 항목끼리는 비교할 수 있지만 다른 행에 있는 값끼리의 비교는 어렵습니다. 따라서 비교 대상끼리 묶어서 하나의 행에 두 개 열로 변환해 보도록 하겠습니다.

  • [리스트 4] PIVOT
  • SELECT *
  FROM (SELECT no, lv
             , SUBSTR(v, lv, 1) v
          FROM t
             , (SELECT LEVEL lv FROM dual
                CONNECT BY LEVEL < = 12)
        )
 PIVOT (MIN(v) FOR no IN (1 v1, 2 v2))
 ORDER BY lv
;

  • [표 4] PIVOT
  •       LV V1       V2
-------- -------- --------
       1 0        1
       2 0        1
       3 0        1
       4 1        1
       5 1        0
       6 1        0
       7 0        0
       8 0        0
       9 0        1
      10 1        1
      11 1        1
      12 1        1

<리스트 4>의 쿼리를 수행해 <표 4>의 결과를 얻었습니다.

      PIVOT (MIN(v) FOR no IN (1 v1, 2 v2))

행을 열로 전환하는 PIVOT 구문입니다. NO 값 1 과 2 에 해당하는 V 값을 V1 과 V2 로 전환하는 구문입니다. 이제 이 V1 과 V2 를 비교하여 결과를 도출하면 될 것입니다.

  • [리스트 5] 논리비교
  • SELECT lv, v1, v2
     , v1 * v2 v3
     , SIGN(v1 + v2) v4
     , DECODE(v1, v2, 0, 1) v5
  FROM (SELECT no, lv
             , SUBSTR(v, lv, 1) v
          FROM t
             , (SELECT LEVEL lv FROM dual
                CONNECT BY LEVEL < = 12)
        )
 PIVOT (MIN(v) FOR no IN (1 v1, 2 v2))
 ORDER BY lv
;

  • [표 5] 논리비교
  •        LV V1       V2               V3         V4         V5
--------- -------- -------- ---------- ---------- ----------
        1 0        1                 0          1          1
        2 0        1                 0          1          1
        3 0        1                 0          1          1
        4 1        1                 1          1          0
        5 1        0                 0          1          1
        6 1        0                 0          1          1
        7 0        0                 0          0          0
        8 0        0                 0          0          0
        9 0        1                 0          1          1
       10 1        1                 1          1          0
       11 1        1                 1          1          0
       12 1        1                 1          1          0

<리스트 5>의 쿼리를 수행하여 <표 5>의 결과를 얻었습니다. 

      , v1 * v2 v3
      --, CASE WHEN v1 = 1 AND v2 = 1 THEN 1 ELSE 0 END v3

논리곱(AND)을 구하는 방법은 두 값이 모두 1인지를 체크하면 됩니다. CASE 문을 이용하여 풀 수도 있지만 저는 간단하게 곱셈(*)을 이용했습니다.

      , SIGN(v1 + v2) v4
      --, CASE WHEN v1 = 1 OR v2 = 1 THEN 1 ELSE 0 END v4

논리합(OR)을 구하는 방법은 둘 중 하나가 1인지를 체크하면 됩니다. 간단하게 덧셈(+)과 SIGN 함수를 이용했습니다.

      , DECODE(v1, v2, 0, 1) v5

배타적논리합(XOR)을 구하는 방법은 하나는 1이고 하나는 0인 경우입니다. 둘 다 1이거나 둘 다 0인 경우는 0이 됩니다. 다시 말하면 두 값이 같으면 0을 다르면 1을 반환하면 됩니다.

저는 DECODE 구문을 이용했습니다. 논리연산은 끝났습니다. 이제 행 복제를 통해 나누어 놓은 문자를 다시 하나로 합쳐보도록 하겠습니다.

  • [리스트 6] 문자열 합치기
  • SELECT LISTAGG(v1) WITHIN GROUP(ORDER BY lv) v1
     , LISTAGG(v2) WITHIN GROUP(ORDER BY lv) v2
     , LISTAGG(v3) WITHIN GROUP(ORDER BY lv) v3
     , LISTAGG(v4) WITHIN GROUP(ORDER BY lv) v4
     , LISTAGG(v5) WITHIN GROUP(ORDER BY lv) v5
  FROM (SELECT lv, v1, v2
             , v1 * v2 v3
             , SIGN(v1 + v2) v4
             , DECODE(v1, v2, 0, 1) v5
          FROM (SELECT no, lv
                     , SUBSTR(v, lv, 1) v
                  FROM t
                     , (SELECT LEVEL lv FROM dual
                        CONNECT BY LEVEL < = 12)
                )
         PIVOT (MIN(v) FOR no IN (1 v1, 2 v2))
        )
;

  • [표 6] 문자열 합치기
  • V1                V2                V3                V4                V5
----------------- ----------------- ----------------- ----------------- ----------------
000111000111      111100001111      000100000111      111111001111      111011001000

<리스트 6>의 쿼리를 수행하여 <표 6>의 결과를 얻었습니다. 

      LISTAGG(v1) WITHIN GROUP(ORDER BY lv) v1

LISTAGG 함수를 이용해 문자열을 하나로 합쳤습니다. 5개의 컬럼으로 결과가 표현이 되었습니다. 5개 행으로 바꿔야 하겠지요? 하지만 이번에는 UNPIVOT을 이용해 보도록 하겠습니다.

  • [리스트 7] UNPIVOT
  • SELECT *
  FROM
 (SELECT LISTAGG(v1) WITHIN GROUP(ORDER BY lv) v1
       , LISTAGG(v2) WITHIN GROUP(ORDER BY lv) v2
       , LISTAGG(v3) WITHIN GROUP(ORDER BY lv) v3
       , LISTAGG(v4) WITHIN GROUP(ORDER BY lv) v4
       , LISTAGG(v5) WITHIN GROUP(ORDER BY lv) v5
    FROM (SELECT lv, v1, v2
               , v1 * v2 v3
               , SIGN(v1 + v2) v4
               , DECODE(v1, v2, 0, 1) v5
            FROM (SELECT no, lv
                       , SUBSTR(v, lv, 1) v
                    FROM t
                       , (SELECT LEVEL lv FROM dual
                          CONNECT BY LEVEL < = 12)
                  )
           PIVOT (MIN(v) FOR no IN (1 v1, 2 v2))
          )
  )
 UNPIVOT (v FOR no IN (v1 AS 1, v2 AS 2, v3 AS 3
                              , v4 AS 4, v5 AS 5))
;

<리스트 7>의 쿼리를 통해 같은 <표 2>의 결과를 얻었습니다.

      UNPIVOT (v FOR no IN (v1 AS 1, v2 AS 2, v3 AS 3, v4 AS 4, v5 AS 5))

열을 행으로 전환하는 UNPIVOT 구문입니다. V1 ~ V5 를 No 1 ~ 5 로 구별하여 V 열로 표현하는 구문입니다.

행 복제 방법을 이용하여 데이터를 복제한 뒤 SUBSTR을 이용해 데이터를 잘라내고, PIVOT을 이용해 행을 열로 바꾼 뒤 논리 연산을 수행하고 LISTAGG를 이용해 연산결과 문자열을 하나로 합친 뒤 UNPIVOT을 이용해 열을 행으로 바꿔 결과를 도출했습니다.

좀 더 간단하게 문제를 풀 수 있는 방법이 있습니다. MODEL절을 이용하는 방법입니다. MODEL절에서는 일반 SQL 에서 어려웠던 행간 자료 비교가 가능합니다. 또한 ITERATE 구문을 이용하면 반복처리도 가능합니다. <리스트 2>의 MODEL절에 대해 설명하겠습니다.

  • - DIMENSION BY (no) -- 식별자 : DIMENSION BY 에서는 식별자를 지정합니다.
  • - MEASURES (v) -- 일반속성 : MEASURES 구문에서는 일반속성을 지정합니다.
  • - RULES ITERATE (12) ( -- 모델참조식 -- ) : RULES 구문에서는 모델참조식을 정의합니다. ITERATE (12)는 모델참조식을 12번 반복 수행하라는 의미입니다. ITERATION_NUMBER는 12번 반복되면서 증가하는 수입니다.(0 ~ 11)

( v[3] = v[3] ||
( SUBSTR(v[1], ITERATION_NUMBER + 1, 1)
* SUBSTR(v[2], ITERATION_NUMBER + 1, 1)
)
)

v[3]은 식별자 3을 갖는 V값을 의미합니다. 여기에 v[1]의 값을 ITERATION_NUMBER를 이용해 잘라냅니다. v[2]의 값도 마찬가지로 잘라내어 논리곱(AND) 연산을 수행하고 그 결과를 v[3]에 연결하는 구문입니다.

ITERATION_NUMBER가 0부터 11까지 12번 반복되면서 12개의 결과를 연결해 반환하는 것입니다. v[4]와 v[5]도 마찬가지 계산식을 적용하여 구할 수 있습니다.

문서에 대하여

  • - 강좌 URL : http://www.gurubee.net/lecture/2908
  • - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
  • - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

- 강좌 URL : http://www.gurubee.net/lecture/2910

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입