SQL Postgres - Générer des sous requêtes dans une boucle

**olivierandré** · 14/06/2020, 15h35

Version Postgresql 9.6

Bonjour à toutes et à tous,

Je me pose une question en Sql et j'aurais besoin d'une piste. Si quelqu’un pourrait m'aider, je suis preneur.

J'explique :
J'ai une table 'prime'. Elle regroupe les différents types de primes que je peux affecter à des clients.
Exemple : Bonus exceptionnel, Bonus de fidelité etc.
Donc, elle comporte des colonnes habituelles : id, name, code etc.

J'ai une table 'purchase_invoice' qui stocke les factures des clients.
Et j'ai une table 'purchase_invoice_prime' qui fait la jointure entre la facture est les différentes primes attribuées.

Quand je souhaite en Sql afficher le montant facturé d'un client et les différentes primes sous forme de colonne, je peux exécuter ce genre de requête :

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
SELECT p.id AS CLIENTID, p.code AS CLIENCODE, p.last_name AS CLIENTNOM, SUM(pi.totalweight) AS POIDSLIVRE, SUM(pi.totalbrut) AS BRUTAPAYER,
SUM(pi.totalnet) AS NETAPAYER,
 
--BONUS EXCEPTIONNEL : id 24
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 24
 AND p2.id = p.id
) AS B_EXCEP,
 
--PRIME PRE-COLLECTE : id 10
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 10
 AND p2.id = p.id
) AS PRECOL,
 
--PRIME INTERESSEMENT : id 11
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 11
 AND p2.id = p.id
) AS INTE,
 
--PRIME FIDELITE : id 23
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 23
 AND p2.id = p.id
) AS FID,
 
--PRIME SOUTIEN : id 9
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 9
 AND p2.id = p.id
) AS SOUT,
 
--PRIME TEH : id 2
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 2
 AND p2.id = p.id
) AS TEH,
 
--PRIME TONNAGE : id 21
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 21
 AND p2.id = p.id
) AS TON,
 
--PRIME RELIQUAT FACTURE : id 34
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 34
 AND p2.id = p.id
) AS RELI,
 
--PRIME SOUTIEN FOURN : id 27
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 27
 AND p2.id = p.id
) AS S_FOUR,
 
--PRIME REMBOURSEMENT TRANSPORT : id 25
(SELECT COALESCE(SUM(pip.value_purchase_invoice_prime),0) FROM purchase_invoice_prime pip
 INNER JOIN view_purchase_invoice pi2 ON pip.purchase_invoice_id = pi2.id
 INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE pip.prime_id = 25
 AND p2.id = p.id
) AS R_TRANS,
 
(SELECT COALESCE(SUM(pit.value_purchase_invoice_tax),0) FROM purchase_invoice_tax pit
INNER JOIN view_purchase_invoice pi2 ON pit.purchase_invoice_id = pi2.id
INNER JOIN planter p2 ON pi2.planter_id = p2.id
 WHERE p2.id = p.id
) AS Tax
 
 
FROM view_purchase_invoice pi
INNER JOIN planter p ON pi.planter_id = p.id
WHERE p.planter_type_id = 2 --type de client
GROUP BY p.id, p.code, p.last_name
ORDER BY p.code ASC

Cette requête fonctionne, même si elle met 10 secondes à s’exécuter ce qui est un peu long.

Mais mon interrogation est la suivante :
Au lieu de créer une sous-requête en dur pour chaque type de prime (en appelant l'id ou le code de la prime), pensez-vous s'il est possible de générer des sous-requêtes dynamiques. Par exemple, dans une boucle. En listant les id de la table prime, et pour chaque id retourné, exécuter dynamiquement une sous-requête.
Je souhaite pouvoir faire cela en Sql sans passer par un langage de programmation.

Si c'est faisable, avez vous une piste ?

Cdlt,

Olivier

**SQLpro** · 15/06/2020, 10h20

Faire des jointures :

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
SELECT p.id AS CLIENTID, p.code AS CLIENCODE, p.last_name AS CLIENTNOM, 
       SUM(pi.totalweight) AS POIDSLIVRE, SUM(pi.totalbrut) AS BRUTAPAYER,
       SUM(pi.totalnet) AS NETAPAYER,
       COALESCE(SUM(pip24.value_purchase_invoice_prime) OVER(), 0) AS B_EXCEP,
       COALESCE(SUM(pip10.value_purchase_invoice_prime) OVER(), 0) AS B_PRECOLLECTE
 
FROM   view_purchase_invoice AS pi
       INNER JOIN planter AS p 
          ON pi.planter_id = p.id
 
--> BONUS EXCEPTIONNEL : id 24
       LEFT OUTER JOIN purchase_invoice_prime AS pip24
          ON p24.id = p.id
       LEFT OUTER JOIN view_purchase_invoice AS pi24 
          ON pip24.purchase_invoice_id = pi24.id AND pip24.prime_id = 24
 
--> PRIME PRE-COLLECTE : id 10
       LEFT OUTER JOIN purchase_invoice_prime AS pip10
          ON p10.id = p.id
       LEFT OUTER JOIN view_purchase_invoice AS pi10 
          ON pip10.purchase_invoice_id = pi210.id AND pip10.prime_id = 10
 
--> ... autres primes ...
 
WHERE  p.planter_type_id = 2 --type de client
GROUP  BY p.id, p.code, p.last_name
ORDER  BY p.code ASC

Cela dit, PostGreSQL est limité en nombre de jointures optimisées du fait de GEQO et comme vous avez des vues qui peut être contiennent des jointures, je pense de toute façon que ce sera pas bon en performances...

En général au delà d'une dizaine de jointures PG n'est pas top... Il n'est pas fait pour des requêtes complexes !

A +

**olivierandré** · 15/06/2020, 14h32

Etant donné que je n'ai pas trouvé de solutions, je vais passer par un langage de programmation pour les boucles (JAVA dans mon cas).

Il doit y avoir des possibilités avec les curseurs Postgresql, mais je ne maîtrise pas.

**olivierandré** · 15/06/2020, 17h49

Tout de même un grand merci à SqlPro, je viens d'apprendre une nouvelle manière de faire les jointures. C'est excellent !

**olivierandré** · 15/06/2020, 18h23

Juste une question SqlPro pour mieux comprendre, quand vous appelez "ON p24.id = p.id", je ne vois pas ou est déclaré p24 dans la requête.
J'ai testé, mais PGADMIn râle me disant que p24 n'est pas déclaré.

Aussi, j'ai essayé de m'approprier cette manière d'écrire la requête.
Voici le code, malheureusement, le deuxième colonne est identique à la première. Une idée ?

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
 
SELECT p.code, 
COALESCE(SUM(pip24.value_purchase_invoice_prime),0) AS BONUSEXCEP,
COALESCE(SUM(pip6.value_purchase_invoice_prime),0) AS PRECOL
 
FROM purchase_invoice pi
INNER JOIN planter p ON pi.planter_id = p.id
 
LEFT JOIN purchase_invoice_prime pip24 ON pi.id = pip24.purchase_invoice_id 
LEFT JOIN prime pri24 ON pip24.prime_id = pri24.id AND pri24.code = 'PR_24'
LEFT JOIN purchase_invoice_prime pip6 ON pi.id = pip6.purchase_invoice_id 
LEFT JOIN prime pri6 ON pip6.prime_id = pri6.id AND pri6.code = 'PR_6'
 
 
WHERE pi.date_invoice >= '2020-04-01 00:00:00'
AND pi.date_invoice  <= '2020-04-30 23:59:59'
 
GROUP BY p.code
ORDER BY p.code ASC
 
;

Capture :

**olivierandré** · 15/06/2020, 19h56

Bon, j'avance doucement, voici une syntaxe qui fonctionne, mais ça ne m'arrange pas de trier sur l'ID (il peut changer), j'aurais préféré trier sur le code de la table prime (en dur).

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
 
SELECT p.code, 
COALESCE(SUM(pip24.value_purchase_invoice_prime),0) AS BONUSEXCEP,
COALESCE(SUM(pip6.value_purchase_invoice_prime),0) AS PRECOL
 
FROM purchase_invoice pi
INNER JOIN planter p ON pi.planter_id = p.id
 
LEFT JOIN purchase_invoice_prime pip24 ON pi.id = pip24.purchase_invoice_id AND pip24.prime_id = '24'
LEFT JOIN purchase_invoice_prime pip6 ON pi.id = pip6.purchase_invoice_id AND pip6.prime_id = '10'
 
WHERE pi.date_invoice >= '2020-04-01 00:00:00'
AND pi.date_invoice  <= '2020-04-30 23:59:59'
 
GROUP BY p.code
ORDER BY p.code ASC
 
;

SQL Postgres - Générer des sous requêtes dans une boucle

Langage SQL

Vue hybride

Discussions similaires

Partager

Partager