꿈꾸는 개발자, DBA 커뮤니티 구루비

이번 퀴즈로 배워보는 SQL 시간에는 연속된 수를 하나로 합쳐서 보여주는 SQL을 작성하는 문제를 풀어보도록 하겠습니다. 지면 특성상 문제와 정답 그리고 해설이 같이 있습니다.

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

문제

다음과 같이 1부터 40까지 수중 몇몇 수가 빠진 데이터가 있습니다. [표 1] 원본 테이블 집합으로부터 [표 2] 결과 테이블 집합을 만들어 내는 SQL을 작성하세요.

[리스트 1] 원본 리스트

CREATE TABLE t AS
WITH t AS
(
 SELECT LEVEL lv
   FROM dual
  WHERE LEVEL NOT IN (7, 9, 15, 22, 23, 24, 25, 33, 36)
CONNECT BY LEVEL <= 40
)
SELECT * FROM t
;

[표 1] 원본 테이블

[표 2] 결과 테이블

V1         V2         V3         V4
---------- ---------- ---------- ----------
1~6        8          10~14      16~21
26~32      34         35         37
38         39         40

문제설명

이 문제는 연속된 수를 하나로 합쳐서 보여주는 문제입니다. 다음과 같은 3가지 요구 조건을 만족하도록 작성하셔야 합니다.

[조건 1] 연속된 수를 하나로 합쳐서 보여주세요

[표1]을 보면 1,2,3,4,5,6이 연속된 수이고 7이 빠지고 8이 왔으며, 다시 또 9가 없이 10부터 14까지가 연속된 수입니다. 이 수들을 1~6, 8, 10~14 와 같이 표현하는 문제입니다.

[조건 2] 연속된 수의 개수가 5개 이상일 때만 합쳐서 보여주세요.

연속된 수의 개수가 5개 미만인 경우에는 자료를 합치지 말고 개별로 보여주세요. 위 예시 중 34~35(2개), 37~40(4개)인 경우가 이에 해당됩니다.

[조건 3] 조건1에서 구한 값을 4개 컬럼에 순차적으로 보여주세요.

[표 2]와 같이 4개 컬럼에 차례대로 값을 보여주고, 5번째 값은 행을 바꾸어 다시 첫 번째 컬럼에 값을 보여주도록 작성합니다.

정답

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

[리스트 2] 정답 리스트

SELECT MIN(DECODE(MOD(rn, 4), 1, v)) v1
     , MIN(DECODE(MOD(rn, 4), 2, v)) v2
     , MIN(DECODE(MOD(rn, 4), 3, v)) v3
     , MIN(DECODE(MOD(rn, 4), 0, v)) v4
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY MIN(lv)) rn
             , MIN(lv) || DECODE(COUNT(*), 1, '', '~'||MAX(lv)) v
          FROM (SELECT lv
                     , ROWNUM rn
                     , lv - ROWNUM grp
                     , COUNT(*) OVER(PARTITION BY lv - ROWNUM) cnt
                  FROM (SELECT lv FROM t ORDER BY lv)
               )
         GROUP BY grp, CASE WHEN cnt < 5 THEN lv END
       )
 GROUP BY CEIL(rn / 4)
 ORDER BY CEIL(rn / 4)
;

어떤가요? 여러분이 만들어본 리스트와 같은가요?

틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

여러 가지 요구조건들을 하나씩 해결해 나가도록 하겠습니다.

첫 번째 조건인 연속된 수를 하나로 합치는 문제입니다.

[리스트 3] 의 쿼리로 순차적인 번호인 ROWNUM 과 테이블의 값 LV 와의 차이값을 확인해 보겠습니다. ROWNUM을 순차적으로 구하기 위해 INLINE VIEW 안에서 우선 정렬을 했습니다.

[리스트 3] Rownum 과 lv 와의 차이값 확인

SELECT lv
     , ROWNUM rn
     , lv - ROWNUM grp
FROM (SELECT lv FROM t ORDER BY lv)
;

[표3 Rownum 과 lv 와의 차이값 확인]

    LV         RN        GRP
------ ---------- ----------
     1          1          0
     2          2          0
     3          3          0
     4          4          0
     5          5          0
     6          6          0
     8          7          1
    10          8          2
    11          9          2
    12         10          2
    13         11          2
    14         12          2
    16         13          3
...

[표 3] 의 결과를 보면 RN 값은 LV 의 값 순서대로 순번이 부여가 되어 있는 것을 확인 할 수 있습니다. 1부터 6까지는 동일하게 나가다가 7이 없이 8이 나오는 순간, 두 값에 차이가 나기 시작합니다. 이러한 특성을 이용한다면 연속구간과 불연속 구간을 알아낼 수가 있습니다.

GRP 값은 RN 값과 LV 값의 차이입니다. 이 값을 보면 연속된 수 구간에서는 동일한 결과를 나타내는 것을 확인할 수 있습니다. 이렇게 구한 GRP 값을 이용해 데이터를 하나로 합쳐보도록 하겠습니다.

[리스트 4] 연속된 값을 하나로 합치기

SELECT MIN(lv) lv_from
     , MAX(lv) lv_to
     , COUNT(*) cnt
     , MIN(lv) || DECODE(COUNT(*), 1, '', '~'||MAX(lv)) v
  FROM (SELECT lv
             , ROWNUM rn
             , lv - ROWNUM grp
          FROM (SELECT lv FROM t ORDER BY lv)
       )
 GROUP BY grp
 ORDER BY lv_from
;

[표 4] 연속된 값을 하나로 합치기

 LV_FROM      LV_TO        CNT V
-------- ---------- ---------- ----------
       1          6          6 1~6
       8          8          1 8
      10         14          5 10~14
      16         21          6 16~21
      26         32          7 26~32
      34         35          2 34~35
      37         40          4 37~40

[리스트 4] 에서는 [리스트 3]에서 구한 GRP 값을 이용하여 GROUP BY 함으로써 연속된 수를 하나의 행으로 합쳐서 보여줄 수 있게 됩니다.

같은 GRP 그룹 내에 MIN(lv) 값이 연속된 수의 시작점이 되며, MAX(lv) 값이 연속된 수가 끝나는 지점이 됩니다. COUNT(*)는 연속된 수의 건수가 되며, 이 3가지 조합(최소값, 최대값, 건수)으로 건수에 따라 데이터를 보여주는 형태를 달리하여 최종 원하는 결과를 도출해 냅니다.

     , MIN(lv) || DECODE(COUNT(*), 1, '', '~'||MAX(lv)) v

1건일때는 최소값만 보여주고, 그렇지 않을 경우에는 최소값에 ‘~’ 과 최대값을 문자열로 연결시켜 V 값을 구했습니다. 자 어떤가요? 이제 첫 번째 조건을 만족하는 SQL을 완성했습니다.

두 번째 조건, 연속된 수가 5개 이상 일때 만 데이터를 합쳐서 보여주어야 합니다. 즉 5건 이상일 때에는 GROUP BY grp 하면 되지만 5건 미만일 때는 grp 로만 GROUP을 짓는다면, 이 또한 하나로 합쳐지므로 이때는 GROUP BY 기준이 달라져야 합니다.

[리스트 5] 건수에 따라 GROUP BY 다르게 지정

SELECT grp grp1
     , cnt
     , CASE WHEN cnt < 5 THEN lv END grp2
     , MIN(lv) || DECODE(COUNT(*), 1, '', '~'||MAX(lv)) v
  FROM (SELECT lv
             , ROWNUM rn
             , lv - ROWNUM grp
             , COUNT(*) OVER(PARTITION BY lv - ROWNUM) cnt
          FROM (SELECT lv FROM t ORDER BY lv)
       )
 GROUP BY grp, cnt, CASE WHEN cnt < 5 THEN lv END
 ORDER BY MIN(lv)
;

[표 5] 건수에 따라 GROUP BY 다르게 지정

   GRP1        CNT       GRP2 V
------- ---------- ---------- ---------
      0          6            1~6
      1          1          8 8
      2          5            10~14
      3          6            16~21
      7          7            26~32
      8          2         34 34
      8          2         35 35
      9          4         37 37
      9          4         38 38
      9          4         39 39
      9          4         40 40

건수에 따라 GROUP BY 기준을 다르게 가져가야 합니다. 그러나 COUNT(*) 는 그룹바이의 결과이므로 그룹바이의 기준으로 삼을 수는 없습니다. 그래서 다음과 같이 분석함수를 이용해 인라인뷰 안에서 미리 건수를 구했습니다.

    , COUNT(*) OVER(PARTITION BY lv - ROWNUM) cnt

인라인뷰 밖에서는 cnt 조건에 따라 그룹바이 기준을 추가했습니다.

 GROUP BY grp, cnt, CASE WHEN cnt < 5 THEN lv END

추가된 조건은 cnt 가 5보다 작은 경우에만 LV 값을 기준으로 하고 있습니다.

[표5]의 결과를 보면 그룹바이 기준인 grp1외에 grp2가 추가된 것을 확인 할 수 있습니다. 이로 인해 기존 grp 만을 기준으로 했을 때 34~35(2건), 37~40(4건)으로 하나로 합쳐져서 표현되던 결과가 34,35 와 37,38,39,40으로 개별 결과로 바뀌었습니다.

GRP 만을 기준으로 하면 하나로 합쳐지겟지만 유일한 값 LV를 추가함으로써 개별로 표현되도록 한 것입니다. 자 어떤가요? 이제 두 번째 조건을 만족하는 SQL을 완성했습니다.

마지막으로 세 번째 조건 4개씩 한행에 표시하기입니다. 이를 위해서는 [표5]의 결과에 순번을 부여한 뒤 이를 이용해 4개씩 잘라서 그룹핑해야 합니다.

[리스트 6] RN 에 따른 행,열 값 확인

SELECT rn, v
     , CEIL(rn / 4) x
     , MOD(rn, 4) y
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY MIN(lv)) rn
             , MIN(lv) || DECODE(COUNT(*), 1, '', '~'||MAX(lv)) v
          FROM (SELECT lv
                     , ROWNUM rn
                     , lv - ROWNUM grp
                     , COUNT(*) OVER(PARTITION BY lv - ROWNUM) cnt
                  FROM (SELECT lv FROM t ORDER BY lv)
               )
         GROUP BY grp, CASE WHEN cnt < 5 THEN lv END
       )
;

[표 6] RN 에 따른 행,열 값 확인

    RN V                   X          Y
------ ---------- ---------- ----------
     1 1~6                 1          1
     2 8                   1          2
     3 10~14               1          3
     4 16~21               1          0
     5 26~32               2          1
     6 34                  2          2
     7 35                  2          3
     8 37                  2          0
     9 38                  3          1
    10 39                  3          2
    11 40                  3          3

[리스트 6]의 쿼리를 보면 V값에 따라 순번 RN을 구하고 이 RN을 이용하여 X 와 Y를 구했습니다.

CEIL(rn / 4) X 는 rn을 4로 나눈 몫을 올림한 정수입니다. MOD(rn, 4) Y 는 rn을 4로 나눈 나머지 값입니다. 이 값 X 는 행을 나누는 기준이 되며, Y 는 열을 나누는 기준이 됩니다.

행을 나누는 기준값 X를 기준으로 GROUP BY 하고, 열을 나누는 기준값 Y를 이용하여 MIN(DECODE()) 하면 최종적으로 정답 쿼리가 완성됩니다.

[리스트 6] 정답 리스트

SELECT MIN(DECODE(MOD(rn, 4), 1, v)) v1
     , MIN(DECODE(MOD(rn, 4), 2, v)) v2
     , MIN(DECODE(MOD(rn, 4), 3, v)) v3
     , MIN(DECODE(MOD(rn, 4), 0, v)) v4
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY MIN(lv)) rn
             , MIN(lv) || DECODE(COUNT(*), 1, '', '~'||MAX(lv)) v
          FROM (SELECT lv
                     , ROWNUM rn
                     , lv - ROWNUM grp
                     , COUNT(*) OVER(PARTITION BY lv - ROWNUM) cnt
                  FROM (SELECT lv FROM t ORDER BY lv)
               )
         GROUP BY grp, CASE WHEN cnt < 5 THEN lv END
       )
 GROUP BY CEIL(rn / 4)
 ORDER BY CEIL(rn / 4)
;

이번 시간에는 하나의 문제 안에 여러 가지 응용문제가 녹아 있는 퀴즈를 풀어보았습니다.

각 세부 요구조건에 대한 해법을 살펴보았으며, 이를 통합하여 전체 문제를 해결해 나가는 과정을 살펴보았습니다. 이번 퀴즈를 통해 배우는 SQL시간이 복잡한 문제에 대한 해결 능 력을 키우는 계기가 되었으면 합니다.