Discussion :

[Nerva]

Merci pour les requêtes, mais là ça fait quand même beaucoup pour un filtre de produit avec en plus des achats/ventes qui ne sont plus sur la même ligne. Je ne pourrais jamais adapter tout ça à une gestion de stock...

[fsmrel]

D’accord, va pour un résultat de 32 lignes.

J’ai codé comme je le faisais avec DB2 il y a 40 ans, c’est-à-dire sans l’opérateur JOIN (et a fortiori l’OUTER JOIN qui nous embarque dans une logique trivaluée dont je me méfie).

C’est rustique, mais ça a l’air de fonctionner correctement, vous me direz.

J’ai créé des vues (avec JOIN), mais c’était pour y voir plus clair.

Comme on ne tient compte que du produit 1, j’ai inondé les SELECT de « PRO_ID = 1 » virables.

Une vue pour les achats :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
create view achats (ACH_DATE, PRO_ID, ACH_PUHT, ACH_QTE, ACH_TTL)
as
select a.ACH_DATE AS OPER_DATE
, PRO_ID
, ACH_PUHT 
, SUM (COALESCE (ACH_QTE, 0)) 
, SUM (ACH_PUHT * ACH_QTE)  
from T_ACHATS as a left join T_ACHATS_SUB as b on  a.ACH_ID = b.ACH_ID
group by a.ACH_DATE, PRO_ID, ACH_PUHT, ACH_QTE, ACH_PUHT * ACH_QTE
;

Une vue pour les ventes :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
create view ventes (VEN_DATE, PRO_ID, VEN_PUHT, VEN_QTE, VEN_TTL)
as
select a.VEN_DATE AS OPER_DATE
, PRO_ID
, VEN_PUHT 
, SUM (COALESCE (VEN_QTE, 0)) 
, SUM (VEN_PUHT * VEN_QTE)   
from T_VENTES as a left join T_VENTES_SUB as b on  a.VEN_ID = b.VEN_ID
group by a.VEN_DATE, PRO_ID, VEN_PUHT, VEN_QTE, VEN_PUHT * VEN_QTE
;

La requête comme il y a 40 ans :

Code SQL :

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
31
32
33
34
35
36
37
38
39
40
41
42
select 
ACH_DATE, a.PRO_ID, ACH_PUHT, ACH_QTE, ACH_TTL
, VEN_PUHT, VEN_QTE, VEN_TTL
from achats as a 
 ,   ventes as b 
where 
a.ACH_DATE = b.VEN_DATE 
and a.PRO_ID = b.PRO_ID
and a.PRO_ID = 1 -- virer si on veut tous les produits
 
union
 
select 
ACH_DATE, a.PRO_ID, ACH_PUHT, ACH_QTE, ACH_TTL
, 0, 0, 0
 
from achats as a 
 
where not exists (
select * from ventes b where a.ACH_DATE = b.VEN_DATE
and a.PRO_ID = b.PRO_ID
)
and a.PRO_ID = 1 -- virer si on veut tous les produits
 
union
 
select 
VEN_DATE, a.PRO_ID, 0, 0, 0, VEN_PUHT, VEN_QTE, VEN_TTL
from ventes as a 
where not exists (
select * from achats b where b.ACH_DATE = a.VEN_DATE
and a.PRO_ID = b.PRO_ID
)
and a.PRO_ID = 1 -- virer si on veut tous les produits
 
union
 
select CAL_DATE, 0, 0, 0, 0, 0, 0, 0
from TR_CALENDRIER as c
where not exists (select * from T_ACHATS where ACH_DATE = c.CAL_DATE)
  and not exists (select * from T_VENTES where VEN_DATE = c.CAL_DATE)
;

Au résultat :

J'espère ne pas m'être planté dans les copier/coller...

[Nerva]

Ok, ça fonctionne en HSQLDB et ça affiche le résultat escompté. Mais ça implique 2 vues préalables et une requête très touffue qui, même si ça n'est pas la question à mon niveau, ne doit pas être très performante en client-serveur, non ?

Donc, j'ai nommé votre requête R_MOUVEMENTS. Je me rends compte d'ailleurs, un peu tardivement, que le filtrage par produit n'était pas réellement nécessaire à ce niveau et qu'il serait beaucoup plus simple de l'intégrer dans la requête "finale".

La finalité c'est la gestion d'un stock au jour le jour. La requête suivante, que j'ai mis du temps à peaufiner, n'est "pas tout à fait" correcte :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
M1.ACH_DATE,
M1.PRO_ID,
M1.ACH_QTE,
M1.ACH_PUHT,
M1.ACH_TTL,
M1.VEN_QTE,
SUM(M2.ACH_QTE - M2.VEN_QTE) AS STO_QTE,
(M1.ACH_TTL + SUM(M2.ACH_TTL)) / (M1.ACH_QTE + SUM(M2.ACH_QTE)) AS STO_CUMP,
SUM(M2.ACH_QTE - M2.VEN_QTE) * (M1.ACH_TTL + SUM(M2.ACH_TTL)) / (M1.ACH_QTE + SUM(M2.ACH_QTE)) AS STO_MNT
FROM R_MOUVEMENTS M1
LEFT OUTER JOIN R_MOUVEMENTS M2 ON M1.PRO_ID = M2.PRO_ID AND M1.ACH_DATE >= M2.ACH_DATE
GROUP BY M1.ACH_DATE, M1.PRO_ID, M1.ACH_QTE, M1.ACH_PUHT, M1.ACH_TTL, M1.VEN_QTE

La copie d'écran suivante c'est le stock calculé dans un tableur (on laisse de côté les colonnes F et G, qui ne sont pas primordiales). C'est dans la colonne I que tout se définit.



Avec la requête, j'obtiens ça :



Pour la colonne STO_QTE, c'est ok. Mais c'est STO_CUMP qui ne va pas (et par conséquent STO_MNT).

Le calcul du stock (méthode CUMP) c'est :

Code :

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

(Montant précédent du stock + Montant de l'achat) / (Quantité précédente du stock + Quantité de l'achat)

La ligne 3, on va dire que c'est le stock initial (concrétisé par un achat dans une table de base de données).

Donc, I4 = (I3 + D4) / (H3 + B4)

Le CUMP (colonne I) change à chaque fois qu'un nouvel achat est fait, si et seulement si, le prix unitaire d'achat est différent, ce qui est toujours le cas dans cet exemple. Concrètement, pour l'achat du 03/01, le CUMP passe à 1449.90 ; mis à part la différence d'arrondi, c'est ok dans la requête. Mais ce montant est identique jusqu'au 07/01 ; il change le 08/01 puisqu'un nouvel achat est fait.

Or, dans la requête, la valeur change dès le 04/01, ce qui n'est pas conforme au calcul, avec 1449.68 qui sort je ne sais d'où. Et tout ce qui suit est erroné.

Il y a donc quelque chose qui ne va pas dans cette requête...

[fsmrel]

Bonjour,
 

Je me rends compte d'ailleurs, un peu tardivement, que le filtrage par produit n'était pas réellement nécessaire à ce niveau et qu'il serait beaucoup plus simple de l'intégrer dans la requête "finale".

 
C’était évidemment prévu. J’avais écrit :
 

Comme on ne tient compte que du produit 1, j’ai inondé les SELECT de « PRO_ID = 1 » virables. »

 
Si vous examinez ma requête, vous trouverez 3 occurrences de "and a.PRO_ID = b.PRO_ID"

Donc à virer pour avoir tous les produits.
 

Mais ça implique 2 vues préalables

 
Comme je vous l’ai expliqué :
 

J’ai créé des vues (avec JOIN), mais c’était pour y voir plus clair.

 
Il s’agissait donc pour moi de me simplifier la vie. On peut se passer de ces vues et coder en conséquence : contrairement à ce que vous croyez, il n’y a pas d’implication !
 

et une requête très touffue qui, même si ça n'est pas la question à mon niveau, ne doit pas être très performante en client-serveur, non ?

 
Ça n’a rien de touffu ! union de 4 sous-requêtes, toutes sont simples à interpréter.
Je rappelle que j’ai pris le parti de coder comme il y a 40 ans, pour montrer que l’opérateur JOIN, et surtout l’opérateur OUTER JOIN ne sont pas nécessaires : à l’époque ils n’existaient pas ! Ce dernier a été inventé à cause de NULL et de la logique ternaire inhérente ([i]vrai, faux, null[i]). NULL est un semeur de m... qui n’aurait pas dû voir le jour : la vraie algèbre relationnelle est calée sur la logique binaire ([i]vrai, faux[i]) et NULL y est évidemment interdit de séjour (donc même punition pour OUTER JOIN).

Quant à la performance, pour avoir fait le DBA pendant des années, chez nombre de mes clients (banques, assurances, industrie, services, etc.), avec engagement sur la performance (pénalités financières en cas de non respect), j’ai toujours construit les bases de données et testé très minutieusement les requêtes, pour ne pas avoir de mauvaise surprise (prévoir, c’est savoir). A votre tour maintenant de coiffer votre casquette de DBA, et dans la soute de choisir les index pertinents, éplucher les statistiques fournies par le SGBD, sans oublier de mettre en oeuvre les campagnes d’EXPLAIN indispensables, pouvant nous conduire à modifier les MPD, virer les produits cartésiens et autres impedimenta.

[Nerva]

Bon, si vous dites que cette requête est "standard" et performante, je vous crois sur-parole. Pour ce qui est de tester, ce n'est de toute façon pas avec trois dizaines de lignes que j'aurai une idée des performances.

Après je ne vais pas revenir sur le NULL (j'avais déjà pas mal échangé là-dessus il y a quelques mois), mais avis perso, donc à mon niveau de connaissances, selon ma compréhension des bases de données : c'est pas le NULL qui me pose un problème mais le fait que pour une absence de valeur, ça puisse être NULL ou vide. Je vais être plus tolérant que vous : c'est l'un des deux qui n'aurait jamais dû voir le jour.

Fin de parenthèse...

[fsmrel]

si vous dites que cette requête est "standard" et performante, je vous crois sur-parole.

Je n’ai pas écrit que la requête était performante mais, pour en juger, qu’il faut utiliser les outils à notre disposition pour mesurer cette performance. En l’occurrence la boule de cristal ça ne marche pas. DBA c’est un métier. A défaut, quand dans une très grande entreprise de transport ferroviaire, un traitement quotidien a duré 240 heures lors de mise en production, il n’y avait manifestement pas de DBA à l’horizon, rien de ce que je préconise n’avait été entrepris : les gens ont codé et mis en production, point-barre. Do you understand ce qu’est la mission cruciale du DBA ?

[fsmrel]

Précision

si vous dites que cette requête est "standard" et performante, je vous crois sur-parole.

Je n’ai pas écrit que la requête était performante mais, pour en juger, qu’il faut utiliser les outils à notre disposition pour mesurer cette performance. En l’occurrence la boule de cristal ça ne marche pas. DBA c’est un métier.

Pour compléter je vous renvoie à ce post :

Jointure, jointure, vous avez dit jointure ?

SQLpro, un champion de SQL s’il en est :

SQLpro confirme.

[Nerva]

Si cette requête a été construite comme il y a 40 ans, on peut s'interroger quand même sur sa performance par rapport à des constructions modernes, sinon, pourquoi évoluer ?

Et ça n'explique toujours pas (à moi en tout cas) pourquoi seule la ligne du 15/01 n'était pas renvoyée avec ma requête (+ la clause WHERE fournie par vanagreg) alors qu'une vingtaine d'autres sont identiques, c'est-à-dire vente seule.

[fsmrel]

Si cette requête a été construite comme il y a 40 ans, on peut s'interroger quand même sur sa performance par rapport à des constructions modernes, sinon, pourquoi évoluer ?

Dans mon précédent message, je vous ai fourni la réponse à votre interrogation :
La performance de la jointure est la même quel que soit le style : WHERE (version "antique") ou JOIN (version "moderne"). je vous renvoie aux liens que j’y ai fournis.

Là où on peut gagner, c’est en remplaçant UNION par UNION ALL. Dans le cas de UNION, le SGBD est obligé de trier pour éviter les doublons. Comme dans la requête que j’ai proposée il ne peut y avoir de doublons, il est mieux d’utiliser UNION ALL.

Avec UNION ALL, pas de tri.

Et ça n'explique toujours pas (à moi en tout cas) pourquoi seule la ligne du 15/01 n'était pas renvoyée avec ma requête (+ la clause WHERE fournie par vanagreg) alors qu'une vingtaine d'autres sont identiques, c'est-à-dire vente seule.

Je répète que je me méfie de la logique ternaire (vrai, faux, null) comme de la peste, et dont vos nombreux outer join usent et abusent. J’ai proposé une solution alternative avec la logique binaire (vrai, faux), et ne peux pas faire mieux.

[Nerva]

Merci pour ces précisions.

Par curiosité, j'ai exposé l'affaire à ChatGPT. Voici la requête qu'il a construite et qui retourne le résultat escompté :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
  C.CAL_DATE,
  COALESCE(A2.PRO_ID, V2.PRO_ID) AS PRO_ID,
  SUM(CASE WHEN A2.PRO_ID IS NOT NULL THEN A2.ACH_QTE ELSE 0 END) AS ACH_QTE,
  SUM(CASE WHEN A2.PRO_ID IS NOT NULL THEN A2.ACH_PUHT * A2.ACH_QTE ELSE 0 END) AS ACH_TTL,
  SUM(CASE WHEN V2.PRO_ID IS NOT NULL THEN V2.VEN_QTE ELSE 0 END) AS VEN_QTE
FROM TR_CALENDRIER C
LEFT JOIN T_ACHATS A1 ON C.CAL_DATE = A1.ACH_DATE
LEFT JOIN T_ACHATS_SUB A2 ON A1.ACH_ID = A2.ACH_ID AND A2.PRO_ID = 1
LEFT JOIN T_VENTES V1 ON C.CAL_DATE = V1.VEN_DATE
LEFT JOIN T_VENTES_SUB V2 ON V1.VEN_ID = V2.VEN_ID AND V2.PRO_ID = 1
GROUP BY C.CAL_DATE, COALESCE(A2.PRO_ID, V2.PRO_ID)
HAVING COALESCE(A2.PRO_ID, V2.PRO_ID) IS NOT NULL
ORDER BY C.CAL_DATE;

Note : je suis même allé plus loin en lui soumettant le calcul des stocks (à partir d'une autre structure, mais qui était la finalité de ce sujet) et il a conçu une énorme vue pour PostgreSql qui retourne exactement ce qu'on obtient dans un tableur. Pour être franc, c'est la première fois que j'ai utilisé une IA pour du SQL : c'est effrayant..........

[fsmrel]

D’accord pour la requête proposée par ChatGPT.

Cela dit, la clause WHERE proposée par vanagreg interdit l’affichage de la vente du 15/01/2025.

A cette date :

Achat produit 2 => P1.PRO_ID = 2
Vente produit 1 => P2.PRO_ID = 1

Si on décompose la clause :

P1.PRO_ID = 1 and P2.PRO_ID = 1 -- condition non satisfaite au 15/01/2025 car P1.PRO_ID = 2
or
P1.PRO_ID = 1 and P2.PRO_ID is null -- condition non satisfaite au 15/01/2025 car P1.PRO_ID = 2
Or
P2.PRO_ID = 1 and P1.PRO_ID is null -- condition non satisfaite au 15/01/2025 car P1.PRO_ID = 2
Or
P1.PRO_ID is null and P2.PRO_ID is null -- condition non satisfaite au 15/01/2025 car P1.PRO_ID = 2

Ceci explique pourquoi la clause n’est pas valide et qu’il n’y a rien au 15/01/2025 (en l’occurrence à cause du produit 2).


ChatGPT a donc évité le piège du WHERE, au bénéfice d'un HAVING.

En principe, le ORDER BY n'est pas nécessaire.

[fsmrel]

Nerva, dans votre 1er message, vous écrivez :

L'affichage de la requête se fait sur une période définie avec T_CALENDRIER : une ligne vide s'affiche le jour où il n'y a ni achat ni vente (au total, les 32 lignes de la table doivent s'afficher).

Le 01/01/2025, il n'y a eu ni achat ni vente : demander à ChatGPT d’en tenir compte (le résultat de sa requête ne compte que 31 lignes) 😉