SQL – Utiliser un alias dans Group By

Juste curieux de la syntaxe SQL. Donc si j’ai

SELECT itemName as ItemName, subssortingng(itemName, 1,1) as FirstLetter, Count(itemName) FROM table1 GROUP BY itemName, FirstLetter 

Ce serait incorrect car

 GROUP BY itemName, FirstLetter 

devrait vraiment être

 GROUP BY itemName, subssortingng(itemName, 1,1) 

Mais pourquoi ne pouvons-nous pas simplement utiliser les premiers pour des raisons de commodité?

SQL est implémenté comme si une requête était exécutée dans l’ordre suivant:

  1. Clause FROM
  2. Clause WHERE
  3. Clause GROUP BY
  4. Clause AYANT
  5. Clause SELECT
  6. Clause ORDER BY

Pour la plupart des systèmes de bases de données relationnelles, cet ordre explique quels noms (colonnes ou alias) sont valides car ils doivent avoir été introduits lors d’une étape précédente.

Ainsi, dans Oracle et SQL Server, vous ne pouvez pas utiliser un terme dans la clause GROUP BY que vous définissez dans la clause SELECT car le paramètre GROUP BY est exécuté avant la clause SELECT.

Il y a cependant des exceptions: MySQL et Postgres semblent avoir une intelligence supplémentaire qui le permet.

Vous pouvez toujours utiliser une sous-requête pour pouvoir utiliser l’alias. Bien sûr, vérifiez les performances (le serveur de firebase database peut fonctionner à la fois de la même manière, mais il n’est jamais difficile de le vérifier):

 SELECT ItemName, FirstLetter, COUNT(ItemName) FROM ( SELECT ItemName, SUBSTRING(ItemName, 1, 1) AS FirstLetter FROM table1 ) ItemNames GROUP BY ItemName, FirstLetter 

Au moins dans PostgreSQL, vous pouvez utiliser le numéro de colonne dans le jeu de résultats de votre clause GROUP BY:

 SELECT itemName as ItemName, subssortingng(itemName, 1,1) as FirstLetter, Count(itemName) FROM table1 GROUP BY 1, 2 

Bien sûr, cela commence à être un problème si vous le faites de manière interactive et que vous modifiez la requête pour modifier le nombre ou l’ordre des colonnes dans le résultat. Mais rest.

SQL Server ne vous permet pas de référencer l’alias dans la clause GROUP BY en raison de l’ordre logique du traitement. La clause GROUP BY est traitée avant la clause SELECT, donc l’alias n’est pas connu lorsque la clause GROUP BY est évaluée. Cela explique également pourquoi vous pouvez utiliser l’alias dans la clause ORDER BY.

Voici une source d’informations sur les phases de traitement logique de SQL Server .

Attention: l’utilisation d’alias dans Group By (pour les services qui la prennent en charge, tels que postgres) peut avoir des résultats inattendus. Par exemple, si vous créez un alias qui existe déjà dans l’instruction interne, le groupe By choisit le nom du champ interne.

 -- Working example in postgres select col1 as col1_1, avg(col3) as col2_1 from (select gender as col1, maritalstatus as col2, yearlyincome as col3 from customer) as layer_1 group by col1_1; -- Failing example in postgres select col2 as col1, avg(col3) from (select gender as col1, maritalstatus as col2, yearlyincome as col3 from customer) as layer_1 group by col1; 

Certains SGBD vous permettront d’utiliser un alias au lieu de répéter toute l’expression.
Teradata en est un exemple.

J’évite la notation de position ordinale telle que recommandée par Bill pour les raisons documentées dans cette question SO .

L’alternative simple et robuste consiste à toujours répéter l’expression dans la clause GROUP BY.
DRY ne s’applique PAS au SQL.

Attention aux alias lors du regroupement des résultats d’une vue dans SQLite. Vous obtiendrez des résultats inattendus si le nom d’alias est identique au nom de colonne des tables sous-jacentes (aux vues).

À l’époque, j’ai trouvé que Rdb, l’ancien produit DEC actuellement pris en charge par Oracle, permettait d’utiliser l’alias de colonne dans GROUP BY. Mainstream Oracle via la version 11 ne permet pas d’utiliser l’alias de colonne dans GROUP BY. Vous ne savez pas ce que Postgresql, SQL Server, MySQL, etc. autoriseront ou ne permettront pas. YMMV.

Je ne réponds pas pourquoi il en est ainsi, mais je voulais seulement montrer un moyen de contourner cette limitation dans SQL Server en utilisant CROSS APPLY pour créer l’alias. Vous l’utilisez ensuite dans la clause GROUP BY , comme ceci:

 SELECT itemName as ItemName, FirstLetter, Count(itemName) FROM table1 CROSS APPLY (SELECT subssortingng(itemName, 1,1) as FirstLetter) Alias GROUP BY itemName, FirstLetter