데이터베이스 질문과 답변
기종은 오라클을 사용하고 있으며 해결하고 싶은 데이터 셋은 아래와 같고
select 100218 panel_code, to_date('2021/01/21 11:50:38','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100218 panel_code, to_date('2021/01/21 12:09:41','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100218 panel_code, to_date('2021/01/21 12:09:46','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100218 panel_code, to_date('2021/01/21 12:09:51','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100218 panel_code, to_date('2021/01/21 15:26:08','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100218 panel_code, to_date('2021/01/21 15:26:14','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100218 panel_code, to_date('2021/01/21 15:26:19','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100218 panel_code, to_date('2021/01/21 15:26:25','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 19:59:48','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 19:59:53','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 19:59:58','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 20:00:03','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 20:00:09','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 20:00:25','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 20:00:31','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 22:36:16','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 22:36:21','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 22:36:27','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 22:36:39','yyyy/mm/dd hh24:mi:ss') register_Date from dual union all
select 100232 panel_code, to_date('2021/01/21 22:36:45','yyyy/mm/dd hh24:mi:ss') register_Date from dual
위에 같이 데이터에서 패널 코드별 register_Date 정렬 후 다음 row차이가 15초 이내인 것들을 한 세션으로 정의하여결과값이
| panel_code | start_date | end_date |
| 100218 | 2021/01/21 11:50:38 | 2021/01/21 11:50:38 |
| 100218 | 2021/01/21 12:09:41 | 2021/01/21 12:09:51 |
| 100218 | 2021/01/21 15:26:08 | 2021/01/21 15:26:25 |
| 100232 | 2021/01/21 19:59:48 | 2021/01/21 19:59:58 |
| 100232 | 2021/01/21 20:00:03 | 2021/01/21 20:00:31 |
| 100232 | 2021/01/21 22:36:16 | 2021/01/21 22:36:45 |
이렇게 (15초 이내의 집합이 한건일 경우 start_date, end_date 값이 동일) 나와야 됩니다. 해결을 못하고 있어 여러분의 도움 부탁 드립니다.
감사합니다.!!
by 마농
[2021.01.27 09:02:20]
WITH t AS
(
SELECT 100218 panel_code, TO_DATE('2021/01/21 11:50:38', 'yyyy/mm/dd hh24:mi:ss') register_date FROM dual
UNION ALL SELECT 100218, TO_DATE('2021/01/21 12:09:41', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 1143
UNION ALL SELECT 100218, TO_DATE('2021/01/21 12:09:46', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 5
UNION ALL SELECT 100218, TO_DATE('2021/01/21 12:09:51', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 5
UNION ALL SELECT 100218, TO_DATE('2021/01/21 15:26:08', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 11777
UNION ALL SELECT 100218, TO_DATE('2021/01/21 15:26:14', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 6
UNION ALL SELECT 100218, TO_DATE('2021/01/21 15:26:19', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 5
UNION ALL SELECT 100218, TO_DATE('2021/01/21 15:26:25', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 6
UNION ALL SELECT 100232, TO_DATE('2021/01/21 19:59:48', 'yyyy/mm/dd hh24:mi:ss') FROM dual --
UNION ALL SELECT 100232, TO_DATE('2021/01/21 19:59:53', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 5
UNION ALL SELECT 100232, TO_DATE('2021/01/21 19:59:58', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 5
UNION ALL SELECT 100232, TO_DATE('2021/01/21 20:00:03', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 5
UNION ALL SELECT 100232, TO_DATE('2021/01/21 20:00:09', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 6
UNION ALL SELECT 100232, TO_DATE('2021/01/21 20:00:25', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 16
UNION ALL SELECT 100232, TO_DATE('2021/01/21 20:00:31', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 6
UNION ALL SELECT 100232, TO_DATE('2021/01/21 22:36:16', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 9345
UNION ALL SELECT 100232, TO_DATE('2021/01/21 22:36:21', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 5
UNION ALL SELECT 100232, TO_DATE('2021/01/21 22:36:27', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 6
UNION ALL SELECT 100232, TO_DATE('2021/01/21 22:36:39', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 12
UNION ALL SELECT 100232, TO_DATE('2021/01/21 22:36:45', 'yyyy/mm/dd hh24:mi:ss') FROM dual -- 6
)
SELECT panel_code
, grp
, MIN(register_date) start_date
, MAX(register_date) end_date
FROM (SELECT panel_code
, register_date
, SUM(flag) OVER(PARTITION BY panel_code ORDER BY register_date) grp
FROM (SELECT panel_code
, register_date
, CASE WHEN ROUND(
( register_date
- LAG(register_date) OVER(PARTITION BY panel_code ORDER BY register_date)
) * 24*60*60) <= 15
THEN 0 ELSE 1 END flag
FROM t
)
)
GROUP BY panel_code, grp
ORDER BY panel_code, grp
;
by 지송요
[2021.01.27 11:19:49]
감사합니다. 또 한번 배워갑니다.
) 버튼을 클릭하여 작성 하시면 됩니다.