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.