Aggregating intervals

Comment rassembler des intervalles qui se chevauchent ?

Dans ma jeunesse, j'ai vu (et proposé) des solutions tirées par les cheveux.

Et l'autre jour sur OTN, j'ai trouvé la méthode de FrankKulash particulièrement élégante.

Petit article hommage pour lui : 

1) Les données

Robert, Marcel et Squelettor travaillent dans la même équipe. Voici la table non normalisée de leur planning : 

CREATE TABLE planning AS

SELECT 'Robert' nom, to_date('20130810 10', 'YYYYMMDD hh24') date_deb, to_date('20130810 14', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Marcel' nom, to_date('20130810 12', 'YYYYMMDD hh24') date_deb, to_date('20130810 20', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Squelettor' nom, to_date('20130810 11', 'YYYYMMDD hh24') date_deb, to_date('20130810 18', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Robert' nom, to_date('20130811 08', 'YYYYMMDD hh24') date_deb, to_date('20130811 10', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Marcel' nom, to_date('20130811 12', 'YYYYMMDD hh24') date_deb, to_date('20130811 14', 'YYYYMMDD hh24') date_fin FROM DUAL

 UNION ALL SELECT 'Squelettor' nom, to_date('20130811 09', 'YYYYMMDD hh24') date_deb, to_date('20130811 13', 'YYYYMMDD hh24') date_fin FROM DUAL;

2) Interval UNION !

La requête suivante répond à la question : "quelles sont les périodes disjointes sur lesquelles il y a au moins une personne présente ?"

SELECT to_char(min(date_deb), 'DD/MM/YYYY hh24:mi') deb_union, to_char(max(date_fin), 'DD/MM/YYYY hh24:mi') fin_union

FROM (

  SELECT date_deb, date_fin, sum(brk) OVER(ORDER BY date_deb) grp

  FROM (

      SELECT date_deb, date_fin

        , CASE WHEN max(date_fin) OVER(ORDER BY date_deb ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) < date_deb

               THEN 1

               ELSE 0 END brk

      FROM planning)

      )

GROUP BY grp

ORDER BY grp;

DEB_UNION        FIN_UNION

---------------- ----------------

10/08/2013 10:00 10/08/2013 20:00

11/08/2013 08:00 11/08/2013 14:00

Le raisonnement : pour une ligne donnée, si parmi les lignes précédentes triées par date de début, la plus grande date de fin est strictement inférieure à ma date de début, la ligne courante crée un nouvel intervalle.

Par défaut, la fenête (le périmètre) d'une fonction analytique comprend toutes les lignes de la partition de dates inférieures où égale à la ligne courante.

Le '1 PRECEDING' permet de s'arrêter un cran avant, et on doit préciser le début de la fenête à UNBOUNDED PRECEDING pour indiquer qu'on prend tout depuis le début de la partition.

Une fois qu'on a ces flags 'BRK' pour chaque ligne, en faire la somme cumulative sur chaque ligne, permet de compter le nombre de ruptures présentes avant la ligne courante.

Ce nombre 'GRP' est un critère de groupement pour rassembler les intervalles, et il ne reste plus qu'à choper le début et la fin de l'intervalle.

3) Interval INTERSECTION !

Histoire d'apporter quand même une touche personnelle, j'ai cherché à répondre à une autre question : 

"Quel est le nombre de personnes présentes par tranches horaires distinctes ?"

J'ai fait des petits dessins où je projetais des intervalles sur un axe, pour finir par me dire : si on colle toutes les extrêmités d'intervalles les unes aux autres, on peut ensuite considérer chacun des segments minimaux et compter le nombre de "segments" du planning dans lesquels ils sont inclus.

SELECT to_char(t.deb_int, 'DAY hh24:mi') deb, to_char(t.fin_int, 'DAY hh24:mi') fin, count(*) nb, LISTAGG(nom, ',') WITHIN GROUP(ORDER BY nom) lstnom

FROM planning p JOIN 

    (SELECT borne_date deb_int, lead(borne_date, 1) OVER(ORDER BY borne_date) fin_int

    FROM planning

    UNPIVOT (borne_date

             FOR type_borne

             IN (date_deb, date_fin))

    GROUP BY borne_date) t

    ON t.deb_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

    AND t.fin_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

GROUP BY t.deb_int, t.fin_int    

ORDER BY t.deb_int

DEB                  FIN                          NB LSTNOM

-------------------- -------------------- ---------- -------------------------

SATURDAY  10:00      SATURDAY  11:00               1 Robert

SATURDAY  11:00      SATURDAY  12:00               2 Robert,Squelettor

SATURDAY  12:00      SATURDAY  14:00               3 Marcel,Robert,Squelettor

SATURDAY  14:00      SATURDAY  18:00               2 Marcel,Squelettor

SATURDAY  18:00      SATURDAY  20:00               1 Marcel

SUNDAY    08:00      SUNDAY    09:00               1 Robert

SUNDAY    09:00      SUNDAY    10:00               2 Robert,Squelettor

SUNDAY    10:00      SUNDAY    12:00               1 Squelettor

SUNDAY    12:00      SUNDAY    13:00               2 Marcel,Squelettor

SUNDAY    13:00      SUNDAY    14:00               1 Marcel

J'ai profité de ma nouvelle 11gR2 pour utiliser UNPIVOT, mais cela se fait bien sûr également (et entre autres) de la manière artisanale suivante : faire le produit cartésien avec une "table" à deux lignes, puis choisir alternativement la bonne colonne.

SELECT to_char(t.deb_int, 'DAY hh24:mi') deb, to_char(t.fin_int, 'DAY hh24:mi') fin, count(*) nb, LISTAGG(nom, ',') WITHIN GROUP(ORDER BY nom) lstnom

FROM planning p JOIN 

    (SELECT CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END deb_int

            , lead(CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END, 1) OVER(ORDER BY CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END) fin_int

    FROM planning

      CROSS JOIN (SELECT 1 n FROM DUAL

                  UNION ALL SELECT 2 FROM DUAL)

    GROUP BY CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END) t

    ON t.deb_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

    AND t.fin_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

GROUP BY t.deb_int, t.fin_int    

ORDER BY t.deb_int

(Et la première fois de ma vie que j'utilise LISTAGG pour mettre dans un même sac Marcel, Robert et Squelettor...)