PGCD pour SQL SERVER

[Resolu]PGCD pour SQL SERVER

Bonjour
Dans une problématique de nomenclature, j' ai besoin de déterminer le PGCD d'une suite d'entier. Je m'explique, j'ai une commande d'un seul produit composé de

4 moteurs REF1
16 roues. REF2

Il faut que je fasse le lien avec la nomenclature composée de
1 moteur REF1
4 Roues

Voilà mes tables
Une table TMP_COMMANDE
REFERENCE varchar(50)
NOMBRE int

Clé sur REFERENCE
Une table DETAIL_NOMENCLATURE
NOMENCLATURE_ID int
REFERENCE varchar(50)
NOMBRE.

J'ai trouvé le moyen de rattacher une commande de
1 moteur REF1
4 Roues
à la nomenclature 10 composé de ces éléments.

Pour rattacher la commande de 4 moteurs , 16 roues, je m'étais dit qu'en premiere aproximation dans mon cas concret c'était le MINIMUM, parce qu'une voiture n'a qu'un seul moteur, on me l'avait affirmé également

Problème dans mon calcul complet j'ai trouvé des voitures à 4 moteurs.
En vrai il ne s'agit pas de voiture mais c'est plus parlant avec des voitures.

Voilà un jeu de données de TMP_COMMANDE
Code:

1 2 3 4 NOMBRE REFERENCE 4 A 16 B
Là je voudrais trouver 4, et le min marche parfaitement
Code:

1 2 3 4 NOMBRE REFERENCE 20 C 16 B
Là je voudrais trouver 4, et le min me renvoie logiquement 16.
Code:

1 2 3 4 5 NOMBRE REFERENCE 1 A 3 B 5 C
Là bien sur c'est 1.

Si vous avez une idée, je prends.
Je cherche à faire cela dans une Procedure Stockée
A+
Soazig

Bonjour,

C'est là que j'ai été content de ne pas avoir jeté mes cours d'arithmétique :D

Le PGCD de 4 nombres a, b, c et d peut se calculer de la façon suivante :

PGCD(a, b, c, d) = PGCD(PGCD(PGCD(a, b), c), d)

Donc on peut écrire une fonction qui calcule le PGCD de deux nombres, et une procédure stockée qui se chargera de calculer le PGCD de plusieurs nombres, stockés dans une colonne d'une table, de façon récursive.

Il faut noter que PGCD(n, 0) = n.
Ceci sera intéressant pour l'écriture de la procédure stockée.

Pour réaliser quelques tests, j'ai créé la table suivante:

Code:

1
2
3
4
5
CREATE TABLE Tb_Test_PGCD
(
	Nombre INT
)
GO

Voici le code de la fonction :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
------------------------------------
-- Calcule le PGCD de deux nombres -
------------------------------------
CREATE FUNCTION [dbo].[udf_Calcule_PGCD](@_a INT, @_b INT)
	RETURNS INT
AS
BEGIN
	DECLARE @pgcd INT
 
	IF @_b = 0
		SET @pgcd = @_a
	ELSE
	BEGIN
		DECLARE @r INT
		SET @r = @_a % @_b
		SELECT @pgcd = dbo.udf_Calcule_PGCD(@_b, @r)
	END
 
	RETURN @pgcd
END
GO

Dès lors, la procédure stockée est:

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
--------------------------------------------------------------
-- Calcule le PGCD des nombres stockés dans dbo.Tb_Test_PGCD -
--------------------------------------------------------------
CREATE PROCEDURE usp_Calcule_PGCD
AS
BEGIN
	WITH
		-- Calcule un indice pour tous les nombres stockés
		-- dans la colonne "Nombre" de la table dbo.Tb_Test_PGCD
		CTE_Nombres AS
		(
			SELECT Nombre,
					ROW_NUMBER() OVER(ORDER BY Nombre DESC) Ordre
			FROM dbo.Tb_Test_PGCD
		),
		-- Calcule récursivement le PGCD de tous les nombres stockés
		-- dans la colonne "Nombre" de la table dbo.Tb_Test_PGCD
		CTE_PGCD AS
		(
				-- Recherche du premier élément
				-- PGCD(n, 0 = n)
				SELECT dbo.udf_Calcule_PGCD(Nombre, 0) AS PGCD,
						1 AS Indice
				FROM CTE_Nombres
				WHERE Ordre = 1
			UNION ALL
				-- Parcours des autres valeurs de la colonne
				-- "Nombre" de la table dbo.Tb_Test_PGCD
				SELECT dbo.udf_Calcule_PGCD(PGCD, Nombre),
						Indice + 1
				FROM CTE_Nombres, CTE_PGCD
				WHERE Ordre = Indice
		),
		CTE_MAX AS
		(
			-- Recherche de l'indice du dernier calcul
			-- Celui-ci a été incrémenté à chaque calcul de PGCD dans CTE_PGCD
			SELECT MAX(Indice) Indice
			FROM CTE_PGCD
		)
	-- Recherche du dernier calcul de PGCD dans CTE_PGCD => résultat
	SELECT CTE_PGCD.PGCD
	FROM CTE_PGCD
	JOIN CTE_MAX ON CTE_MAX.Indice = CTE_PGCD.Indice
END
GO

Et voici quelques tests qui produisent un résultat correct :

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
---------
-- Test -
---------
SET NOCOUNT ON
GO
 
TRUNCATE TABLE dbo.Tb_Test_PGCD
GO
 
INSERT INTO dbo.Tb_Test_PGCD VALUES(1820)
INSERT INTO dbo.Tb_Test_PGCD VALUES(1176)
INSERT INTO dbo.Tb_Test_PGCD VALUES(700)
INSERT INTO dbo.Tb_Test_PGCD VALUES(9100)
GO
 
EXEC dbo.usp_Calcule_PGCD
GO
 
TRUNCATE TABLE dbo.Tb_Test_PGCD
GO
 
INSERT INTO dbo.Tb_Test_PGCD VALUES(270)
INSERT INTO dbo.Tb_Test_PGCD VALUES(144)
GO
 
EXEC dbo.usp_Calcule_PGCD
GO
 
TRUNCATE TABLE dbo.Tb_Test_PGCD
GO
 
INSERT INTO dbo.Tb_Test_PGCD VALUES(18)
INSERT INTO dbo.Tb_Test_PGCD VALUES(15)
INSERT INTO dbo.Tb_Test_PGCD VALUES(21)
INSERT INTO dbo.Tb_Test_PGCD VALUES(36)
GO
 
EXEC dbo.usp_Calcule_PGCD
GO
 
TRUNCATE TABLE dbo.Tb_Test_PGCD
GO
 
INSERT INTO dbo.Tb_Test_PGCD VALUES(408)
INSERT INTO dbo.Tb_Test_PGCD VALUES(126)
GO
 
EXEC dbo.usp_Calcule_PGCD
GO

Le code de la procédure stockée n'est valable que sous SQL Server 2005, parce qu'il utilise une expression de table commune récursive.

@++ ;)

wouah! Je suis impressionnée.

Merci beaucoup je teste cela dans mon calcul de Nomenclature lundi et je vous donne des nouvelles.
[edit Lundi]Ca marche Nickel[/edit]
Merci beaucoup