create table logins
( Personid varchar2(10)
, Login_time date
)
Table created.
begin
insert into logins (Personid, Login_time) values (1, to_date('01012021 00:00', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (1, to_date('01012021 01:00', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (1, to_date('01012021 01:59', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (1, to_date('01012021 02:00', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (1, to_date('01012021 02:39', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (1, to_date('01012021 03:00', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (1, to_date('01012021 04:59', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (2, to_date('01012021 01:01', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (2, to_date('01012021 01:30', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (2, to_date('01012021 02:00', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (2, to_date('01012021 05:00', 'DDMMYYYY HH24:MI'));
insert into logins (Personid, Login_time) values (2, to_date('01012021 06:00', 'DDMMYYYY HH24:MI'));
end;
Statement processed.
create or replace view
my_query
as
select personid
, to_char(login_time,'HH24:MI') block_start_login_time
from logins
match_recognize (
partition by personid -- regard every person independently
order by login_time
measures
match_number() mn,
block_start.login_time as login_time
one row per match -- every block is represented by a single record
pattern (block_start same_block*) -- collect a log in record and all subsequent rows in same two hour block; once a row is assigned to a block it cannot start or be part of another block
define
same_block as (login_time <= block_start.login_time + interval '2' hour) -- a login row is in the same block with its subsequent rows within 2 hours
)
View created.
with result_to_verify as
( select block_start_login_time login_time
from my_query
where personid = 1
)
, expected_result as
( select '00:00' from dual
union all
select '02:39' from dual
union all
select '04:59' from dual
)
(select *
from result_to_verify
minus
select *
from expected_result
) -- superfluous results
union all
(
select *
from expected_result
minus
select *
from result_to_verify
) -- missing results
no data found
with result_to_verify as
( select block_start_login_time login_time
from my_query
where personid = 1
)
, expected_result as
( select '00:00' from dual
union all
select '02:39' from dual
union all
select '04:59' from dual
)
(select *
from result_to_verify
minus
select *
from expected_result
) -- superfluous results
union all
(
select *
from expected_result
minus
select *
from result_to_verify
) -- missing results
no data found
with result_to_verify as
( select block_start_login_time login_time
from my_query
where personid = 2
)
, expected_result as
( select '01:01' from dual
union all
select '05:00' from dual
)
(select *
from result_to_verify
minus
select *
from expected_result
) -- superfluous results
union all
(
select *
from expected_result
minus
select *
from result_to_verify
) -- missing results
no data found
Define expected results as JSON document
with expected_result as (
SELECT *
FROM JSON_TABLE('
[ {"PERSONID" : "1", "BLOCK_START_LOGIN_TIME" : "00:00"}
, {"PERSONID" : "1", "BLOCK_START_LOGIN_TIME" : "02:39"}
, {"PERSONID" : "1", "BLOCK_START_LOGIN_TIME" : "04:59"}
]' , '$[*]'
COLUMNS (PERSONID VARCHAR2(10) PATH '$.PERSONID',
BLOCK_START_LOGIN_TIME VARCHAR2 PATH '$.BLOCK_START_LOGIN_TIME'
)
)
)
, result_to_verify as (
select PERSONID
, BLOCK_START_LOGIN_TIME
from my_query
)
(select *
from result_to_verify
minus
select *
from expected_result
) -- superfluous results
union all
(
select *
from expected_result
minus
select *
from result_to_verify
) -- missing results
More compact JSON definition
with expected_result as (
SELECT *
FROM JSON_TABLE('
[ {"P" : "1", "B" : "00:00"}
, {"P" : "1", "B" : "02:39"}
, {"P" : "1", "B" : "04:59"}
]' , '$[*]'
COLUMNS (PERSONID VARCHAR2(10) PATH '$.P',
BLOCK_START_LOGIN_TIME VARCHAR2 PATH '$.B'
)
)
)
, result_to_verify as (
select PERSONID
, BLOCK_START_LOGIN_TIME
from my_query
)
(select *
from result_to_verify
minus
select *
from expected_result
) -- superfluous results
union all
(
select *
from expected_result
minus
select *
from result_to_verify
) -- missing results
And even more compact and cryptic JSON document
with expected_result as (
SELECT *
FROM JSON_TABLE('[
["1", "00:00"]
, ["1", "02:39"]
, ["1", "04:59"]
]' , '$[*]'
COLUMNS (X VARCHAR2(10) PATH '$[0]',
Y VARCHAR2 PATH '$[1]'
)
)
)
, result_to_verify as (
select PERSONID
, BLOCK_START_LOGIN_TIME
from my_query
)
( select *
from result_to_verify
minus
select *
from expected_result
) -- superfluous results
union all
(
select *
from expected_result
minus
select *
from result_to_verify
) -- missing results