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

이번 퀴즈로 배워보는 SQL 시간에는 당구장의 당구대 운영시간을 분석하는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.

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

문제

마농군은 당구대 4대를 보유한 작은 규모의 마소당구장를 운영하고 있습니다

마농군은 당구장에 기록된 시간정보를 활용하여 당구대의 운영현황을 분석하고, 이 정보를 바탕으로 당구장 매출 감소의 원인을 분석하고 매출 증대 방안을 수립해 보려고 합니다.

당구대 운영 시간정보 테이블은 당구대 번호(NO), 시작시간(STM), 종료시간(ETM) 항목으로 구성되어 있습니다.

<표 1> 의 원본테이블로부터 시작시간과 종료시간이 서로 겹치는 부분들을 별도의 시간대(시작~종료)로 분리하고, 분리된 시간대별로 동시 운영되는 당구대의 개수(CNT)와 운영시간(USE_TIME)을 구한뒤, 동시 운영 당구대 개수별로 운영시간의 합계를 함께 표현하는 문제입니다.

당구장의 영업시간은 11시부터 9시까지입니다.

[리스트 1] 당구대 운영 테이블

CREATE TABLE t
AS
SELECT 1 no, '11:30' stm, '12:30' etm FROM dual
UNION ALL SELECT 2, '12:30', '13:40' FROM dual
UNION ALL SELECT 3, '12:50', '13:30' FROM dual
UNION ALL SELECT 4, '13:00', '14:20' FROM dual
UNION ALL SELECT 1, '13:30', '14:20' FROM dual
UNION ALL SELECT 3, '15:00', '16:00' FROM dual
UNION ALL SELECT 1, '15:30', '16:30' FROM dual
UNION ALL SELECT 2, '15:30', '16:30' FROM dual
UNION ALL SELECT 4, '15:30', '16:50' FROM dual
UNION ALL SELECT 1, '17:00', '18:00' FROM dual
UNION ALL SELECT 3, '17:30', '18:30' FROM dual
UNION ALL SELECT 4, '17:30', '19:00' FROM dual
UNION ALL SELECT 2, '18:00', '19:00' FROM dual
UNION ALL SELECT 3, '19:30', '20:30' FROM dual
;

SELECT * FROM t
 ORDER BY stm, no
;

[표 1] 원본 테이블

    NO STM        ETM
------ ---------- ---------
     1 11:30      12:30
     2 12:30      13:40
     3 12:50      13:30
     4 13:00      14:20
     1 13:30      14:20
     3 15:00      16:00
     1 15:30      16:30
     2 15:30      16:30
     4 15:30      16:50
     1 17:00      18:00
     3 17:30      18:30
     4 17:30      19:00
     2 18:00      19:00
     3 19:30      20:30

[표 2] 결과 테이블

STM        ETM               CNT   USE_TIME
---------- ---------- ---------- ----------
11:00      11:30               0         30
11:30      12:30               1         60
12:30      12:50               1         20
12:50      13:00               2         10
13:00      13:30               3         30
13:30      13:40               3         10
13:40      14:20               2         40
14:20      15:00               0         40
15:00      15:30               1         30
15:30      16:00               4         30
16:00      16:30               3         30
16:30      16:50               1         20
16:50      17:00               0         10
17:00      17:30               1         30
17:30      18:00               3         30
18:00      18:30               3         30
18:30      19:00               2         30
19:00      19:30               0         30
19:30      20:30               1         60
20:30      21:00               0         30
                               0        140
                               1        220
                               2         80
                               3        130
                               4         30

문제설명

<표 2>의 비고(문제설명) 부분을 참조해 설명하겠습니다. 영업 시작시간인 11시부터 첫 운영 개시 시간인 1번 시작시간 11시30분까지의 시간을 나타냅니다.

이 시간동안 운영된 당구대 개수는 0이며 경과시간은 30분입니다. 11시30분에 1번이 시작해 12시30분에 종료됩니다. 즉, 2번째 행은 1번 당구대 1대가 60분동안 운영된다는 것을 나타냅니다.

3행에서 12시30분에 1번 종료후 2번 시작이 되며 4행에서 12시50분에 3번 시작이 되며, 바로 이 4행의 시작시간이 3행의 종료시간이 됩니다.

3행에서는 2번 당구대 한 대가 운영중이며, 4행에서는 2번이 끝나지 않았으므로 2번, 3번 두 대의 당구대가 동시에 운영됩니다.

5행에서는 4번 당구대가 시작되어 운영 당구대는 총 세 대가 됩니다. 6행에서는 3번이 종료되지만 1번이 시작하므로 운영당구대 대수에는 변화가 없습니다.

9행에서 3번 시작후 10행에서 1,2,4번 동시 시작해 모든 당구대(1,2,3,4)가 운영되게 됩니다. 4대가 모두 운영되는 시간은 15시30분부터 16시까지가 됩니다.

20행은 마지막 운영 종료시간인 20시30분을 시작으로 영업종료시간인 21시까지를 종료시간으로 계산합니다. 21행부터는 동시운영 당구대 개수별 운영시간 합계를 나타냅니다.

정답

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

[리스트 2] 정답 리스트

SELECT stm, etm, cnt
     , SUM( TO_DATE(etm, 'hh24:mi')
          - TO_DATE(stm, 'hh24:mi')
          )*24*60 use_time 
  FROM (SELECT stm
             , SUM(cnt) OVER(ORDER BY stm) cnt
             , LEAD(stm, 1, '21:00')
               OVER(ORDER BY stm) etm
          FROM (SELECT '11:00' stm, 0 cnt FROM dual
                 UNION ALL
                SELECT DECODE(s, 1, stm, etm) stm
                     , SUM(s) cnt
                  FROM t
                     , (SELECT 3-LEVEL*2 s FROM dual
                         CONNECT BY LEVEL < = 2)
                 GROUP BY DECODE(s, 1, stm, etm)
                )
        )
 GROUP BY cnt, ROLLUP((stm, etm))
 ORDER BY stm, cnt
;

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

해설

이번 문제는 선분이력(시작~종료) 형태의 자료를 겹치는 구간을 쪼개 새로운 형태의 선분이력으로 변환하는 문제입니다.

선분형태에서 바로 선분형태로 변경하기는 힘듭니다. 그래서 선분이력을 점이력(이벤트 발생 시각) 형태로 변형한 뒤 이를 다시 선분이력 형태로 변형하는 방법으로 문제에 접근하겠습니다.

[리스트 3] 점이력

SELECT DECODE(s, 1, stm, etm) stm
     , no, s
  FROM t
     , (SELECT 3-LEVEL*2 s FROM dual
        CONNECT BY LEVEL < = 2)
 ORDER BY stm, s
;

[표 3] 점이력 결과

STM                NO          S
---------- ---------- ----------
11:30               1          1
12:30               1         -1
12:30               2          1
12:50               3          1
13:00               4          1
13:30               3         -1
13:30               1          1
13:40               2         -1
14:20               1         -1
14:20               4         -1
15:00               3          1
15:30               2          1
15:30               1          1
15:30               4          1
16:00               3         -1
16:30               1         -1
16:30               2         -1
16:50               4         -1
17:00               1          1
17:30               4          1
17:30               3          1
18:00               1         -1
18:00               2          1
18:30               3         -1
19:00               2         -1
19:00               4         -1
19:30               3          1
20:30               3         -1

<리스트 3>의 쿼리를 수행해 <표 3>의 결과를 얻었습니다. <표 3>은 선분이력을 점이력으로 바꾸는 구문입니다.

시작시간과 종료시간 두 개의 항목이 발생?으로 +1(시작)과 -1(종료)를 사용했습니다. +1은 1대가 운영 대수가 1개 증가함을 의미하며 -1은 1대 감소함을 의미합니다. 중복되는 시간은 하나로 합쳐보도록 하겠습니다.

[리스트 4] 중복제거

SELECT '11:00' stm, 0 cnt FROM dual
 UNION ALL
SELECT DECODE(s, 1, stm, etm) stm
     , SUM(s) cnt
  FROM t
     , (SELECT 3-LEVEL*2 s FROM dual
        CONNECT BY LEVEL < = 2)
 GROUP BY DECODE(s, 1, stm, etm)
 ORDER BY stm
;

[표 4] 중복제거 결과

STM               CNT
---------- ----------
11:00               0
11:30               1
12:30               0
12:50               1
13:00               1
13:30               0
13:40              -1
14:20              -2
15:00               1
15:30               3
16:00              -1
16:30              -2
16:50              -1
17:00               1
17:30               2
18:00               0
18:30              -1
19:00              -2
19:30               1
20:30              -1

<리스트 4>의 쿼리를 수행해 <표 4>의 결과를 얻었습니다. 시간으로 GROUP BY하고 구분값을 합산(SUM)했습니다. 영업개시시간을 UNION ALL로 추가했습니다.

[리스트 5] 분석함수

SELECT stm, cnt
     , SUM(cnt) OVER(ORDER BY stm) cnt1
     , LEAD(stm, 1, '21:00') OVER(ORDER BY stm) etm
  FROM (SELECT '11:00' stm, 0 cnt FROM dual
         UNION ALL
        SELECT DECODE(s, 1, stm, etm) stm
             , SUM(s) cnt
          FROM t
             , (SELECT 3-LEVEL*2 s FROM dual
                CONNECT BY LEVEL < = 2)
         GROUP BY DECODE(s, 1, stm, etm)
         ORDER BY stm
        )
;

[표 5] 분석함수 결과

STM               CNT       CNT1 ETM
---------- ---------- ---------- ---------
11:00               0          0 11:30
11:30               1          1 12:30
12:30               0          1 12:50
12:50               1          2 13:00
13:00               1          3 13:30
13:30               0          3 13:40
13:40              -1          2 14:20
14:20              -2          0 15:00
15:00               1          1 15:30
15:30               3          4 16:00
16:00              -1          3 16:30
16:30              -2          1 16:50
16:50              -1          0 17:00
17:00               1          1 17:30
17:30               2          3 18:00
18:00               0          3 18:30
18:30              -1          2 19:00
19:00              -2          0 19:30
19:30               1          1 20:30
20:30              -1          0 21:00

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

LEAD 함수를 이용해 다음행의 시간을 가져옵니다. 마지막 행은 다음행이 없으므로 영업종료시간이 21:00 으로 대체합니다.

앞에서 구한 CNT 값을 순차적으로 누적 합산해 CNT1 값을 구했습니다. 이 값이 바로 이 시간대에 동시 운영되는 당구대의 대수가 됩니다.

어떻게 이렇게 간단하게 결과가 도출되는지 선뜻 이해하기 힘들 것입니다. 이해를 돕기 위해 <표 2>에서 사용했던 비고항목을 <표 5>에서 다시 사용했습니다. +1은 운영대수가 1대 늘어나는 것이고 -1은 운영대수가 1대 줄어드는 것이죠.

이 값을 시간의 흐름에 따라 누적합산하게 되면, 해당 시점의 동시운영대수를 알 수 있습니다.

[리스트 6] Group By

SELECT cnt
     , SUM( TO_DATE(etm, 'hh24:mi')
          - TO_DATE(stm, 'hh24:mi')
          )*24*60 tm 
  FROM (SELECT stm
             , SUM(cnt) OVER(ORDER BY stm) cnt
             , LEAD(stm, 1, '21:00')
               OVER(ORDER BY stm) etm
          FROM (SELECT '11:00' stm, 0 cnt FROM dual
                 UNION ALL
                SELECT DECODE(s, 1, stm, etm) stm
                     , SUM(s) cnt
                  FROM t
                     , (SELECT 3-LEVEL*2 s FROM dual
                         CONNECT BY LEVEL < = 2)
                 GROUP BY DECODE(s, 1, stm, etm)
                )
        )
 GROUP BY cnt
 ORDER BY cnt
;

[표 6] Group By 결과

    CNT         TM
------- ----------
      0        140
      1        220
      2         80
      3        130
      4         30

리스트 6>의 쿼리를 수행해 <표 6>의 결과를 얻었습니다. 동시운영대수별 운영시간합계를 구하는 부분입니다. 동시운영대수를 기준으로 GROUP BY 운영시간을 합산하면 합계가 나오게 됩니다.

마지막으로 <표 5>의 결과와 <표 6>의 결과를 함께 보여줘야 하는데요. UNION ALL을 이용해 합집합으로 만들 수도 있겠으나 ROLLUP 기능을 이용하면 간단합니다.

[리스트 7] Rollup
```
 GROUP BY cnt, ROLLUP((stm, etm))
  
```

마지막으로 GROUP BY ROLLUP 을 통해 <리스트 2>의 정답 쿼리가 완성되었습니다.

이번 퀴즈의 풀이 절차를 정리해 보면 다음과 같습니다.

1) 선분이력을 시작과 종료 이벤트별 점이력 형태로 바꾸고 구분값으로 +1(시작)과 -1(종료)를 사용한다.

2) 점이력 데이터 중 시간이 중복되는 자료는 구분값을 합산한다.

3) 시간의 흐름에 따라 구분값을 누적합산하고 LEAD 함수를 이용해 종료시간(다음행 시작시간)을 가져온다.

4) GROUP BY, ROLLUP을 이용해 최종 결과를 도출한다.