Procédure stockée nombre d'arguments dynamique

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:

---

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:

---

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 :)

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 ?

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...

Code:

---

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

---

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.

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.

Bein utilisez des jointures : http://sqlpro.developpez.com/cours/sqlaz/jointures/

:mrgreen: Je connais les jointures, merci...

La question n'est pas là...
Comment fais-tu, même avec des jointures pour faire 1000 fois une requête en faisant à chaque fois varier une donnée, puis pour prendre les 10 premiers résultats par ordre décroissant ?

Parce que ton premier exemple, même s'il est sans jointure, ne fonctionne pas, je l'ai testé auparavant. On ne peut pas faire un sum() de toutes les lignes.
Il faut faire un sum d'une ligne pour tous les produits, le diviser par mon volume global, puis stocker. Et recommencer mille fois, une fois par nouvelle ligne de la table product_scenarios en fait.

En fait ce que je ne comprend dans votre démarche, c'est de faire 1000 fois la requete.


Non, il faut faire 1 seul requete qui fait le calcul ... et le limit.


Quelles sont les relations entre vos différentes tables ? (MCD)

Non il faut bien faire X fois la même requête, puis comparer les résultats obtenus.

Voici un exemple concret avec 2 produit et 3 scénarios :

produits et leurs volumes : 18 (1 000 000), 30 (250 000), 45 (350 000)
pour le scénario 1, la valeur de 18 est 0,04, la valeur de 30 est 0,16, la valeur de 45 est 0,02
pour le scénario 2, la valeur de 18 est 0,15, la valeur de 30 est 0,07, la valeur de 45 est 0,12
pour le scénario 3, la valeur de 18 est 0,05, la valeur de 30 est 0,17, la valeur de 45 est 0,13
le volume global est de 1 600 000

donc premier calcul (pour le scénario 1) :
( (1 000 000 x 0.04) + (250 000 x 0,16) + (350 000 x 0,02) ) / 1 600 000

second calcul
( (1 000 000 x 0.15) + (250 000 x 0,07) + (350 000 x 0,12) ) / 1 600 000

troisième calcul
( (1 000 000 x 0.05) + (250 000 x 0,17) + (350 000 x 0,13) ) / 1 600 000

Cela me donne donc trois valeurs, que je dois ordonner par ordre décroissant et je dois récupérer, par exemple, les 2 premières seulement.

Si je devais l'écrire avec les champs, cela donnerait en gros :

Code:

---

1 2 3 4 5

SELECT ( (Volume.volume1 * Scenario.value1) + (Volume.volume2 * Scenario.value2) + (Volume.volume3 * Scenario.value3) ) / 1 600 000 FROM volumes AS Volume LEFT JOIN products_portfolios ON (products_portfolios.product_id = Volume.product_id) LEFT JOIN scenarios AS Scenario ON (Scenario.product_id = Volume.product_id) WHERE Scenario.scenario = 1 and products_portfolios.portfolio_id = 1

---

Comment faire en une seule requête pour faire varier l'id scénario 1000 fois (ce qui fera varier aussi les value1, value2, value3) et ensuite comparer les résultats ?

Pour le MCD, je vous l'ai donné dans l'un des premiers posts :

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

On va faire plus simple :

- donner le mcd réel avec les liens entre les tables (perso c'est pas intuitif votre nommage) :
Ex : Personne-0,n------habite-----0,n-Appartement
(on traduit ca par une personne peut habiter dans un 0 a n appartement, un appartement peut être habité par 0 à n personnes)

- donner un jeu de donnée succin, mais représentatif, de vos tables
- donner le résultat que vous souhaitez (au global, le top 10, ramenez le au top 2 si c'est plus simple pour le jeu de donnée)

Portfolio-n,m------contient-----m,n-Product
Product-1,1-----possède-----1,1-Volume
Product-1,n-----possède-----n,1-Scenario

Pas sûr de moi... plus l'habitude des MLD/MPD :)

Donc en français :
- un portefeuille comporte 1 à n produits et un produit peut appartenir à 1 ou n portefeuilles
- un produit a un seul volume
- un produit a N valeurs de scénarios et un scénario concerne N produits

Pour les données, je les ai données dans le post précédent.

Pour le résultat
- dans le scénario 1 : 0,054375
- dans le scénario 2 : 0,1309375
- dans le scénario 3 : 0,08625

Donc au final, je dois récupérer le 3, puis le 2

oki,

Donc je prendrai cette conversion, niveau nommage :
Portfolio (PORT_ID)
Product(PRO_ID)
Portfolio_Product(#PORT_ID, #PRO_ID)

Volume(VOL_ID, #PRO_ID, VOL_VOLUME)

scenario(SCE_ID)

scenario_produit(#SCE_ID, #PRO_ID, SCP_VALEUR)

=> on retombe sur votre MPD.


On recherche, pour un portfolio donné, le top 10 des calculs en découpant par scénario.

Ceci me semble ok non ?

Code:

---

1 2 3 4 5 6 7 8 9

SELECT SCE_ID, SUM(SCP_VALEUR * VOL_VOLUME) / SUM(VOL_VOLUME) AS VOL_TOTAL FROM Portfolio_Product POP INNER JOIN Volume VOL ON VOL.PRO_ID = POP.PRO_ID INNER JOIN scenario_produit SCP ON SCP.PRO_ID WHERE POP.PORT_ID = ? GROUP BY SCE_ID ORDER BY SUM(SCP_VALEUR * VOL_VOLUME) / SUM(VOL_VOLUME) desc limit 10

---

Je vais regarder de plus près votre requête pour voir ce que cela donne. Mais je sais déjà que cela ne conviendra pas dans un cas que je n'ai pas précisé : dans mon application, l'utilisateur peut faire varier les volumes de produits de chaque portefeuille "artificiellement" pour voir les impacts sur la performance. Et ces variations ne peuvent pas être stockées par défaut en BDD.

Voilà aussi pourquoi j'ai essayé de faire un système dynamique avec une itération et un passage de paramètres.

Désolé, cela ne fonctionne pas avec votre requête on obtient 10 fois la même valeur...
Voilà la version que j'ai utilisée avec les bons champs :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT SCP.id, SUM(SCP.value * VOL.chf_volume) / SUM(VOL.chf_volume) AS VOL_TOTAL FROM portfolios_products POP INNER JOIN volumes VOL ON VOL.product_id = POP.product_id INNER JOIN product_scenarios SCP ON SCP.product_id = POP.product_id WHERE POP.portfolio_id = 2 GROUP BY SCP.id ORDER BY VOL_TOTAL DESC LIMIT 10

---

Et voilà les résultats :
ID | VOL_TOTAL
443346 0.37
443289 0.37
443385 0.37
443564 0.37
443238 0.37
443155 0.37
443552 0.37
443584 0.37
443194 0.37
443239 0.37

dans ce cas là vous avez mal présenté vos relations entre vos tables, ou vos données différe de ce que vous avez dit.

(d'où l'utilité de donner un jeu de test succin mais représentatif des données)

Personnellement j'avais testé avec cette structure :

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 Portfolio_Product AS ( SELECT 1 AS PORT_ID, 1 AS PRO_ID FROM dual union ALL SELECT 2, 1 FROM dual union ALL SELECT 2, 2 FROM dual union ALL SELECT 2, 3 FROM dual ),   scenario_produit AS ( SELECT 1 AS SCE_ID, 1 AS PRO_ID, 0.04 AS SCP_VALEUR FROM dual union ALL SELECT 1, 2, 0.16 FROM dual union ALL SELECT 1, 3, 0.02 FROM dual union ALL SELECT 2, 1, 0.15 FROM dual union ALL SELECT 2, 2, 0.07 FROM dual union ALL SELECT 2, 3, 0.12 FROM dual union ALL SELECT 3, 1, 0.05 FROM dual union ALL SELECT 3, 2, 0.17 FROM dual union ALL SELECT 3, 3, 0.13 FROM dual ),   volume AS ( SELECT 1 AS PRO_ID, 1000000 AS VOL_VOLUME FROM dual union ALL SELECT 2, 250000 FROM dual union ALL SELECT 3, 350000 FROM dual )   SELECT SCE_ID, SUM(ROUND(SCP_VALEUR * VOL_VOLUME, 5)) / SUM(VOL_VOLUME) AS VOL_TOTAL FROM Portfolio_Product POP INNER JOIN Volume VOL ON VOL.PRO_ID = POP.PRO_ID INNER JOIN scenario_produit SCP ON SCP.PRO_ID = POP.PRO_ID WHERE POP.PORT_ID = 2 GROUP BY SCE_ID ORDER BY SUM(SCP_VALEUR * VOL_VOLUME) / SUM(VOL_VOLUME) DESC

---

Résultat :

Code:

---

1 2 3 4 5 6

SCE_ID TOTAL ---------------------- 2 0,1309375 3 0,08625 1 0,054375

---

Bref, de toute manière si vous voulez restreindre aux produits vous n'aurez pas 36 solutions.
Soit passer par un "IN" (construire la chaien en amont) soit passer par une table temporaire si vous êtes dans la même session.

les relations entre les tables sont celles que j'ai mentionnées :

volumes, scenarios et portfolio_product sont liées par les champs "product_id", il n'y a aucune autre liaison à prendre en compte.

Dans votre nouvel exemple, vous avez fait beaucoup plus de requêtes que dans le premier, donc vous semblez tomber sur les bons résultats. Mais à chaque fois vous avez codé les champs variables en dur...

Pour mémoire, les variables sont les id produits, les volumes et la valeur de chaque produit pour chaque scénario.

J'avais bien l'idée du IN, mais mes premiers essais étaient infructueux, je vais retester. Pour la table temporaire, c'est bien ce que je fais, mais j'y stocke les 1000 résultats, alors que vous pensez peut-être y stocker des éléments intermédiaires de calcul ?

Par contre, comme je vous le disais, tout ceci fonctionnera peut-être pour des volumes qui ne varient pas, mais dès que je vais faire varier les volumes depuis l'application, comment les transmettre à la requête ?

Citation:

---

Envoyé par avairet

Dans votre nouvel exemple, vous avez fait beaucoup plus de requêtes que dans le premier, donc vous semblez tomber sur les bons résultats. Mais à chaque fois vous avez codé les champs variables en dur...

---

Non c'est la même requete, les "with" sont la pour créer des tables "virtuelle" et j'ai mis dedans les données de votre exemple.

Voyez ces tables comme des tables temporaires, pour simuler le calcul.
Mais bon le problème est plus complexe donc ..

Je récapitule :
- vous avez des données en bases, statique
- l'utilisateur peut modifier dynamiquement ces données, sans que ceci modifie les données en base afin de faire des simulations.

Si vous voulez continuer à faire les calcules avec le SGBD, je vois 2 solutions :
- créer un référentiel de simulation, où l'on charge les différente données pour le calcul
- gérer les données grâce à des tables temporaires.

Dans les deux cas vous n'aurez qu'une seule requête qui fait le calcul.

C'est la démarche que je ferai, après d'autre auront surement différentes idées.

Merci en tout cas pour votre patience et vos pistes de solutions.

J'ai toujours à l'esprit de faire les calculs via le SGBD et avec le minimum d'instructions. Ce que j'ai du mal à comprendre, c'est comment transmettre à la requête et/ou aux sous-requêtes pour gérer les tables temporaires, les données dont j'ai besoin...

D'après ce que je comprends de vos exemples, il me suffirait de passer (pour la première phase de calcul sur les données statiques), les ids des produits pour faire un IN ? Et la requête se chargerait ensuite de trouver les bons volumes et de faire les calculs pour les 1000 scénarios, en me renvoyant les 10 premiers ?

Et ensuite, lorsque l'on fera varier les volumes sans modifier la base, comme vous l'avez très bien compris, comment faire passer ces nouveaux volumes à la requête ?

Sachant qu'il peut y avoir plus de 100 produits dans un portefeuille et que je peux faire en parallèle les calculs pour 5 portefeuilles (donc 5 jeux de 100 produits), tous les calculs à base de UNION ne seront-ils pas pénalisants ? En effet, là nous sommes partis sur 3 produits, 3 scénarios et un 1 seul portefeuille, ce qui est très éloigné de la réalité.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

DROP TEMPORARY TABLE IF EXISTS mesperfs;   CREATE TEMPORARY TABLE mesperfs ENGINE MEMORY SELECT SCE.id, POP.product_id, VOL.chf_volume,SCE.value FROM volumes as VOL INNER JOIN portfolios_products as POP on (VOL.product_id = POP.product_id) INNER JOIN product_scenarios AS SCE on (SCE.product_id = POP.product_id) WHERE POP.portfolio_id = 2 AND VOL.client_id = 3;   SELECT id, SUM(chf_volume * value) / SUM(chf_volume) AS total FROM mesperfs GROUP BY id ORDER BY total DESC limit 10;

---

Voilà une idée sur le principe que vous m'avez suggéré, mais là encore, je trouve 10 fois le même résultat (qui est faux d'ailleurs)... A quel niveau se trouve mon erreur ? Je pense que c'est au niveau du SUM et de la division, mais je n'arrive pas à formaliser autrement

Citation:

---

Voilà une idée sur le principe que vous m'avez suggéré, mais là encore, je trouve 10 fois le même résultat (qui est faux d'ailleurs)... A quel niveau se trouve mon erreur ? Je pense que c'est au niveau du SUM et de la division, mais je n'arrive pas à formaliser autrement

---

N'ayant pas accès à vos données, la seul personne qui peut trouver d'où vient le problème, c'est vous.

dégagez le group by / sum de la requete et analyser pour voir d'où vient le problème ..

Si le mcd que vous avez présenté est le bon vous ne devriez avoir qu'une seule ligne par couple SCE.id, POP.product_id pour un même portfolio.

Pour moi, les données que j'ai indiquées et le MCD sont bons. J'ai essayé de virer le GROUP BY ou le SUM mais cela ne change rien.

Voici les données des 3 tables qui entrent en jeu :

# product_scenarios
ID | product_id | scenario | value
1 18 0 0,04
2 18 1 0,08
3 18 2 0,03
4 30 0 0,12
5 30 1 0,07
6 30 2 0,10
7 45 0 0,02
8 45 1 0,07
9 45 2 0,08
...

#volumes
ID | product_id | volume | client_id
1 18 1 000 000 3
2 30 250 000 3
3 45 350 000 3
4 18 3 450 000 4
5 78 1 760 000 3
...

#portfolios_products
ID | portfolio_id | product_id
1 2 18
2 2 30
3 2 45
4 3 18
5 3 78
...

Avec ces données, lorsque je fais la première requête pour remplir la table temporaire, j'obtiens bien une seule ligne par quadruplet SCE.scenario, POP.prod_id, VOL.volume, SCE.value, soit 9 lignes au total pour 3 produits avec 3 scénarios :

scenario | product_id | volume | value
0 18 1 000 000 0,04
1 18 1 000 000 0,08
2 18 1 000 000 0,03
0 30 250 000 0,12
1 30 250 000 0,07
2 30 250 000 0,10
0 45 350 000 0,02
1 45 350 000 0,07
2 45 350 000 0,08

Mais lorsqu'ensuite je calcule les sommes en divisant par le volume total, alors le résultat est toujours le même et en plus il est faux...

Bon, je ne sais pas trop pourquoi, mais je viens de refaire un test et j'obtiens enfin des résultats cohérents !

Merci pour tout !