Reconstitution de périodes

**finedel** · 12/09/2017, 10h03

Bonjour,

J'interroge des tables oracle via SQL plus. J'aurai souhaité reconstituer des périodes. Je m'explique.

Est ce que quelqu'un saurait me dire comment je peux reconstituer des périodes qui peuvent être coupées de 3 jours.
Ma table contient 3 variables : Identifiant, début période et fin période
Un tri est réalisé par identifiant, par début de période et par fin de période

Voici un exemple

table intial
PAT DEB FIN
1 20160101 20160106
1 20160108 20160110
1 20160111 20160115
1 20160201 20160210
2 20160501 20160504
2 20160506 20160510

ce que je veux en sortie : Je considère que la période est la même lorsque la différence entre le début de la période (ligne l +1) et la fin de la période (ligne l) est inférieure ou égale à 3
PAT DEB FIN
1 20160101 20160115
1 20160201 20160210
2 20160501 20160510

une idée en SQL ?

d'avance merci !

**CinePhil** · 12/09/2017, 13h58

Je pense qu'il faut une CTE. Je ne suis pas du tout à l'aise avec ça vu que je travaille principalement avec MySQL et que ça n'y existe pas mais ça doit être un truc de ce style :

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
WITH periodes_groupees AS
(
	SELECT t1.PAT, 
		t1.DEB DEB_t1, 
		t1.FIN FIN_t1,
		t2.DEB DEB_t2,
		t2.FIN FIN_t2
	FROM la_table t1
	LEFT OUTER JOIN la_table t2 ON t2.PAT = t1.PAT
	WHERE TO_DATE(t2.DEB, 'YYYYMMDD') - TO_DATE(t1.FIN, 'YYYYMMDD') < 4
)
SELECT PAT, 
	DEB_t1 DEB,
	FIN_t2 FIN
FROM periodes_groupees
ORDER BY PAT, DEB, FIN

**skuatamad** · 12/09/2017, 14h26

Une piste :

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
27
with t as (
select 1 as pat, 20160101 as deb, 20160106 as fin from dual union all
select 1, 20160108, 20160110 from dual union all
select 1, 20160111, 20160115 from dual union all
select 1, 20160201, 20160210 from dual union all
select 2, 20160501, 20160504 from dual union all
select 2, 20160506, 20160510 from dual
),
       regroupe as (
select pat, deb, fin
     , row_number() over(partition by pat order by deb)
     - case when lag(deb) over(partition by pat order by deb) is null then 0
            when deb - lag(fin,1,deb) over(partition by pat order by deb) > 3 then 0
            else row_number() over(partition by pat order by deb) -1 
        end as grp
  from t
)
select pat, min(deb) as min_deb, max(fin) as max_deb
  from regroupe
 group by pat, grp
 order by pat, min_deb
 
       PAT    MIN_DEB    MAX_DEB
---------- ---------- ----------
         1   20160101   20160115
         1   20160201   20160210
         2   20160501   20160510

**SQLpro** · 12/09/2017, 15h48

Élargissez vos périodes sur la borne basse en y ajoutant 3 jours et utilisez une des requêtes d'agrégation d'intervalle que j'ai donné dans ce papier :
https://blog.developpez.com/sqlpro/p...alles_en_sql_1

A +

**Mystro** · 12/09/2017, 16h17

Envoyé par skuatamad

Une piste :

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
27
with t as (
select 1 as pat, 20160101 as deb, 20160106 as fin from dual union all
select 1, 20160108, 20160110 from dual union all
select 1, 20160111, 20160115 from dual union all
select 1, 20160201, 20160210 from dual union all
select 2, 20160501, 20160504 from dual union all
select 2, 20160506, 20160510 from dual
),
       regroupe as (
select pat, deb, fin
     , row_number() over(partition by pat order by deb)
     - case when lag(deb) over(partition by pat order by deb) is null then 0
            when deb - lag(fin,1,deb) over(partition by pat order by deb) > 3 then 0
            else row_number() over(partition by pat order by deb) -1 
        end as grp
  from t
)
select pat, min(deb) as min_deb, max(fin) as max_deb
  from regroupe
 group by pat, grp
 order by pat, min_deb
 
       PAT    MIN_DEB    MAX_DEB
---------- ---------- ----------
         1   20160101   20160115
         1   20160201   20160210
         2   20160501   20160510

Attention, si tu ajoute les lignes suivantes dans ta table de départ ça ne fonctionne plus ...

Code :

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

1
2
select 1, 20160301, 20160310 from dual union all
select 1, 20160311, 20160315 from dual union all

**skuatamad** · 12/09/2017, 16h41

Envoyé par Mystro

Attention, si tu ajoute les lignes suivantes dans ta table de départ ça ne fonctionne plus ...

Effectivement, plutôt comme ça :

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
27
with t as (
select 1 as pat, 20160101 as deb, 20160106 as fin from dual union all
select 1, 20160108, 20160110 from dual union all
select 1, 20160111, 20160115 from dual union all
select 1, 20160201, 20160210 from dual union all
select 1, 20160301, 20160310 from dual union all
select 1, 20160311, 20160315 from dual union all
select 2, 20160501, 20160504 from dual union all
select 2, 20160506, 20160510 from dual
),
       detecte_groupe as (
select pat, deb, fin
     , case when lag(deb) over(partition by pat order by deb) is null then 1
            when deb - lag(fin,1,deb) over(partition by pat order by deb) > 3 then 1
            else  0
        end as flag_grp
  from t
),
       regroupe as ( 
select pat, deb, fin
     , sum(flag_grp) over(partition by pat order by deb) as grp
  from detecte_groupe d
)
select pat, min(deb) as min_deb, max(fin) as max_deb
  from regroupe
 group by pat, grp
 order by pat, min_deb

**finedel** · 12/09/2017, 17h28

Merci pour vos rapides retours

juste une question : pourquoi cela ne fonctionnait pas en ajoutant les 2 lignes de codes

select 1, 20160301, 20160310 from dual union all
select 1, 20160311, 20160315 from dual union all

merci !

**skuatamad** · 12/09/2017, 17h44

Pour comprendre les étapes intermédiaires particulièrement avec des résultats agrégés, il suffit d'exécuter les différentes étapes une par une sans agrégation.
Faites donc un select * from regroupe avec la 1ere requête et vous allez constater que la colonne GRP n'est pas correctement valorisée pour la ligne 1, 20160311, 20160315

**finedel** · 12/09/2017, 17h56

Ok merci

**skuatamad** · 13/09/2017, 12h08

Une autre piste avec la clause MODEL.
Un peu obscure cette clause, mais peut être plus performant pour déterminer le groupe que la solution précédente en 2 étapes :

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 t as (
select 1 as pat, 20160101 as deb, 20160106 as fin from dual union all
select 2, 20160501, 20160504 from dual union all
select 1, 20160111, 20160115 from dual union all
select 1, 20160201, 20160210 from dual union all
select 1, 20160301, 20160310 from dual union all
select 1, 20160108, 20160110 from dual union all
select 1, 20160311, 20160315 from dual union all
select 2, 20160506, 20160510 from dual
),
       regroupe as (
select pat, deb, fin, grp
from t
model
partition by (pat)
dimension by (row_number() over(partition by pat order by deb) rn)
measures (deb, fin, 0 grp)
rules (
   grp[rn] = case when deb[cv()] - fin[cv()-1] > 3 then nvl(grp[cv()-1],0)+1 else nvl(grp[cv()-1],0) end
 )
)
select pat, min(deb) as min_deb, max(fin) as max_fin
  from regroupe
 group by pat, grp
 order by pat, min_deb

Reconstitution de périodes

SQL Oracle

Discussions similaires

Partager

Partager