데이터베이스 질문과 답변
안녕하세요 3년차 개발자입니다
DB에서 데이터를 가져와야 하는데, 도저히 제 머리로는 생각이 안돼서 이렇게 질문드립니다!
| id | contact_type | state | start | end |
| A | CHAT | NOT_READY | 2021-06-15 12:00:00 | 2021-06-15 12:30:00 |
| A | CHAT | NOT_READY | 2021-06-15 12:30:00 | 2021-06-15 12:50:00 |
| A | VOICE | NOT_READY | 2021-06-15 12:50:00 | 2021-06-15 13:00:00 |
| A | CHAT | NOT_READY | 2021-06-15 13:00:00 | 2021-06-15 13:30:00 |
위와 같은 테이블이 있을 때, id와 contact_type, state 가 order by id , start 순으로 연속될 경우(위 테이블에서는 1,2번 로우) 연속되는 업무를 한것이므로 한개의 로우로 합쳐야되는 상황이 발생하였습니다...
즉 아래와 같이 보여줘야만 하는데,
| id | contact_type | state | start | end |
| A | CHAT | NOT_READY | 2021-06-15 12:00:00 | 2021-06-15 12:50:00 |
| A | VOICE | NOT_READY | 2021-06-15 12:50:00 | 2021-06-15 13:00:00 |
| A | CHAT | NOT_READY | 2021-06-15 13:00:00 | 2021-06-15 13:30:00 |
제가 알고 있는 지식선에서는 도저희 생각이 나지 않네요 ㅠㅠ
group by id, contact_type, state 를 한후 min(start) , max(end) 할 경우 위와같이 3개로 구분되어 나오는게 아닌, 2줄로 나올뿐더러 중복되는 시간도 발생하기에 window 함수를 사용해보려 했으나 결국 partition by 에서 동일하게 그룹을 주면서 막히게 되었습니다..
lead 와 Lag를 이용해보기도 했는데 잘 안되네요 ㅠ
선배님들이 보실 때 좋은 방법이 있을까요?
by 샤랄라
[2021.06.15 16:56:00]
with t(id, contact_type, state, sdt, edt) as
( select 'A', 'CHAT', 'NOT_READY', '2021-06-15 12:00:00', '2021-06-15 12:30:00' from dual union all
select 'A', 'CHAT', 'NOT_READY', '2021-06-15 12:30:00', '2021-06-15 12:50:00' from dual union all
select 'A', 'VOICE', 'NOT_READY', '2021-06-15 12:50:00', '2021-06-15 13:00:00' from dual union all
select 'A', 'CHAT', 'NOT_READY', '2021-06-15 13:00:00', '2021-06-15 13:30:00' from dual
)
select id
, contact_type
, state
, min(sdt) sdt
, max(edt) edt
from (select t.*
, row_number() over(order by sdt, edt) r1
, row_number() over(partition by id, contact_type, state order by sdt, edt) r2
from t
)
group by id, contact_type, state, r1-r2
order by id, sdt
;
by 랑에1
[2021.06.15 17:06:24]
이어지지 않는 경우에 결과가 잘 못 나올수 있어보입니다.
(예를들면 두번째 sdt가 12:30 -> 12:35 인 경우)
by 랑에1
[2021.06.15 17:04:01]
WITH T(id, contact_type, state, s, e) AS
(
SELECT 'A', 'CHAT', 'NOT_READY', TO_DATE('20210615 12:00:00', 'yyyymmdd hh24:mi:ss'), TO_DATE('20210615 12:30:00', 'yyyymmdd hh24:mi:ss') FROM dual UNION ALL
SELECT 'A', 'CHAT', 'NOT_READY', TO_DATE('20210615 12:30:00', 'yyyymmdd hh24:mi:ss'), TO_DATE('20210615 12:50:00', 'yyyymmdd hh24:mi:ss') FROM dual UNION ALL
SELECT 'A', 'VOICE', 'NOT_READY', TO_DATE('20210615 12:50:00', 'yyyymmdd hh24:mi:ss'), TO_DATE('20210615 13:00:00', 'yyyymmdd hh24:mi:ss') FROM dual UNION ALL
SELECT 'A', 'CHAT', 'NOT_READY', TO_DATE('20210615 13:00:00', 'yyyymmdd hh24:mi:ss'), TO_DATE('20210615 13:30:00', 'yyyymmdd hh24:mi:ss') FROM dual
)
SELECT id, contact_type, state, MIN(s) s, MAX(e) e
FROM
(
SELECT T.*
, SUM(T.tmp) OVER(PARTITION BY T.id, T.contact_type, T.state ORDER BY T.s) tmp2
FROM
(
SELECT T.*
, CASE WHEN T.s = LAG(T.e) OVER(PARTITION BY T.id, T.contact_type, T.state ORDER BY T.s) THEN 0 ELSE 1 END tmp
FROM T
) T
) T
GROUP BY id, contact_type, state, tmp2
ORDER BY id, contact_type, state, s
by 열정가이
[2021.06.15 18:13:48]
와... 진짜 오늘도 많이 배우고갑니다..
모두들 감사드립니다
덕분에 해결했습니다!
) 버튼을 클릭하여 작성 하시면 됩니다.