Discussion :

[tri_yann]

Bonjour,

Je suis devant une impasse même si ca parait simple de premier abord. Je souhaite comparer le contenu de 2 tables comportant des articles composées de "composants". Je cherche à connaître les articles de @t1 qui ont exactement la même composition que les articles de @t2.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
DECLARE @t1 table (Article1 varchar(255), Composant1 varchar(255))
DECLARE @t2 table (Article2 varchar(255), Composant2 varchar(255))
 
Insert into @t1 values ('Boulon 1', 'Vis')
Insert into @t1 values ('Boulon 1', 'Ecrou')
Insert into @t1 values ('Tringle à rideaux 1', 'Barre')
Insert into @t1 values ('Tringle à rideaux 1', 'Support mural')
 
Insert into @t2 values ('Boulon 2', 'Vis')
Insert into @t2 values ('Boulon 2', 'Ecrou')
Insert into @t2 values ('Boulon 2', 'Rondelle')
Insert into @t2 values ('Tringle à rideaux 2', 'Barre')
Insert into @t2 values ('Tringle à rideaux 2', 'Support mural')

Dans cet exemple, comment faire pour savoir que "Tringle à rideaux 1" a la même composition que "Tringle à rideaux 2". Ce n'est pas le cas pour les boulons 1 & 2.

Merci de votre aide

[Kropernic]

Il y a très certainement moyen d'optimiser le tout mais le noob que je suis est arrivé à qqch avec ceci :
(j'ai ajouté quelques valeurs)

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
DECLARE @t1 TABLE (Article1 varchar(255), Composant1 varchar(255))
DECLARE @t2 TABLE (Article2 varchar(255), Composant2 varchar(255))
 
INSERT INTO @t1 VALUES ('Boulon 1', 'Vis')
INSERT INTO @t1 VALUES ('Boulon 1', 'Ecrou')
INSERT INTO @t1 VALUES ('Tringle à rideaux 1', 'Barre')
INSERT INTO @t1 VALUES ('Tringle à rideaux 1', 'Support mural')
INSERT INTO @t1 VALUES ('Truc 1', 'comp 1.0')
INSERT INTO @t1 VALUES ('Truc 1', 'comp 1.1')
INSERT INTO @t1 VALUES ('Truc 1', 'comp 1.2')
INSERT INTO @t1 VALUES ('Brol 1', 'comp 2.0')
 
INSERT INTO @t2 VALUES ('Boulon 2', 'Vis')
INSERT INTO @t2 VALUES ('Boulon 2', 'Ecrou')
INSERT INTO @t2 VALUES ('Boulon 2', 'Rondelle')
INSERT INTO @t2 VALUES ('Tringle à rideaux 2', 'Barre')
INSERT INTO @t2 VALUES ('Tringle à rideaux 2', 'Support mural')
INSERT INTO @t2 VALUES ('Truc 2', 'comp 1.0')
INSERT INTO @t2 VALUES ('Truc 2', 'comp 1.1')
INSERT INTO @t2 VALUES ('Truc 2', 'comp 1.3')
INSERT INTO @t2 VALUES ('Brol 2', 'comp 2.0')
 
select 
		a.article1, b.article2
from 
		@t1 as a
			inner join @t2 as b
				on a.composant1 = b.composant2
			inner join (select article1, count(*) as 'count'
						from @t1
						group by article1) c
				on a.article1 = c.article1
			inner join (select article2, count(*) as 'count'
						from @t2
						group by article2) d
				on b.article2 = d.article2
 
group by a.article1, b.article2, c.count, d.count
having c.count = count(*) and d.count=count(*)

Après, à toi d'adapter les champs de la clause select pour avoir ce que tu veux.

En espérant avoir aider,

Kropernic.

P.S. : Je serais assez curieux de savoir commet qqn comme sqlpro ou elsuket, pour ce citer qu'eux deux (sorry pour les autres, faut bien faire un choix), aurait fait cela.

[punkoff]

Salut,

une approche un peu différente, mais je ne sais pas si c'est mieux :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
with tmp as (
select distinct a.article1, b.article2 
from tb1 a
full outer join tb2 b on a.composant1 = b.composant2)
 
select * 
from tmp a 
where not exists (select null from tmp b where a.article1 = b.article1 and b.article2 is null)
and not exists (select null from tmp b where a.article2 = b.article2 and b.article1 is null);

edit : c'est nul en fait ca gère pas le cas initial

[Kropernic]

Ah cette fonction exists !! Faudrait vraiment que je me rappelle qu'elle existe.

Pour ma part, je pense que cette approche est plus performante (mais il faudrait analyser les plans de requête respectif pour en être certain). De prime abord, il y a tellement moins de comptage qui sont fait que cela ne peut être que bénéfique je dirais.

Non ?

Kropernic.

[Sergejack]

Juste pour le plaisir d'utiliser INTERSECT

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
 
SELECT
	TB1.Article1
	, TB2.Article2
FROM (
	SELECT 
		TB1.Article1
		, COUNT(*) AS Cnt
	FROM @t1 AS TB1
	GROUP BY TB1.Article1
) AS TB1
INNER JOIN (
	SELECT 
		TB2.Article2
		, COUNT(*) AS Cnt
	FROM @t2 AS TB2
	GROUP BY TB2.Article2
) AS TB2 ON (
	TB2.Cnt = TB1.Cnt
)
CROSS APPLY (
	SELECT
		COUNT(*) AS Cnt
	FROM (
		SELECT
			T1.Composant1
		FROM @t1 AS T1
		WHERE T1.Article1 = TB1.Article1
		INTERSECT
		SELECT
			T2.Composant2
		FROM @t2 AS T2
		WHERE T2.Article2 = TB2.Article2
	) AS X
) AS X
WHERE X.Cnt = TB1.Cnt

Note: c'est peut-être aussi un poil plus performant

[tri_yann]

Merci à vous tous. Malheureusement, je n'ai pas la méthode miracle que j'espérais. Mes 2 tables comptent 100000 enr chacune pour 30000 articles. Dans toutes vos solutions, je passe par une combinaison de plus 3 500 000 enr. La comparaison des comptages prend donc 5 min. Ca marche mais c'est long. La plus performante est celle de Kropernic : 5min.
La solution de Punkoff, pourtant charmante, je ne l'ai pas laissée finir. arrivée à 2 000 000 d'enr, j'ai annulé puis 35 min de Rollback !!!

Malgré tout, j'ai appris de nouvelles méthodes et pour ca, je vous en remercie.

Pour info, je dois trouver une méthode pour attaquer 2 tables d'1 000 000 d'enr chacune (c'est la même table que je compare à elle même) en passant par une procédure stockée de préparation de 10sec. maxi pour pouvoir faire des select d'1 sec. maxi.

Cordialement

[punkoff]

question conne, cette table c'est quoi sa structure réelle ?

(car 1m d'enregistrement c'est pas beaucoup surtout qu'ici on devrait avoir juste une table d'association entre un article et un composant...donc comparaison d'ID de type integer)

[Sergejack]

La comparaison des comptages prend donc 5 min. Ca marche mais c'est long. La plus performante est celle de Kropernic : 5min.

Et la mienne prend combien de temps ?
Essayez-la aussi en rajoutant à la fin

Code :

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

1
2
 
OPTION (FORCE ORDER)

Puis laissez moi savoir ce que cela aura donné, si vous le voulez bien (sauf si ça prend tellement longtemps que vous préférez l'interrompre).