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';
CREATE TABLE view_TABLE (date_column, user_1,ip, in_ip,out_ip,session_1,activity) AS
SELECT DATE '2023-02-09' + INTERVAL '15:06' HOUR TO MINUTE, 'X', 123, 123, NULL, 1, 'IN' FROM DUAL UNION ALL
SELECT DATE '2023-02-09' + INTERVAL '15:08' HOUR TO MINUTE, 'X', 123, NULL, 123, 1, 'OUT' FROM DUAL UNION ALL
SELECT DATE '2023-02-08' + INTERVAL '16:08' HOUR TO MINUTE, 'Y', 143, NULL, 143, 2, 'OUT' FROM DUAL UNION ALL
SELECT DATE '2023-02-08' + INTERVAL '16:04' HOUR TO MINUTE, 'Y', 153, 153, NULL, 2, 'IN' FROM DUAL UNION ALL
SELECT DATE '2023-02-08' + INTERVAL '12:45' HOUR TO MINUTE, 'X', 163, 163, NULL, 3, 'IN' FROM DUAL UNION ALL
SELECT DATE '2023-02-08' + INTERVAL '12:48' HOUR TO MINUTE, 'X', 163, NULL, 163, 3, 'OUT' FROM DUAL;
6 rows affected
select
USER_1,
IP,
max(case when activity = 'IN' then DATE_COLUMN end) IN_1,
max(case when activity = 'OUT' then DATE_COLUMN end) OUT_1
from view_table
group by USER_1, IP, SESSION_1
| USER_1 | IP | IN_1 | OUT_1 |
|---|---|---|---|
| X | 123 | 2023-02-09 15:06:00 | 2023-02-09 15:08:00 |
| Y | 143 | null | 2023-02-08 16:08:00 |
| Y | 153 | 2023-02-08 16:04:00 | null |
| X | 163 | 2023-02-08 12:45:00 | 2023-02-08 12:48:00 |
SELECT user_1, in_ip, in_time, out_ip, out_time
FROM (
SELECT v.*,
TRUNC(date_column) AS day
FROM view_table v
)
MATCH_RECOGNIZE(
PARTITION BY user_1, day
ORDER BY date_column
MEASURES
user_in.ip AS in_ip,
user_in.date_column AS in_time,
user_out.ip AS out_ip,
user_out.date_column AS out_time
PATTERN ( user_in user_out? | user_out user_in? )
DEFINE
user_in AS activity = 'IN',
user_out AS activity = 'OUT'
)
| USER_1 | IN_IP | IN_TIME | OUT_IP | OUT_TIME |
|---|---|---|---|---|
| X | 163 | 2023-02-08 12:45:00 | 163 | 2023-02-08 12:48:00 |
| X | 123 | 2023-02-09 15:06:00 | 123 | 2023-02-09 15:08:00 |
| Y | 153 | 2023-02-08 16:04:00 | 143 | 2023-02-08 16:08:00 |