Forum Programmation.SQL Sous requêtes et alias

Posté par  (site web personnel, Mastodon) . Licence CC By‑SA.
Étiquettes :
3
10
avr.
2025

Bonjour, j'essaye de faire une mini base de gestion de stock. Ça ressemble à cela (sous sqlite) :

DROP VIEW IF EXISTS VueMouvements;

DROP TABLE IF EXISTS Mouvements;

DROP TABLE IF EXISTS Emplacements;

DROP TABLE IF EXISTS Produits;

CREATE TABLE
  Produits (
    id INTEGER PRIMARY KEY,
    nom TEXT UNIQUE NOT NULL
  );

CREATE TABLE
  Emplacements (id INTEGER PRIMARY KEY, nom TEXT UNIQUE NOT NULL);

CREATE TABLE
  Mouvements (
    id INTEGER PRIMARY KEY,
    produit INTEGER NOT NULL REFERENCES Produits (id),
    qte_mvmt INTEGER NOT NULL DEFAULT 1 CHECK (qte_mvmt >= 1),
    origine INTEGER NOT NULL REFERENCES Emplacements (id),
    destination INTEGER NOT NULL REFERENCES Emplacements (id),
    CHECK (origine != destination)
  );

INSERT INTO
  Produits (nom)
VALUES
  ('chaussettes'),
  ('pull'),
  ('chemise');

INSERT INTO
  Emplacements (nom)
VALUES
  ('Tiroir'),
  ('Placard'),
  ('Commode');


INSERT INTO
  Mouvements (produit, qte_mvmt, origine, destination)
VALUES
  (1, 5, 3, 1),
  (2, 5, 3, 1),
  (3, 5, 3, 1),
  (1, 1, 1, 2),
  (2, 2, 1, 3);

CREATE VIEW
  VueMouvements AS
SELECT
  Mouvements.id AS 'id',
  Produits.nom AS 'prod',
  Mouvements.qte_mvmt AS 'qté',
  orig.nom AS 'orig.',
  dest.nom AS 'dest.'
FROM
  Mouvements
  JOIN Produits ON Produits.id = Mouvements.produit
  JOIN Emplacements AS orig ON orig.id = Mouvements.origine
  JOIN Emplacements AS dest ON dest.id = Mouvements.destination;

Maintenant, je voudrais connaître l'état des stocks (éventuellement négatif, j'en suis conscient) par produit et emplacement.

La requête suivante fonctionne (elle produit le résultat escompté, sommes respectives des entrées et sorties de produits par emplacements) :

SELECT 
    Produits.nom, 
    Emplacements.nom,
    (SELECT 
        SUM(Mouvements.qte_mvmt) 
        FROM Mouvements 
        WHERE (Mouvements.produit = Produits.id AND Mouvements.destination = Emplacements.id) 
        GROUP BY Mouvements.produit, Mouvements.origine) AS 'entrées',
    (SELECT 
        SUM(Mouvements.qte_mvmt) 
        FROM Mouvements 
        WHERE (Mouvements.produit = Produits.id AND Mouvements.origine = Emplacements.id) 
        GROUP BY Mouvements.produit, Mouvements.origine) AS 'sorties'
FROM Produits, Emplacements;

mais je n'arrive pas à ensuite effectuer la différence des deux résultats de sous-requêtes, genre :

SELECT ('entrées' - 'sorties')

qui renvoie 0 sur chaque ligne.

J'ai essayé laborieusement avec différentes combinaisons de simples/doubles guillemets sur les noms d'alias mais sans succès.

Est-ce que c'est un problème de structure inadaptée ? J'ai envisagé de mettre à jour une table d'état des stocks par produit et emplacement en utilisant des triggers sur les modifications de la table Mouvements mais cela m'a paru laborieux.

  • # structure

    Posté par  . Évalué à 6 (+4/-0).

    Est-ce que c'est un problème de structure inadaptée ?

    je dirais que oui. Perso, je modélise les DB en entité-relations. L'idée d'une DB, c'est de stocker des données (captain obvious est dans la place :) ), et la table Mouvements, ça ressemble à une action. Ça peut avoir du sens si tu veux journaliser les opérations de déplacements, mais c'est "pas normal" que pour avoir l'état de tes données, tu doives "recalculer" tous les mouvements.

    Si tes produits sont fongibles (pas de différence entre une chaussette et une autre), tu pourrais aussi considérer que la quantité stockée est un attribut de la relation entre produits et emplaçements. Un mouvement serait alors simplement une addition et une soustraction pour les emplacements concernés de la quantité déplacée dans la table représentant cette relation.

    j'espère que je suis relativement clair. Je peux développer si t'as des questions.

    • [^] # Re: structure

      Posté par  (site web personnel, Mastodon) . Évalué à 3 (+1/-0).

      +1
      Je m’attendais à avoir, dans ce cas simple, une table de liaison 

      CREATE TABLE
  Etats (
    produit INTEGER NOT NULL REFERENCES Produits (id),
    emplacement INTEGER NOT NULL REFERENCES Emplacements (id),
    stock INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (produit, emplacement)
  );

      et pour tracer/historiciser les mouvements (mais est-ce vraiment nécessaire ?)

      CREATE TABLE
  Mouvements (
    produit INTEGER NOT NULL REFERENCES Produits (id),
    quantite INTEGER NOT NULL DEFAULT 1 CHECK (quantite >= 1),
    origine INTEGER NOT NULL REFERENCES Emplacements (id),
    destination INTEGER NOT NULL REFERENCES Emplacements (id),
    moment DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (moment, produit),
    -- on peut remplacer moment ici ou le completer par origine,destination
    CHECK (origine != destination)
  );

      “It is seldom that liberty of any kind is lost all at once.” ― David Hume

  • # table stock

    Posté par  (site web personnel) . Évalué à 3 (+2/-0).

    Je ne sais pas si ta question est purement théorique (pour le fun ou didactique), ou alors si tu comptes réellement utiliser ça comme un vrai logiciel de gestion de stock.
    Dans le second cas, il serait préférable d'ajouter deux tables :
    - une qui donne le stock global par article ;
    - une qui donne le stock des articles par emplacements.
    Ces deux tables sont à mettre à jour à chaque mouvement.

    Pourquoi ? Parce que faite une requête qui tape dans les mouvements de stocks pour calculer un stock global est une hérésie en terme de performances. Que se passera-t-il quand ta table des mouvements de stocks fera une centaine de millions d'enregistrements ?

    La consultation d'un stock est une chose qui revient très souvent, beaucoup plus souvent que de consulter les mouvements qui ont été fait, il vaut mieux donc optimiser l'interrogation, quitte à pénaliser un peu l'écriture des mouvements en ajoutant/retirant la quantité mouvementée dans les deux tables. C'est logique, et c'est comme cela que font tous les ERP que j'ai pu croiser dans ma longue carrière.

    • [^] # Re: table stock

      Posté par  . Évalué à 3 (+2/-0).

      Le vrai sujet réside dans le maintient de la cohérence du stock par rapport aux mouvements. L’idée de se passer d’une table des stocks est séduisante : pas de risque d’avoir un stock qui ne correspond pas aux mouvements. Mais comme souligné dans plusieurs commentaires, les performances en souffriront au fil du temps.

      Finalement, un SGBD seul n’est pas suffisant pour construire une application : il faut du code, et l’essentiel est de bien prévoir que les mise à jour du stock et des mouvements soient quasi-synchrones (en vrai il y aura forcément une faite avant l’autre). Il faut bien cerner l’unité logique de traitement pour savoir quand commiter les màj et quand pouvoir les annuler en cas d’erreur. Au final c’est « la base » du développement quand on a à faire des màj en table.

      « Y a même des gens qui ont l’air vivant, mais ils sont morts depuis longtemps ! »

      • [^] # Re: table stock

        Posté par  . Évalué à 3 (+1/-0).

        les performances en souffriront au fil du temps.

        oui, mais il ne faut pas optimiser prématurément (cf Knuth). Rajouter une table de gestion globale du stock a postériori, si c'est anticipé, ça sera facile à faire au moment où il y en aura vraiment besoin. Tant que les perfs ne l'exigent pas, autant ne pas risquer des problèmes d'intégrité de données (comme tu l'as noté)

        • [^] # Re: table stock

          Posté par  . Évalué à 4 (+3/-0).

          Dans le cas présent, je ne pense pas qu’il s’agisse d’optimisation, même si on parle de performance. Il s’agit de poser correctement les objets dont on a besoin et de leur représentation.

          Par ailleurs, j’ai pour coutume de dire que lorsque l’on se rend compte de l’éventualité d’un problème dès la conception, pour peu qu’on travaille sur un logiciel de la « vraie vie » (qui tournera un jour sur la production de vrais clients), alors ce problème nous pètera à la tronche nécessairement. On se prend déjà des tas de portes pas prévues, il est dommage de ne pas éviter de prendre celles dont on sait tôt ou tard qu’elles se présenteront.

          Pour finir sur l’aspect performance, l’ajout de la table des stocks a très peu de chance de dégrader les accès avec peu de volume, ce qui conduit à l’implémenter dès le début.

          « Y a même des gens qui ont l’air vivant, mais ils sont morts depuis longtemps ! »

          • [^] # Re: table stock

            Posté par  . Évalué à 2 (+0/-0).

            une vue permettrait d'accéder à l'information de stock global sans ajouter une table dédiée. Vu les perf des machines actuelles, ça devrait suffire pour beaucoup de cas d'usage, et on pourrait même passer à une vue matérialisée avant d'être vraiment contraint à ajouter une table juste pour ça. La duplication de données dans une DB, perso, je déconseille tant qu'on a des moyens raisonnables de l'éviter.

            il est dommage de ne pas éviter de prendre celles dont on sait tôt ou tard qu’elles se présenteront.

            Oui, mais pas forcément avec du code.
            Là, il y a une question plus générale. Faut-il systématiquement développer du code pour faire face à tous les problèmes qu'on anticipe ? et je crois que la réponse est vraiment non.
            Si le problème ne se présente jamais, t'auras développé du code inutile.
            Si/Quand un autre problème imprévu se présente, la façon dont tu vas y répondre va peut-être t'amener à devoir jeter une partie du code que t'avais écrit pour anticiper un problème que tu n'as pas encore rencontré.

            Ça ne veut pas dire qu'il ne faut rien faire pour gérer les problèmes qu'on anticipe, mais je pense que l'anticipation doit se faire au niveaux des choix d'architecture (pas seulement de l'architecture technique globale, mais aussi de l'architecture du code de chaque module) et des choix techniques. On peut penser les solutions, mais l'implémentation, ça vaut le coup de la délayer jusqu'au moment où le problème menace vraiment de se présenter. C'est une question de gestion de la ressource "temps de développeur".

            • [^] # Re: table stock

              Posté par  (site web personnel, Mastodon) . Évalué à 2 (+0/-0). Dernière modification le 11 avril 2025 à 13:23.

              Je pense que vous êtes en phase même si vous n’avez pas les mêmes formulations… Il ne s’agit pas d’anticiper tous les problèmes (est-ce vraiment possible ?) ou de se lancer dans de la production de code quand on peut faire sans frontal à priori…
              Ici, il s’agit justement de remettre en question l’architecture (plus précisément le « modèle de données » s’il y a) car on se rend compte avec peu de données que ça pose déjà des problèmes de maniabilité/praticité… (et je parie qu’il y a des trucs plus poussés qui sont prévus) : ce n’est donc pas une optimisation prématurée mais bien le moment de revoir la copie.

              Ceci dit, je pense que vouloir gérer des « mouvements » est plus de l’ordre du code frontal ou il faut passer par des procédures stockées si on veut faire cela en base.

              “It is seldom that liberty of any kind is lost all at once.” ― David Hume

  • # requête mal faite

    Posté par  (site web personnel, Mastodon) . Évalué à 3 (+2/-0).

    Ta requête sera extrêmement lente: tu fait 2 requête par lignes de requête qui est un join exhaustif.

    Il te faut faire un Join propre entre des sous-requêtes. Je peux regarder tout à l’heure mais il me faudra tester rapidement.
    Ainsi tu aura 3 requêtes. Les 2 sous-requetes rapides. Et une requête sur les 2 résultats en en ram…

    Sous licence Creative common. Lisez, copiez, modifiez faites en ce que vous voulez.

    • [^] # Re: requête mal faite

      Posté par  (site web personnel, Mastodon) . Évalué à 1 (+0/-0). Dernière modification le 11 avril 2025 à 14:04.

      Voilà une requête propre et efficace ;) :

          SELECT Produits.nom, Emplacements.nom,
        entree.somme as entree, sortie.somme as sortie,
        entree.somme-sortie.somme as diff
    FROM Produits
    JOIN Emplacements
    LEFT JOIN (SELECT produit, destination, SUM(qte_mvmt) as somme
            FROM Mouvements
            GROUP BY produit, destination) entree
        ON entree.produit = Produits.id AND entree.destination = Emplacements.id
    LEFT JOIN (SELECT produit, origine, SUM(qte_mvmt) as somme
            FROM Mouvements
            GROUP BY produit, origine) sortie
        ON sortie.produit = Produits.id AND sortie.origine = Emplacements.id;

      PS : Ca marche en MySQL. Je ne sais pas quelle est ta cible.

      Sous licence Creative common. Lisez, copiez, modifiez faites en ce que vous voulez.

      • [^] # Re: requête mal faite

        Posté par  (site web personnel, Mastodon) . Évalué à 3 (+1/-0). Dernière modification le 11 avril 2025 à 13:09.

        Ta requête sera extrêmement lente: tu fait 2 requête par lignes de requête qui est un join exhaustif.

        Je pense que le plan d’exécution est quand même intelligent (mais ça n’empêchera pas plusieurs scans complets) d’où les zéros : il n’attend pas de calculer chaque champ…

        PS : Ca marche en MySQL. Je ne sais pas quelle est ta cible.

        J’ai cru comprendre SQLite, et à première vue ça devrait passer aussi (et presque partout où il y a le support des jointures)

        PPS : si la modération peut transformer la longue multiligne de code en bloc SQL…

        “It is seldom that liberty of any kind is lost all at once.” ― David Hume

        • [^] # Re: requête mal faite

          Posté par  (site web personnel) . Évalué à 4 (+1/-0).

          PPS : si la modération peut transformer la longue multiligne de code en bloc SQL…

          Fait, merci.

          • [^] # Re: requête mal faite

            Posté par  (site web personnel, Mastodon) . Évalué à 1 (+0/-0).

            Je serais curieux de savoir comment j’aurais du faire. Un truc genre [code lang=sql] xxx [/code] ?

            Sous licence Creative common. Lisez, copiez, modifiez faites en ce que vous voulez.

            • [^] # Re: requête mal faite

              Posté par  (site web personnel, Mastodon) . Évalué à 3 (+1/-0).

              Dans l’aide mémoire affiché en bas de la fenêtre de rédaction, tu peux repérer dans le tableau les deux lignes de type :

              (ce qu’il faut saisir) → (le résultat)

              `select 'foo' from dual;` → select 'foo' from dual;

               ```sql
 select 'foo' from dual;
 ```

              →

              select 'foo' from dual;

              Voir aussi la documentation sur le wiki.

              “It is seldom that liberty of any kind is lost all at once.” ― David Hume

  • # Commentaire supprimé

    Posté par  (site web personnel, Mastodon) . Évalué à 1 (+0/-0). Dernière modification le 13 avril 2025 à 11:05.

    Ce commentaire a été supprimé par l’équipe de modération.

  • # Réponse collective

    Posté par  (site web personnel, Mastodon) . Évalué à 1 (+0/-0).

    Merci pour les commentaires et suggestions.

    Notamment à https://linuxfr.org/users/abriotde qui a proposé une réponse explicite à la demande de requête (et qui fonctionne sous sqlite).

    En ce qui concerne la question table d'états mise à jour à chaque mouvement ou requête qui synthétise les états à chaque demande, je m'étais posé cette question, et j'avais effectivement pris en compte les performances et fait le choix d'une table des états.

    Mais je ne vous ai pas tout dit, en réalité c'est la faute de https://sql-page.com/ qui m'a motivé à bricoler ce truc, et au départ j'avais donc soigneusement mitonné des triggers lors des insertions/màj/suppression sur la table Mouvements et cela fonctionnait correctement. Mais lors de l'implémentation des pages sqlpage, je me suis inspiré des bouts de code trouvés sur internet et j'ai voulu utiliser la syntaxe INSERT OR REPLACE et là évidemment, j'ai eu des surprises. Et après quelques heures de recherche, j'ai fini par envisager cette solution de la requête systématique, en me basant sur l'idée que les dimension seraient de l'ordre de moins d'une centaine de produit, moins d'une vingtaine d'emplacements et moins de 500 mouvements par an (pour mon cas d'usage, l'objectif étant de faire tourner le bazar sur raspberry pi 2B).

    Pour le moment, et en prenant en compte les échanges ici, j'ai fini par modifier mes pages sqlpage et gérer une table des états mise à jour à chaque modification de la table.

    Exemple des triggers dans la définition de la base :

    CREATE TABLE
  Bilans (
    emplacement INTEGER NOT NULL REFERENCES Emplacements (id),
    produit INTEGER NOT NULL REFERENCES Produits (id),
    nombre INTEGER NOT NULL,
    PRIMARY KEY (emplacement, produit)
  );

CREATE TRIGGER maj_bilans_insert AFTER INSERT ON Mouvements BEGIN
INSERT INTO
  Bilans (emplacement, produit, nombre)
VALUES
  (NEW.origine, NEW.produit, - NEW.qte_mvmt) ON CONFLICT (emplacement, produit) DO
UPDATE
SET
  nombre = nombre - NEW.qte_mvmt;

INSERT INTO
  Bilans (emplacement, produit, nombre)
VALUES
  (NEW.destination, NEW.produit, NEW.qte_mvmt) ON CONFLICT (emplacement, produit) DO
UPDATE
SET
  nombre = nombre + NEW.qte_mvmt;

END;

CREATE TRIGGER maj_bilans_delete AFTER DELETE ON Mouvements BEGIN
UPDATE Bilans
SET
  nombre = nombre - OLD.qte_mvmt
WHERE
  (
    Bilans.emplacement = OLD.destination
    AND Bilans.produit = OLD.produit
  );

UPDATE Bilans
SET
  nombre = nombre + OLD.qte_mvmt
WHERE
  (
    Bilans.emplacement = OLD.origine
    AND Bilans.produit = OLD.produit
  );

END;

CREATE TRIGGER maj_bilans_update BEFORE
UPDATE ON Mouvements BEGIN
UPDATE Bilans
SET
  nombre = nombre - OLD.qte_mvmt
WHERE
  (
    Bilans.emplacement = OLD.destination
    AND Bilans.produit = OLD.produit
  );

UPDATE Bilans
SET
  nombre = nombre + OLD.qte_mvmt
WHERE
  (
    Bilans.emplacement = OLD.origine
    AND Bilans.produit = OLD.produit
  );

INSERT INTO
  Bilans (emplacement, produit, nombre)
VALUES
  (NEW.origine, NEW.produit, - NEW.qte_mvmt) ON CONFLICT (emplacement, produit) DO
UPDATE
SET
  nombre = nombre - NEW.qte_mvmt;

INSERT INTO
  Bilans (emplacement, produit, nombre)
VALUES
  (NEW.destination, NEW.produit, NEW.qte_mvmt) ON CONFLICT (emplacement, produit) DO
UPDATE
SET
  nombre = nombre + NEW.qte_mvmt;

END;

Envoyer un commentaire

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.