Discussion :

[J1]

Bonjour à toutes et à tous,

Vous le savez sûrement, MySQL permet d'inclure dans la clause SELECT d'une requête des colonnes non-listées dans la clause GROUP BY.
La documentation MySQL indique bien que dans ce cas, les valeurs renvoyées pour ces colonnes sont choisies de façon non-déterministe.

Mais j'aurais besoin de savoir ce que cela signifie exactement.
Prenons l'exemple ci-dessous :

Code :

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

1
2
3
4
5
6
7
8
SELECT a, b, c, d
FROM (
  SELECT 1 AS a, 11 AS b, 111 AS c, 1111 AS d UNION ALL
  SELECT 1 AS a, 22 AS b, 222 AS c, 2222 AS d UNION ALL
  SELECT 1 AS a, 33 AS b, 333 AS c, 3333 AS d UNION ALL
  SELECT 1 AS a, 44 AS b, 444 AS c, 4444 AS d UNION ALL
  SELECT 1 AS a, 55 AS b, 555 AS c, 5555 AS d) x
GROUP BY a ;

Concrètement, MySQL renvoie 1, 11, 111, 1111.
Mais, non-déterminisme oblige, il serait tout aussi acceptable qu'il renvoie 1, 22, 222, 2222 ou bien 1, 33, 333, 3333 ou encore 1, 44, 444, 4444 ou enfin 1, 55, 555, 5555.

La question que je me pose est : pourrait-il aller jusqu'à renvoyer 1, 33, 222, 5555 ?
En d'autres termes, MySQL renverra-t-il toujours les valeurs b, c et d issues d'une même ligne (ligne choisie de façon non-déterministe) ou l'aspect non-déterministe évoqué dans la documentation signifie-t-il que les valeurs b, c et d pourraient être prises chacune sur des lignes différentes ?

Merci d'avance pour vos lumières.

[tatayo]

Bonjour,
Pour ma part je ne me pose même pas la question (quand je dois interroger une base MySQL. Pour les autres le "problème" ne se pose pas).
Ce n'est pas déterministe => ce n'est pas utilisable.

Tatayo.

[Oishiiii]

Je n'arrive pas à comprendre comment on peut utiliser GROUP BY sans aucune fonction d’agrégat.

[J1]

Schématiquement, on me demande de renvoyer une ligne pour chaque valeur de a.
Peu importe la ligne choisie mais celle-ci doit être cohérente, c'est-à-dire que les valeurs de b, c et d doivent être issues de la même ligne.

Je souhaiterais donc savoir si je peux me reposer sur un simple GROUP BY pour parvenir à ce résultat (il y aurait bien sûr d'autres manières d'y parvenir, mais plus complexes).

[tatayo]

Puisque les valeurs des autres colonnes importent peu, il suffit de leur appliquer un MIN (ou un MAX) et le tour est joué. Les valeurs renvoyées ne seront plus choisies sur un coup de dé.
Et le jour où MySQL est remplacé par un autre moteur (ou qu'il décide d'appliquer la norme), la requête fonctionne toujours.

Tatayo.

[J1]

Non, justement, si j'applique un MIN() ou un MAX() sur les colonnes hors-GROUP BY, les valeurs renvoyées ne seront pas issues de la même ligne. Enfin, sur le jeu de données restreint que j'ai indiqué, si, mais dans l'absolu, non.

En fait, pour que les choses restent simples, il faudrait soit une fonction FIRST() ou LAST() comme celles d'Access, soit les fonctions de fenêtrage de SQL Server. Mais comme MySQL ne dispose de rien de tout ça, je me demandais si je pouvais exploiter les spécificités de "son" GROUP BY.

[Artemus24]

Salut à tous.

les valeurs renvoyées pour ces colonnes sont choisies de façon non-déterministe.

Ce n'est pas tout à fair vrai.
Si c'était non déterministe, à chaque ex"cution de la même requête, vous obtiendriez un résultat différent.
Or ce n'est pas le cas !

Je n'arrive pas à comprendre comment on peut utiliser GROUP BY sans aucune fonction d’agrégat.

Il est obligatoire sur les colonnes qui ne sont spécifiquement pas déclarées après le "group by", de les associer à une fonction.
Vous connaissez tous les fonctions : min(), max(), count(), sum(), avg(), ...

Une fonction moins connue est : any_value().
Elle retourne la première valeur rencontrée de la colonne qi ne fait pas partie des colonnes du "group by".

Peu importe la ligne choisie mais celle-ci doit être cohérente, c'est-à-dire que les valeurs de b, c et d doivent être issues de la même ligne.

Dans ce cas, vous devez sélectionner par vous même la ligne que vous désirez récupérer. 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
--------------
select    *
    from  `test`
   where  b = ( select  avg(b)
                  from  `test`
              group by  a
        )
--------------
 
+----+---+----+-----+------+
| id | a | b  | c   | d    |
+----+---+----+-----+------+
|  3 | 1 | 33 | 333 | 3333 |
+----+---+----+-----+------+

Dans cet exemple, je cherche la ligne qui correspond à la moyenne de la colonne b de toutes les lignes où je fais le regroupement.
Soit (11+22+33+44+55)/5 = 165/5 = 33. Soit la troisème ligne, c'est-à-dire la ligne où b = 33.

Puisque les valeurs des autres colonnes importent peu, il suffit de leur appliquer un MIN (ou un MAX) et le tour est joué.

Vous n'avez pas compris comment fonctionne le "group by".
Si la colonne n'a aucune importance, ne la mettez pas dans la requête !
Si vous désirez récupérer la première valeur de la colonne, mettez "any_value()".
Si vous désirez appliquer une fonction, dans ce cas, vous avez le choix entre plusieurs possibilités.
si c'est la dernière ligne du regroupement que vous désirez récupérer, faites un max sur la colonne de l'identifiant.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--------------
select    *
    from  `test`
   where  id = ( select  max(id)
                  from  `test`
              group by  a
        )
--------------
 
+----+---+----+-----+------+
| id | a | b  | c   | d    |
+----+---+----+-----+------+
|  5 | 1 | 55 | 555 | 5555 |
+----+---+----+-----+------+

Vous avez le choix entre plusieurs solutions. Tout dépend ce que vous cherchez à faire.

ou qu'il décide d'appliquer la norme

La plupart du temps, MySql applique la norme, sauf que l'on peut les court-circuiter en faisant un "set sql_mode =''".
--> https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

@ J1 : si vous avez un problème pour récupérer une ligne en particulier, détaillez votre demande et je vous aiderai pour la création de votre requête.

@+

[J1]

Une fonction moins connue est : any_value().
Elle retourne la première valeur rencontrée de la colonne qi ne fait pas partie des colonnes du "group by".

Merci pour votre réponse.

Concernant la fonction ANY_VALUE(), je la connais mais elle n'est implémentée qu'à partir de MySQL 5.7 or je travaille sur une version antérieure.
Quoi qu'il en soit, la documentation de MySQL n'indique pas explicitement que la fonction prend la première valeur de la colonne (cela solutionnerait mon problème car cela garantirait que les valeurs choisies pour les colonnes concernées sont bien issues de la même ligne). Par conséquent, le problème reste le même que précédemment.

C'est ballot, je risque d'être contraint de passer par un mécanisme relativement lourd d'auto-jointure ou de sous-requête corrélée alors que le GROUP BY de MySQL répond peut-être déjà à mon besoin (mais j'ai besoin d'en avoir la certitude alors que là, je n'en ai que l'intuition).

[CinePhil]

Si la table n'est pas trop grosse, prendre l'une des solutions d'Artemus ne devrait pas poser de problèmes de performance et le résultat sera certain.

[Artemus24]

Salut J1.

cela solutionnerait mon problème car cela garantirait que les valeurs choisies pour les colonnes concernées sont bien issues de la même ligne

Je déconseille l'usage de la fonction "any_value()", qui est une solution batarde proposé par MySql, si l'on veut respecter les normes.

La bonne façon, dans un select, de nommer les colonnes quand on fait l'usage d'un "group by" est de :
--> nommer les colonnes qui sont dans la clause "group by"
--> associer les autres colonnes à une fonction de regroupement, comme "min()", "max()", ...
Exemple :

Code :

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

1
2
3
4
select    col1,
          max(col2) as maximum
    from  `votre_table`
group by  col1;

Ceci est la bonne façon d'écrire un select avec la clause "group by".

Pour récupérer une ligne en particulier, vous devez le faire sur un critère. Par exemple, sur la date la plus récente :

Code :

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

1
2
3
4
5
6
select  *
  from  `votre_table` as t1
 where date_saisie = (  select  max(date_saisie)
                          from  `votre_table` as t2
                         where  t2.critere = t1.critere
                     );

Il est dans ce cas là, inutile de faire un "group by" sur le critère car vous récupérerez qu'une ligne par critère.

Par contre, en utilisant une jointure, vous devez préciser le "group by" :

Code :

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

1
2
3
4
5
6
7
8
9
10
select      *
      from  `votre_table` as t1
 
inner join  (  select  critere,
                       max(date_saisie) as date_saisie
                 from  `votre_table`
             group by  critere
            ) as t2
        on  t2.critere     = t1.critere
       and  t2.date_saisie = t1.date_saisie;

@+

[SQLpro]

Salut à tous.


Ce n'est pas tout à fair vrai.
Si c'était non déterministe, à chaque ex"cution de la même requête, vous obtiendriez un résultat différent.
Or ce n'est pas le cas !

Si c'est bien le cas. Il suffit juste de construire le même jeu d'essais en insérant les lignes dans un ordre différent pour vous en convaincre... C'est simplement parce que MySQmerde n'a jamais été un SGBD Relationnel. En effet dans un SGBDR l'ordre des lignes dans une table n'a aucune importance. or MySQL en tient compte systématiquement lorsqu'il ne sait pas quoi faire des lignes. Ce qui est une hérésie imbécile et grotesque !

A +

[Artemus24]

Salut SQLPRO.

Si c'est bien le cas.

Non, c'est faux. Pour bien préciser ma pensé, je parle de la fonction "any_value()".

Il suffit de faire le test que vous me conseillez de faire et de constater que les valeurs retournées concernent toujours celle de la première ligne sélectionnée.
Et même si cette première ligne n'est jamais la même, il retournera toujours les valeurs de cette première ligne et non d'un autre.
C'est en cela que je ne suis pas d'accord car "non déterministe" signifie que l'on ne peut pas prévoir le résultat.

En effet dans un SGBDR l'ordre des lignes dans une table n'a aucune importance.

Je suis d'accord avec vous car les fonctions (min(), max(), count(), sum(), ...) sont indépendantes de l'ordre de stockage, et c'est pourquoi, je dis :

Je déconseille l'usage de la fonction "any_value()", qui est une solution batarde proposé par MySql, si l'on veut respecter les normes.

L'usage de cette fonction "any_value()", est absurde à cause d'un problème de compatibilité avec les anciennes versions qui elles, ne respectaient pas la norme.

En gros, nous disons la même chose.

@+

[J1]

Bon, les dernières interventions n'ont plus grand chose à voir avec la question que j'avais posée initialement (et à laquelle je ne trouve malheureusement pas de réponse) mais merci tout de même aux différents intervenants.

[CinePhil]

Schématiquement, on me demande de renvoyer une ligne pour chaque valeur de a.
Peu importe la ligne choisie mais celle-ci doit être cohérente, c'est-à-dire que les valeurs de b, c et d doivent être issues de la même ligne.

Y a t-il une autre colonne discriminante (idéalement, la clé primaire) ?

Une solution si c'est le cas :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT t1.a, t1.b, t1.c, t1.d
FROM la_table t1
INNER JOIN
(
	SELECT a, MIN(id) AS min_id
	FROM la_table
	GROUP BY a
) t2
	ON t2.a = t1.a
	AND t2.min_id = t1.id

[Artemus24]

Salut à tous.

Pour clarifier le débat, je parle bien sûr de la fonction "any_value()".

Bon, les dernières interventions n'ont plus grand chose à voir avec la question que j'avais posée initialement (et à laquelle je ne trouve malheureusement pas de réponse) mais merci tout de même aux différents intervenants.

J'ai pourtant répondu à votre question ! La valeur retournée est toujours celle de la première ligne sélectionnée.

Là où je ne suis pas d'accord avec SQLPRO, c'est sur le sens que l'on attribue à "non déterministe". Pour moi, cela est synonyme de "au hasard" !
Et si je comprends bien votre question, c'est dans ce sens que vous l'interprété aussi.

A vrai dire, cela dépend du traitement que vous effectuez pour extraire les lignes qui vont subir un regroupement.
Si vous avez plusieurs lignes qui sont candidates, c'est toujours de la première ligne que sont extraits les informations qui ne sont pas regroupées.

Si l'on fait l'extraction en deux temps, à savoir une view qui fera office de sous-requête, vous pouvez voire où va se situer cette première ligne dans vos extractions.

Pourquoi dire que c'est "non déterministe" ?
Si je vais dans le sens de SQLPRO, MySql est un serveur multithread et de ce fait, on ne peut jamais savoir à l'avance où va se situer cette fameuse première ligne.
Dans le cas d'un traitement mono-tâche, le problème ne se pose pas et c'est en cela que j'ai répondu "la première ligne".

Maintenant, en quoi votre question est un faux problème ?
Parce que la requête qui fait un regroupement s'écrit avec deux types de colonnes, à savoir :
--> les colonnes servant de critères au regroupent (celle spécifiées dans la clause "group by").
--> les colonnes qui vont subir un regroupement (celle où l'on ajoute une des fonctions suivantes : sum(), count(), avg(), max(), min(), ...).

Comme on peut le voir, le résultat n'a rien d'aléatoire puisqu'il dépend de l'algorithme d'extraction des données que vous utilisées.

Et si vous désirez récupérer autre choses que les critères de regroupements et les colonnes subissant ce regroupement ?
Autrement dit, est-ce logique d'utiliser la fonction "any_value()" ?
Je réponds non car récupérer une ligne doit se faire selon un critère particulier.
Prenons l'exemple de l'extraction de la ligne ayant la date la plus récente :
[code]select critere, max(date)
from votre_table
group by critere;[/code)
Avec cette requête, j'obiens pour chaque critère, la date la plus grande.

Maintenant, si je désire extraire la ligne qui est associée à cette date, vous devez écrire votre requête de cette façon :

Code :

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

1
2
3
4
5
6
7
8
 
 
select *
 from  votre_table as t1
where  date = (  select  max(t2.date)
                   from  votre_table az t2
                  where  t2.critere = t1.critere
              );

Dans la sous-requête, il n'est pas nécessaire de mettre un "group by" car le regroupement se fera bien selon la colonne critere, qui sera corrélée avec la table t1.

@+

[J1]

Y a t-il une autre colonne discriminante (idéalement, la clé primaire) ?

Une solution si c'est le cas :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT t1.a, t1.b, t1.c, t1.d
FROM la_table t1
INNER JOIN
(
	SELECT a, MIN(id) AS min_id
	FROM la_table
	GROUP BY a
) t2
	ON t2.a = t1.a
	AND t2.min_id = t1.id

Oui, j'ai possibilité de procéder ainsi mais la solution sera assez lourde à mettre en place car le jeu d'enregistrements que je dois filtrer n'est pas directement issu d'une table mais d'une sous-requête assez complexe. Du coup, pour faire l'auto-jointure, il va falloir que je répète deux fois cette sous-requête (je n'ai pas possibilité de mettre en place des vues).

C'est la raison pour laquelle j'essayais de savoir si je ne pouvais pas simplifier tout cela en passant par un simple GROUP BY plutôt que par une auto-jointure. D'où mon premier post.

[CinePhil]

Du coup, pour faire l'auto-jointure, il va falloir que je répète deux fois cette sous-requête

Ça ce n'est pas forcément gênant car l'optimiseur de MySQL ne devrait l'exécuter qu'une seule fois et stocker le résultat en mémoire pour l'utiliser deux fois.

Mais si vous nous donnez la requête complexe, on peut aussi essayer de l'améliorer.

[Artemus24]

Salut J1.

C'est la raison pour laquelle j'essayais de savoir si je ne pouvais pas simplifier tout cela en passant par un simple GROUP BY plutôt que par une auto-jointure. D'où mon premier post.

Ce n'est pas le sujet de votre première message. Voici votre interrogation première :

En d'autres termes, MySQL renverra-t-il toujours les valeurs b, c et d issues d'une même ligne (ligne choisie de façon non-déterministe) ou l'aspect non-déterministe évoqué dans la documentation signifie-t-il que les valeurs b, c et d pourraient être prises chacune sur des lignes différentes ?

Les valeurs des colonnes sans fonction sont toujours issues de la même ligne à savoir la première lue.

Maintenant l'usage d'une sous-requête ou d'une jointure est un problème différent et qui dépend de ce que vous chezchez à faire.

Au lieu de partir sur un exemple non représentatif de votre problème et d'essayer de comprendre le pourquoi du comment de votre faux problème, il aurait été plus judicieux de nous donner les élements suivants :
1) le descriptif de vos tables entrant dans la requête.
2) un jeu d'essai complet et représentatif de votre problème.
3) la requête que vous avez testé et qui selon vous ne fonctionne pas.
4) le résultat que vous attendez.

Sans cela, toute discussion sera stérile et sera soumise à l'opinion de chacun d'entre nous.

@+

[J1]

Salut à tous.

Pour clarifier le débat, je parle bien sûr de la fonction "any_value()".


J'ai pourtant répondu à votre question ! La valeur retournée est toujours celle de la première ligne sélectionnée.

Mais est-ce une constatation empirique que vous avez faite ou est-ce garanti par le fonctionnement de la fonction ? Car je ne vois nulle part dans la documentation mention du fait que ANY_VALUE renverrait systématiquement la valeur de la première ligne sélectionnée.
Pour rappel, mon objectif est que toutes les valeurs mentionnées par le SELECT sans être présentes dans le GROUP BY soient bien issues de la même ligne de mon jeu de données original. Empiriquement, je constate que c'est le cas mais rien ne l'atteste dans la documentation.

[Artemus24]

Salut J1.

Oui, c'est bien une constatation empirique du fonctionnement du "group by".
Mais comme je l'ai dit, c'est un faux problème car ce n'est pas ce qu'indique la norme pour l'usage de la clause "group by".
Les colonnes de la clause select doivent être celle du "group by" ou bien être associées à une fonction.
Même si mysql permet de faire l'usage d'une colonne non référencée dans la clause "group by", c'est une erreur.

@+