Pour illustrer, supposons que j'ai les valeurs suivantes dans une table:
--------------------------------------- | ID_USER | START_DATE | --------------------------------------- | 1 | 01/01/2018 08:00:00 | | 2 | 01/01/2018 08:30:00 | | 1 | 01/01/2018 08:45:00 | | 2 | 01/01/2018 09:15:00 | | 1 | 01/01/2018 09:45:00 | ---------------------------------------
3 Réponses :
comparer la ligne actuelle et précédente à l'aide de Lag code>:
with s (id_user, start_date) as ( select 1, to_date('01/01/2018 08:00:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 1, to_date('01/01/2018 08:15:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 2, to_date('01/01/2018 08:30:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 1, to_date('01/01/2018 08:45:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 1, to_date('01/01/2018 09:00:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 2, to_date('01/01/2018 09:15:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 2, to_date('01/01/2018 09:30:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 1, to_date('01/01/2018 09:45:00', 'dd.mm.yyyy hh24:mi:ss') from dual) select id_user, start_date from (select s.*, lag(id_user) over (order by start_date) prev_user from s ) where lnnvl(prev_user = id_user); ID_USER START_DATE ---------- ------------------- 1 2018-01-01 08:00:00 2 2018-01-01 08:30:00 1 2018-01-01 08:45:00 2 2018-01-01 09:15:00 1 2018-01-01 09:45:00
-- Oracle 12c+: Pattern matching with s (id_user, start_date) as ( select 1, to_date('01/01/2018 08:00:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 1, to_date('01/01/2018 08:15:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 2, to_date('01/01/2018 08:30:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 1, to_date('01/01/2018 08:45:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 1, to_date('01/01/2018 09:00:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 2, to_date('01/01/2018 09:15:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 2, to_date('01/01/2018 09:30:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all select 1, to_date('01/01/2018 09:45:00', 'dd.mm.yyyy hh24:mi:ss') from dual) select id_user, start_date from s match_recognize( order by start_date measures id_user as id_user, start_date as start_date pattern (v+) define v as id_user = first(id_user) ); ID_USER START_DATE ---------- ------------------- 1 2018-01-01 08:15:00 2 2018-01-01 08:30:00 1 2018-01-01 09:00:00 2 2018-01-01 09:30:00 1 2018-01-01 09:45:00
Vérifiez ce lien. je vais vous aider à résoudre ce problème.