Discussion :

[milka]

Bonjour à tous,

sous oracle 10.2,
je cherche à établir une requête permettant de grouper des dates consécutives pour en obtenir des périodes.

TABLE_ORIGINE(d_date date)
RESULTAT(d_date_debut date, d_date_fin date)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
Exemple : 
 
TABLE_ORIGINE
01/09/2010
02/09/2010
03/09/2010
15/09/2010
 
RESULTAT :
01/09/2010 au 03/09/2010
15/09/2010 au 15/09/2010

Je pense qu'il faut utiliser une fonction analytique mais je n'y parviens pas.

[Jorafali]

Bonjour,

Je ne suis pas certain de comprendre ton exemple, mais je crois que tu devrais chercher du cote des fonctions LEAD et LAG.

J.

[xdescamp]

Bonjour,

La solution suivante devrait permettre de répondre à la demande avec des fonctions analytiques.

La construction par étapes :
- on commence par comparer la date de chaque enregistrement avec celle du précédent (LAG) pour voir s'il s'agit d'un début de période

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
With Table_origine As (Select To_date ('01/09/2010', 'DD/MM/YYYY') Ma_date From Dual Union All
                       Select To_date ('02/09/2010', 'DD/MM/YYYY') Ma_date From Dual Union All
                       Select To_date ('03/09/2010', 'DD/MM/YYYY') Ma_date From Dual Union All
                       Select To_date ('15/09/2010', 'DD/MM/YYYY') Ma_date From Dual)
Select Ma_date,
       Case When Lag (Ma_date) Over (Order By Ma_date Asc) = (Ma_date - 1) Then Null Else Ma_date End Date_debut
From Table_origine;
 
MA_DATE  DATE_DEB
-------- --------
01/09/10 01/09/10
02/09/10
03/09/10
15/09/10 15/09/10

- on détermine ensuite la plus grande date de début des enregistrements précédents avec un MAX(...) OVER (ORDER BY...) pour propager la date de début à l'ensemble des enregistrements de la période

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
With Table_origine As (Select To_date ('01/09/2010', 'DD/MM/YYYY') Ma_date From Dual Union All
                       Select To_date ('02/09/2010', 'DD/MM/YYYY') Ma_date From Dual Union All
                       Select To_date ('03/09/2010', 'DD/MM/YYYY') Ma_date From Dual Union All
                       Select To_date ('15/09/2010', 'DD/MM/YYYY') Ma_date From Dual)
Select Ma_date,
       Max (Date_debut) Over (Order By Ma_date Asc) Date_debut_periode
From (Select Ma_date,
             Case When Lag (Ma_date) Over (Order By Ma_date Asc) = (Ma_date - 1) Then Null Else Ma_date End Date_debut
      From Table_origine);
 
MA_DATE  DATE_DEB
-------- --------
01/09/10 01/09/10
02/09/10 01/09/10
03/09/10 01/09/10
15/09/10 15/09/10

- on garde ensuite pour chaque période la ligne ayant la plus grande date, il s'agit alors de la date de fin.

Ce qui donne la requête finale :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
With Table_origine As (Select To_date ('01/09/2010', 'DD/MM/YYYY') Ma_date From Dual Union All
                       Select To_date ('02/09/2010', 'DD/MM/YYYY') Ma_date From Dual Union All
                       Select To_date ('03/09/2010', 'DD/MM/YYYY') Ma_date From Dual Union All
                       Select To_date ('15/09/2010', 'DD/MM/YYYY') Ma_date From Dual)
Select Date_debut_periode,
       Max (Ma_date) Date_fin_periode,
       To_char (Date_debut_periode, 'DD/MM/YYYY') || ' au ' || To_Char (Max (Ma_date), 'DD/MM/YYYY') Periode
From (Select Ma_date,
             Max (Date_debut) Over (Order By Ma_date Asc) Date_debut_periode
      From (Select Ma_date,
                   Case When Lag (Ma_date) Over (Order By Ma_date Asc) = (Ma_date - 1) Then Null Else Ma_date End Date_debut
            From Table_origine))
Group By Date_debut_periode;
 
DATE_DEB DATE_FIN PERIODE
-------- -------- ------------------------
01/09/10 03/09/10 01/09/2010 au 03/09/2010
15/09/10 15/09/10 15/09/2010 au 15/09/2010

J'espère que les explications sont à peu près claires et que le résultat correspond .

[Waldar]

Une autre solution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH MaTable AS
(
select date '2010-09-01' as dt from dual union all
select date '2010-09-02'       from dual union all
select date '2010-09-03'       from dual union all
select date '2010-09-15'       from dual
)
  ,  sr as
(
select dt,
       dt - row_number() over(order by dt asc) as grp
  from MaTable
)
  select min(dt) as dt_deb, max(dt) as dt_fin
    from sr
group by grp
order by min(dt) asc;
 
DT_DEB        DT_FIN
01/09/2010    03/09/2010
15/09/2010    15/09/2010

[xdescamp]

Une autre solution :

Astucieux, j'aime bien.
Voilà pourquoi j'aime ce forum : on y trouve toujours de nouvelles choses !

[milka]

Ok Waldar, super la solution, c'est bien pensé.

comment faire dans le cas où la table d'origine contient 2 fois la même date ?
solution possible : FROM (SELECT DISTINCT madate FROM table_origine) ?
ou existe t'il une autre technique ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Exemple : 
 
WITH MaTable AS
(
SELECT date '2010-09-01' AS dt FROM dual union ALL
SELECT date '2010-09-02'       FROM dual union ALL
SELECT date '2010-09-03'       FROM dual union ALL
SELECT date '2010-09-15'       FROM dual union ALL
SELECT date '2011-01-01'       FROM dual union ALL
SELECT date '2011-01-01'       FROM dual union ALL
SELECT date '2011-01-02'       FROM dual union ALL
SELECT date '2011-01-03'       FROM dual
)

[daliok]

Waldar

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
WITH MaTable AS
(
SELECT date '2010-09-01' AS dt FROM dual union ALL
SELECT date '2010-09-02'       FROM dual union ALL
SELECT date '2010-09-03'       FROM dual union ALL
SELECT date '2010-09-15'       FROM dual union ALL
SELECT date '2011-01-01'       FROM dual union ALL
SELECT date '2011-01-01'       FROM dual union ALL
SELECT date '2011-01-02'       FROM dual union ALL
SELECT date '2011-01-03'       FROM dual
),ok as (select distinct dt from matable order by dt )
  ,  sr AS
(
SELECT dt,
       dt - row_number() over(ORDER BY dt ASC) AS grp
  FROM ok
)
  SELECT min(dt) AS dt_deb, max(dt) AS dt_fin
    FROM sr
GROUP BY grp
ORDER BY min(dt) ASC;

[xdescamp]

comment faire dans le cas où la table d'origine contient 2 fois la même date ?
...
ou existe t'il une autre technique ?

Dans ce cas mon script fonctionne.

[Waldar]

On peut utiliser dense_rank au lieu de row_number pour gérer les doublons :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
with matable as
(
select date '2010-09-01' as dt from dual union all
select date '2010-09-02'       from dual union all
select date '2010-09-03'       from dual union all
select date '2010-09-15'       from dual union all
select date '2011-01-01'       from dual union all
select date '2011-01-01'       from dual union all
select date '2011-01-02'       from dual union all
select date '2011-01-03'       from dual
) 
  ,  sr as
(
select dt,
       dt - dense_rank() over(order by dt asc) as grp
  from matable
)
  select min(dt) as dt_deb, max(dt) as dt_fin
    from sr
group by grp
order by min(dt) asc;
 
DT_DEB        DT_FIN
01/09/2010    03/09/2010
15/09/2010    15/09/2010
01/01/2011    03/01/2011

[milka]

Bonjour à tous,
je reviens sur ce sujet.
J'aurais souhaité maintenant qu'à chaque changement de mois, la période se décompose.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
WITH matable AS
(
SELECT date '2010-09-01' AS dt FROM dual union ALL
SELECT date '2010-09-02'       FROM dual union ALL
SELECT date '2010-09-03'       FROM dual union ALL
SELECT date '2010-09-15'       FROM dual union ALL
SELECT date '2010-09-29'       FROM dual union ALL
SELECT date '2010-09-30'       FROM dual union ALL
SELECT date '2010-10-01'       FROM dual union ALL
SELECT date '2010-10-02'       FROM dual union ALL
SELECT date '2011-01-01'       FROM dual union ALL
SELECT date '2011-01-01'       FROM dual union ALL
SELECT date '2011-01-02'       FROM dual union ALL
SELECT date '2011-01-03'       FROM dual
) 
  ,  sr AS
(
SELECT dt,
       dt - dense_rank() over(ORDER BY dt ASC) AS grp
  FROM matable
)
  SELECT min(dt) AS dt_deb, max(dt) AS dt_fin
    FROM sr
GROUP BY grp
ORDER BY min(dt) ASC;

Résultat attendu :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
DT_DEB                    DT_FIN                    
------------------------- ------------------------- 
01/09/10                  03/09/10                  
15/09/10                  15/09/10                  
29/09/10                  30/09/10                <--  
01/10/10                  02/10/10                <--  
01/01/11                  03/01/11

Merci d'avance pour votre aide toujours précieuse.

[Waldar]

On ajoute le partitionnement dans la fonction de fenêtrage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

dense_rank() over(PARTITION BY to_char(dt, 'yyyymm') ORDER BY dt ASC) AS grp

[milka]

Merci beaucoup !
En fait j'avais trouvé mais j'avais oublié de remplacer sysdate par dt dans mes tests.

Merci

[milka]

Du coup je me posais la question de comment obtenir le même genre de résultat avec non plus une liste de dates mais des périodes en entrée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
WITH matable AS
(
SELECT date '2009-07-13' AS deb, date '2009-07-16' AS fin FROM dual union ALL
SELECT date '2009-09-30' AS deb, date '2009-10-05' AS fin FROM dual
)

Résultat attendu :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
DEB                       FIN                       
------------------------- ------------------------- 
13/07/09                  16/07/09                  
30/09/09                  30/09/09                  
01/10/09                  05/10/09

[fri.du]

Vraiment très très intéressante cette discussion.

Milka, as-tu trouvé la réponse à ta dernière question ?

[Waldar]

À partir d'une plage c'est très simple de recréer tous les jours un par un en effectuant une jointure avec un calendrier.

Dès lors on peut ré-appliquer la solution précédemment énoncée !