Aide pour écrire une meilleure requête

**florin.samson** · 02/12/2009, 17h05

Bonjour

J'ai besoin d'un aide rapide SVP

ID_LISTE ID_VALUE VAL_TYPE

1 val1 int
1 val2 int
2 val2 float
2 val1 float
5 val1 int
5 val2 int
6 valX int
6 valY int

En fait je dois ecrire une query qui doit sortir le fait que les listes 1 et 5 sont en dublons unuiquement quand elles contient les meme id_valeurs et que le valeurs soit dans la meme ordre.
LISTE 1 et 2 ne sont pas des dublons car l'ordre de valeurs n'est pas le meme
LISTE 1 et 6 ne sont pas des dublons car les valeurs sont differentes

ID_LISTE ID_DUBLON
1 5

J'ai trouvé un moyen de le faire mais c n'est pas optime et ça donnera environ 20 minutes avec 500 listes a une moyenne de 80 elements par liste

**elsuket** · 02/12/2009, 17h15

Bonjour,

Quelle version de SQL Server utilisez-vous ?
La solution est plus simple dès SQL Server 2005 ...

@++

**florin.samson** · 02/12/2009, 17h39

C'est uniquement pour un sql server 2005.Je vous remercie pour votre aide

**elsuket** · 02/12/2009, 19h09

Voici :

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
	CTE_NUMEROTE AS
	(
		SELECT ID_LISTE,
				ID_VALUE,
				VAL_TYPE,
				ROW_NUMBER() OVER(PARTITION BY ID_LISTE ORDER BY ID_VALUE, VAL_TYPE) AS n
		FROM dbo.TEST
	)
SELECT N1.ID_LISTE,
		N2.ID_LISTE AS ID_DUBLON
FROM CTE_NUMEROTE AS N1
JOIN CTE_NUMEROTE AS N2
	ON N1.ID_VALUE = N2.ID_VALUE
	AND N1.VAL_TYPE = N2.VAL_TYPE
	AND N1.n = N2.n
	AND N1.ID_LISTE < N2.ID_LISTE
GROUP BY N1.ID_LISTE, N2.ID_LISTE

@++

**florin.samson** · 03/12/2009, 10h55

close to the solution but the problem is that i get some lists as "DUBLONS" only if it's just one line that fulls the JOIN conditions. SDorry maybe i wasn't so clear but I need to select only and only the LIST_ID for wich all the ID_VALUE are identicals and the "size of the lists" are the same (the number of VAL_ID for one fixed LIST_ID is egal)

1. I don't understand how really works this test
AND N1.LST_ID < N2.LST_ID (an dublon can be also an bigger list_id ?)

2.Tryed to modify and to use something like but it doesn't recognize the second CTE expression and i'm really not mastering at all

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT N1.LST_ID,
	   N2.LST_ID AS ID_DUBLON
FROM CTE_NUMEROTE AS N1 
WHERE 
(EXISTS CTE_NUMEROTE AS N2
	WHERE
             N1.VAL_ID = N2.VAL_ID	
	  AND N1.n = N2.n
	  AND N1.LST_ID < N2.LST_ID
)
GROUP BY N1.LST_ID, N2.LST_ID

**elsuket** · 03/12/2009, 12h03

I'm not sure I understand exactly your need.
Can you please be more accurate by giving us a longer dataset, so that we can test it in full ?

As it is a French forum, I'm not sure also that the moderators will appreciate to see a thread partially written in English.

@++

**florin.samson** · 03/12/2009, 14h38

Desolé pour l'anglais mais je comunique un peu moins bien en français j'essaie de faire mon mieu.

d'accord je vais tenter de retrouver un jeu de donnes qui n'est "pas ok" et je le poste au plus vite possible car ça sera genial d'en finir jusqu'a ce soire

**florin.samson** · 03/12/2009, 15h03

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
-- table avec jeu de test --
-- code à copier --
 
CREATE TABLE #V_C_AuditValuesCL
(
 LST_ID int,
 VAL_ID int
)
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (357691,206991);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (357691,353351);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (357691,1);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (357691,355361);
 
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (356231,206991);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (356231,353301);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (356231,1);
 
select * from #V_C_AuditValuesCL
--truncate TABLE #V_C_AuditValuesCL
 
WITH
	CTE_NUMEROTE AS
	(
		SELECT 
			   LST_ID,
			   VAL_ID,			   			   
			   ROW_NUMBER() OVER(PARTITION BY LST_ID ORDER BY VAL_ID) AS n
		FROM dbo.#V_C_AuditValuesCL --where lng='FR' 
	)
SELECT N1.LST_ID,
	   N2.LST_ID AS ID_DUBLON
FROM CTE_NUMEROTE AS N1 
JOIN CTE_NUMEROTE AS N2
	ON 
		N1.VAL_ID = N2.VAL_ID	
	AND N1.n = N2.n
	AND N1.LST_ID < N2.LST_ID
GROUP BY N1.LST_ID, N2.LST_ID

La requette sort 357691 comme etant dublons avec 356231 mais il faudrait pas car:
1. il y a moins de valeurs dans la premierre liste que dans la deuxiemme 4 dans la premiere versus 3 dans la deuxiemme
2. l'ordre (position de valeurs) doit être aussi identique dans chaque liste
j'espere que j'ai etait un peu plus clair là

Merci bcp

**elsuket** · 03/12/2009, 15h42

Desolé pour l'anglais mais je comunique un peu moins bien en français j'essaie de faire mon mieu.

C'est déjà très bien, et nous nous comprenons : n'est-ce pas le plus important ?

Essayez celle-ci :

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
	CTE_NUMEROTE AS
	(
		SELECT AVCL.LST_ID,
				AVCL.VAL_ID,
				ROW_NUMBER() OVER(PARTITION BY AVCL.LST_ID ORDER BY AVCL.LST_ID) AS n,
				TMP.nbElements
		FROM dbo.#V_C_AuditValuesCL AS AVCL
		JOIN (
				SELECT LST_ID,
						COUNT(*) AS nbElements
				FROM dbo.#V_C_AuditValuesCL
				GROUP BY LST_ID
			) AS TMP
			ON AVCL.LST_ID = TMP.LST_ID
	)
SELECT N1.LST_ID,
		N2.LST_ID AS ID_DUBLON
FROM CTE_NUMEROTE AS N1
JOIN CTE_NUMEROTE AS N2
	ON N1.VAL_ID = N2.VAL_ID
	AND N1.n = N2.n
	AND N1.LST_ID < N2.LST_ID
	AND N1.nbElements = N2.nbElements
GROUP BY N1.LST_ID, N2.LST_ID

La première ne fonctionnait pas puisque, en outre, j'ordonnais sur LST_ID

@++

**florin.samson** · 03/12/2009, 16h26

Le count s'applique super bien, on y est presque soif que j'ai l'impression que la condition ON N1.VAL_ID = N2.VAL_ID n'est aps assez forte donc elle s'apliquera pas pour chaque ligne du N1 por verifier avec chaque ligne du N2

Pour cet jeu de donnes ça ne marche pas en fait les valeurs ne sont pas identiques

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
 
/*
CREATE TABLE #V_C_AuditValuesCL
(
 LST_ID int,
 VAL_ID int
)
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (357691,206991);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (357691,353351);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (357691,1);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (357691,355361);
 
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (356231,206991);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (356231,353301);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (356231,1);
insert into #V_C_AuditValuesCL (LST_ID,VAL_ID) values (356231,346821);
select * from #V_C_AuditValuesCL 
*/

**florin.samson** · 03/12/2009, 17h04

En fait en executent ça, avec le jeu de resultat ecrit dans le post precedent

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
ON OBTIEN
 
WITH
	CTE_NUMEROTE AS
	(
		SELECT AVCL.LST_ID,
				AVCL.VAL_ID,
				ROW_NUMBER() OVER(PARTITION BY AVCL.LST_ID ORDER BY AVCL.LST_ID) AS n,
				TMP.nbElements
		FROM dbo.#V_C_AuditValuesCL AS AVCL
		JOIN (
				SELECT LST_ID,
						COUNT(*) AS nbElements
				FROM dbo.#V_C_AuditValuesCL
				GROUP BY LST_ID
			) AS TMP
			ON AVCL.LST_ID = TMP.LST_ID
	)
SELECT * FROM CTE_NUMEROTE
 
356231	206991	1	4
356231	353301	2	4
356231	1	3	4
356231	346821	4	4
357691	206991	1	4
357691	353351	2	4
357691	1	3	4
357691	355361	4	4
 
et si on fait SELECT N1.LST_ID,
		N2.LST_ID AS ID_DUBLON
FROM CTE_NUMEROTE AS N1
JOIN CTE_NUMEROTE AS N2 + conditions +

comme il y a deux copies logiques il va faire un match pour les lignes respectend le conditions de join, donc si il y a une moins une qui est bien, ça va sortir une ligne de dublons meme si c n'est pas le cas.

Je pense que la solution sera de faire, car ça va faire verifier pour chaque valeur de N1 tout les valeurs de N2 ....

Code :

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

1
2
3
4
5
6
7
8
select  N1.LST_ID from CTE_NUMEROTE as N1 
where 
(exists 
  (select CTE_NUMEROTE as N2 where 
     N1.val_id = N2.val_id and 
     ...
  )
)

**florin.samson** · 04/12/2009, 07h39

Bonjour,
Je serais absent aujourd'hui mais si vous avez encore un reponse n'hesitez pas de poster SVP car je vais reprendre de lundi matin

Ms bcp,bonne week'end a vous

**florin.samson** · 08/12/2009, 10h42

personne n'a pas aucunne autre idée ?

**Waldar** · 08/12/2009, 11h51

J'ai simplement rajouté un aggrégat final pour vérifier que tous les éléments sont bien concernés :

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
WITH AuditValuesCL_Extended (LST_ID, VAL_ID, ORD_ID, CNT_ID) AS
(
select LST_ID, VAL_ID,
       ROW_number() over(partition by LST_ID order by VAL_ID asc),
       count(*) over(partition by LST_ID)
  from dbo.#V_C_AuditValuesCL
)
select
    AV1.LST_ID,
    AV2.LST_ID
from
    AuditValuesCL_Extended as AV1
    inner join AuditValuesCL_Extended as AV2
      on AV2.LST_ID > AV1.LST_ID
     and AV2.VAL_ID = AV1.VAL_ID
     and AV2.ORD_ID = AV1.ORD_ID
     and AV2.CNT_ID = AV1.CNT_ID
group by
    AV1.LST_ID,
    AV2.LST_ID,
    AV1.CNT_ID
having
    count(*) = AV1.CNT_ID;

**florin.samson** · 08/12/2009, 12h27

impecable, je vais faire qqs tests et je vais marquer le post comme resolu

**florin.samson** · 08/12/2009, 15h41

Une question de +

Je n'ai pas compris pour quoi la condition de la jointure ON AV2.LST_ID > AV1.LST_ID n'est pas : ON AV2.LST_ID <> AV1.LST_ID
Est ce que tu peux m'expliquer un peu comment ça marche? et pour quoi on teste pour le ">" ?

**Waldar** · 08/12/2009, 16h03

C'est pour éviter d'avoir les couples (id1, id2) et (id2, id1) tout simplement.

Jeu de test (donc des doublons) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DECLARE @AuditValuesCL TABLE 
(
 LST_ID int,
 VAL_ID int
);
 
INSERT INTO @AuditValuesCL (LST_ID,VAL_ID) values (357691,1);
INSERT INTO @AuditValuesCL (LST_ID,VAL_ID) values (357691,206991);
 
INSERT INTO @AuditValuesCL (LST_ID,VAL_ID) values (356231,1);
INSERT INTO @AuditValuesCL (LST_ID,VAL_ID) values (356231,206991);

Résulats avec > :

Code :

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

1
2
3
LST_ID      LST_ID
----------- -----------
356231      357691

Résultats avec <> :

Code :

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

1
2
3
4
LST_ID      LST_ID
----------- -----------
356231      357691
357691      356231

**florin.samson** · 09/12/2009, 15h25

Merci encore une fois car ce n'etait pas evident

Aide pour écrire une meilleure requête

Développement SQL Server

Discussions similaires

Partager

Partager