récursivité ou non ?

**gontrand16** · 11/12/2008, 19h36

Bonjour,

j'ai un problème qui m'a été posé et je ne sais pas comment le solutionner. Ca me semble de la récursivité et je ne sais pas si je peux le faire en une et une seule requête. Je suis sous teradata.

J'ai un numéro d'établissement avec une date de début et une date de fin. Parfois l'établissement peut être fermé et rouvert le lendemain. Des fois, il peut être fermé et ouvert quelques mois plus tard. Voici un exemple, pour un établissement 1, j'ai une date de début à 2000-01-01 et la fermeture à 2000-01-31. Il rouvre le lendemain à 2000-02-01 et ferme le 2000-03-01. Il ouvre à nouveau le 2000-03-02 et ferme le 2000-03-31. Et pour terminer, il ouvre le 2000-06-01 et ferme le 2000-06-30.

Ce que je dois faire afficher est les périodes qui se suivent. Ici se serait du 2000-01-01 au 2000-03-31. Ainsi que le 2000-06-01 au 2000-06-30.

Est-ce que quelqu'un aurait une piste de solution ?

Voici un bout de code qui me permet de trouver ma date minimum et de commencer ma période :

Code :

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

1
2
3
4
5
6
7
select C.etab_no_etab_usuel, C.etab_dd_etab_usuel, C.etab_df_etab_usuel
from prod.v_etab_usuel C
where
C.etab_no_etab_usuel is not null and
(C.etab_no_etab_usuel, C.etab_dd_etab_usuel) in (select D.etab_no_etab_usuel, min(D.etab_dd_etab_usuel)
from prod.v_etab_usuel D
group by 1)

Merci !

**SQLpro** · 11/12/2008, 23h56

C'est un agrégat de période temporelles. l'une des requêtes les plus difficile à mettre au point (voire les ouvrages de Chris Date et Richard Stonegrass sur le sujet). j'ai déjà fait cela à plusieurs reprise. Mais je ne puis vous expliquer comment cela marche, il faudrait que j'y consacre beaucoup de temps. Or le temps c'est de l'argent...

Si vous voulez en revanche vous offrir mes services... ;-)

A +

**Waldar** · 12/12/2008, 10h47

Votre première étape sera de créer une table calendrier avec tous les jours présents, sur une plage respectable.

Scorpi0 · 12/12/2008, 11h03

Sous Oracle, il existe les fonctions LAG et LEAD vous seront d'une grande utilité.
Sinon on peux toujours les simuler, effectuer une autojointure sur un rank() ordonner par date, et ensuite bricoler pour arriver au résultat.

Il nous faudrait des scripts de données si vous voulez une requête ^^

**cavo789** · 12/12/2008, 11h56

J'ai déjà dû réaliser un truc du genre en (désolé, honte à moi, ...) MSAccess.

J'avais alors créé un query qui reprennant le champs primary key me permettant de retrouver la société et la date de début et de fin. Date de début à laquelle je retranchais un jour.

Ainsi, dans ton exemple :

Société ID Date de début Date de fermeture
AAAAAAAA 2000-01-01 2000-01-31
AAAAAAAA 2000-02-01 2000-03-01
AAAAAAAA 2000-03-02 2000-03-31
AAAAAAAA 2000-06-01 2000-06-30

Deviendrait avec un petit SQL date de début-1

Société ID Date de début-1
AAAAAAAA 1999-12-31
AAAAAAAA 2000-01-31
AAAAAAAA 2000-03-01
AAAAAAAA 2000-05-31

Ensuite, quand tu lies ce query et ta table d'origine, tu fais le lien sur Date fermeture et date début -1. Si tu as une égalité, c'est que la société a été fermée et ouverte le lendemain. Tu prends donc dans ce cas un MIN sur la date de début (la première date de l'intervalle donc) et un MAX sur la date de fermeture. De cette manière, dans l'exemple ci-dessus, tu ne retrouveras que deux enregistrements, soit les trois premiers qui deviendraient

Société ID MIN(Date de début) MAX(Date de fermeture)
AAAAAAAA 2000-01-01 2000-03-31

et
AAAAAAAA 2000-06-01 2000-06-30

En gros, c'était comme cela que j'avais fais. J'espère ne pas avoir oublié une finesse.

Bon coding.

**pacmann** · 12/12/2008, 12h22

Salut !

Dites, vous êtes en train de casser le business de SQLPro en essayant de répondre

@Cavo, Scorpio, Valdar : le côté compliqué, c'est qu'il faut agréger éventuellement plusieurs périodes. Donc ce n'est pas en une jointure qu'on y arrive... (ni avec une utilisation basique de LEAD et LAG).

Par contre, petite question pour gontrand16 : est-ce que tu as droit aux fonctions analytiques (j'ai une vague idée marrante, mais on va avoir besoin de numéroter pas mal de trucs...) ?

**cavo789** · 12/12/2008, 12h32

Je pense que SQLPro a déjà une montagne de travail et que ses journées sont mille fois plus remplies que les nôtres (je reste toujours impressionné par son débordement d'activités)

@pacmann : peut-être ai-je été maladroit dans ma réponse mais je pense bien avoir répondu à la question. En quoi me serais-je trompé ?

**pacmann** · 12/12/2008, 12h42

Ben, j'ai eu un peu de mal à comprendre tout ton système...
... mais je crois que quand tu "lies ce query et ta table d'origine" ne permet de faire le lien qu'entre deux périodes adjacentes.
Enfin, si tu prends un MIN et un MAX, il faut savoir sur quel critère tu groupes. (c'est la partie compliquée)

Tu peux nous faire un petit exemple simple de ta requête ?

**cavo789** · 12/12/2008, 13h45

Ouch! Il m'a fallut un peu chipoter; il y avait tellement longtemps que je l'avais fait et en plus, c'était en MSAccess.

Le code ci-dessous permet de retrouver toutes les périodes consécutives. Je n'ai plus réussi à retrouver la période non consécutive mais nul doute qu'avec un UNION il devrait avoir moyen de le faire mais je n'ai plus le temps d'y travailler.

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
CREATE TABLE #Test  (Fournisseur varchar(40), DateDebut DateTime, DateFin DateTime)
CREATE TABLE #Test2 (Fournisseur varchar(40), [DateDebut-1] DateTime, [DateFin+1] DateTime)
 
INSERT INTO #Test VALUES ('AAAAAAAA', '2000-01-01', '2000-01-31')
INSERT INTO #Test VALUES ('AAAAAAAA', '2000-02-01', '2000-03-01')
INSERT INTO #Test VALUES ('AAAAAAAA', '2000-03-02', '2000-03-31')
INSERT INTO #Test VALUES ('AAAAAAAA', '2000-06-01', '2000-06-30')
 
INSERT INTO #Test2 SELECT Fournisseur, DateAdd(day, -1, DateDebut) As [DateDebut-1], DateAdd(day, +1, DateFin) As  [DateFin+1] FROM #Test
 
SELECT #Test.Fournisseur, Min(#Test.DateDebut) As MinDebut, Max(#Test.DateFin) As MaxFin
FROM #Test inner JOIN #Test2
   ON (#Test.Fournisseur=#Test2.Fournisseur) AND ((#Test2.[DateFin+1]=#Test.DateDebut) OR (#Test2.[DateDebut-1]=#Test.DateFin))
GROUP BY #Test.Fournisseur
 
DROP TABLE #Test
DROP TABLE #Test2

**pacmann** · 12/12/2008, 13h58

GROUP BY Fournisseur

=> On n'aura qu'une ligne par fournisseur ?

**pacmann** · 12/12/2008, 16h45

Bon allez, pour être constructif et ne pas me contenter de critiquer ta solution, je tente ma chance :

1) Utilisons le LEAD et LAG de scorpio pour que sur chaque ligne, on récupère la date d'ouverture suivante et la date de fermeture précédente
2) En faisant des additions et soustraction de dates, on exprime pour chaque ligne si elle est collée à la suivante / à la précendente
3) On attribue un numéro à chaque ligne un rang global en ordonnant par date d'ouverture
Voilà où on en est. Je ne mets pas les dates précédentes et suivantes, parce que seul les flags nous intéressent pour créer une chaine :

Etablissement DateD DateF ColleS ColleP Rang
1 2000-01-01 2000-01-31 1 0 1
1 2000-02-01 2000-03-01 1 1 2
1 2000-02-01 2000-03-01 0 1 3
1 2000-06-01 2000-06-31 0 0 4

4) Regardons très fort ce truc, et demandons nous ce qui permet de regrouper les séquences contigues :
Une séquence commence avec ColleS = 1 et se finit au premier ColleS = 0
Les couples ColleS = 0, ColleP = 0 sont des séquences mono-élément.
Donc, pour attribuer un numéro de groupe conformément à ce constat, il suffit de d'affecter comme numéro de chaque ligne :
- soit son rang lorsque son Colles S = 0
- soit le rang de la ligne de la première ligne de rang strictement supérieur et de ColleS = 0
Bon au final, ça fait juste : la première ligne de rang >= dont le ColleS vaut 0

On remarquera qu'en fait, on n'a pas besoin du CollP, mais bon dans le principe, ça pouvait être utile.

Maintenant, la requête finale pour faire tout ç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
 
WITH t AS 
(
SELECT Etablissement, DateD, DateF
, CASE(EXTRACT(DAY FROM LAG(DateD, 1) OVER(PARTITION BY etablissement ORDER BY DateD) - DateF)) WHEN 1 THEN 1 ELSE 0 END as ColleS
, row_number() OVER(PARTITION BY Etablissement ORDER BY DateD) As Rk
FROM TaTable
) 
SELECT Etablissement min(DateD), max(DateF)
FROM(
SELECT t1.Etablissement, t1.DateD, t1.DateF, 
            (SELECT min(t2.Rk) FROM t as t2 
             WHERE t2.Etablissement = t1.Etablissement 
               AND t2.Rk >= t1.rk
               AND t2.ColleS = 0) AS grp
FROM t as t1 
) t3
GROUP BY Etablissement, grp

Scorpi0 · 12/12/2008, 17h19

Envoyé par pacmann

ce n'est pas en une jointure qu'on y arrive... (ni avec une utilisation basique de LEAD et LAG).

Mais si mais si :p

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
28
29
30
31
32
33
34
35
36
37
with myTable as (
select 'AAAAAAAA' as code, to_date('2000-01-01','YYYY-MM-DD') as dt_deb, to_date('2000-01-15','YYYY-MM-DD') as dt_fin from dual union all
select 'AAAAAAAA', to_date('2000-01-15','YYYY-MM-DD'), to_date('2000-01-30','YYYY-MM-DD') from dual union all
select 'AAAAAAAA', to_date('2000-03-01','YYYY-MM-DD'), to_date('2000-03-10','YYYY-MM-DD') from dual union all
select 'AAAAAAAA', to_date('2000-03-11','YYYY-MM-DD'), to_date('2000-03-12','YYYY-MM-DD') from dual union all
select 'AAAAAAAA', to_date('2000-03-13','YYYY-MM-DD'), to_date('2000-03-14','YYYY-MM-DD') from dual union all
select 'AAAAAAAA', to_date('2000-03-15','YYYY-MM-DD'), to_date('2000-03-16','YYYY-MM-DD') from dual union all
select 'AAAAAAAA', to_date('2000-03-17','YYYY-MM-DD'), to_date('2000-03-30','YYYY-MM-DD') from dual union all
select 'AAAAAAAA', to_date('2000-08-01','YYYY-MM-DD'), to_date('2000-08-31','YYYY-MM-DD') from dual union all
select 'AAAAAAAA', to_date('2000-10-01','YYYY-MM-DD'), to_date('2000-10-15','YYYY-MM-DD') from dual union all
select 'AAAAAAAA', to_date('2000-10-16','YYYY-MM-DD'), to_date('2000-10-21','YYYY-MM-DD') from dual union all
select 'AAAAAAAA', to_date('2000-10-22','YYYY-MM-DD'), to_date('2000-10-31','YYYY-MM-DD') from dual union all
select 'BBBBBBBB', to_date('2000-03-17','YYYY-MM-DD'), to_date('2000-03-30','YYYY-MM-DD') from dual union all
select 'BBBBBBBB', to_date('2000-08-01','YYYY-MM-DD'), to_date('2000-08-31','YYYY-MM-DD') from dual union all
select 'BBBBBBBB', to_date('2000-10-01','YYYY-MM-DD'), to_date('2000-10-15','YYYY-MM-DD') from dual union all
select 'BBBBBBBB', to_date('2000-10-16','YYYY-MM-DD'), to_date('2000-10-21','YYYY-MM-DD') from dual union all
select 'BBBBBBBB', to_date('2000-10-22','YYYY-MM-DD'), to_date('2000-10-31','YYYY-MM-DD') from dual)
select * from
(
  select code,
         case when dt_deb_previous is null then dt_deb else dt_deb_next end as dt_deb,
         case when dt_deb_next is null then dt_fin else lead(dt_fin, 1) over(partition by code order by dt_deb) end as dt_fin
  from
  (
    select code,
           lag(dt_deb, 1) over(partition by code order by dt_deb) dt_deb_previous,
           lag(dt_fin, 1) over(partition by code order by dt_deb) dt_fin_previous,
           dt_deb, dt_fin,
           lead(dt_deb, 1) over(partition by code order by dt_deb) dt_deb_next,
           lead(dt_fin, 1) over(partition by code order by dt_deb) dt_fin_next
    from myTable
  )
  where dt_fin != dt_deb_next - 1
  or dt_deb_next is null
  or dt_deb_previous is null
)
where dt_deb is not null

Resultat :

Code :

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

1
2
3
4
5
6
AAAAAAAA	01/01/2000	30/01/2000
AAAAAAAA	01/03/2000	30/03/2000
AAAAAAAA	01/08/2000	31/08/2000
AAAAAAAA	01/10/2000	31/10/2000
BBBBBBBB	17/03/2000	31/08/2000
BBBBBBBB	01/10/2000	31/10/2000

**SQLpro** · 12/12/2008, 18h01

Votre requête ne traitera que les cas dans lesquels la date d'ouverture est le jour d'après le jour de fermeture. Si le jour de fermeture a été prévu après et que l'on rouvre avant, je pense que la requête ne donnera pas le bon résultat.

A +

**gontrand16** · 12/12/2008, 18h37

Merci, je vais regarder vos suggestions et voir si quelque chose peut m'être utile.

Je savais que c'était compliqué.

Merci d'avoir porté attention à ma demande, cela m'encourage.

J1 · 13/12/2008, 16h27

Bonjour,

Envoyé par gontrand16

j'ai un problème qui m'a été posé et je ne sais pas comment le solutionner. Ca me semble de la récursivité et je ne sais pas si je peux le faire en une et une seule requête. Je suis sous teradata.

J'ai un numéro d'établissement avec une date de début et une date de fin. Parfois l'établissement peut être fermé et rouvert le lendemain. Des fois, il peut être fermé et ouvert quelques mois plus tard. Voici un exemple, pour un établissement 1, j'ai une date de début à 2000-01-01 et la fermeture à 2000-01-31. Il rouvre le lendemain à 2000-02-01 et ferme le 2000-03-01. Il ouvre à nouveau le 2000-03-02 et ferme le 2000-03-31. Et pour terminer, il ouvre le 2000-06-01 et ferme le 2000-06-30.

Ce que je dois faire afficher est les périodes qui se suivent. Ici se serait du 2000-01-01 au 2000-03-31. Ainsi que le 2000-06-01 au 2000-06-30.

Question intéressante !
Une solution sans fonction de classement/fenêtrage :

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
 
SELECT O1.E, O1.DD , (
-- 3. on identifie la date de fin de la période :
	SELECT MIN(DF)
	FROM O O2
	WHERE E = O1.E
	AND DF >= O1.DF
	AND NOT EXISTS (
		SELECT 1
		FROM O
		WHERE E = O2.E
		AND DD = O2.DF+1)) AS DF
-- 1. on balaye la table :
FROM O O1
-- 2. on se limite aux lignes dont la date de début 
-- constitue le début d'une période pour l'établissement concerné :
WHERE NOT EXISTS (
	SELECT 1
	FROM O
	WHERE E = O1.E
	AND DF = O1.DD-1);

Le jeu de données pour la tester :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
-- On crée la table de périodes d'ouverture O.
-- La colonne E indique l'établissement, la colonne DD la date de début, la colonne DF la date de fin
CREATE TABLE O (E INTEGER, DD DATETIME, DF DATETIME);
INSERT INTO O VALUES (1, '01/01/01', '03/01/01');
INSERT INTO O VALUES (1, '04/01/01', '06/01/01');
INSERT INTO O VALUES (1, '08/01/01', '09/01/01');
INSERT INTO O VALUES (1, '10/01/01', '11/01/01');
INSERT INTO O VALUES (1, '12/01/01', '14/01/01');
INSERT INTO O VALUES (2, '07/01/01', '09/01/01');

Le résultat :

Code :

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

1
2
3
4
5
 
E	DD		DF
1	01/01/2001	06/01/2001
1	08/01/2001	14/01/2001
2	07/01/2001	09/01/2001

@pacmann : je n'ai pas testé ta solution, mais chapeau pour l'aspect didactique de ton post. C'était très intéressant à lire !

@SQLpro : la règle de gestion sous-entendue par gontrand16 ne s'intéresse qu'aux périodes qui se succèdent strictement (DD=DF+1), mais il serait en effet intéressant de gérer des chevauchements de périodes. A étudier à l'occasion.

**SQLpro** · 14/12/2008, 16h44

De mémoire j'ai du donné un exercice dans mon bouquin qui donne une solution à ce problème.
CHAPITRE 5, Exercice 28 dans la première édition (sans CTE, ni fonctions de fenêtrage) deux solutions différentes.

A +

**cavo789** · 15/12/2008, 07h19

Envoyé par pacmann

GROUP BY Fournisseur

Oups...

Dès que possible, je jetterais un coup d'oeil sur ta solution. Malheureusement je vais être très pris maintenant jusqu'à fin 2008.

jnore · 15/12/2008, 22h20

Envoyé par SQLpro

De mémoire j'ai du donné un exercice dans mon bouquin qui donne une solution à ce problème.
CHAPITRE 5, Exercice 28 dans la première édition (sans CTE, ni fonctions de fenêtrage) deux solutions différentes.

A +

Sur votre book version 2001, je ne vois rien de tel dans ce chapitre.
serait-ce dans le chapitre 8 ?