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