Concaténation de dates

Version imprimable

15/09/2010, 15h15
milka

Concaténation de dates
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:

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.
15/09/2010, 16h05
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.

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:

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:

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:

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 ;).

Une autre solution :

Code:

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

15/09/2010, 16h51
xdescamp

Citation:

Envoyé par Waldar

Une autre solution :

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

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:

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
)

Waldar :ave: :bravo:

Code:

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;

15/09/2010, 18h21
xdescamp

Citation:

Envoyé par milka

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.

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

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
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

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

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
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:

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.

01/02/2012, 12h38
Waldar

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

Code:

dense_rank() over(PARTITION BY to_char(dt, 'yyyymm') ORDER BY dt ASC) AS grp
01/02/2012, 14h49
milka

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

Merci

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:

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:

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

03/02/2012, 10h04
fri.du

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

Milka, as-tu trouvé la réponse à ta dernière question ?
03/02/2012, 11h19
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 !