Faire intervenir un groupe inexistant [Résolu]

[Madfrix]

Bonjour,

dans une table j'ai un champ nommé symbole ainsi qu'un champ nommé sens (valant 0, 1 ou 2). Pour chaque symbole, je peux avoir 1, 2 ou 3 sens. Je cherche faire une requête de groupement me retournant dans n'importe quelles conditions la somme des sens par symbole.

Code sql :

1
2
3
4
 
SELECT symbole, sens, SUM(quantite)
FROM maTable
GROUP BY symbole, sens

me retourne (par exemple) :

symbole1 0 1000
symbole1 1 200

Moi je souhaiterais avoir cette forme de résultat :

symbole1 0 1000
symbole1 1 200
symbole1 2 0

bref avoir quoi qu'il arrive les 3 sens avec une somme des quantité valant éventuellement 0. Comment faire ceci ?

Merci

[aieeeuuuuu]

Bonjour

Code sql :

1
2
3
4
5
6
7
8
9
10
 
SELECT symbole, ListeSens.sens, COALESCE(SUM(quantite), 0)
FROM maTable
LEFT OUTER JOIN (
    SELECT 0 AS sens
    UNION ALL SELECT 1 AS sens
    UNION ALL SELECT 2 AS sens
) AS ListeSens
ON MaTable.sens = ListeSens.sens
GROUP BY symbole, sens

[Madfrix]

Bonjour et merci de ta réponse. Je me doutais bien qu'il fallait utiliser COALESCE mais je bloquais sur la jointure, j'essayais de tout faire d'un bloc...

Par contre, ca me retourne le même résultat au final

J'ai testé :

Code sql :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT symbole, derivee.sens, COALESCE(SUM(quantite), 0) AS quantite
FROM historique
LEFT OUTER JOIN (
       SELECT 0 AS sens
       UNION ALL SELECT 1 AS sens
       UNION ALL SELECT 2 AS sens
       ) AS derivee
ON derivee.sens = historique.sens
WHERE DATE_FORMAT( date_heure, "%Y-%m-%d" ) = DATE_FORMAT( NOW( ) , "%Y-%m-%d" )
GROUP BY symbole, sens

puis en testant avec un RIGHT OUTER JOIN qui me semblait plus logique mais sans résultat...

[aieeeuuuuu]

Envoyé par Madfrix

puis en testant avec un RIGHT OUTER JOIN qui me semblait plus logique

Tout a fait, il faut un RIGHT OUTER JOIN, une erreur de ma part

J'ai testé :

Code sql :

puis en testant avec un RIGHT OUTER JOIN qui me semblait plus logique mais sans résultat...

D'ou sort cette colonne date_heure ?
Votre RIGHT OUTER JOIN vous donnera un NULL pour date_heure quand il n'y aura aucune correspondance dans la table historique, et donc votre condition dans la clause WHERE ne pourra pas être vérifiée. Ajoutez OR date_heure IS NULL :

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT symbole, derivee.sens, COALESCE(SUM(quantite), 0) AS quantite
FROM historique
LEFT OUTER JOIN (
       SELECT 0 AS sens
       UNION ALL SELECT 1 AS sens
       UNION ALL SELECT 2 AS sens
       ) AS derivee
ON derivee.sens = historique.sens
WHERE DATE_FORMAT( date_heure, "%Y-%m-%d" ) = DATE_FORMAT( NOW( ) , "%Y-%m-%d" ) 
OR date_heure IS NULL
GROUP BY symbole, sens

[Madfrix]

effectivement, en testant le IS NULL, cela semble beaucoup plus logique merci. Par contre, toujours les mêmes résultats...

Et chose étrange avec le LEFT j'ai un temps de requête de 0.5s, avec le RIGHT et le IS NULL j'obtiens 7,5s !

J'ai une course à faire je reviendrai plus tard.

Merci de votre implication

[aieeeuuuuu]

en effet, je me suis basé sur votre exemple avec un seul symbole, mais des qu'il y en as plusieurs...

il faut donc faire le produit cartésien de vos différents symboles, avec vos différents sens, et ensuite une jointure, pour n'oublier personne

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SELECT 
  symboles.symbole, 
  derivee.sens, 
  COALESCE(SUM(Historique.quantite), 0) AS quantite
FROM (
  SELECT DISTINCT symbole
  FROM historique
) AS Symbols
CROSS JOIN(
       SELECT 0 AS sens
       UNION ALL SELECT 1 AS sens
       UNION ALL SELECT 2 AS sens
       ) AS derivee
LEFT OUTER JOIN Historique 
  ON Historique.symbole = Symbols.symbole 
  AND Historique.sens = derivee.sens
WHERE DATE_FORMAT( date_heure, "%Y-%m-%d" ) = DATE_FORMAT( NOW( ) , "%Y-%m-%d" ) 
OR date_heure IS NULL
GROUP BY symbole, sens

Pour faire propre et bien, ces pseudo tables devraient être de vraies tables :
Symboles(IDSymbole, nomSymbole)
Sens(IDSens, libelleSens)
Historique(IDSymbole, IDSens, Dateheure)

[Madfrix]

Merci pour ta réponse

Voilà ce que j'ai essayé de mon côté :

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT DISTINCT derivee2.symbole, derivee1.sens, COALESCE(SUM(derivee3.quantite), 0) AS quantite
FROM (
   SELECT symbole, sens FROM insertions, 
   (
   SELECT 0 AS sens
   UNION ALL SELECT 1 AS sens
   UNION ALL SELECT 2 AS sens
	) AS a
	) AS derivee1
INNER JOIN (
	SELECT DISTINCT symbole, sens, quantite FROM historique 
	WHERE DATE_FORMAT(date_heure, "%Y-%m-%d") = DATE_FORMAT(NOW() , "%Y-%m-%d")	
) AS derivee2
LEFT JOIN (
	SELECT DISTINCT symbole, sens, quantite FROM historique 
	WHERE DATE_FORMAT(date_heure, "%Y-%m-%d") = DATE_FORMAT(NOW() , "%Y-%m-%d")
) AS derivee3
ON derivee1.symbole = derivee2.symbole
AND derivee1.sens = derivee3.sens
GROUP BY derivee2.symbole, derivee1.sens

j'obtiens bien les bon symboles, les 3 sens mais les sommes des quantités ne sont pas bonnes...

Je continue à rechercher

[Madfrix]

Héhé trouvé, il manquait une autre condition de jointure

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
SELECT DISTINCT derivee2.symbole, derivee1.sens, COALESCE(SUM(derivee3.quantite), 0) AS quantite 
FROM ( 
   SELECT symbole, sens FROM insertions, 
   (
   SELECT 0 AS sens
   UNION ALL SELECT 1 AS sens
   UNION ALL SELECT 2 AS sens
	) AS a
	) AS derivee1
INNER JOIN (
	SELECT DISTINCT symbole FROM historique 
	WHERE DATE_FORMAT(date_heure, "%Y-%m-%d") = DATE_FORMAT(NOW() , "%Y-%m-%d")	
) AS derivee2
LEFT JOIN (
	SELECT DISTINCT symbole, sens, quantite FROM historique 
	WHERE DATE_FORMAT(date_heure, "%Y-%m-%d") = DATE_FORMAT(NOW() , "%Y-%m-%d")
) AS derivee3
ON derivee1.symbole = derivee2.symbole
AND derivee1.sens = derivee3.sens
AND derivee2.symbole = derivee3.symbole
GROUP BY derivee2.symbole, derivee1.sens

(suis pas contre une optimisation ^^)

[CinePhil]

Comme a dit aieeeuuuuu, une table des sens aurait été préférable.
Les valeurs textuelles s'écrivent entre apostrophes et pas entre guillemets.

Comme tu veux tous les sens, il faut partir de la table des sens (ou sa construction par les requête UNION) et faire un LEFT JOIN vers la table historique.
Et comme tu as une condition sur une colonne de la table historique, il faut mettre celle-ci dans la condition de jointure sinon c'est équivalent à un INNER JOIN, comme je l'explique dans mon blog.

Est-ce que cette requête plus simple ne donnerait pas le bon résultat par hasard ?

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT h.symbole, tmp.sens, COALESCE(SUM(h.quantite), 0) AS quantite
FROM
(
  SELECT 0 AS sens
  UNION ALL SELECT 1 AS sens
  UNION ALL SELECT 2 AS sens
) tmp
LEFT OUTER JOIN historique h 
  ON h.sens = tmp.sens
  AND DATE_FORMAT(h.date_heure, '%Y-%m-%d') = DATE_FORMAT(CURRENT_DATE , '%Y-%m-%d')
GROUP BY h.symbole, tmp.sens

[Madfrix]

Bonjour CinePhil et merci pour ces explications. Ta requête me donne les bons résultats excepté que je n'ai que les sens significatifs (>0) je n'ai pas les sens dont la quantité n'est pas > 0.

Autre chose, j'exécute cette requête en 7s environ contre 1s pour la mienne (malgré les apparences !). Je pense, sans en être sur, que la condition de jointure sur la date fait ramer au possible. C'est pour cette raison que j'avais essayé de constituer des groupes "filtrés" sur les heures avant d'effectuer les diverses jointures.
Pour infos, voici ma dernière mouture un poil modifié afin de ne sélectionner que les symboles dont insertions.code != NULL :

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SELECT DISTINCT derivee1.symbole AS symbole, derivee1.sens AS sens, COALESCE(SUM(derivee3.quantite), 0) AS quantite 
FROM ( 
   SELECT symbole, sens FROM insertions, 
   (
   SELECT 0 AS sens
   UNION ALL SELECT 1 AS sens
   UNION ALL SELECT 2 AS sens
    ) AS a
   WHERE code IS NOT NULL
   ) AS derivee1
INNER JOIN (
   SELECT DISTINCT symbole FROM historique 
   WHERE DATE_FORMAT(date_heure, "%Y-%m-%d") = DATE_FORMAT(NOW() , "%Y-%m-%d")	
	) AS derivee2
LEFT JOIN (
   SELECT DISTINCT symbole, sens, quantite FROM historique 
   WHERE DATE_FORMAT(date_heure, "%Y-%m-%d") = DATE_FORMAT(NOW() , "%Y-%m-%d")
) AS derivee3
ON derivee1.symbole = derivee2.symbole
AND derivee1.sens = derivee3.sens
AND derivee1.symbole = derivee3.symbole
GROUP BY derivee1.symbole, derivee1.sens

PS: pour info, ma table insertions fait et fera toujours 20 lignes donc le temps de calcul du produit cartésien est négligeable. Quant à ma table historique, elle fait actuellement 450k lignes et augmente de 20k lignes par jour

Envoyé par CinePhil

Et comme tu as une condition sur une colonne de la table historique, il faut mettre celle-ci dans la condition de jointure sinon c'est équivalent à un INNER JOIN

par contre ici, je n'ai pas compris ce que tu veux dire. En l'état, si je modifie mon LEFT par INNER, je tombe dans le cas de figure de ta requête à savoir que les sens inexistants sur un jour et un symbole n'apparaissent pas.

Merci de ton implication


EDIT: et quand aux simples quotes, est ce la norme ? J'avoue qu'avec mon code PHP les doubles quotes me plaisent mieux sinon faut que je backquote tout

[CinePhil]

La colonne date_heure est-elle de type datetime ou timestamp ?
Si oui, pourquoi t'embêter avec le DATE_FORMAT ?
DATE_FORMAT empêche probablement l'utilisation d'un index. Cette colonne est-elle d'ailleurs indexée ?

DISTINCT + GROUP BY, ça fait double emploi ! À mon avis, le DISTINCT est inutile.

Je ne sais pas si c'est parce que j'ai les neurones engourdis par la maladie mais j'ai beau relire ma requête, je ne vois pas pourquoi elle ne te donne pas le bon résultat.

On peut avoir la structure de la table historique et un petit jeu de données pour tester ?

[Madfrix]

Bonjour, voilà mes 2 tables ainsi qu'un petit jeu de test modifié pour l'occasion :

Code sql :

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
 
CREATE TABLE IF NOT EXISTS `insertions` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `symbole` varchar(10) NOT NULL,
  `code` smallint(6) DEFAULT NULL,
  `derniere_insertion` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
 
INSERT INTO `insertions` (`id`, `symbole`, `code`, `derniere_insertion`) VALUES
(1, 'ubi', 297, '2011-01-09 17:35:01'),
(2, 'teo', 56, '2011-01-09 17:35:02'),
(3, 'tch', NULL, '2011-01-09 17:35:38'),
(4, 'tam', NULL, '2011-01-06 17:35:02'),
(5, 'sk', NULL, '2011-01-06 17:35:01'),
(6, 'siph', NULL, '2011-01-06 17:35:02'),
(7, 'rod', NULL, '2011-01-06 17:28:57'),
(8, 'pony', NULL, '2011-01-06 17:37:37'),
(9, 'oxi', NULL, '2011-01-06 17:35:02'),
(10, 'kn', NULL, '2011-01-06 17:38:15'),
(11, 'jxr', NULL, '2011-01-06 17:35:02'),
(12, 'gecp', NULL, '2011-01-06 17:24:31'),
(13, 'eur', NULL, '2011-01-06 17:03:25'),
(14, 'cri', NULL, '2011-01-06 17:35:02'),
(15, 'cei', NULL, '2011-01-06 17:36:49'),
(16, 'bvd', NULL, '2011-01-06 17:35:42'),
(17, 'bna', NULL, '2011-01-06 17:35:02'),
(18, 'alaup', NULL, '2011-01-06 17:29:20'),
(19, 'va', NULL, '2011-01-06 17:21:31'),
(20, 'cib', NULL, '2011-01-06 17:35:02');
 
CREATE TABLE IF NOT EXISTS `historique` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date_heure` datetime NOT NULL,
  `cours` decimal(7,3) UNSIGNED NOT NULL,
  `quantite` mediumint(5) UNSIGNED NOT NULL,
  `symbole` varchar(10) CHARACTER SET utf8 NOT NULL,
  `sens` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=572053 ;
 
INSERT INTO `boursier`.`historique` (`id`, `date_heure`, `cours`, `quantite`, `symbole`, `sens`) VALUES (NULL, '2011-01-09 10:33:09', '2', '1000', 'ubi', '1');
INSERT INTO `boursier`.`historique` (`id`, `date_heure`, `cours`, `quantite`, `symbole`, `sens`) VALUES (NULL, '2011-01-09 10:33:09', '2.2', '10000', 'ubi', '2');
INSERT INTO `boursier`.`historique` (`id`, `date_heure`, `cours`, `quantite`, `symbole`, `sens`) VALUES (NULL, '2011-01-09 10:33:09', '2', '200', 'teo', '0');
INSERT INTO `boursier`.`historique` (`id`, `date_heure`, `cours`, `quantite`, `symbole`, `sens`) VALUES (NULL, '2011-01-09 10:33:09', '2.8', '50', 'va', '1');

PS: ne pas faire attention à ma structure de table
PS2: ma clé de jointure étant en varchar(10) j'ai essayé de mettre un index dessus mais la requête était beaucoup plus lente ensuite

[aieeeuuuuu]

Envoyé par CinePhil

Je ne sais pas si c'est parce que j'ai les neurones engourdis par la maladie mais j'ai beau relire ma requête, je ne vois pas pourquoi elle ne te donne pas le bon résultat.

Tu as fait la même erreur que moi dans la première requete
Il lui faut tous les symboles pour tous les sens. Donc il faut un produit cartésien, sinon si un symbole n'a pas tous les sens, mais qu'un autre symbole a le sens en question,un LEFT OUTER JOIN renverra une seule ligne, au lieu de deux...

Envoyé par Madfrix

PS2: ma clé de jointure étant en varchar(10) j'ai essayé de mettre un index dessus mais la requête était beaucoup plus lente ensuite

Quel index as-tu placé ?

Aussi, as-tu essayé la dernière requete que je t'ai envoyé ? qu'est ce que ca donne ?

[Madfrix]

Bonjour aieeeuuuuu

#2006 - MySQL server has gone away

pour l'éxécution de ta requête. Quand à l'index, correction, cela me donne quasiment le même temps de traitement (1.1s contre 1,15s sans). J'ai utilisé un index "INDEX"

[CinePhil]

Essaie ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT i.symbole, tmp.sens, COALESCE(SUM(h.quantite), 0) AS quantite
FROM historique h
RIGHT OUTER JOIN
(
  SELECT '0' AS sens
  UNION ALL SELECT '1' AS sens
  UNION ALL SELECT '2' AS sens
) tmp 
    ON tmp.sens = h.sens
    AND DATE_FORMAT(h.date_heure, '%Y-%m-%d') = CURRENT_DATE
RIGHT OUTER JOIN insertions i 
    ON i.symbole = h.symbole
    AND DATE_FORMAT(h.date_heure, '%Y-%m-%d') = CURRENT_DATE
GROUP BY i.symbole, tmp.sens

Avec les données que tu as fournies et en remplaçant CURRENT_DATE par la date des données fournies, ça fonctionne.

[aieeeuuuuu]

Envoyé par Madfrix

#2006 - MySQL server has gone away

heu... il a pas dit quand est-ce qu'il comptait revenir

plus sérieusement, il n'y a rien dans les logs, aucun message d'erreur ?
je n'ai pas de MySQL sous la main pour tester, mais c'est quand même violent comme erreur pour une requete qui n'a rien d'extraordinaire...

Quand à l'index, correction, cela me donne quasiment le même temps de traitement (1.1s contre 1,15s sans). J'ai utilisé un index "INDEX"

Quelles colonnes as-tu indexées.

Perso je verrais deux index :
Un index unique sur la colonne symbole de ta table insertions
Un index sur les colonnes (Symbole, sens, dateheure) de ta table historique, unique si tu n'a pas plusieurs mesures pour un symbole et un sens à une date donnée

[Madfrix]

Bonjour CinePhil,

il semble qu'en mettant une condition de jointure sur date_heure, la requête n'aboutisse pas (du moins ta requête est toujours en train de tourner sur mon pc !). C'est pour cela que j'ai filtré avec un WHERE sur chaque groupe de jointure. Ce groupe sera constitué journalièrement de 20 lignes maximum au lieu des 450k lignes actuelles sur lequel porte ta jointure. Je pense que le delta de temps provient de là.

[Madfrix]

Envoyé par aieeeuuuuu

Un index sur les colonnes (Symbole, sens, dateheure) de ta table historique, unique si tu n'a pas plusieurs mesures pour un symbole et un sens à une date donnée

En fait le cas bien que rare, peut arriver.

Mince maintenant PHPMyAdmin est dans les choux

#2002 - Le serveur ne répond pas. (ou l'interface de connexion ("socket") vers le serveur MySQL local n'est pas correctement configurée)

suis obligé de lancer via la console

[CinePhil]

Tu peux faire un EXPLAIN de ma requête et nous donner le résultat ?

[Madfrix]

Voici le EXPLAIN :

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY i ALL NULL NULL NULL NULL 20 Using temporary; Using filesort
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3
1 PRIMARY h ALL NULL NULL NULL NULL 429801
2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL