데이터베이스 질문과 답변
안녕하세요. 쿼리를 짜다 도저히 안될거 같아 문의 드립니다.
샘플 데이터는 아래와 같고
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를 부여하려면 어떻게 쿼리를 짜면 좋을까요?
by 마농
[2021.10.28 07:48:57]
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
;
by 지송요
[2021.10.28 10:53:26]
감사합니다. 또 배워 갑니다.
) 버튼을 클릭하여 작성 하시면 됩니다.