데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

임시테이블을 사용하지 않고, 작업을 수행하도록 구성하다보니, 인라인뷰가 너무 많이 사용되는 느낌입니다.
아직 수준이 일천한지라 이정도에서 만족해주시길....

(창이 작아서 보시기에 불편하실겁니다. 그대로 복사하셔서 일반에티터에서 확인하시길....)

◆ 샘플테이블 생성

CREATE TABLE CARGO
(
    CARNO       VARCHAR2(10),
    CDATE       DATE,
    CUNIT       NUMBER
)

◆ 샘플데이터 로드

INSERT INTO CARGO VALUES ('1111',TO_DATE('2005-01-19 09:50','YYYY-MM-DD HH24-MI'), 1);
INSERT INTO CARGO VALUES ('1111',TO_DATE('2005-01-19 09:52','YYYY-MM-DD HH24-MI'), 3);
INSERT INTO CARGO VALUES ('1111',TO_DATE('2005-01-19 09:57','YYYY-MM-DD HH24-MI'), 2);
INSERT INTO CARGO VALUES ('3333',TO_DATE('2005-01-19 09:57','YYYY-MM-DD HH24-MI'), 5);
INSERT INTO CARGO VALUES ('2222',TO_DATE('2005-01-19 09:58','YYYY-MM-DD HH24-MI'), 1);
INSERT INTO CARGO VALUES ('2222',TO_DATE('2005-01-19 10:00','YYYY-MM-DD HH24-MI'), 4);
INSERT INTO CARGO VALUES ('2222',TO_DATE('2005-01-19 10:02','YYYY-MM-DD HH24-MI'), 7);
INSERT INTO CARGO VALUES ('4444',TO_DATE('2005-01-19 11:30','YYYY-MM-DD HH24-MI'), 2);
INSERT INTO CARGO VALUES ('4444',TO_DATE('2005-01-19 11:32','YYYY-MM-DD HH24-MI'), 3);
INSERT INTO CARGO VALUES ('1111',TO_DATE('2005-01-19 11:30','YYYY-MM-DD HH24-MI'), 8);
INSERT INTO CARGO VALUES ('1111',TO_DATE('2005-01-19 11:32','YYYY-MM-DD HH24-MI'), 2);
INSERT INTO CARGO VALUES ('1111',TO_DATE('2005-01-19 11:35','YYYY-MM-DD HH24-MI'), 3);

◆ 작업시퀄

SELECT MIN(CARNO), MAX(CDATE), SUM(CUNIT)
FROM (
        SELECT RNUM, CARNO, CDATE, CUNIT, B_RNUM, B_CARNO, B_CDATE, TERM, GRPID, B_GRPID,
                CASE WHEN CARNO = B_CARNO AND (CDATE - B_CDATE) * 24 * 60 < 10 THEN B_GRPID
                                                                             ELSE GRPID      END L_GRPID
        FROM (
                SELECT RNUM, CARNO, CDATE, CUNIT, B_RNUM, B_CARNO, B_CDATE, TERM, GRPID, LAG(GRPID) OVER(ORDER BY RNUM) B_GRPID
                FROM (
                        SELECT RNUM, CARNO, CDATE, CUNIT, B_RNUM, B_CARNO, B_CDATE, (CDATE - B_CDATE) * 24 * 60 TERM,
                                CASE WHEN CARNO = B_CARNO AND (CDATE - B_CDATE) * 24 * 60 < 10 THEN B_RNUM
                                                                                                ELSE RNUM    END GRPID
                        FROM (
                                SELECT RNUM, CARNO, CDATE, CUNIT, LAG(RNUM) OVER(ORDER BY RNUM) B_RNUM, LAG(CARNO) OVER(ORDER BY RNUM) B_CARNO, LAG(CDATE) OVER(ORDER BY RNUM) B_CDATE
                                FROM (
                                        SELECT ROW_NUMBER() OVER(ORDER BY CARNO,CDATE) RNUM, CARNO, CDATE, CUNIT
                                        FROM    CARGO
                                      )
                              )
                      )
              )
      )
GROUP BY L_GRPID

◆ 작업결과

CARNO   CDATE                       CUNIT
=========================================
1111    2005-01-19 오전 9:57:00     6
1111    2005-01-19 오전 11:35:00    13
2222    2005-01-19 오전 10:02:00    12
3333    2005-01-19 오전 9:57:00     5
4444    2005-01-19 오전 11:32:00    5

◆ 인라인뷰 단계별 결과(작업시퀄의 내부단위부터 실행한 결과입니다)

1'ST ROW_NUMBER()을 이용하여 차량번호와 출고일시순서로 일련번호를 부여합니다.(입력된 순서로 하기에는 자료의 정합성이 우려가 되므로...)

  RNUM    CARNO   CDATE                       CUNIT
  =================================================
        1       1111    2005-01-19 오전 9:50:00     1
        2       1111    2005-01-19 오전 9:52:00     3
        3       1111    2005-01-19 오전 9:57:00     2
        4       1111    2005-01-19 오전 11:30:00    8
        5       1111    2005-01-19 오전 11:32:00    2
        6       1111    2005-01-19 오전 11:35:00    3
        7       2222    2005-01-19 오전 9:58:00     1
        8       2222    2005-01-19 오전 10:00:00    4
        9       2222    2005-01-19 오전 10:02:00    7
        10      3333    2005-01-19 오전 9:57:00     5
        11      4444    2005-01-19 오전 11:30:00    2
        12      4444    2005-01-19 오전 11:32:00    3

2'ND LAG()를 이용하여, 직전 ROW의 차량번호, 출고일시를 확인합니다.(B_~가 직전ROW의 값입니다)

  RNUM    CARNO   CDATE                       CUNIT   B_RNUM B_CARNO B_CDATE
  ===========================================================================================
        1       1111    2005-01-19 오전 9:50:00     1
        2       1111    2005-01-19 오전 9:52:00     3       1       1111    2005-01-19 오전 9:50:00
        3       1111    2005-01-19 오전 9:57:00     2       2       1111    2005-01-19 오전 9:52:00
        4       1111    2005-01-19 오전 11:30:00    8       3       1111    2005-01-19 오전 9:57:00
        5       1111    2005-01-19 오전 11:32:00    2       4       1111    2005-01-19 오전 11:30:00
        6       1111    2005-01-19 오전 11:35:00    3       5       1111    2005-01-19 오전 11:32:00
        7       2222    2005-01-19 오전 9:58:00     1       6       1111    2005-01-19 오전 11:35:00
        8       2222    2005-01-19 오전 10:00:00    4       7       2222    2005-01-19 오전 9:58:00
        9       2222    2005-01-19 오전 10:02:00    7       8       2222    2005-01-19 오전 10:00:00
        10      3333    2005-01-19 오전 9:57:00     5       9       2222    2005-01-19 오전 10:02:00
        11      4444    2005-01-19 오전 11:30:00    2       10      3333    2005-01-19 오전 9:57:00
        12      4444    2005-01-19 오전 11:32:00    3       11      4444    2005-01-19 오전 11:30:00

3'RD    직전ROW의 값과 비교하여, 차량번호가 같고, 시간차이가 10분이내인 건을 동일그룹으로 설정합니다.
        이때 시간차이가 10분이내인 것은 (CDATE - B_CDATE) * 24 * 60 < 10 에서 처리되며, 만일 시간배정을 바꾸실 경우에는 이 값을 조정하시면 됩니다.
        동일그룹으로 설정한다는 것은 직전ROW의 RNUM을 부여하는 것을 의미합니다.

  RNUM    CARNO   CDATE                       CUNIT   B_RNUM B_CARNO B_CDATE                     TERM    GRPID
  =============================================================================================================
        1       1111    2005-01-19 오전 9:50:00     1                                                           1
        2       1111    2005-01-19 오전 9:52:00     3       1       1111    2005-01-19 오전 9:50:00     2       1
        3       1111    2005-01-19 오전 9:57:00     2       2       1111    2005-01-19 오전 9:52:00     5       2
        4       1111    2005-01-19 오전 11:30:00    8       3       1111    2005-01-19 오전 9:57:00     93      4
        5       1111    2005-01-19 오전 11:32:00    2       4       1111    2005-01-19 오전 11:30:00    2       4
        6       1111    2005-01-19 오전 11:35:00    3       5       1111    2005-01-19 오전 11:32:00    3       5
        7       2222    2005-01-19 오전 9:58:00     1       6       1111    2005-01-19 오전 11:35:00    -97     7
        8       2222    2005-01-19 오전 10:00:00    4       7       2222    2005-01-19 오전 9:58:00     2       7
        9       2222    2005-01-19 오전 10:02:00    7       8       2222    2005-01-19 오전 10:00:00    2       8
        10      3333    2005-01-19 오전 9:57:00     5       9       2222    2005-01-19 오전 10:02:00    -5      10
        11      4444    2005-01-19 오전 11:30:00    2       10      3333    2005-01-19 오전 9:57:00     93      11
        12      4444    2005-01-19 오전 11:32:00    3       11      4444    2005-01-19 오전 11:30:00    2       11

4'TH    동일차량에 대한 출고가 3건까지 가능하다고 하셨으면 (2)~(3)의 작업을 한번 더 수행합니다. 다만, 이때는 RNUM 대신에
        그룹아이디(GRPID)에 대하여 LAG()를 적용합니다.

  RNUM    CARNO   CDATE                       CUNIT   B_RNUM B_CARNO B_CDATE                     TERM    GRPID   B_GRPID
  =======================================================================================================================
        1       1111    2005-01-19 오전 9:50:00     1                                                           1
        2       1111    2005-01-19 오전 9:52:00     3       1       1111    2005-01-19 오전 9:50:00     2       1       1
        3       1111    2005-01-19 오전 9:57:00     2       2       1111    2005-01-19 오전 9:52:00     5       2       1
        4       1111    2005-01-19 오전 11:30:00    8       3       1111    2005-01-19 오전 9:57:00     93      4       2
        5       1111    2005-01-19 오전 11:32:00    2       4       1111    2005-01-19 오전 11:30:00    2       4       4
        6       1111    2005-01-19 오전 11:35:00    3       5       1111    2005-01-19 오전 11:32:00    3       5       4
        7       2222    2005-01-19 오전 9:58:00     1       6       1111    2005-01-19 오전 11:35:00    -97     7       5
        8       2222    2005-01-19 오전 10:00:00    4       7       2222    2005-01-19 오전 9:58:00     2       7       7
        9       2222    2005-01-19 오전 10:02:00    7       8       2222    2005-01-19 오전 10:00:00    2       8       7
        10      3333    2005-01-19 오전 9:57:00     5       9       2222    2005-01-19 오전 10:02:00    -5      10      8
        11      4444    2005-01-19 오전 11:30:00    2       10      3333    2005-01-19 오전 9:57:00     93      11      10
        12      4444    2005-01-19 오전 11:32:00    3       11      4444    2005-01-19 오전 11:30:00    2       11      11

5'TH 최종적으로 L_GRPID 가 설정됩니다. 결과적으로 동일차량에 대한 출고간격이 10분이내인 건을 동일 그룹으로 설정하게 됩니다.

  RNUM    CARNO   CDATE                       CUNIT   B_RNUM B_CARNO B_CDATE                     TERM    GRPID   B_GRPID L_GRPID
  ===============================================================================================================================
        1       1111    2005-01-19 오전 9:50:00     1                                                           1               1
        2       1111    2005-01-19 오전 9:52:00     3       1       1111    2005-01-19 오전 9:50:00     2       1       1       1
        3       1111    2005-01-19 오전 9:57:00     2       2       1111    2005-01-19 오전 9:52:00     5       2       1       1
        4       1111    2005-01-19 오전 11:30:00    8       3       1111    2005-01-19 오전 9:57:00     93      4       2       4
        5       1111    2005-01-19 오전 11:32:00    2       4       1111    2005-01-19 오전 11:30:00    2       4       4       4
        6       1111    2005-01-19 오전 11:35:00    3       5       1111    2005-01-19 오전 11:32:00    3       5       4       4
        7       2222    2005-01-19 오전 9:58:00     1       6       1111    2005-01-19 오전 11:35:00    -97     7       5       7
        8       2222    2005-01-19 오전 10:00:00    4       7       2222    2005-01-19 오전 9:58:00     2       7       7       7
        9       2222    2005-01-19 오전 10:02:00    7       8       2222    2005-01-19 오전 10:00:00    2       8       7       7
        10      3333    2005-01-19 오전 9:57:00     5       9       2222    2005-01-19 오전 10:02:00    -5      10      8       10
        11      4444    2005-01-19 오전 11:30:00    2       10      3333    2005-01-19 오전 9:57:00     93      11      10      11
        12      4444    2005-01-19 오전 11:32:00    3       11      4444    2005-01-19 오전 11:30:00    2       11      11      11

LAST 최종그룹아이디(L_GRPID)를 적용하여 GROUP BY를 수행합니다.

  CARNO   CDATE                       CUNIT
        =========================================
        1111    2005-01-19 오전 9:57:00     6
        1111    2005-01-19 오전 11:35:00    13
        2222    2005-01-19 오전 10:02:00    12
        3333    2005-01-19 오전 9:57:00     5
        4444    2005-01-19 오전 11:32:00    5

지니가며님이 2005-01-21 10:05에 작성한 댓글입니다. Edit Ｘ