[SQL] Oracle 선분이력 테이블 조회 방법(MAX 분석 함수, WITH 절, MODEL 절)

선분 이력 테이블을 작업하다 보면 겹치는 부분에 대해서 병합하고, 겹치지 않는 구간에 대해서는 그대로 남겨두는 방식을 많이 하게 됩니다. 사실 선분 이력 테이블을 제대로 관리하려면 종료 시점과 시작 시점이 끊어지지 않게 상호 관리하는 것이 바람직하지만, 보편적으로 관리의 어려움이 있다 보니 여러 데이터를 쌓아 놓고 비지니스 로직(SQL)로 해결 하는 경우가 많을 것입니다.

1. 선분이력 데이터 보기

아래는 사용자 별로 이벤트 시작 일시와 이벤트 종료 일시에 대해서 표로 작성했습니다. 파란색 부분은 병합이 되지 않는 구간이고, 노란색 부분은 병합이 되는 부분입니다.

병합 그룹 번호를 부여한 이유는 이벤트 시작 일시과 이벤트 종료 일시를 연결하고자 하는 것입니다. 즉, 2013/08/12 오후 4:45:00 ~ 2013/08/12 오후 8:00:00와 같이 병합 후 최근 시간과 최종 시간을 나열하게 됩니다.

기본적인 이론에 대한 부분은 표를 보시고 정리하시기 바랍니다.

2. 테이블 생성 및 데이터 생성

/* EST_USER_EVENT 테이블 생성 */
CREATE TABLE EST_USER_EVENT(USER_ID       VARCHAR2(20) NOT NULL
                           ,EVENT_START   DATE NOT NULL
                           ,EVENT_END     DATE NOT NULL
                           );

/* EST_USER_EVENT 인덱스 생성 */
CREATE UNIQUE INDEX EST_USER_EVENT_PK 
ON EST_USER_EVENT(USER_ID
                 ,EVENT_START
                 ,EVENT_END
                 )
;

/* EST_USER_EVENT PK 제약조건 생성 */
ALTER TABLE EST_USER_EVENT ADD (
  CONSTRAINT EST_USER_EVENT_PK
  PRIMARY KEY
  (USER_ID, EVENT_START, EVENT_END)
  USING INDEX EST_USER_EVENT_PK
  ENABLE VALIDATE);


/* EST_USER_EVENT 데이터 생성 */
SET DEFINE OFF;
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/12/2013 16:45:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/12/2013 18:55:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST01');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/12/2013 18:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/12/2013 20:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST01');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/12/2013 22:45:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/12/2013 23:55:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST01');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/13/2013 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/14/2013 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST01');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/14/2013 09:00:01', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/14/2013 11:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST01');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/14/2013 11:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/14/2013 18:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST01');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/14/2013 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/14/2013 20:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST02');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/14/2013 11:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/14/2013 18:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST02');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/14/2013 20:00:10', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/14/2013 21:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST02');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/14/2013 21:10:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/14/2013 22:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST02');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/14/2013 22:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/15/2013 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST02');
Insert into STG.EST_USER_EVENT
   (EVENT_START, EVENT_END, USER_ID)
 Values
   (TO_DATE('08/15/2013 08:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/15/2013 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'TEST02');
COMMIT;

3. MAX 분석 함수를 활용한 데이터 조회

/* MAX 분석 함수 활용 방법 */
SELECT    A.USER_ID
         ,MIN(A.EVENT_START) AS EVENT_START
         ,MAX(A.EVENT_END)   AS EVENT_END
FROM      (
           SELECT    A.USER_ID
                    ,A.EVENT_START
                    ,A.EVENT_END
                    ,SUM(A.FLAG) OVER (PARTITION BY A.USER_ID ORDER BY A.EVENT_START) GRP_NO
           FROM      (
                      SELECT    A.USER_ID
                               ,A.EVENT_START
                               ,A.EVENT_END
                               ,CASE
                                  WHEN EVENT_START >
                                         MAX(A.EVENT_END) OVER (PARTITION BY A.USER_ID ORDER BY A.USER_ID, A.EVENT_START ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
                                  THEN 1
                                  ELSE 0
                                END AS FLAG
                      FROM      EST_USER_EVENT A
                      ) A
          ) A
GROUP BY  A.USER_ID, A.GRP_NO
ORDER BY  A.USER_ID, MIN(A.EVENT_START)
;

4. WITH 절을 이용한 조회 방법(MAX 분석 함수 + RANGE)

/* WITH + MAX 분석함수(RANGE) */
WITH W_EST_USER_EVENT
     AS (SELECT    A.USER_ID
                  ,A.EVENT_START
                  ,A.EVENT_END
                  ,CASE
                     WHEN EVENT_START >
                            MAX(EVENT_END) OVER (PARTITION BY A.USER_ID ORDER BY A.EVENT_START RANGE BETWEEN UNBOUNDED PRECEDING AND 1E-6 PRECEDING )
                     THEN 1
                     ELSE 0
                   END AS FLAG
         FROM      EST_USER_EVENT A)
     ,W_GRP_NO 
     AS ( SELECT    A.USER_ID
                   ,A.EVENT_START
                   ,A.EVENT_END
                   ,SUM(A.FLAG) OVER (PARTITION BY A.USER_ID ORDER BY A.EVENT_START) AS GRP_ID
          FROM      W_EST_USER_EVENT A
          )
SELECT    USER_ID, MIN(A.EVENT_START) AS EVENT_START, MAX(A.EVENT_END) AS EVENT_END
FROM      W_GRP_NO A
GROUP BY  A.USER_ID, GRP_ID
ORDER BY  A.USER_ID, MIN(A.EVENT_START);

5. MODEL절 조회 방법

/* MODEL절 활용 방법 */
SELECT    A.USER_ID
         ,MIN(EVENT_START) AS EVENT_START
         ,MAX(EVENT_END)   AS EVENT_END
FROM      (
          SELECT    USER_ID
                   ,EVENT_START
                   ,EVENT_END
                   ,FLAG
          FROM      EST_USER_EVENT
          MODEL
          PARTITION BY (USER_ID)
          DIMENSION BY (ROW_NUMBER() OVER(ORDER BY USER_ID, EVENT_START, EVENT_END)  RNO)
          MEASURES     (EVENT_START, EVENT_END, 0 FLAG)
          RULES        (
                       FLAG[ANY] = CASE WHEN EVENT_START[CV()] > EVENT_END[CV()-1]
                                        THEN NVL(FLAG[CV()-1], 0) + 1
                                        ELSE NVL(FLAG[CV()-1], 0)
                                   END
                       )
         ) A
GROUP BY A.USER_ID, A.FLAG
ORDER BY A.USER_ID, MIN(A.EVENT_START);

6. 최종 결과