Discussion :

[Caplande]

Bonjour,

Je n'arrive pas à savoir s'il est possible à partir d'une requête de créer une union de 2 sous requêtes de cette requête. Et si cette structure est possible quelle doit être son architecture.

Plus précisément:
1) Je dispose d'une requête de départ , soit R1, construite à partir de plusieurs tables jointes.
2) Je construis 2 sous requêtes s'appuyant sur R1 qui permettent d'extraire chacune les mêmes champs CH1,CH2,..CHn.
Les méthodes d'extraction de ces champs étant différentes, il est nécessaire de bâtir deux sous requêtes. Soit SR1 et SR2 ces deux sous requêtes.
3) Je voudrais faire une requête union de SR1 et SR2 pour obtenir le jeu de CH1,CH2,...CHn résultant de leur deux calculs.

Ma question: Si une telle construction est possible, quelle doit être sa structure.

Merci d'avance pour votre éclairage.

Caplande

[escartefigue]

Je ne vois aucune difficulté particulière faites tout simplement SR1 union(all) SR2

[Caplande]

Je ne vois aucune difficulté particulière faites tout simplement SR1 union(all) SR2

Je ne sais pas relier l'union des 2 sous-requêtes à l'unique requête R1 dont toutes les deux dépendent. En effet R1 contient des JOIN et je ne peux la formuler qu'une seule fois alors qu'elle est la 'source' des 2 sous-requêtes.

[aieeeuuuuu]

Bonjour,

En ayant la requête, ce serait plus facile.

Comment est utilisée votre requête R1 ? en clause EXISTS, en jointure ? ...

vous pouvez faire votre UNION entre SR1 et SR2 dans une sous requete, et ajouter R1 ensuite.

[Caplande]

Bonjour,

En ayant la requête, ce serait plus facile.

Comment est utilisée votre requête R1 ? en clause EXISTS, en jointure ? ...

vous pouvez faire votre UNION entre SR1 et SR2 dans une sous requete, et ajouter R1 ensuite.

Je ne voulais insérer requête qui me pose pb car elle est un peu compliquée. Je la joins néanmoins ci-dessous, si vous avez la patience d'y jeter un oeil, merci d'avance pour votre aide:

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
SELECT *
FROM (
	(SELECT pa_id , dc , CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle , IF(pa_id = '1' , om_mtt , IF(pa_id = '3' , om_mtt/2 , 0)) AS mtt
		FROM mySelect1)
	UNION
	(SELECT pa_id , dc , CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle , IF(pa_id = '2' , om_mtt , IF(pa_id = '3' , om_mtt/2 , 0)) AS mtt
		FROM mySelect1)
) AS monSelect2
FROM (
	SELECT 
		YEAR(e.oe_date_ope) AS annee, 
		m.om_dc AS dc, 
		e.oe_na_id AS na_id,
		e.oe_im_id AS im_id,
		c.cp_pa_id AS pa_id,
		m.om_mtt AS om_mtt
	FROM ope_entetes e 
	LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
	LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
	HAVING annee <= 2015
) AS monSelect1
WHERE pa_id <> 3;

[escartefigue]

il faut faire une CTE avec la requete que vous avez nommée "myselect1" et "myselect2" puis faire la requete union qui utilise ces tables temporaires

[escartefigue]

Quelque chose comme ça :

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
 
WITH MYSELECT1 AS
    (SELECT YEAR(e.oe_date_ope) AS annee, 
            m.om_dc AS dc, 
            e.oe_na_id AS na_id,
            e.oe_im_id AS im_id,
            c.cp_pa_id AS pa_id,
            m.om_mtt AS om_mtt
     FROM ope_entetes e 
     LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
     LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
     HAVING annee <= 2015)
     MYSELECT2 AS
    (... à compléter...)
 
SELECT pa_id
      ,dc 
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '1', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect1)
 
UNION
 
SELECT pa_id 
      ,dc
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '2', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect2)

[escartefigue]

Oups pardon, oubliez les CTE, MySQL ne les accepte pas

[Caplande]

il faut faire une CTE avec la requete que vous avez nommée "myselect1" et "myselect2" puis faire la requete union qui utilise ces tables temporaires

CTE??? S'agit-il d'une abréviation ou d'une instruction en bonne et due forme. Toujours est-il que je ne vois pas de doc en ligne qui fasse référence au sujet 'créer une CTE'. Excusez mon ignorance, et je suis preneur de tout lien qui me documenterait sur le sujet et me permettrait d'avancer en utilisant cette fonctionnalité. Je travaille avec MySQL 5.6.12

[escartefigue]

CTE = Common Table Expression, il s'agit d'une table temporaire qui permet de faire des requêtes récursives, mais qui malheureusement n'est pas supportée par MySQL
Désolé, à force de passer d'un topic à l'autre j'avais oublié que votre post concernait MySQL

Vous devez donc procéder autrement (avec des vues ou des select imbriqués) ce qui sera moins performant mais vous n'avez pas le choix

[aieeeuuuuu]

Comme je le disais, il faut donc "sortir" le UNION, le faire en amont, puis faire une jointure sur votre requête principale :

soit quelque chose comme (a tester)

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
 
 
	SELECT 
		pa_id , 
		dc , 
		CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle , 
		IF(pa_id = val , om_mtt , IF(pa_id = '3' , om_mtt/2 , 0)) AS mtt
	FROM (
		SELECT 1 AS val
		UNION ALL SELECT 2
	) as tmp
	CROSS JOIN(
		SELECT 
			YEAR(e.oe_date_ope) AS annee, 
			m.om_dc AS dc, 
			e.oe_na_id AS na_id,
			e.oe_im_id AS im_id,
			c.cp_pa_id AS pa_id,
			m.om_mtt AS om_mtt
		FROM ope_entetes e 
		LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
		LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
		HAVING annee <= 2015
	) AS monSelect1
WHERE pa_id <> 3;

[Caplande]

Merci c'est très astucieux !
Mais ce n'est pas encore tout à fait la solution. En effet, seule la valeur 1 de la variable 'val' est exploitée.
Caplande

Comme je le disais, il faut donc "sortir" le UNION, le faire en amont, puis faire une jointure sur votre requête principale :

soit quelque chose comme (a tester)

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
 
 
	SELECT 
		pa_id , 
		dc , 
		CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle , 
		IF(pa_id = val , om_mtt , IF(pa_id = '3' , om_mtt/2 , 0)) AS mtt
	FROM (
		SELECT 1 AS val
		UNION ALL SELECT 2
	) as tmp
	CROSS JOIN(
		SELECT 
			YEAR(e.oe_date_ope) AS annee, 
			m.om_dc AS dc, 
			e.oe_na_id AS na_id,
			e.oe_im_id AS im_id,
			c.cp_pa_id AS pa_id,
			m.om_mtt AS om_mtt
		FROM ope_entetes e 
		LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
		LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
		HAVING annee <= 2015
	) AS monSelect1
WHERE pa_id <> 3;

[StringBuilder]

Sinon, quand on ne peux pas faire une CTE, on peut se baser sur une vue...

MySQL accepte les vues quand même ?

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
 
WITH MYSELECT1 AS
    (SELECT YEAR(e.oe_date_ope) AS annee, 
            m.om_dc AS dc, 
            e.oe_na_id AS na_id,
            e.oe_im_id AS im_id,
            c.cp_pa_id AS pa_id,
            m.om_mtt AS om_mtt
     FROM ope_entetes e 
     LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
     LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
     HAVING annee <= 2015)
     MYSELECT2 AS
    (... à compléter...)
 
SELECT pa_id
      ,dc 
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '1', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect1)
 
UNION
 
SELECT pa_id 
      ,dc
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '2', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect2)

Devient donc :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE MYSELECT1 AS
    SELECT YEAR(e.oe_date_ope) AS annee, 
            m.om_dc AS dc, 
            e.oe_na_id AS na_id,
            e.oe_im_id AS im_id,
            c.cp_pa_id AS pa_id,
            m.om_mtt AS om_mtt
     FROM ope_entetes e 
     LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
     LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
     HAVING annee <= 2015

et

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT pa_id
      ,dc 
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '1', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect1)
UNION
SELECT pa_id 
      ,dc
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '2', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect2)

[Caplande]

Ok ,merci pour votre long développement. Je n'ai pas tout compris dans votre réponse, je dois d'abord m'initier à la notion de vue. Je vous donnerai le résultat dès mise en oeuvre.

Sinon, quand on ne peux pas faire une CTE, on peut se baser sur une vue...

MySQL accepte les vues quand même ?

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
 
WITH MYSELECT1 AS
    (SELECT YEAR(e.oe_date_ope) AS annee, 
            m.om_dc AS dc, 
            e.oe_na_id AS na_id,
            e.oe_im_id AS im_id,
            c.cp_pa_id AS pa_id,
            m.om_mtt AS om_mtt
     FROM ope_entetes e 
     LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
     LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
     HAVING annee <= 2015)
     MYSELECT2 AS
    (... à compléter...)
 
SELECT pa_id
      ,dc 
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '1', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect1)
 
UNION
 
SELECT pa_id 
      ,dc
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '2', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect2)

Devient donc :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE MYSELECT1 AS
    SELECT YEAR(e.oe_date_ope) AS annee, 
            m.om_dc AS dc, 
            e.oe_na_id AS na_id,
            e.oe_im_id AS im_id,
            c.cp_pa_id AS pa_id,
            m.om_mtt AS om_mtt
     FROM ope_entetes e 
     LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
     LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
     HAVING annee <= 2015

et

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT pa_id
      ,dc 
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '1', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect1)
UNION
SELECT pa_id 
      ,dc
      ,CONCAT(IF(na_id = 4, 'PRT-', 'HPRT-'),'2015','-',im_id,'-',pa_id) AS cle
      ,IF(pa_id = '2', om_mtt, IF(pa_id = '3', om_mtt/2 , 0)) AS mtt
FROM mySelect2)

[Caplande]

La solution 'création de vue' suppose une succession d'étapes. Je cherche à tout concentrer dans une seule et même requête. Est ce possible dans mon cas de figure?

Ok ,merci pour votre long développement. Je n'ai pas tout compris dans votre réponse, je dois d'abord m'initier à la notion de vue. Je vous donnerai le résultat dès mise en oeuvre.

[StringBuilder]

Créer une vue pour du "one shot", c'est clairement pas terrible.

Je dis pas que ça marche pas, mais ce sera clairement pas la meilleure solution.

A la limite, autant passer par une table temporaire, qui a une réalité physique, et donc des performances accrues.

Mais à nouveau, il faudra une succession de requêtes.

Qu'est-ce qui vous empêche de créer une bonne fois pour toute votre vue ?

[Caplande]

En effet, rien ne m'empêche d'avoir recours à cette solution puisqu'il s'agit d'une requête incluse dans une méthode d'une application Symfony. Mais à saucissonner la tâche, je crois qu'il est au moins aussi performant de faire une requête plus simple dont je traiterai le résultat au sein de la méthode Symfony par une boucle PHP.
Mes compétences en SQL sont limitées et ma curiosité est maintenant complètement satisfaite. Je voulais aussi me rendre compte si, au moyen d'une requête unique, à partir d'un enregistrement d'une table de base (ope_entetes), il est possible de créer plusieurs enregistrement en sortie. Selon les essais faits, la réponse semble être positive. Cette solution n'est pas la plus performante, soit, je vais donc adopter l'option traitement complémentaire par PHP d'une requête simple.
Grand merci pour votre aide opiniâtre et patiente, elle m'a vraiment permis d'avancer.

Caplande

Créer une vue pour du "one shot", c'est clairement pas terrible.

Je dis pas que ça marche pas, mais ce sera clairement pas la meilleure solution.

A la limite, autant passer par une table temporaire, qui a une réalité physique, et donc des performances accrues.

Mais à nouveau, il faudra une succession de requêtes.

Qu'est-ce qui vous empêche de créer une bonne fois pour toute votre vue ?

[StringBuilder]

Bonjour,

Tant que le traitement consiste à retrouver de la données (appliquer des filtres, et travailler sur des ensembles) alors c'est au SGBD et uniquement à lui de le faire, aussi complexe soit le traitement.

Le PHP ne doit servir qu'à mettre en forme les données en sortie. C'est une erreur de penser que c'est plus simple/plus rapide de faire un filtre avec du PHP.
=> Vous forcez le SGBD à retourner bien plus de données que nécessaires, saturant les IO en lectures inutile, mais aussi et surtout, l'interface réseau
=> Vous forcez PHP à traiter séquentiellement (donc sans possibilité de paralléliser) de grands volumes de données, ce pour quoi il n'est pas fait

Ainsi, vous réduisez drastiquement les performances de l'application.

Si l'utilisation d'une vue est la solution pour avoir un code SQL clair, alors vous ne devez pas hésiter une seconde ! Vous créez la vue une bonne fois pour toutes.

Vous y gagnerez en :
- Performance (même si MySQL fait figure de mauvais élève, il sera toujours plus performant pour gérer des données que PHP)
- Maintenabilité (besoin de modifier que du SQL pour changer la sélection)
- Lisibilité du code (pas besoin de connaître SQL et PHP pour comprendre le filtre)

[Caplande]

Merci pour cette importante remarque que je vais , bien sûr, prendre en compte. Je la prends d'autant plus volontiers que je viens (enfin!!!) de mettre au point la requête unique qui répond à mon attente. A toutes fins utiles, je la joins ci-dessous, :

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
SELECT 
	cle , SUM(mtt) AS montant
FROM (	
	SELECT
		pa_id,
		IF(pa_id = 1 OR pa_id = 2,IF(dc = 'C' , om_mtt , -om_mtt),0) AS mtt,
		CONCAT(IF(na_id = 4 , 'PRT-' , 'HPRT-') , im_id , '-' , pa_id) AS cle
	FROM (
		SELECT
			YEAR(e.oe_date_ope) AS annee, 
			m.om_dc AS dc, 
			e.oe_na_id AS na_id,
			e.oe_im_id AS im_id,
			c.cp_pa_id AS pa_id,
			m.om_mtt AS om_mtt
		FROM ope_entetes e 
		LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
		LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
		HAVING annee <= 2009
	) AS temp1
	UNION ALL
	SELECT
		pa_id,
		IF(pa_id = 3,IF(dc = 'C' , om_mtt/2 , -om_mtt/2),0) AS mtt,
		CONCAT(IF(na_id = 4 , 'PRT-' , 'HPRT-') , im_id , '-1') AS cle
	FROM (
		SELECT
			YEAR(e.oe_date_ope) AS annee, 
			m.om_dc AS dc, 
			e.oe_na_id AS na_id,
			e.oe_im_id AS im_id,
			c.cp_pa_id AS pa_id,
			m.om_mtt AS om_mtt
		FROM ope_entetes e 
		LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
		LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
		HAVING annee <= 2009
	) AS temp2
	UNION ALL
	SELECT
		pa_id,
		IF(pa_id = 3,IF(dc = 'C' , om_mtt/2 , -om_mtt/2),0) AS mtt,
		CONCAT(IF(na_id = 4 , 'PRT-' , 'HPRT-') , im_id , '-2') AS cle
	FROM (
		SELECT
			YEAR(e.oe_date_ope) AS annee, 
			m.om_dc AS dc, 
			e.oe_na_id AS na_id,
			e.oe_im_id AS im_id,
			c.cp_pa_id AS pa_id,
			m.om_mtt AS om_mtt
		FROM ope_entetes e 
		LEFT JOIN ope_mtts m ON e.oe_id = m.om_oe_id 
		LEFT JOIN comptes c ON m.om_cp_id = c.cp_id 
		HAVING annee <= 2009
	) AS temp3
) AS temp4
GROUP BY cle
ORDER BY cle;

Bonjour,

Tant que le traitement consiste à retrouver de la données (appliquer des filtres, et travailler sur des ensembles) alors c'est au SGBD et uniquement à lui de le faire, aussi complexe soit le traitement.

Le PHP ne doit servir qu'à mettre en forme les données en sortie. C'est une erreur de penser que c'est plus simple/plus rapide de faire un filtre avec du PHP.
=> Vous forcez le SGBD à retourner bien plus de données que nécessaires, saturant les IO en lectures inutile, mais aussi et surtout, l'interface réseau
=> Vous forcez PHP à traiter séquentiellement (donc sans possibilité de paralléliser) de grands volumes de données, ce pour quoi il n'est pas fait

Ainsi, vous réduisez drastiquement les performances de l'application.

Si l'utilisation d'une vue est la solution pour avoir un code SQL clair, alors vous ne devez pas hésiter une seconde ! Vous créez la vue une bonne fois pour toutes.

Vous y gagnerez en :
- Performance (même si MySQL fait figure de mauvais élève, il sera toujours plus performant pour gérer des données que PHP)
- Maintenabilité (besoin de modifier que du SQL pour changer la sélection)
- Lisibilité du code (pas besoin de connaître SQL et PHP pour comprendre le filtre)