Question 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 penser à une explication logique de la raison pour laquelle cela devrait être vrai ...


38
2018-05-13 00:45


origine


Réponses:


Imaginez ce qui suit:

    A    B    C
   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 avez seulement deux lignes (ou tuples) parce que vous avez deux valeurs pour A - mais comment affiche-t-il B?

Si vous groupez par A, B, vous obtenez 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 seul 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.


30
2018-05-13 00:50



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

Mais 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 sont dans le groupe 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.


14
2018-05-13 00:50



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, il va juste tirer 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 déjà tous 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.


7
2018-05-13 00:48



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


1
2018-05-13 00:49



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 de 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 les valeurs supplémentaires (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 souhaitais lors de l'écriture de 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?


1
2018-05-13 00:54



Donc, la réponse simple est: cela dépend. Mysql le permet, vertica ne le permet 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?


1
2018-01-09 23:04



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;

0
2017-12-24 21:33