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

Version imprimable

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:

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

Faire des jointures :

Code:

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 +

15/06/2020, 14h32
olivierandré

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.
15/06/2020, 17h49
olivierandré

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

1 pièce(s) jointe(s)

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:

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 :

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:

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
 
;

Bon, on va dire que c'est surement une requête de cochon .... mais voici une avancée :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
WITH PR AS (SELECT id AS A, code  AS B FROM prime)  
 
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 = (SELECT A FROM PR WHERE PR.B = 'PR_24')
LEFT JOIN purchase_invoice_prime pip6 ON pi.id = pip6.purchase_invoice_id AND pip6.prime_id = (SELECT A FROM PR WHERE PR.B = '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
 
;

bonjour,

vu que vos sous requetes scalaire font toutes les memes jointure, vous pourriez plutot passer par un pivot par exemple comme ceci (à compléter)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
 
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(CASE WHEN pip.prime = 24 THEN pip.value_purchase_invoice_prime END), 0 ) AS B_EXCEPTIONNEL,
	COALESCE(SUM(CASE WHEN pip.prime = 10 THEN pip.value_purchase_invoice_prime END), 0 ) AS PRECOLLECTE,
	COALESCE(SUM(CASE WHEN pip.prime = 11 THEN pip.value_purchase_invoice_prime END), 0 ) AS INTERESSEMENT,
	...
 
FROM view_purchase_invoice pi
INNER JOIN planter p ON pi.planter_id = p.id
 
LEFT JOIN 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
 ON p2.id = p.id
 AND pip.prime_id IN (24, 10,11,...)
 
WHERE p.planter_type_id = 2 --type de client
GROUP BY p.id, p.code, p.last_name
ORDER BY p.code ASC

Vous pourriez même surement dans ce cas vous passer d'une des deux jointures sur la table planter...
Cela devrait nettement améliorer les performances

20/06/2020, 15h30
olivierandré

Merci aieeeuuuuu pour cette proposition.