By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE table1 (data_timestamp TIMESTAMP(0))
INSERT INTO table1 (data_timestamp)
SELECT TIMESTAMP '2024-07-10 10:21:10' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:18' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:21' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:22' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:23' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:25' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:26' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:29' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:30' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:31' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:32' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:40' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:49' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:55' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:21:56' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:22:01' FROM DUAL UNION ALL
SELECT TIMESTAMP '2024-07-10 10:22:40' FROM DUAL;
17 rows affected
SELECT *
FROM table1
MATCH_RECOGNIZE(
ORDER BY data_timestamp
MEASURES
MATCH_NUMBER() AS grp
ALL ROWS PER MATCH
PATTERN (within_20_seconds+)
DEFINE
within_20_seconds AS data_timestamp < FIRST(data_timestamp)
+ INTERVAL '20' SECOND
)
| DATA_TIMESTAMP | GRP |
|---|---|
| 2024-07-10 10:21:10. | 1 |
| 2024-07-10 10:21:18. | 1 |
| 2024-07-10 10:21:21. | 1 |
| 2024-07-10 10:21:22. | 1 |
| 2024-07-10 10:21:23. | 1 |
| 2024-07-10 10:21:25. | 1 |
| 2024-07-10 10:21:26. | 1 |
| 2024-07-10 10:21:29. | 1 |
| 2024-07-10 10:21:30. | 2 |
| 2024-07-10 10:21:31. | 2 |
| 2024-07-10 10:21:32. | 2 |
| 2024-07-10 10:21:40. | 2 |
| 2024-07-10 10:21:49. | 2 |
| 2024-07-10 10:21:55. | 3 |
| 2024-07-10 10:21:56. | 3 |
| 2024-07-10 10:22:01. | 3 |
| 2024-07-10 10:22:40. | 4 |
SELECT data_timestamp,
DENSE_RANK() OVER (
ORDER BY TRUNC(data_timestamp, 'MI')
+ TRUNC(EXTRACT(SECOND FROM data_timestamp) / 20)
* INTERVAL '20' SECOND
) AS grp
FROM table1
ORDER BY data_timestamp
| DATA_TIMESTAMP | GRP |
|---|---|
| 2024-07-10 10:21:10. | 1 |
| 2024-07-10 10:21:18. | 1 |
| 2024-07-10 10:21:21. | 2 |
| 2024-07-10 10:21:22. | 2 |
| 2024-07-10 10:21:23. | 2 |
| 2024-07-10 10:21:25. | 2 |
| 2024-07-10 10:21:26. | 2 |
| 2024-07-10 10:21:29. | 2 |
| 2024-07-10 10:21:30. | 2 |
| 2024-07-10 10:21:31. | 2 |
| 2024-07-10 10:21:32. | 2 |
| 2024-07-10 10:21:40. | 3 |
| 2024-07-10 10:21:49. | 3 |
| 2024-07-10 10:21:55. | 3 |
| 2024-07-10 10:21:56. | 3 |
| 2024-07-10 10:22:01. | 4 |
| 2024-07-10 10:22:40. | 5 |
SELECT data_timestamp,
ROUND(
(
( TRUNC(data_timestamp, 'MI')
+ TRUNC(EXTRACT(SECOND FROM data_timestamp) / 20) * INTERVAL '20' SECOND
)
- MIN(
TRUNC(data_timestamp, 'MI')
+ TRUNC(EXTRACT(SECOND FROM data_timestamp) / 20) * INTERVAL '20' SECOND
) OVER ()
) * 4320
) + 1 AS grp
FROM table1
| DATA_TIMESTAMP | GRP |
|---|---|
| 2024-07-10 10:21:10. | 1 |
| 2024-07-10 10:21:18. | 1 |
| 2024-07-10 10:21:21. | 2 |
| 2024-07-10 10:21:22. | 2 |
| 2024-07-10 10:21:23. | 2 |
| 2024-07-10 10:21:25. | 2 |
| 2024-07-10 10:21:26. | 2 |
| 2024-07-10 10:21:29. | 2 |
| 2024-07-10 10:21:30. | 2 |
| 2024-07-10 10:21:31. | 2 |
| 2024-07-10 10:21:32. | 2 |
| 2024-07-10 10:21:40. | 3 |
| 2024-07-10 10:21:49. | 3 |
| 2024-07-10 10:21:55. | 3 |
| 2024-07-10 10:21:56. | 3 |
| 2024-07-10 10:22:01. | 4 |
| 2024-07-10 10:22:40. | 6 |
SELECT data_timestamp,
ROUND(
(CAST(data_timestamp AS DATE) - MIN(CAST(data_timestamp AS DATE)) OVER ())
* 4320
) + 1 AS grp
FROM table1
| DATA_TIMESTAMP | GRP |
|---|---|
| 2024-07-10 10:21:10. | 1 |
| 2024-07-10 10:21:18. | 1 |
| 2024-07-10 10:21:21. | 2 |
| 2024-07-10 10:21:22. | 2 |
| 2024-07-10 10:21:23. | 2 |
| 2024-07-10 10:21:25. | 2 |
| 2024-07-10 10:21:26. | 2 |
| 2024-07-10 10:21:29. | 2 |
| 2024-07-10 10:21:30. | 2 |
| 2024-07-10 10:21:31. | 2 |
| 2024-07-10 10:21:32. | 2 |
| 2024-07-10 10:21:40. | 3 |
| 2024-07-10 10:21:49. | 3 |
| 2024-07-10 10:21:55. | 3 |
| 2024-07-10 10:21:56. | 3 |
| 2024-07-10 10:22:01. | 4 |
| 2024-07-10 10:22:40. | 6 |