안녕하세요. 쿼리를 짜다 도저히 안될거 같아 문의 드립니다. 샘플 데이터는 아래와 같고 select to_date('2021/08/02 00:00:05','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 7 sec from dual union all select to_date('2021/08/02 00:00:12','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 0 sec from dual union all select to_date('2021/08/02 00:00:12','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 0 sec from dual union all select to_date('2021/08/02 00:00:12','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 3 sec from dual union all select to_date('2021/08/02 00:00:15','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 4 sec from dual union all select to_date('2021/08/02 00:00:19','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 1 sec from dual union all select to_date('2021/08/02 00:00:20','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 1 sec from dual union all select to_date('2021/08/02 00:00:21','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 1 sec from dual union all select to_date('2021/08/02 00:00:22','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 2 sec from dual union all select to_date('2021/08/02 00:00:24','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 2 sec from dual union all select to_date('2021/08/02 00:00:26','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 0 sec from dual union all select to_date('2021/08/02 00:00:26','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 0 sec from dual union all select to_date('2021/08/02 00:00:26','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 3 sec from dual union all select to_date('2021/08/02 00:00:29','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 3 sec from dual
데이터는 시간순으로 정렬이 된 상태이며 sec컬럼은 다음 row의 시간차이 입니다. 그룹을 매기는 조건은 시간차가 3초 이상 나면 새로운 그룹id 발급
sec이 3초이하면 sec을 합산해서 3초 이상이 되는 데이터까지 그룹으로 묶어 그룹id 발급 이 조건으로 생성 됩니다. 임의로 데이터를 만들면 아래와 같이 gid가 생성 됩니다.
select to_date('2021/08/02 00:00:05','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 7 sec, 1 gid from dual union all select to_date('2021/08/02 00:00:12','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 0 sec, 2 gid from dual union all select to_date('2021/08/02 00:00:12','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 0 sec, 2 gid from dual union all select to_date('2021/08/02 00:00:12','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 3 sec, 2 gid from dual union all select to_date('2021/08/02 00:00:15','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 4 sec, 3 gid from dual union all select to_date('2021/08/02 00:00:19','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 1 sec, 4 gid from dual union all select to_date('2021/08/02 00:00:20','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 1 sec, 4 gid from dual union all select to_date('2021/08/02 00:00:21','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 1 sec, 4 gid from dual union all select to_date('2021/08/02 00:00:22','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 2 sec, 5 gid from dual union all select to_date('2021/08/02 00:00:24','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 2 sec, 5 gid from dual union all select to_date('2021/08/02 00:00:26','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 0 sec, 6 gid from dual union all select to_date('2021/08/02 00:00:26','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 0 sec, 6 gid from dual union all select to_date('2021/08/02 00:00:26','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 3 sec, 6 gid from dual union all select to_date('2021/08/02 00:00:29','yyyy/mm/dd/ hh24:mi:ss') REQ_DATE, 3 sec, 7 gid from dual
위에 같이 gid를 부여하려면 어떻게 쿼리를 짜면 좋을까요?
WITH t AS ( SELECT TO_DATE('2021/08/02 00:00:05', 'yyyy/mm/dd/ hh24:mi:ss') req_date, 7 sec FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:12', 'yyyy/mm/dd/ hh24:mi:ss'), 0 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:12', 'yyyy/mm/dd/ hh24:mi:ss'), 0 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:12', 'yyyy/mm/dd/ hh24:mi:ss'), 3 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:15', 'yyyy/mm/dd/ hh24:mi:ss'), 4 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:19', 'yyyy/mm/dd/ hh24:mi:ss'), 1 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:20', 'yyyy/mm/dd/ hh24:mi:ss'), 1 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:21', 'yyyy/mm/dd/ hh24:mi:ss'), 1 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:22', 'yyyy/mm/dd/ hh24:mi:ss'), 2 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:24', 'yyyy/mm/dd/ hh24:mi:ss'), 2 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:26', 'yyyy/mm/dd/ hh24:mi:ss'), 0 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:26', 'yyyy/mm/dd/ hh24:mi:ss'), 0 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:26', 'yyyy/mm/dd/ hh24:mi:ss'), 3 FROM dual UNION ALL SELECT TO_DATE('2021/08/02 00:00:29', 'yyyy/mm/dd/ hh24:mi:ss'), 3 FROM dual ) , t1 AS ( SELECT ROWNUM rn , req_date, sec FROM t ) , t2(rn, req_date, sec, gid, s) AS ( SELECT rn, req_date, sec , 1 gid , sec s FROM t1 WHERE rn = 1 UNION ALL SELECT a.rn, a.req_date, a.sec , CASE WHEN s >= 3 THEN 1 ELSE 0 END + b.gid gid , CASE WHEN s >= 3 THEN 0 ELSE s END + a.sec s FROM t1 a , t2 b WHERE a.rn = b.rn + 1 ) SELECT rn, req_date, sec, gid FROM t2 ;