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.

[punkoff]

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

[avairet]

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.

[punkoff]

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)

[avairet]

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 :

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

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 ?

[avairet]

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)

[punkoff]

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)

[avairet]

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

[punkoff]

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 :

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

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

[avairet]

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.

[avairet]

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 :

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

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

[punkoff]

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 :

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

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

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.

[avairet]

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 ?

[punkoff]

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.

[avairet]

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