Récursivité chronologique

**ampex** · 28/10/2015, 12h38

Bonjour,

je travaille actuellement dans la création d'un CTE qui aura comme particularité de rechercher une suite de ligne qui succèdent ou précèdent à l'une d'entre elle.
La CTE est temporelle : elle se base sur les identifiants des lignes qui précèdent ou succèdent dans le temps.

Concrètement, je me suis basé sur l’excellent article de sqlpro en créant une union de 2 CTE, une pour rechercher les successeurs, une autre pour les prédécesseurs.

Les CTE fonctionnent bien mais génèrent néanmoins des doublons. En effet, la CTE me génère plusieurs séries identiques selon la ligne de départ.
Je m'explique.
Soit les lignes A, B, C, D chacune se succédant dans cette ordre.
La CET me génère les successions A, B, C, D mais aussi B, C, D et C, D.

J'aimerai pourtant ne garder que la succession la plus longue !

voici ma CTE, basé sur le paragraphe IV-B-1. du l'article avec 3 statistiques : JOUR (nombre de jour lors de la succession, CHANGE (changement d'id d'activité), NIVEAU (pour l'arborescence) et CASTING (Exemple 27 de l'article, pour eviter 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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
WITH 
	pred(rchch,Etiquette,[ETA_Id], [ETA_DateControle],[ETA_Predecesseur_ETA_Id],[ETA_Successeur_ETA_Id], [ETA_DateDebut],[ETA_DateFin],	new_surface,
	 JOUR, CHANGE, niveau, CASTING)
AS (SELECT 'pred', T_Etablissement.Etiquette	, T_Etablissement.[ETA_Id], [ETA_DateControle],[ETA_Predecesseur_ETA_Id],[ETA_Successeur_ETA_Id], [ETA_DateDebut],[ETA_DateFin], [VINFODTBX].new_surface,
		 0, CAST([ETA_ApeEtablissement_APE_Id] as varchar(max)) , 0,
		CAST(T_Etablissement.ETA_Id as varchar(max))
  FROM [Observatoire].dbo.T_Etablissement
	INNER JOIN [Observatoire].dbo.T_Entreprise 
		ON T_Entreprise.ENT_Id = T_Etablissement.ETA_Entreprise_ENT_Id
	INNER JOIN [Observatoire].[EXPLOITATION].[VINFODTBX]
		ON [VINFODTBX].ETA_Id=T_Etablissement.ETA_Id
    WHERE  [VINFODTBX].new_surface is not null  
UNION ALL
SELECT 'pred', P.Etiquette, P.ETA_Id ,P.[ETA_DateControle],P.[ETA_Predecesseur_ETA_Id],P.[ETA_Successeur_ETA_Id], P.[ETA_DateDebut],P.[ETA_DateFin],[VINFODTBX].new_surface,
/*JOUR*/		DATEDIFF(DAY,t.ETA_DateDebut,P.ETA_DateFin),
/*CHANGE*/		CASE WHEN t.CHANGE!=CAST(P.ETA_ApeEtablissement_APE_Id as varchar(max)) then CAST('1' as varchar(max))  else CAST('0' as varchar(max)) end,
/*NIVEAU*/      t.niveau + 1, 
/*CASTING*/		CASTING + ', '+CAST(P.ETA_Id as varchar(max))
  FROM [Observatoire].[dbo].[T_Etablissement] P
	INNER JOIN [Observatoire].dbo.T_Entreprise 
		ON T_Entreprise.ENT_Id = P.ETA_Entreprise_ENT_Id
	INNER JOIN [Observatoire].[EXPLOITATION].[VINFODTBX]
		ON [VINFODTBX].ETA_Id=P.ETA_Id
    INNER JOIN pred t on P.ETA_Id=t.ETA_Predecesseur_ETA_Id 
	WHERE  CASTING not like '%, ' + cast(P.ETA_Id as varchar(40)) + '%'
	) 
,
	succ(rchch, Etiquette,[ETA_Id],[ETA_DateControle],[ETA_Predecesseur_ETA_Id],[ETA_Successeur_ETA_Id],  [ETA_DateDebut],[ETA_DateFin], new_surface,
		JOUR, CHANGE, niveau, CASTING)
AS (SELECT  'succ', T_Etablissement.Etiquette,  T_Etablissement.[ETA_Id],[ETA_DateControle],[ETA_Predecesseur_ETA_Id],[ETA_Successeur_ETA_Id],[ETA_DateDebut], [ETA_DateFin],[VINFODTBX].new_surface,
		 0, CAST(ETA_ApeEtablissement_APE_Id as varchar(max)), 0,
		CAST(T_Etablissement.ETA_Id as varchar(max))
  FROM [Observatoire].dbo.T_Etablissement
	INNER JOIN [Observatoire].dbo.T_Entreprise 
		ON T_Entreprise.ENT_Id = T_Etablissement.ETA_Entreprise_ENT_Id
	INNER JOIN [Observatoire].[EXPLOITATION].[VINFODTBX]
		ON [VINFODTBX].ETA_Id=T_Etablissement.ETA_Id
    WHERE  [VINFODTBX].new_surface is not null  
 
UNION ALL
SELECT 'succ', S.Etiquette, S.[ETA_Id],S.[ETA_DateControle],S.[ETA_Predecesseur_ETA_Id],S.[ETA_Successeur_ETA_Id], S.[ETA_DateDebut], S.[ETA_DateFin],[VINFODTBX].new_surface,
/*JOUR*/		DATEDIFF(DAY,tt.ETA_DateDebut,S.ETA_DateFin),
/*CHANGE*/		CASE WHEN  tt.CHANGE!=CAST(S.ETA_ApeEtablissement_APE_Id as varchar(max)) then CAST('1' as varchar(max))  else CAST('0' as varchar(max)) end,
/*NIVEAU*/      tt.niveau + 1, 
/*CASTING*/		CASTING + ', '+CAST(S.ETA_Id as varchar(max))
  FROM [Observatoire].[dbo].[T_Etablissement] S
  	INNER JOIN [Observatoire].dbo.T_Entreprise 
		ON T_Entreprise.ENT_Id = S.ETA_Entreprise_ENT_Id
	INNER JOIN [Observatoire].[EXPLOITATION].[VINFODTBX]
		ON [VINFODTBX].ETA_Id=S.ETA_Id
    INNER JOIN succ tt on S.ETA_Id=tt.ETA_Successeur_ETA_Id
    WHERE  CASTING not like '%, ' + cast(S.ETA_Id as varchar(40)) + '%' 
    and  not exists(select ETA_Id from pred where pred.ETA_Id=S.ETA_Id)
    )
 
 
select DISTINCT  niveau , SPACE(niveau)+Etiquette as Etiquette
		,[ETA_Id],[ETA_DateControle],[ETA_Predecesseur_ETA_Id],[ETA_Successeur_ETA_Id],  [ETA_DateDebut], 
				[ETA_DateFin],new_surface,JOUR,CHANGE,CASTING 
	from (Select  *  from pred 
			union 
		  Select  * from succ --where not exists(select ETA_Id from pred where pred.ETA_Id=succ.ETA_Id)
		  )  as info 
	order by CASTING

Merci pour vos lumières, je sèche.

amp

**aieeeuuuuu** · 28/10/2015, 13h38

Bonjour,

L'exemple 27 de l'article vise à éviter les cycles sans fin, mais cela ne semble pas être votre cas.

Dans votre cas, il suffirait peut-être de modifier la partie ancrage de la CTE afin de ne prendre que la ligne "A", et non pas A,B,C et D.

Je dis peu-être, car je ne suis pas sûr d'avoir bien compris votre besoin, et je n'ai pas encore lu en détail la requête que vous avez posté.

A ce propos, vous pourriez utiliser une CTE (non récursive) qui ferait dans un premier temps la jointure entre toutes vos tables, afin de factoriser votre requête et la rendre plus lisible

**ampex** · 28/10/2015, 14h09

Envoyé par aieeeuuuuu

Bonjour,

Dans votre cas, il suffirait peut-être de modifier la partie ancrage de la CTE afin de ne prendre que la ligne "A", et non pas A,B,C et D.

Merci pour votre intervention.
Le problème c'est que mon A n'est pas "déterminable" car la CTE se fixe seulement sur les lignes qui ont [new_surface] is not null. Ces lignes ont souvent des prédécesseur mais peuvent aussi avoir des successeurs (donc une position en "A"), successeurs qui peuvent avoir des "new_surface" is not null (d’où le doublon)
EX :
position=>new_surface
A=>500
B=>300
C=>NULL
D=>NULL
résultat : A,B,C,D et B,C,D

C'est pour ça que j'ai utiliser la méthode pour éviter les cycles mais qui effectivement n’empêche pas les doublons dans ces cas.

Envoyé par aieeeuuuuu

A ce propos, vous pourriez utiliser une CTE (non récursive) qui ferait dans un premier temps la jointure entre toutes vos tables, afin de factoriser votre requête et la rendre plus lisible

Alors là ça dépasse mes capacités de compréhension.

**Oishiiii** · 28/10/2015, 14h26

Est-ce vous pourriez nous fournir les tables (CREATE TABLE..) et quelques données (INSERT..) pour que l'on puisse tester facilement de notre côté ?

**ampex** · 28/10/2015, 15h39

Envoyé par Oishiiii

Est-ce vous pourriez nous fournir les tables (CREATE TABLE..) et quelques données (INSERT..) pour que l'on puisse tester facilement de notre côté ?

Voila :

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
CREATE TABLE [dbo].[TABLE](
	[ETA_Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ETA_DateDebut] [date] NULL,
	[ETA_DateFin] [date] NULL,
	[ETA_Predecesseur_ETA_Id] [uniqueidentifier] NULL,
	[ETA_Successeur_ETA_Id] [uniqueidentifier] NULL,
 
INSERT INTO [dbo].[TABLE]
           ([ETA_Id]
           ,[ETA_DateDebut]
           ,[ETA_DateFin]
           ,[ETA_Predecesseur_ETA_Id]
           ,[ETA_Successeur_ETA_Id]
)
     VALUES
('0254AAD4-040E-4E30-9835-C20CC0527263','2010-02-28','NULL','A5A4326D-CB99-4912-BEEC-D93CBAD12C25','NULL','450'),
('A5A4326D-CB99-4912-BEEC-D93CBAD12C25','2003-03-31','2010-02-27','3F427DD3-8B01-4B3F-BF07-7FC90757AA1D','0254AAD4-040E-4E30-9835-C20CC0527263','NULL'),
('3F427DD3-8B01-4B3F-BF07-7FC90757AA1D','1988-02-19','2002-04-29','NULL','A5A4326D-CB99-4912-BEEC-D93CBAD12C25','NULL'),
('52CE697E-6A98-4C48-9AC6-B1818D989A55','2015-06-30','NULL','0397F4FE-AE86-434B-A749-A47FF1C7C2F5','NULL','400'),
('0397F4FE-AE86-434B-A749-A47FF1C7C2F5','2008-09-09','2015-06-30','6B6D36DE-2977-42C6-ACF1-5DD216CFF914','52CE697E-6A98-4C48-9AC6-B1818D989A55','400'),
('6B6D36DE-2977-42C6-ACF1-5DD216CFF914','2006-04-30','2008-08-30','NULL','0397F4FE-AE86-434B-A749-A47FF1C7C2F5','NULL')

**aieeeuuuuu** · 28/10/2015, 14h36

Envoyé par ampex

EX :
position=>new_surface
A=>500
B=>300
C=>NULL
D=>NULL
résultat : A,B,C,D et B,C,D

Qu'attendez vous comme résultat dans ce cas, et surtout, qu'attendez vous comme résultat pour le cas suivant :

position=>new_surface
A=>NULL
B=>300
C=>500
D=>NULL

Envoyé par ampex

Alors là ça dépasse mes capacités de compréhension.

L'idée est simplement d'utiliser une CTE comme une vue (d'ailleurs, vous pourriez aussi créer une vue, surtout si d'autre requêtes font les mêmes jointures) :

Quelque chose sur ce principe

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
 
WITH Vue AS (
    SELECT --les colonnes nécessaires pour la suite
    FROM [Observatoire].[dbo].[T_Etablissement] P
	INNER JOIN [Observatoire].dbo.T_Entreprise 
		ON T_Entreprise.ENT_Id = P.ETA_Entreprise_ENT_Id
	INNER JOIN [Observatoire].[EXPLOITATION].[VINFODTBX]
		ON [VINFODTBX].ETA_Id=P.ETA_Id
    INNER JOIN pred t on P.ETA_Id=t.ETA_Predecesseur_ETA_Id 
),
pred(...) AS (
    SELECT 'pred' ,...
    FROM Vue --
    UNION ALL
    SELECT ....
    FROM Vue
),...

Cela évite de répéter quatre fois tout votre bloc de jointures, mais également de mettre des alias sur les noms de colonne afin d'alléger l'écriture du reste de la requête.

**ampex** · 28/10/2015, 14h59

Envoyé par aieeeuuuuu

Qu'attendez vous comme résultat dans ce cas, et surtout, qu'attendez vous comme résultat pour le cas suivant :

position=>new_surface
A=>NULL
B=>300
C=>500
D=>NULL

Dans ce cas de figure, c'est bien ce resultat que j'attend, voire même une réplication de la new_surface la plus récente (B) sur la position la plus récente (A) et inversement, soit :
A=>300
B=>300
C=>500
D=>500

**ampex** · 29/10/2015, 09h05

Envoyé par aieeeuuuuu

Bonjour,

L'exemple 27 de l'article vise à éviter les cycles sans fin, mais cela ne semble pas être votre cas.

En faite si, car il arrive dans certain cas que la succession se fasse mal et qu'un cycle se forme sur une partie des lignes. Si je ne mets pas ce contrôle, le maxrecursion est atteint pour ces cas et la CTE ne va pas jusqu'au bout.

**rudib** · 29/10/2015, 09h31

Hello,

Je m'apprêtais à regarde ta requête pour t'aider. Mais je vois la requête et je me dis : "je laisse tomber, je n'ai pas de données de test". Comment faire pour corriger la requête et obtenir le bon résultat à l'aveugle. Tu penses qu'on est les Garry Kasparov du SQL ?
Ensuite je vois le message d'Oishiiii. Je me dis "quel brave homme/femme !", et je me dis aussi qu'il doit avoir l'habitude et faire des copier/coller pour ce genre de message, et qu'il doit l'avoir envoyé des centaines de fois.
Je scrolle un peu pour voir si tu l'as fait, et je tombe sur un CREATE TABLE, je me dis "parfait, allons-y !"
Ensuite je regarde la requête, et je vois qu'elle fait des jointures sur plusieurs tables, et qu'elle manipule des colonnes comme "CHANGE, niveau, CASTING" que je ne vois pas dans la seule table qui est donnée comme exemple.
Et là je laisse tomber, je me dis "encore un cas de personne qui n'a pas envie de prendre plus de deux minutes à poser une question et à qui n'est pas dérangé par l'idée que celui qui voudrait répondre sérieusement devrait passer au moins une demi-heure à recréer des tables et des données pour ce faire".

**Oishiiii** · 29/10/2015, 09h46

J'ai essayé de bricoler quelque chose de mon côté; sans succès.
Au final, je crains n'avoir pas bien compris le besoin.

**ampex** · 29/10/2015, 09h59

Envoyé par rudib

Hello,

Je m'apprêtais à regarde ta requête pour t'aider. Mais je vois la requête et je me dis : "je laisse tomber, je n'ai pas de données de test". Comment faire pour corriger la requête et obtenir le bon résultat à l'aveugle. Tu penses qu'on est les Garry Kasparov du SQL ?
Ensuite je vois le message d'Oishiiii. Je me dis "quel brave homme/femme !", et je me dis aussi qu'il doit avoir l'habitude et faire des copier/coller pour ce genre de message, et qu'il doit l'avoir envoyé des centaines de fois.
Je scrolle un peu pour voir si tu l'as fait, et je tombe sur un CREATE TABLE, je me dis "parfait, allons-y !"
Ensuite je regarde la requête, et je vois qu'elle fait des jointures sur plusieurs tables, et qu'elle manipule des colonnes comme "CHANGE, niveau, CASTING" que je ne vois pas dans la seule table qui est donnée comme exemple.
Et là je laisse tomber, je me dis "encore un cas de personne qui n'a pas envie de prendre plus de deux minutes à poser une question et à qui n'est pas dérangé par l'idée que celui qui voudrait répondre sérieusement devrait passer au moins une demi-heure à recréer des tables et des données pour ce faire".

ok désolé pour la confusion.. J'ai hésité justement de mettre quelques données car la requête fait appel à plusieurs tables et ça devient vite compliqué dans un forum.
Donc j'ai simplifié au maximum en ne prenant que la table qui a des données qui intéresse la CTE et j'ai occulté les stat que je fais générer lors de la CTE ("CHANGE, niveau"). Reste CASTING que je dois garder pour éviter le maxrecursion.

Je n'ai garder donc que les ID et ID prédécesseur et ID Successeur sur lesquelles tourne la CTE, avec les date de début et de fin pour vérifier la cohérence.

Cet effort de simplification proposé par aieeeuuuuu m'a permis d'obtenir un nouveau résultat encourageant avec "que" 36 doublons que je suis en train de pointer pour vérifier leur provenance.

**ampex** · 29/10/2015, 15h01

bon, pas besoin d'être Kasparov pour voir ce qui cloche : c'est bien le fait de supprimer les cyles (CASTING) avec la méthode sqlpro qui génère des doublons. Dans cette méthode, on part d'une attribut de "départ" (PARIS) et d'un attribut d'arrivé alors que dans mon cas, l'attribut de départ est la notion de surface et il n'y a pas d'attribut d'arrivé.

Exemple, si je reprend mes données (simplifiée en 2 cycles, lettre et chiffre) :

ID PRED SUCC SURF
1 2 NULL 450
2 3 1 NULL
3 NULL 2 NULL
A B NULL 400
B C A 400
C NULL B NULL

J'obtiens bien un cycle 1,2,3 et A,B,C mais j'obtiens aussi B,C car A et B contiennent tout deux une valeur SURF qui est la clé d'entré.

Pour qu'un select distinct sur CASTING m’enlève bien les doublons il faudrait "désagréger" le CASTING dans le select final ou alors trouver une autre méthode que ce CASTING.

Récursivité chronologique

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager