Select Distinct dans un CONCAT

Version imprimable

06/09/2016, 15h08
ampex

2 pièce(s) jointe(s)

Select Distinct dans un CONCAT

Bonjour,

Je cherche à réduire les résultat d'un SELECt en ne prenant qu'une fois les GUID compris dans un champs issu d'une concaténation.
ex : j'ai mis en couleur les GUID en doulbon...
Pièce jointe 219731
J'aimerai obtenir ça :

Pièce jointe 219732

Est ce techniquement possible dans un select de ne garder que les GUID compris dans la plus grande chaine de concatenation ?

Merci pour vos lumières
amp
06/09/2016, 15h32
escartefigue

Bonjour,

Le plus simple est d'extraire les valeurs distinctes avant de concaténer les résultats
Sinon ca va être une usine a gaz, est ce qu'il peut y avoir au maxi 3 concaténations comme dans votre exemple ?
06/09/2016, 15h38
ampex

bonjour,

les concaténations sont issues d'une boucle récursive qui concatène des guid en fonction d'autres valeurs. J'ai obtenu au max de 8 concaténations successive pour les boucles les plus longues.
(oui je sais c'est tordu !)
vous pensiez à quoi ? une série de substring ?
06/09/2016, 16h14
escartefigue

Du coup il faut soit faire une autre requete recursive soit plein de tests en dur :aie: pour vérifier qu'il n'existe pas une autre ligne de taille plus courte qui contienne l'une des 8 valeurs de GUID concaténées dans votre colonne.
Quoi qu'il en soit c'est assez goret comme méthode :?

exemple :aie:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT DISTINCT Mon_Texte
FROM   Ma_Table AS M                                                     
WHERE [filtrage éventuel]
  AND NOT EXISTS                                                   
     (SELECT 1 from Ma_Table AS s
      WHERE S.Mon_Texte<> M.Mon_Texte                                    
        AND (   SUBSTR(S.Mon_Texte, 01, 37) = SUBSTR(M.Mon_Texte, 01, 37)
             OR SUBSTR(S.Mon_Texte, 01, 37) = SUBSTR(M.Mon_Texte, 38, 37)
             OR SUBSTR(S.Mon_Texte, 01, 37) = SUBSTR(M.Mon_Texte, 76, 37)
        [...]
             OR SUBSTR(S.Mon_Texte, 38, 37) = SUBSTR(M.Mon_Texte, 01, 37)
        [etc]
        AND LENGTH(LTRIM(RTRIM(S.Mon_Texte))) >= LENGTH(LTRIM(RTRIM(M.Mon_Texte)))))

06/09/2016, 16h23
ampex

Merci...
je vais regarder en ce sens... sans oublier la récursive..
06/09/2016, 17h30
SQLpro

Le plus simple serait d'ordonner les valeurs à concaténer et de ne concaténer effectivement que si la valeur finale est différente de la valeur nouvelle à rajouter, tout cela de manière récursive.

A +
06/09/2016, 17h41
StringBuilder

Juste en passant...

Sinon, avec le besoin (et requête) complet, peut-être qu'une solution plus simple/propre pourrait nous sauter aux yeux ?

La plupart du temps, quand on ne trouve pas de solution simple, c'est qu'on cherche pas au bon endroit :)
06/09/2016, 17h46
François DORIN
Dans les champs issus de concaténation, est-ce que l'ordre sera toujours le même ?

Exemple (avec des lettres, pas des GUID, mais le principe reste le même) :
Code:

1 2 3 4 5 6 7 8 9 10 A B C A, B A, C A, B, C A, C, B B, C, A B, C
Est-il possible d'avoir ce genre de jeu de données ?
06/09/2016, 17h49
StringBuilder

Et moi je veux toujours savoir par quel moyen on arrive à ce résultat intermédiaire :o

Car là on essaie de faire un traitement ensembliste sur des données mises en forme.

Selon le problème initial, il est peut-être parfaitement possible d'avoir d'autres résultats intermédiaires ayant déjà le distinct effectué, qu'il sera alors plus simple d'agréger.
Ou alors la méthode de concaténation utilisée impose-t-elle des contraintes qu'un GROUP_CONCAT n'aurait pas ?

Bonjour à tous et merci pour vos interventions.

La concaténation obtenue de manière récursive est basée sur le modèle de sqlpro proposé dans ce même forum.
voila le principe : un ID, un ID précédent, un ID suivant
Code:

1 2 3 4 ID AVANT APRES B A C C B null A null B
le résultat voulu :
Code:

1 2 ID CONCAT A A,B,C
le résultat obtenu :
Code:

1 2 3 4 5 6 7 ID CONCAT A A A A,B A A,B,C C C B B,C B B
d'où ma demande...
Je suis obligé de prendre tout les ID en compte dans la récursive car le renseignement des données AVANT et APRES n'est pas nécessairement symétrique.

le code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 WITH pred(rchch,[ID], [AVANT],[APRES],Castinactive, CONCAT) AS (SELECT 'pred', [ID], [AVANT],[APRES], case when ETA_DateFin is not null then 0 when ETA_DateFin is null then 1 end , CAST(ID as varchar(max)) FROM [T_Table] UNION ALL SELECT 'pred', V.ID ,V.[AVANT],V.[APRES], Castinactive+case when V.ETA_DateFin is not null then 0 when V.ETA_DateFin is null then 1 end , CONCAT + ', '+CAST(V.ID as varchar(max)) FROM [T_Table] V inner join pred t on V.ID=t.AVANT WHERE CONCAT not like '%, ' + cast(V.ID as varchar(40)) + '%') Select Castinactive,CONCAT from pred

07/09/2016, 11h42
Bernardos
Citation:
Envoyé par ampex

La concaténation obtenue de manière récursive est basée sur le modèle de sqlpro proposé dans ce même forum.
voila le principe : un ID, un ID précédent, un ID suivant

Code:

1 2 3 4 ID AVANT APRES B A C C B null A null B

le résultat voulu :

Code:

1 2 ID CONCAT A A,B,C

l
si je comprend bien ton énoncé avec le résultat souhaité en gras.
l'attendu serait plutot
Code:

1 2 3 ID CONCAT A A,null,B B B,A,C
non?

Cette solution simple ?

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
27
28
29
30
 
with data (id, txt)
as
(
	select 'A', 'A'
	union all
	select 'A', 'A,B'
	union all
	select 'A', 'A,B,C'
	union all
	select 'C', 'C'
	union all
	select 'B', 'B,C'
	union all
	select 'B', 'B'
	union all
	select '1', '1'
	union all
	select '1', '1,2'
	union all
	select '2', '2'
)
select d1.id, d1.txt
from data d1
where not exists
(
	select 'j''aime les nouilles pas toi ?'
	from data d2
	where d2.txt like concat('%', d1.txt, '%') and d1.txt <> d2.txt
)

(Ma CTE étant à remplacer par ta requête actuelle).

07/09/2016, 12h28
ampex

heu non, c'est peut être AVANT et APRES qui mets dans la confusion !
AVANT et APRES sont équivalant à l'ID précédent et l'ID qui succède. Il faut imaginer que chaque ID a une date de début et une date de fin et que les ID se succèdent, sont liés temporellement les un aux autres..
d'où une récursive sur chaque ID afin de trouver le successeur et prédécesseur et de reconstruire la plus grande chaîne de succession...
en occurrence : A | A,B,C ou encore C | C,B,A...
07/09/2016, 12h29
StringBuilder

Cependant, je pense qu'en modifiant la requête originale, il doit y avoir moyen de déterminer si un noeud est fils d'un noeud ou non. Dans ce cas, il faut simplement écarter la ligne, afin de ne conserver que les lignes qui partent d'un élément sans parent.
07/09/2016, 12h41
ampex

Citation:

Envoyé par StringBuilder

Cependant, je pense qu'en modifiant la requête originale, il doit y avoir moyen de déterminer si un noeud est fils d'un noeud ou non. Dans ce cas, il faut simplement écarter la ligne, afin de ne conserver que les lignes qui partent d'un élément sans parent.

Ce serait TOP !

Dans le début de votre requête, qu'est-ce que ça donne si vous rajoutez une clause WHERE au premier SELECT du UNION ?
Code:

1 2 3 4 5 6 7 SELECT 'pred', [ID], [AVANT],[APRES], case when ETA_DateFin is not null then 0 when ETA_DateFin is null then 1 end , CAST(ID as varchar(max)) FROM [T_Table] where AVANT is null

Citation:

Envoyé par StringBuilder

Dans le début de votre requête, qu'est-ce que ça donne si vous rajoutez une clause WHERE au premier SELECT du UNION ?

Si je fais ça, ça me limite les boucles sur les ID dont le AVANT est null.. donc je me retrouve avec tout les ID qui n'ont pas de AVANT, avec leur ID seul dans CONCAT.

Parallèlement j'ai rajouté un conteur (TRN) dans la CTE :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 WITH pred(rchch,[ID], [AVANT],[APRES],Castinactive, CONCAT,TRN) AS (SELECT 'pred', [ID], [AVANT],[APRES], case when ETA_DateFin is not null then 0 when ETA_DateFin is null then 1 end , CAST(ID as varchar(max)), 0 FROM [T_Table] UNION ALL SELECT 'pred', V.ID ,V.[AVANT],V.[APRES], Castinactive+case when V.ETA_DateFin is not null then 0 when V.ETA_DateFin is null then 1 end , CONCAT + ', '+CAST(V.ID as varchar(max)), TRN+1 FROM [T_Table] V inner join pred t on V.ID=t.AVANT WHERE CONCAT not like '%, ' + cast(V.ID as varchar(40)) + '%') Select Castinactive,CONCAT,TRN from pred
j'obtient :
Code:

1 2 3 4 5 6 7 ID CONCAT TRN A A 1 A A,B 2 A A,B,C 3 C C 1 B B,C 2 B B 1

Citation:

Envoyé par StringBuilder

Cette solution simple ?

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
27
28
29
30
 
with data (id, txt)
as
(
	select 'A', 'A'
	union all
	select 'A', 'A,B'
	union all
	select 'A', 'A,B,C'
	union all
	select 'C', 'C'
	union all
	select 'B', 'B,C'
	union all
	select 'B', 'B'
	union all
	select '1', '1'
	union all
	select '1', '1,2'
	union all
	select '2', '2'
)
select d1.id, d1.txt
from data d1
where not exists
(
	select 'j''aime les nouilles pas toi ?'
	from data d2
	where d2.txt like concat('%', d1.txt, '%') and d1.txt <> d2.txt
)

(Ma CTE étant à remplacer par ta requête actuelle).

En tout cas, je confirme que ma solution fonctionne parfaitement :

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
27
28
29
30
31
32
 
/*
create table noeud
(
	id char(1) not null primary key,
	avant char(1) null references noeud(id),
	apres char(1) null references noeud(id),
	unique (avant, apres)
);
go
 
insert into noeud (id, avant, apres) values ('A', null, 'B'), ('B', 'A', 'C'), ('C', 'B', null);
*/
 
with pred (id, avant, apres, concatene)
as
(
	select id, avant, apres, cast(id as varchar(max))
	from noeud
	union all
	select v.id, v.avant, v.apres, cast(concat(v.id, ',', t.concatene) as varchar(max))
	from noeud v
	inner join pred t on t.avant = v.id
)
select d1.id, d1.concatene
from pred d1
where not exists
(
	select 'j''aime les nouilles pas toi ?'
	from pred d2
	where d2.concatene like concat('%', d1.concatene, '%') and d1.concatene <> d2.concatene
)

On peut éventuellement modifier le "not exists" comme suit si on a des valeurs de longeur variable :

Code:

1
2
3
4
5
6
7
 
where not exists
(
	select 'j''aime les nouilles pas toi ?'
	from pred d2
	where (d2.concatene like concat(d1.concatene, '%') or d2.concatene like concat('%', d1.concatene) or d2.concatene like concat('%', d1.concatene, ',%')) and d1.concatene <> d2.concatene
)

08/09/2016, 09h54
ampex

oui effectivement ça fonctionne.
mais je n'arrive pas au bout de la requête.. 20 min et elle tourne tjs.
j'ai tenté de passer par une #temp mais ça reste lourd..
08/09/2016, 11h30
Bernardos

Citation:

Envoyé par ampex

oui effectivement ça fonctionne.
mais je n'arrive pas au bout de la requête.. 20 min et elle tourne tjs.
j'ai tenté de passer par une #temp mais ça reste lourd..

je suppose que tu as exécuté la requête sur des données réelles avec un plus ou moins gros volume?
Si oui, Combien de ligne as-tu dans ta table ?
as tu mis les index qui vont bien ?

Cordialement,
08/09/2016, 14h08
ampex

le résultat de la CTE c'est 135 000 lignes, sans filtres, en 15s.
en mettant un index sur la table temporaire et exécutant les filtres, j'obtiens les mêmes délais :( (> de 30 min)

Bonjour,

N'étant pas sur d'avoir compris à 100% le résultat attendu, voici une proposition :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 WITH data(ID, pred, suiv) AS ( SELECT 'B' AS id, 'A' as pred, 'C' as suiv UNION SELECT 'C', 'B', null UNION SELECT 'A', null, 'B' ), chaine(ID, suiv, result) AS ( SELECT id, suiv, CAST(id AS VARCHAR(MAX)) FROM data WHERE pred IS NULL UNION ALL SELECT D.id, E.suiv, D.result + ', ' + E.id FROM chaine AS D INNER JOIN data AS E ON D.suiv = E.id ) SELECT * FROM chaine WHERE suiv IS NULL;
La CTE data est bien entendu a remplacer par la source de données.
En gros, la requête construit la chaîne de manière récursive (comme le fait la solution de StringBuilder). La différence, c'est qu'elle ne recherche pas les doublons puisqu'elle ne retient que les chaînes complètes, sans prédécesseur, ni successeur.

Mais sans jeu de données sur lequel confronter les résultats, ce n'est pas évident de bien comprendre le problème et d'y proposer une solution adéquat.

08/09/2016, 14h51
François DORIN

Citation:

Envoyé par ampex

le résultat de la CTE c'est 135 000 lignes, sans filtres, en 15s.
en mettant un index sur la table temporaire et exécutant les filtres, j'obtiens les mêmes délais :( (> de 30 min)

Je ne sais pas où tu as mis tes index, mais avec des requêtes avec des LIKE '%xxx' cela m'étonnerait qu'ils soient utilisés...
08/09/2016, 14h54
escartefigue

Citation:

Envoyé par dorinf

N'étant pas sur d'avoir compris à 100% le résultat attendu, voici une proposition :
Mais sans jeu de données sur lequel confronter les résultats, ce n'est pas évident de bien comprendre le problème et d'y proposer une solution adéquat.

Nous sommes d'accord :?

Citation:

Envoyé par dorinf

Je ne sais pas où tu as mis tes index, mais avec des requêtes avec des LIKE '%xxx' cela m'étonnerait qu'ils soient utilisés...

bis !

Pourquoi utiliser une table temporaire ? c'est contre-performant.

que donne

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
27
28
29
30
31
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
and OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) = 'noeud'
ORDER BY Avg_Estimated_Impact DESC
GO

au besoin modifie le nom de ta table 'noeud' dans le script dans la clause where

Cordialement,