create table logins
( Personid varchar2(10)
, Login_time date
)
Table created.
insert into logins (Personid, Login_time) values (1, to_date('01012021 00:00', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (1, to_date('01012021 01:00', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (1, to_date('01012021 01:59', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (1, to_date('01012021 02:00', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (1, to_date('01012021 02:39', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (1, to_date('01012021 03:00', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (1, to_date('01012021 04:59', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (2, to_date('01012021 01:01', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (2, to_date('01012021 01:30', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (2, to_date('01012021 02:00', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (2, to_date('01012021 05:00', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
insert into logins (Personid, Login_time) values (2, to_date('01012021 06:00', 'DDMMYYYY HH24:MI'))
1 row(s) inserted.
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
)
PERSONID | BLOCK_START_LOGIN_TIME | 1 | 00:00 | 1 | 02:39 | 1 | 04:59 | 2 | 01:01 | 2 | 05:00 |
---|
with recurs (personid, lvl, login_time, last_time, rn) as
( select personid
, 1 lvl
, min(login_time)
, min(login_time) + interval '2' hour
, 1
from logins
group
by personid -- return a single login record for each person - representing the earliest login and indicating the end of that first login block
union all
select r.personid
, r.lvl + 1
, l.login_time
, l.login_time + interval '2' hour
, row_number() over ( partition by r.personid
order by l.login_time
) rn -- determine rank of login record among its siblings by login time; we are really only interested in the earliest of these, the ones that start a new block
from recurs r
join
logins l
on -- find all successor records for the new rows produced by the previous iteration (logins for the same person that were later than the end of the two hour block started by the predecessor)
( r.rn =1 -- only start recursion from block starters - those "parent" records with rn = 1
and
l.personid = r.personid
and
l.login_time > r.last_time -- login record has to be outside the block started by its predecessor or parent record
)
)
select personid
, to_char(login_time, 'HH24:MI') block_start_login_time
, lvl as iteration
from recurs
where rn = 1 -- only retain the first row in the block
order
by personid
, login_time
PERSONID | BLOCK_START_LOGIN_TIME | ITERATION | 1 | 00:00 | 1 | 1 | 02:39 | 2 | 1 | 04:59 | 3 | 2 | 01:01 | 1 | 2 | 05:00 | 2 |
---|
select personid
, to_char(login_time,'HH24:MI') block_start_login_time
from ( select *
from logins
model
partition by (personid)
dimension by (row_number() over ( partition by personid
order by login_time
) as rn
)
measures ( login_time
, 'N' blockstarter_yn
)
rules ( blockstarter_yn[any] -- assign a value to the cell count_yn for all login_times
order by rn -- go through the cells ordered by login time, starting with the earliest
= case
when cv(rn) = 1
then 'Y' -- first login by a person
when login_time[cv()] > -- when the login time in a cell is more than two hours later later than the last (or max)
-- login time in an earlier cell (for a row that was identified as a block starter
max( case when blockstarter_yn = 'Y'
then login_time
end
) [rn < cv()]
+ interval '2' hour -- when the login_time
then 'Y'
else 'N'
end
)
)
where blockstarter_yn = 'Y'
PERSONID | BLOCK_START_LOGIN_TIME | 1 | 00:00 | 1 | 02:39 | 1 | 04:59 | 2 | 01:01 | 2 | 05:00 |
---|