GROUP BY spécial

Bonjour,

J'ai une table comme suit :

ID	UNITE	DEBUT	FIN
003492	UNITE A	18/11/2021 12:41:00	18/11/2021 16:00:00
003492	UNITE A	18/11/2021 16:00:00	24/11/2021 10:25:00
003492	UNITE B	01/04/2022 14:56:00	02/04/2022 15:00:00
003492	UNITE B	02/04/2022 15:00:00	03/04/2022 20:20:00
014588	UNITE C	05/03/2019 15:13:00	05/03/2019 16:48:41
014588	UNITE C	05/03/2019 17:00:00	23/03/2019 10:00:00

Et je cherche à regrouper par ID et UNITE mais seulement si les dates et heures de fin et de début correspondent.
Voici ce que je veux obtenir :

ID	UNITE	DEBUT	FIN
003492	UNITE A	18/11/2021 12:41:00	24/11/2021 10:25:00
003492	UNITE B	01/04/2022 14:56:00	03/04/2022 20:20:00
014588	UNITE C	05/03/2019 15:13:00	05/03/2019 16:50:00
014588	UNITE C	05/03/2019 17:00:00	23/03/2019 10:00:00

Quelqu'un peut-il m'aider car je m'arrache les quelques cheveux qu'il me reste ^^
Merci !

Bonjour,
Pour moi ce n'est pas un GROUP BY qu'il faut utiliser ici, mais une jointure.
Un truc du genre:

Code:

---

1 2 3 4 5

Select t1.id, t1.unit, t1.debut, coalesce(t2.fin, t1.fin) From LaTable t1 Left outer join LaTable t2 On t1.id = t2.id and t1.unit = t2.unit and t1.fin = t2.debut

---

Tatayo

Bonjour,

C'est la fameuse requête "start of group". La requête classique est :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

with group_starts as ( select t.*, case when debut = lag(fin) over (partition by id, unite order by debut) then 0 else 1 end group_start_flag from t ), groups as (select g.*, sum(group_start_flag) over (partition by id, unite order by debut) group_id from group_starts g ) select id, unite, min(debut) debut, max(fin) fin from groups group by id, unite, group_id order by 1, 2;

---

Sinon à partir de la 12c il y a une version beaucoup plus simple en MATCH_RECOGNIZE:

Code:

---

1 2 3 4 5 6 7 8 9 10

select * from t match_recognize( partition by id, unite order by debut measures a.debut debut, fin fin pattern (a b*) define b as debut = prev(fin) );

---

Comme vanagreg l'a déjà proposé voici une solution avec match_recognize

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

create table demo as with got_my_data(id, unite, date_deb, date_fin) as ( select '003492', 'UNITE A', to_date('18/11/2021 12:41:00','dd/mm/yyyy hh24:mi:ss') ,to_date('18/11/2021 16:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual union all select '003492', 'UNITE A', to_date('18/11/2021 16:00:00','dd/mm/yyyy hh24:mi:ss') ,to_date('24/11/2021 10:25:00', 'dd/mm/yyyy hh24:mi:ss') from dual union all select '003492', 'UNITE B', to_date('01/04/2022 14:56:00','dd/mm/yyyy hh24:mi:ss') ,to_date('02/04/2022 15:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual union all select '003492', 'UNITE B', to_date('02/04/2022 15:00:00','dd/mm/yyyy hh24:mi:ss') ,to_date('03/04/2022 20:20:00', 'dd/mm/yyyy hh24:mi:ss') from dual union all select '014588', 'UNITE C', to_date('05/03/2019 15:13:00','dd/mm/yyyy hh24:mi:ss') ,to_date('05/03/2019 16:48:41', 'dd/mm/yyyy hh24:mi:ss') from dual union all select '014588', 'UNITE C', to_date('05/03/2019 17:00:00','dd/mm/yyyy hh24:mi:ss') ,to_date('23/03/2019 10:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual ) select * from got_my_data;   SELECT id, unite, start_dat, end_dat FROM demo match_recognize ( partition by id, unite order by date_deb measures first(date_deb) as start_dat, last(date_fin) as end_dat one row per match pattern (a x* ) define x as date_deb = prev(date_fin) ) ORDER BY id, unite;   ID UNITE START_DAT END_DAT ------ ------- ------------------- ------------------- 003492 UNITE A 18/11/2021 12:41:00 24/11/2021 10:25:00 003492 UNITE B 01/04/2022 14:56:00 03/04/2022 20:20:00 014588 UNITE C 05/03/2019 15:13:00 05/03/2019 16:48:41 014588 UNITE C 05/03/2019 17:00:00 23/03/2019 10:00:00

---

Bien à vous
Mohamed Houri