Toutes les colonnes d’une liste SELECT doivent-elles apparaître dans une clause GROUP BY

Mon conférencier a déclaré:

Tous les noms de colonne de la liste SELECT doivent apparaître dans la clause GROUP BY, sauf si name est utilisé uniquement dans une fonction d’agrégat

Je veux juste une confirmation de cela car je ne peux pas trouver une explication logique à la raison pour laquelle cela devrait être vrai …

Imaginez ce qui suit:

ABC Cat 10 False Dog 25 True Dog 20 False Cat 5 False 

Si vous sélectionnez A, B et Group By Only A, quelle serait votre sortie? Vous n’auriez que deux lignes (ou tuples) car vous avez deux valeurs pour A – mais comment affiche-t-il B?

Si vous groupez par A, B, vous obtiendrez quatre lignes, pas de problèmes. Si vous regroupez par A et effectuez une fonction sur B – comme SUM (B), vous obtenez à nouveau deux lignes:

  Cat 15 Dog 45 

Mais si vous sélectionnez A, B et uniquement le groupe par A – il ne sait pas quoi faire. En vérité, je crois qu’il existe des bases de données qui sélectionneront une valeur aléatoire pour B dans ce cas et je crois qu’il y en a qui vous donneront un message d’erreur.

C’est historiquement vrai. L’omission de colonnes non agrégées entraîne un comportement indéterminé. SQL vise à déterminer pleinement le comportement.

Toutefois, les normes SQL ont récemment été modifiées pour vous permettre d’omettre les colonnes de la clause GROUP BY qui dépendent fonctionnellement des colonnes figurant dans GROUP BY. PostgreSQL suit les normes SQL les plus récentes. (Ce n’est pas le seul.) Le comportement est toujours pleinement déterminé.

 create table a ( a_id integer primary key, xfr_date date not null ); create table b ( a_id integer not null references a (a_id), recd_date date not null, units_recd integer not null check (units_recd >= 0), primary key (a_id, recd_date) ); select a.a_id, a.xfr_date, sum(b.units_recd) from a inner join b on a.a_id = b.a_id group by a.a_id; -- The column a.xfr_date is functionally dependent -- on a.a_id; it doesn't have to appear in the -- GROUP BY clause. 

L’écart notable par rapport aux normes SQL est MySQL. Il vous permet d’omettre à peu près tout de la GROUP BY. Mais ce choix de conception rend son comportement indéterminé lorsque vous omettez les colonnes de la liste SELECT.

En fait, dans MySQL, vous n’avez pas besoin de regrouper toutes les colonnes. Vous pouvez simplement regrouper par les colonnes de votre choix. Le problème est que cela va simplement extraire une valeur aléatoire (de l’ensemble des lignes disponibles dans le groupe) pour les champs qui ne sont pas dans le groupe par. Si vous savez que vous regroupez par quelque chose qui est une clé unique, le regroupement par les autres champs est inutile, car ils auront tous déjà la même valeur. Il peut en fait accélérer le processus pour ne pas avoir à regrouper tous les champs lorsque cela est complètement inutile.

Si vous regroupez sur quelque chose, vous ne pouvez pas voir les valeurs individuelles des colonnes non groupées car il peut y avoir plusieurs valeurs dans chaque groupe. Tout ce que vous pouvez faire est de rapporter des fonctions d’agrégation (sum, nombre, min & etc) – celles-ci sont capables de combiner les multiples valeurs en une seule cellule dans le résultat.

Il y a des exceptions, comme l’a noté Sam Saffron, mais généralement ce que votre conférencier a dit est vrai.

Si je sélectionne 3 colonnes et groupe par 2, que devrait faire le SGBDR avec la 3ème colonne?

Les développeurs du SGBDR peuvent prendre une décision sur la manière de gérer la quantité supplémentaire (comme cela semble être le cas pour les développeurs de MySQL), mais est-ce la décision que j’aurais prise ou celle que je veux lorsque vous écrivez la sélection? La décision sera-t-elle toujours valable? Je préfère certainement l’approche de type Oracle qui me contraint à énoncer explicitement ce qui doit se passer.

Si je sélectionne 3 colonnes et que je les groupe par 2, le groupe RDBS doit-il être au nombre de 3, choisir une valeur aléatoire parmi le 3ème, le plus grand ou le plus petit, le plus courant?

Donc, la réponse simple est: cela dépend. Mysql le permet, vertica pas.

Il y a en fait un cas d’utilisation valide pour l’omission et c’est quand vous sélectionnez déjà dire avec MIN ().

Voici un exemple concret de suivi des événements. Imaging vous avez des événements de crédit et d’achat.

Pour simplifier, nous disons que a = crédit, b, c, d sont un type d’événement d’achat et que le temps est suivi d’un numéro courant. Maintenant, vous voulez trouver la date du premier achat après chaque crédit. Nous arrivons également à avoir un seul client 0:

 create table events (user_id int ,created_at int, event varchar(255)); insert into events values (0,0, 'a'); insert into events values (0,1, 'b'); insert into events values (0,2, 'c'); insert into events values (0,3, 'a'); insert into events values (0,4, 'c'); insert into events values (0,5, 'b'); insert into events values (0,6, 'a'); insert into events values (0,7, 'a'); insert into events values (0,8, 'd'); mysql> SELECT user_id, MAX(purchased) AS purchased, spent, event FROM (SELECT e1.User_ID AS user_id, e1.created_at AS purchased, MIN(e2.created_at) AS spent, e2.event AS event FROM events e1, events e2 WHERE e1.user_id = e2.user_id AND e1.created_at <= e2.created_at AND e1.Event = 'a' AND e2.Event != 'a' GROUP BY e1.user_id, e1.created_at) e3 GROUP BY user_id, spent; +---------+-----------+-------+-------+ | user_id | purchased | spent | event | +---------+-----------+-------+-------+ | 0 | 0 | 1 | b | | 0 | 3 | 4 | c | | 0 | 7 | 8 | d | +---------+-----------+-------+-------+ 3 rows in set (0.00 sec) 

semble bien en mysql, ne fonctionne pas en vertica:

ERROR 2640: La colonne "e2.event" doit apparaître dans la clause GROUP BY ou être utilisée dans une fonction d'agrégat

Si j'omets la colonne event, cela fonctionne dans les deux cas, mais je veux vraiment savoir quel événement de valeur spécifique a eu pour la ligne sélectionnée par min.

Alors ma réponse se termine par une demande de commentaire 🙂 Des idées?

Ceci est une réponse à l’exemple / question de Michael Will.

 SELECT e3.user_id, MAX(e3.purchased) AS purchased, e3.spent, e.event FROM events e INNER JOIN (SELECT e1.user_id AS user_id, MIN(e1.created_at) as spent, e2.created_at as purchased FROM events e1 INNER JOIN (SELECT e.user_id, e.created_at from events e WHERE e.event = 'a') e2 ON e1.user_id = e2.user_id AND e1.created_at >= e2.created_at AND e1.event != 'a' GROUP BY e1.User_ID, e2.created_at ) e3 ON e.user_id = e3.user_id AND e.created_at = e3.spent GROUP BY e3.user_id, e3.spent, e.event;