Programmation » SQL - Récursivité (sort of)
Catégorie:  
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 12:55:42,
Par Schnick
Bonjour les experts db,

Je suis confronté à un problème de données et de table à constituer dans un environnement SQL.

Le principe business est le suivant, j'ai une table qui comprend toutes les recettes (alimentaire) d'une entreprise de restauration (pas Quick :sol:)

En simplifié, la table est ainsi :
- Clé
- RecetteID
- IngrédientID
- SousRecetteID
- Quantité

Un recette peut donc être composée de 1..n ingrédients et/ou de 1..n sous-recettes auxquels sont à chaque fois associés une quantité.

Exemple :
Recette R1 est composée de :
  1. - Ingrédient I1
  2. - Ingrédient I2
  3. - Ingrédient I3
  4. - SousRecette R2
  5. - SousRecette R3


Recette R2 est composée de:
  1. - Ingrédient I5
  2. - Ingrédient I6
  3. - SousRecette R4


etc.

Le nombre de sous-recettes (i.e. recette de recette) peut aller jusqu'à une hiérarchie de 7 niveaux mais je n'ai aucune garantie de stabilité à ce niveau.

Ce que j'ai besoin, c'est de créer une table (ou en tout cas d'extraire les données) avec toutes les recettes composées uniquement d'ingrédients. Donc je dois aller avec une sorte de récursivité aller chercher les ingrédients de toutes les sous-recettes et les associer à la recette de niveau 1.

J'ai essayé de le faire avec les requêtes récursives en utilisant les tables communes (CTE) mais je n'y arrive pas. Est-ce que quelqu'un peut m'aider ?
La mort, c'est un peu comme une connerie. Le mort, lui, il ne sait pas qu'il est mort. Ce sont les autres qui sont tristes. Le con, c'est pareil. Philippe Geluck
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 13:09:35,
Par Pinou
Je comprend pas bien ta [table qui comprend toutes les recettes] :
- Clé
- RecetteID
- IngrédientID
- SousRecetteID
- Quantité

Quantité est la quantité de l'ingrédient ou de la sous recette ?
IngrédientID et SousRecetteID peuvent tous deux contenir une valeur pour une même row ?

Si je devais le faire rapidement et sous réserve des perfs je ferait comme ça :

SELECT RecetteID
,SUM(CASE WHEN IngrédientID IS NULL THEN 0 ELSE 1 END) AS CountIngrédientID
,SUM(CASE WHEN SousRecetteID IS NULL THEN 0 ELSE 1 END) AS CountSousRecetteID
FROM [table qui comprend toutes les recettes]
GROUP BY RecetteID
HAVING CountIngrédientID > 0
AND CountSousRecetteID = 0
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 14:13:15,
Par Schnick
Merci de ton retour mais je ne crois pas que cela fonctionnerait.

Un exemple pour illustrer.

La recette Steak :
Elle est composée de l'ingrédient Steak (qté 1) et de l'ingrédient Haricot Vert (Qté 50g)
Elle est également composée de la recette frites (50%) et de la recette purée (35%) et de la recette pomme de terre (15%)

La recette frites est composée de de l'ingrédient frites (130g), de l'ingrédient huile (30ml) et de la recette poivre et sel (Qté 1).

La recette poivre & sel est composée de l'ingrédient poivre à 1g et de sel à 1.5g.

Ce que je voudrais obtenir c'est la recette steak =
- Ingrédient Steak 1 pièce
- Haricot Vert 50 grammes
- Frites 65g (50% de la recette)
- Huile 15 ml (50% de la recette)
- Poivre 0.5g (50% de la recette de la recette)
- Sel 0.75g) (50% de la recette de la recette)
- etc. avec les autres ingrédients...

Pour l'exemple, dans ma table source (LignesDeRecettes) j'aurai :
Clé1 RecetteSteak Steak null 1 pièce
Clé1 RecetteSteak Haricot null 50 gramme
Clé1 RecetteSteak null RecetteFrite 50 %
Clé1 RecetteSteak null RecettePurée 35 %
Clé1 RecetteSteak null RecettePdt 15 %
Clé2 RecetteFrite Frites null 130 gramme
Clé2 RecetteFrite Huile null 30 ml
Clé2 RecetteFrite null RecetteP&Sel 1 pièce
Clé3 RecetteP&Sel Poivre null 1 gramme
Clé3 RecetteP&Sel Sel null 1.5 gramme


Et je voudrais

Clé1 RecetteSteak ingré1 qté unité
Clé1 RecetteSteak ingré2 qté unité
etc.

J'espère que c'est plus clair.
La mort, c'est un peu comme une connerie. Le mort, lui, il ne sait pas qu'il est mort. Ce sont les autres qui sont tristes. Le con, c'est pareil. Philippe Geluck
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 14:16:53,
Par zion
Et tu dois en sortir quoi? Une table à afficher, ou autre chose?

toujours se dire que la seule solution vient du SQL le cas échéant? Tu as des gigas de recettes? Non, ce ne sont que des IDs, non? Pourquoi ne pas faire un simple query pour mettre cela en mémoire, puis de parcourir ton arbre et de compter. En terme de performance, à moins d'avoir le query idéal, tu auras certainement un meilleur résultat.

Quitte, si tu as besoin d'aller chercher plus d'infos après, à ne faire ta sélection que sur base des ids trouvés (bon après si t'as 200.000 ids, on en reparle bien sûr, mais c'est plus simple de maîtriser le O() de ton algo que celui de ton query).
Je suis le Roy :ocube:
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 14:17:38,
Par zion

J'espère que c'est plus clair.



Non :ddr555:
Je suis le Roy :ocube:
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 14:29:20,
Par Schnick
Je dois sortir des ventes par ingrédient au final de mon rapport.
J'ai un table avec les ventes de produits : x steak
Je dois avoir : x tranches steak, x grammes de haricots, etc.

EDIT : La table des ventes n'est évidemment pas la même que la table contenant les lignes de recettes.

Compter je veux bien mais je n'arrive pas à faire le saut (intellectuel) de comment va être pris en compte la recette de recette de recette de recette. Je ne peux pas compter le nombre d’occurrences puisque chaque recette peut être rappelée par n'importe quelle autre recette.

Mais c'est peut-être moi auquel il manque une case :smile:

Dernière édition: 06/10/2015 @ 14:31:10
La mort, c'est un peu comme une connerie. Le mort, lui, il ne sait pas qu'il est mort. Ce sont les autres qui sont tristes. Le con, c'est pareil. Philippe Geluck
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 19:56:41,
Par zion
T'as pas des millions de recettes, si?

Ce que je ferais, un array statique, pour taille le plus grand id de recette. Ca va bouffer qqs KB de mémoire, pas grave. Tu y charges chaque recette avec un array pour les ingrédients, et un array pour les ids des sous recettes.

Puis, tu te fais un array avec les produits, tous à 0, même chose, de la taille du plus grand id, avec ce que tu as besoin pour ton rapport déjà lié (le nom du produit, etc).

Puis tu fais simplement ton query des ventes, et pour chaque ligne, tu parcours ton array.

Je te promets que ce sera ultra rapide, même avec des dizaines de milliers de rows, vu que tu t'indexes toi même ton truc grâce à tes ids.

Sinon tu peux le faire en SQL pur, mais vu que tu as pas de certitude de ton degré de récursivité, soit tu t'en forces un, soit t'es parti pour la gloire.
Je suis le Roy :ocube:
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 22:22:38,
Par Pinou
OK, donc en effet, j'avais rien compris :tinostar:
Je pensais que tu voulais sortir la liste de "toutes les recettes composées uniquement d'ingrédients."

Je vais relire pour essayer de comprendre.
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 22:43:04,
Par Schnick
T'as pas des millions de recettes, si?

Ce que je ferais, un array statique, pour taille le plus grand id de recette. Ca va bouffer qqs KB de mémoire, pas grave. Tu y charges chaque recette avec un array pour les ingrédients, et un array pour les ids des sous recettes.

Puis, tu te fais un array avec les produits, tous à 0, même chose, de la taille du plus grand id, avec ce que tu as besoin pour ton rapport déjà lié (le nom du produit, etc).

Puis tu fais simplement ton query des ventes, et pour chaque ligne, tu parcours ton array.

Je te promets que ce sera ultra rapide, même avec des dizaines de milliers de rows, vu que tu t'indexes toi même ton truc grâce à tes ids.

Sinon tu peux le faire en SQL pur, mais vu que tu as pas de certitude de ton degré de récursivité, soit tu t'en forces un, soit t'es parti pour la gloire.


L'idée est séduisante, faut juste que je me renseigne sur le comment faire un array. :banzai:

Puis faut des arrays deux dimensions, recette | sous recette et recette | ingredients, non. Sinon je parcourerais un table sans référence à la recette et je ne saurai pas faire le lien entre le produit et la recette.
La mort, c'est un peu comme une connerie. Le mort, lui, il ne sait pas qu'il est mort. Ce sont les autres qui sont tristes. Le con, c'est pareil. Philippe Geluck
   
SQL - Récursivité (sort of)
Publié le 06/10/2015 @ 22:49:18,
Par philanthrope
Bonsoir,

Je pense que la réponse se trouve au moins à cette adresse: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

Il y a bcp d'autres exemples. Le mots clés sont: mysql requête récursive arborescence
   
SQL - Récursivité (sort of)
Publié le 07/10/2015 @ 09:49:07,
Par Schnick
Bonsoir,

Je pense que la réponse se trouve au moins à cette adresse: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

Il y a bcp d'autres exemples. Le mots clés sont: mysql requête récursive arborescence


Oui j'ai commencé par Google et donc ici ou là mais n'étant pas un expert je me suis retrouvé bloqué. Je n'arrive pas à appliquer les exemples à mon cas pratique.

Bon, sachant que je devais mettre les données à disposition d'un cube cognos, je vais les donner en l'état et c'est lui qui va s'occuper de la récursivité. Donc problème résolu pour le client.

Mais je vais quand même essayer de trouver comment faire. :banzai:

EDIT : c'est marrant dans la prévisualisation le lien fonctionne mais pas dans l'affichage final :roll:

Dernière édition: 07/10/2015 @ 11:14:20
La mort, c'est un peu comme une connerie. Le mort, lui, il ne sait pas qu'il est mort. Ce sont les autres qui sont tristes. Le con, c'est pareil. Philippe Geluck
   
SQL - Récursivité (sort of)
Publié le 07/10/2015 @ 10:40:48,
Par zion
c'est [url = lelien ] plop [ / url ]
Je suis le Roy :ocube:
   
SQL - Récursivité (sort of)
Publié le 07/10/2015 @ 11:13:22,
Par Schnick
Il apprécie pas les guillemets. Mais c'est amusant qu'en prévisionnage çà passe. :bombe:

Dernière édition: 07/10/2015 @ 11:13:59
La mort, c'est un peu comme une connerie. Le mort, lui, il ne sait pas qu'il est mort. Ce sont les autres qui sont tristes. Le con, c'est pareil. Philippe Geluck
   
SQL - Récursivité (sort of)
Publié le 07/10/2015 @ 16:18:47,
Par Pinou
Pas mal comme solution.
Je n'avais jamais utilisé de CTE avec UNION ALL.
Je viens de l'appliquer dans une DB pour améliorer requête moins propre et légèrement buguée.

Mais puisque tu n'en a plus besoin, j'ai pas trop le courage d'essayer de le refaire pour ta tale de recette. :smile:
Répondre - Catégorie:  
Informaticien.be - © 2002-2024 AkretioSPRL  - Generated via Kelare
The Akretio Network: Akretio - Freedelity - KelCommerce - Votre publicité sur informaticien.be ?