Est-ce que SQL GROUP BY est un défaut de conception ?

Question

Pourquoi SQL exige-t-il que je spécifie les attributs à regrouper ? Pourquoi ne peut-il pas simplement utiliser tous les attributs non groupés ?

Si un attribut n'est pas agrégé et ne se trouve pas dans la liste des attributs de l'UE. GROUPE PAR alors le choix non déterministe serait la seule option en supposant que les tuples ne sont pas ordonnés (mysql le fait plus ou moins) et c'est un énorme problème. Pour autant que je sache, Postgresql exige que tous les attributs n'apparaissant pas dans la clause GROUPE PAR doit être agrégée, ce qui renforce le fait qu'elle est superflue.

• Est-ce que j'ai raté quelque chose ou est-ce que c'est un défaut de conception du langage qui favorise les implémentations lâches et rend les requêtes plus difficiles à écrire ?
• Si je ne comprends pas, quel est un exemple de requête où les attributs de groupe ne peuvent pas être déduits ?

Answer 1

Il n'est pas nécessaire de regrouper par la même chose que ce que vous sélectionnez, par exemple :

SQL:select priority,count(*) from rule_class
group by priority

PRIORITY   COUNT(*)
      70          1
      50          4
      30          1
      90          2
      10          4

SQL:select decode(priority,50,'Norm','Odd'),count(*) from rule_class
group by priority

DECO   COUNT(*)
Odd           1
Norm          4
Odd           1
Odd           2
Odd           4

SQL:select decode(priority,50,'Norm','Odd'),count(*) from rule_class
group by decode(priority,50,'Norm','Odd')

DECO   COUNT(*)
Norm          4
Odd           8

Answer 2

Il y a une raison de plus pour pourquoi SQL exige-t-il que je spécifie sur quels attributs grouper.

Disons que nous avons deux tableaux simples : friend et car où nous stockons des informations sur nos amis et leurs voitures.

Et disons que nous voulons montrer les données de tous nos amis (de la table friend ) et pour chacun de nos amis, le nombre de voitures qu'ils possèdent actuellement, qu'ils ont vendues, qu'ils ont accidentées et le nombre total. Oh, et on veut les anciens en premier, les jeunes en dernier.

Nous ferions quelque chose comme :

SELECT f.id
     , f.firstname
     , f.lastname
     , f.birthdate
     , COUNT(NOT c.sold AND NOT c.crashed) AS owned
     , COUNT(c.sold) AS sold
     , COUNT(c.crashed) AS crashed
     , COUNT(c.friendid) AS totalcars
FROM friend f
LEFT JOIN car c     <--to catch (shame!) those friends who have never had a car 
  ON f.id = c.friendid
GROUP BY f.id
       , f.firstname
       , f.lastname
       , f.birthdate
ORDER BY f.birthdate DESC

Mais avons-nous vraiment besoin de tous ces champs dans la base de données des GROUP BY ? Chaque ami n'est-il pas déterminé de manière unique par son id ? En d'autres termes, est-ce que les firstname, lastname and birthdate dépendent fonctionnellement de la f.id ? Pourquoi ne pas simplement faire (comme on peut le faire dans MySQL) :

SELECT f.id
     , f.firstname
     , f.lastname
     , f.birthdate
     , COUNT(NOT c.sold AND NOT c.crashed) AS owned
     , COUNT(c.sold) AS sold
     , COUNT(c.crashed) AS crashed
     , COUNT(c.friendid) AS totalcars
FROM friend f
LEFT JOIN car c     <--to catch (shame!) those friends who have never had a car 
  ON f.id = c.friendid
GROUP BY f.id
ORDER BY f.birthdate 

Et si nous avions 20 champs dans le SELECT (plus ORDER BY ) ? La seconde requête n'est-elle pas plus courte, plus claire et probablement plus rapide (dans les SGBDR qui l'acceptent) ?

Je dis oui. Alors, les spécifications de SQL 1999 et 2003 disent-elles, si cet article est correct : Démystifier le groupe par les mythes

Answer 3

Je dirais que si vous avez un grand nombre d'éléments dans la clause de regroupement par, alors peut-être que les informations de base devraient être extraites dans une sous-requête tabulaire que vous joindrez à l'intérieur.

Il y a probablement un impact sur les performances, mais cela permet d'avoir un code plus soigné.

select  id, count(a), b, c, d
from    table
group by
        id, b, c, d

devient

select  id, myCount, b, c, d
from    table t
        inner join (
            select id, count(*) as myCount
            from table
            group by id
        ) as myCountTable on myCountTable.id = t.id

Cela dit, j'aimerais entendre les contre-arguments qui justifient cette façon de faire plutôt que d'avoir un grand groupe par clause.

Answer 4

Je suis d'accord sur le fait que la liste des groupes par ne devrait pas être implicitement la même que les colonnes de sélection non agrégées. Dans Sas, il existe des opérations d'agrégation de données qui sont plus succinctes.

De plus, il est difficile de trouver un exemple où il serait utile d'avoir une liste de colonnes plus longue dans la liste de groupe que dans la liste de sélection. Le meilleur exemple que je puisse trouver est ...

create table people
(  Nam char(10)
  ,Adr char(10)
)

insert into people values ('Peter', 'Tibet')
insert into people values ('Peter', 'OZ')
insert into people values ('Peter', 'OZ')

insert into people values ('Joe', 'NY')
insert into people values ('Joe', 'Texas')
insert into people values ('Joe', 'France')

-- Give me people where there is a duplicate address record

select * from people where nam in 
(
select nam              
from People        
group by nam, adr        -- group list different from select list
having count(*) > 1
)

Answer 5

Si vous souhaitez simplement trouver un moyen plus facile d'écrire des scripts. Voici une astuce :

Dans MS SQL MGMS écrivez votre requête en texte quelque chose comme select * from my_table Après avoir sélectionné le texte, faites un clic droit et sélectionnez "Design Query in Editor ". Sql studio ouvrira un nouvel éditeur avec tous les champs remplis, après quoi vous ferez à nouveau un clic droit et sélectionnerez "Add Gruop BY". Sql MGM studio ajoutera le code pour vous.

Je trouve cette méthode extrêmement utile pour les déclarations d'insertion. Lorsque j'ai besoin d'écrire un script pour insérer un grand nombre de champs dans une table, je fais simplement select * from table_where_want_to_insert et après cela, je change le type d'instruction select en insert,