By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE text_length_records (source, entry_dt, entry_time, content_len) AS
SELECT 1, 20210910, 95059, 37 FROM DUAL UNION ALL
SELECT 1, 20210910, 95102, 999 FROM DUAL UNION ALL
SELECT 1, 20210910, 95959, 139 FROM DUAL UNION ALL
SELECT 2, 20210910, 83320, 999 FROM DUAL UNION ALL
SELECT 2, 20210910, 83322, 999 FROM DUAL UNION ALL
SELECT 2, 20210910, 83324, 456 FROM DUAL;
6 rows affected
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
SELECT *
FROM (
SELECT source,
TO_DATE(entry_dt || LPAD(entry_time, 6, '0'), 'YYYYMMDDHH24MISS') AS entry_dt,
content_len
FROM text_length_records
)
MATCH_RECOGNIZE(
PARTITION BY source
ORDER BY entry_dt
MEASURES
FIRST(entry_dt) AS start_entry_dt,
LAST(entry_dt) AS end_entry_dt,
SUM(content_len) AS content_len
ONE ROW PER MATCH
PATTERN (within_20* last_time)
DEFINE
within_20 AS entry_dt + INTERVAL '20' SECOND >= NEXT(entry_dt)
)
| SOURCE | START_ENTRY_DT | END_ENTRY_DT | CONTENT_LEN |
|---|---|---|---|
| 1 | 2021-09-10 09:50:59 | 2021-09-10 09:51:02 | 1036 |
| 1 | 2021-09-10 09:59:59 | 2021-09-10 09:59:59 | 139 |
| 2 | 2021-09-10 08:33:20 | 2021-09-10 08:33:24 | 2454 |
SELECT *
FROM (
SELECT source,
TO_DATE(entry_dt || LPAD(entry_time, 6, '0'), 'YYYYMMDDHH24MISS') AS entry_dt,
content_len
FROM text_length_records
)
MATCH_RECOGNIZE(
PARTITION BY source
ORDER BY entry_dt
MEASURES
FIRST(entry_dt) AS start_entry_dt,
LAST(entry_dt) AS end_entry_dt,
SUM(content_len) AS content_len
ONE ROW PER MATCH
PATTERN (within_20*)
DEFINE
within_20 AS entry_dt <= FIRST(entry_dt) + INTERVAL '20' SECOND
)
| SOURCE | START_ENTRY_DT | END_ENTRY_DT | CONTENT_LEN |
|---|---|---|---|
| 1 | 2021-09-10 09:50:59 | 2021-09-10 09:51:02 | 1036 |
| 1 | 2021-09-10 09:59:59 | 2021-09-10 09:59:59 | 139 |
| 2 | 2021-09-10 08:33:20 | 2021-09-10 08:33:24 | 2454 |