SELECT EXISTS et sous-requête correlée

**Catab** · 06/07/2021, 17h09

Bonjour les internautes,

Je dispose d'une table, appelons là Subjects pour laquelle je voudrais vérifier l'existence d'une vente dans une table que nous appellerons Sales

Voici le code de la création de mon exemple :

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
CREATE TABLE IF NOT EXISTS Subjects
(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	subject VARCHAR(50),
	option1 NUMERIC,
	option2 NUMERIC,
	option3 NUMERIC,
	PRIMARY KEY (ID)
);
 
CREATE TABLE IF NOT EXISTS Sales
(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	subject VARCHAR(50),
	sale NUMERIC,
	dateSale DATE,
	PRIMARY KEY (ID)
);

Populons ces tables avec quelques données :

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
INSERT INTO Subjects
VALUES
(NULL, 'A', 1, 0, 0),
(NULL, 'B', 2, 0, 0),
(NULL, 'C', 0, 10, 0),
(NULL, 'D', 0, 20, 0),
(NULL, 'E', 0, 30, 0),
(NULL, 'F', 0, 40, 0),
(NULL, 'G', 0, 0, 100),
(NULL, 'H', 0, 0, 200),
(NULL, 'I', 0, 0, 300),
(NULL, 'J', 1, 10, 0),
(NULL, 'K', 1, 0, 100),
(NULL, 'L', 0, 10, 100);
 
INSERT INTO Sales
VALUES
(NULL, 'A', 25, '2019-02-12 00:00:00'),
(NULL, 'A', 10, '2020-04-20 00:00:00'),
(NULL, 'C', 15, '2019-03-15 00:00:00'),
(NULL, 'C', 42, '2019-05-17 00:00:00'),
(NULL, 'C', 75, '2019-10-10 00:00:00'),
(NULL, 'D', 12, '2020-06-07 00:00:00'),
(NULL, 'E', 29, '2019-02-12 00:00:00'),
(NULL, 'F', 77, '2020-05-14 00:00:00'),
(NULL, 'F', 88, '2020-11-12 00:00:00'),
(NULL, 'K', 99, '2018-09-30 00:00:00');

Mon but est de savoir, pour chaque Subject, quelle est l'option qu'on lui a choisis, et savoir si oui ou non on l'a vendu à partir d'une date donnée.
Ensuite, j'utilise des fonctions d'aggrégation pour compter le nombre de Subjets de chaque option et son statut de vente.

Le résultat doit ressemble à ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
+---------+-------+---------+
| choice  | saled | nbr_sub |
+---------+-------+---------+
| option1 |     1 |       1 |
| option1 |     0 |       1 |
| option2 |     0 |       2 |
| option2 |     1 |       2 |
| option3 |     0 |       3 |
| unknown |     0 |       3 |
+---------+-------+---------+

Les unknown désignent les Subjects avec plusieurs options complétées.

Pour arriver à ce résultat, j'ai utilisé la requête suivante :

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
SELECT
s.opt,
s.saled,
COUNT(s.id) AS nbr_sub
 
FROM
(
	SELECT
	su.id,
	su.subject,
	CASE
		WHEN su.option1 <> 0 AND su.option2 = 0 AND su.option3 = 0 THEN 'option1'
		WHEN su.option1 = 0 AND su.option2 <> 0 AND su.option3 = 0 THEN 'option2'
		WHEN su.option1 = 0 AND su.option2 = 0 AND su.option3 <> 0 THEN 'option3'
		ELSE 'unknown'
	END AS opt,
	EXISTS
	(
		SELECT
		1
 
		FROM
		Sales AS sa
 
		WHERE
		sa.subject = su.subject
		AND YEAR(sa.dateSale) > 2019
	) AS saled
 
	FROM
	Subjects AS su
) AS s
 
GROUP BY
s.opt,
s.saled

Celle-ci fonctionne à merveille sur serveur MySQL. Mais une fois adaptée à ma DB de production DB2, ça coince.
Le soucis semble venir de la sous-requête du EXISTS dans le SELECT. En effet, j'y fait référence à une table de la requête parent. Cependant, le FROM se situe après le SELECT et je suspecte que ce soit la cause de mon malheur. Parce que la même requête avec le EXISTS dans la clause WHERE me donne un résultat.
Quelque chose comme ceci :

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
SELECT
s.opt,
-- s.saled,
COUNT(s.id) AS nbr_sub
 
FROM
(
	SELECT
	su.id,
	su.subject,
	CASE
		WHEN su.option1 <> 0 AND su.option2 = 0 AND su.option3 = 0 THEN 'option1'
		WHEN su.option1 = 0 AND su.option2 <> 0 AND su.option3 = 0 THEN 'option2'
		WHEN su.option1 = 0 AND su.option2 = 0 AND su.option3 <> 0 THEN 'option3'
		ELSE 'unknown'
	END AS opt
 
	FROM
	Subjects AS su
) AS s
 
WHERE
EXISTS
(
	SELECT
	1
 
	FROM
	Sales AS sa
 
	WHERE
	sa.subject = s.subject
	AND YEAR(sa.dateSale) > 2019
)
 
GROUP BY
s.opt

Pouvez-vous m'aider à résoudre ce casse-tête ?

Je vous remercie.

**skuatamad** · 08/07/2021, 14h46

Je ne connais pas cette syntaxe avec EXISTS dans le SELECT, j'imagine que c'est une syntaxe spécifique mysql, ou en tout cas que BD2 ne connais pas.

Je rempacerais cette partie par :
Un max pour ne ramener q'une ligne dans la sous-requête scalaire ce qui revient à faire une sorte de test d'existence.
Un COALESCE pour transformer les NULL renvoyés par la non existence en 0.

Code :

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

1
2
3
4
5
coalesce(SELECT max(1)
          FROM Sales AS sa
         WHERE sa.subject = su.subject
           AND YEAR(sa.dateSale) > 2019
	, 0) AS saled

**vazymimil** · 08/07/2021, 16h19

Bonjour,

Il y a une subtilité (voire un bug) dans l'utilisation du prédicat EXISTS dans les SELECT en DB2 comme BOOLEAN, l'optimisation fait que ça retourne une valeur nulle dans une colonne non nulle et le client qui ne sais pas quoi en faire le plus souvent considère TRUE.
Il faut l'entourer d'une CASE expression pour que ça fonctionne bien,

Code :

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

case when exists(...) then true else false end

Voir https://dbfiddle.uk/?rdbms=db2_11.1&...458e4c0c0f624e

**Catab** · 13/07/2021, 13h36

Bonjour,

Merci à vous deux pour vos interventions.

vazymimil, j'ai tenté d'encapsuler mon EXISTS par un CASE mais rien n'y fait. J'ai toujours un message d'erreur.
J'ai d'ailleurs essayé une version ultra simplifiée d'un SELECT EXISTS... (avec et sans le CASE) sur ma DB de production. Pas moyen d'obtenir un résultat.

J'ai trouvé une autre solution, un peu moche mais qui fait le travail. Il s'agit de joindre deux requêtes par un UNION. La première avec les Subjects vendus, avec un EXISTS dans le WHERE et où je force Saled à 1, suivi de la seconde, avec les Subjects non vendus, avec un NOT EXISTS où je force Saled à 0.
Comme ceci en somme :

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
SELECT
s.opt,
1 AS saled,
COUNT(s.id) AS nbr_sub
 
FROM
(
	SELECT
	su.id,
	su.subject,
	CASE
		WHEN su.option1 <> 0 AND su.option2 = 0 AND su.option3 = 0 THEN 'option1'
		WHEN su.option1 = 0 AND su.option2 <> 0 AND su.option3 = 0 THEN 'option2'
		WHEN su.option1 = 0 AND su.option2 = 0 AND su.option3 <> 0 THEN 'option3'
		ELSE 'unknown'
	END AS opt
 
	FROM
	Subjects AS su
) AS s
 
WHERE
EXISTS
(
	SELECT
	1
 
	FROM
	Sales AS sa
 
	WHERE
	sa.subject = s.subject
	AND YEAR(sa.dateSale) > 2019
)
 
GROUP BY
s.opt
 
UNION
 
SELECT
s.opt,
0 AS saled,
COUNT(s.id) AS nbr_sub
 
FROM
(
	SELECT
	su.id,
	su.subject,
	CASE
		WHEN su.option1 <> 0 AND su.option2 = 0 AND su.option3 = 0 THEN 'option1'
		WHEN su.option1 = 0 AND su.option2 <> 0 AND su.option3 = 0 THEN 'option2'
		WHEN su.option1 = 0 AND su.option2 = 0 AND su.option3 <> 0 THEN 'option3'
		ELSE 'unknown'
	END AS opt
 
	FROM
	Subjects AS su
) AS s
 
WHERE
NOT EXISTS
(
	SELECT
	1
 
	FROM
	Sales AS sa
 
	WHERE
	sa.subject = s.subject
	AND YEAR(sa.dateSale) > 2019
)
 
GROUP BY
s.opt

Et ça fait le travail. J'aimerais avoir le temps d'investiguer plus la question mais ce n'est pas le cas.
Je vous remercie pour votre aide.

Catab

SELECT EXISTS et sous-requête correlée

DB2

Discussions similaires

Partager

Partager