Discussion :

[avairet]

Bonjour à tous,

Je vais essayer de schématiser ce que je souhaite faire :
- j'ai un volume global G
- j'ai N produit avec pour chaque produit un volume V
- j'ai une table scenarios qui, pour chaque produit N me donne 1000 value (scenario0 à scenario999)
- je dois faire le calcul suivant pour chaque scenario, puis classer les résultats par ordre inverse et sélectionner les 10 premiers :

resultat0 = ( (N1 x V1) + (N2 x V2) + [...] ) / G

Bien entendu, le nombre de produits est variable, il peut y en avoir 2 comme dans mon exemple, 65, 123, 18, etc.

Donc mon idée était de faire une procédure stockée + une fonction qui feraient les choses suivantes :

- la fonction fait le calcul value x volume pour un produit_id et un scenario :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE FUNCTION `perf`(
	prod_id INTEGER UNSIGNED,
	vol INTEGER UNSIGNED,
	scen INTEGER UNSIGNED
) RETURNS double
BEGIN
	DECLARE result DOUBLE;
	SET result = (SELECT value * vol
		FROM product_scenarios
		WHERE product_id=prod_id AND scenario=scen);
RETURN result;
END

- la procédure stockée crée une table temporaire, fait une boucle de 0 à 999 en appelant à chaque fois la fonction pour un produit, un volume et un scénario, stocke le résultat dans la table temporaire, à la fin sélectionne les 10 premiers résultats order by desc, puis supprime la table temporaire.

La difficulté que j'ai c'est de pouvoir passer les couples produit_id/volume de manière dynamique...

Pour le moment, j'ai trouvé une astuce qui fonctionne pas trop mal mais qui m'oblige à faire la boucle de 0 à 1000 en PHP et de faire donc 1000 call de ma procédure... Si j'ai un petit nombre de produit, c'est rapide, mais dès que j'ai 50/60 produits, ça prend quelques secondes.

Voilà ce que je fais actuellement dans la procédure; "perfs" étant la table temporaire et "req" contenant en fait la concaténation des appels à ma fonction et de la division par G "perf(18,100000,0) + perf(234,2500000,0) + perf()... / G" :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE `store_perf`(
	req TEXT
)
BEGIN
        DECLARE sqlCMD TEXT;
        SET @sqlCMD = CONCAT('INSERT INTO perfs (theperf) SELECT ',req,' FROM product_scenarios LIMIT 1');
        PREPARE stmt FROM @sqlCMD;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END

Y aurait-il une manière de passer à ma procédure l'ensemble des couples (N,V) et lui laisser faire la boucle de 0 à 999 en stockant au fur et à mesure les résultats renvoyés par la fonction ?

Si je ne suis pas assez clair, n'hésitez pas

[punkoff]

bonjour,

pourquoi traiter votre besoin avec une méthode itérative, alors que vous devriez y arriver sans trop de problème avec une seule requête ?

[avairet]

Je suis tout à fait ouvert à une solution plus simple

Mon problème est surtout lié au fait que je dois calculer les 1000 performances pour les X produits, puis sélectionner les 10 meilleures par ordre décroissant, d'où mon idée de faire une itération en stockant les résultats au fur et à mesure...

[punkoff]

Code :

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

1
2
3
4
5
6
 
select col_grp, sum(col_calc)
from ma_table
group by col_grp
order by sum(col_calc) desc
limit 10

[avairet]

Ah oui, sauf que les données sont dans plusieurs tables en fait...

Voici les tables qui entre en jeu :

- portfolios_products (ID, portfolio_id, product_id)
- volumes (ID, volume, product_id)
- product_scenarios (ID, scenario, value, product_id)

Donc pour schématiser, pour un portfolio_id donné, je dois récupérer la liste de tous les produits qui le constitue avec leur volume, puis pour les scenarios de 0 à 999, je dois faire mon calcul :

( (value1 x volume1) + (value2 x volume2) ... ) / G

Ce calcul doit donc être fait mille fois pour un seul portfolio_id et au final, je ne dois prendre que les 10 premiers résultats par ordre décroissant.
Sachant aussi que potentiellement je peux avoir besoin de le faire pour plusieurs portfolio_id.

[avairet]

Une précision : dans mon idée, je devais passer à MySQL tous les couples (produit/volume) utiles aux calculs, car j'ai déjà ces données issues d'une première requête dans mon PHP.