Un guide du débutant pour le véritable ordre des opérations SQL

Le langage SQL est très intuitif. Jusqu’à ce qu’il ne le soit pas.

Au fil des années, beaucoup de gens ont critiqué le langage SQL pour diverses raisons. Par exemple : Les IDE ne peuvent pas facilement deviner quelles options de complétion automatique proposer, car tant que vous ne spécifiez pas la clause FROM, il n’y a pas de tables dans la portée (encore):

-- Don't you wish this would be completed to first_name?SELECT first_na...-- Aaah, now it works:SELECT first_na...FROM customer

Ces choses sont bizarres, car l’ordre lexical des opérations ne correspond pas à l’ordre logique des opérations. Nous, les humains, pouvons parfois (souvent) comprendre intuitivement cette différence d’ordre. Par exemple, nous savons que nous sommes sur le point de faire une sélection dans la table des clients. Mais l’IDE ne le sait pas.

GROUP BY apporte le plus de confusion

Lorsqu’un développeur junior / débutant en SQL commence à travailler avec SQL, assez rapidement, il découvrira l’agrégation et GROUP BY. Et ils écriront rapidement des choses comme :

SELECT count(*)FROM customer

Yay, nous avons 200 clients !

Et ensuite:

SELECT count(*)FROM customerWHERE first_name = 'Steve'

Wow, 90 d’entre eux s’appellent Steve ! Intéressant. Trouvons combien nous en avons par nom…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

FIRST_NAME COUNT------------------Steve 90Jane 80Joe 20Janet 10

Très bien. Mais sont-ils tous identiques ? Vérifions aussi le nom de famille

SELECT first_name, last_name, count(*)FROM customerGROUP BY first_name

Oops!

ORA-00979: not a GROUP BY expression

Jeez, qu’est-ce que cela signifie ? (note, malheureusement, les utilisateurs de MySQL qui n’utilisent pas le mode STRICT obtiendront toujours un résultat ici avec des noms de famille arbitraires !, donc un nouvel utilisateur de MySQL ne comprendra pas leur erreur)

Comment expliquer facilement cela à un novice en SQL ? Cela semble évident pour les « pros », mais est-ce vraiment évident ? Est-ce suffisamment évident pour que vous puissiez l’expliquer facilement à un junior ? Réfléchissez-y. Pourquoi chacune de ces déclarations sont-elles sémantiquement correctes ou fausses ?

Le problème est lié à la syntaxe

La syntaxe SQL fonctionne de manière similaire à la langue anglaise. Il s’agit d’une commande. On commence les commandes par des verbes. Le verbe est SELECT (ou INSERT, UPDATE, DELETE, CREATE, DROP, etc. etc.)

Malheureusement, le langage humain est incroyablement mal adapté au monde beaucoup plus formel de la programmation. Bien que cela offre une certaine consolation aux nouveaux utilisateurs (éventuellement non programmeurs) qui sont des débutants absolus, cela ne fait que rendre les choses difficiles pour tous les autres. Toutes les différentes clauses SQL ont des interdépendances extrêmement complexes. Par exemple :

  • En présence d’une clause GROUP BY, seules les expressions construites à partir d’expressions GROUP BY (ou leurs dépendances fonctionnelles), ou les fonctions agrégées peuvent être utilisées dans les clauses HAVING, SELECT et ORDER BY.
  • Pour des raisons de simplicité, ne parlons même pas de GROUPING SETS
  • En fait, il y a même quelques cas dans lesquels GROUP BY est implicite. Par ex. si vous écrivez une clause HAVING « nue »
  • Une seule fonction d’agrégation dans la clause SELECT (en l’absence de GROUP BY) forcera l’agrégation en une seule ligne
  • En fait, cela peut aussi être implicite en mettant cette fonction d’agrégation dans ORDER BY (pour quelque raison que ce soit)
  • Vous pouvez ORDER BYbeaucoup d’expressions qui font référence à n’importe quelles colonnes de la clause FROM sans les SELECT. Mais ce n’est plus vrai si vous écrivez SELECT DISTINCT

La liste est infinie. Si vous êtes intéressé, vous pouvez lire les documents standards SQL et vérifier combien d’interdépendances bizarres et compliquées existent entre les nombreuses clauses de l’instruction SELECT.

Peut-on jamais comprendre cela ?

Heureusement, oui ! Il y a une astuce simple, que j’explique toujours aux délégués qui visitent ma Masterclass SQL. L’ordre lexical (syntaxique) des opérations SQL (clauses) ne correspond pas du tout à l’ordre logique des opérations (même si, parfois, ils le font par coïncidence). Grâce aux optimiseurs modernes, l’ordre ne correspond pas non plus à l’ordre réel des opérations, de sorte que nous avons réellement : syntaxique -> logique -> ordre réel, mais laissons cela de côté pour le moment.

L’ordre logique des opérations est le suivant (pour la « simplicité », je laisse de côté les choses spécifiques au vendeur comme CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT et toutes les autres):

  • FROM : C’est en fait la première chose qui se passe, logiquement. Avant toute chose, on charge toutes les lignes de toutes les tables et on les joint. Avant que tu ne cries et ne t’énerves : Encore une fois, c’est ce qui se passe en premier logiquement, pas réellement. L’optimiseur ne fera très probablement pas cette opération en premier, ce serait stupide, mais accédera à un index basé sur la clause WHERE. Mais encore une fois, logiquement, cela arrive en premier. Aussi : toutes les clauses JOIN font en fait partie de cette clause FROM. JOIN est un opérateur dans l’algèbre relationnelle. Tout comme + et - sont des opérateurs en arithmétique. Ce n’est pas une clause indépendante, comme SELECT ou FROM
  • WHERE : Une fois que nous avons chargé toutes les lignes des tableaux ci-dessus, nous pouvons maintenant les jeter à nouveau en utilisant WHERE
  • GROUP BY : Si vous voulez, vous pouvez prendre les lignes qui restent après WHERE et les mettre dans des groupes ou des seaux, où chaque groupe contient la même valeur pour l’expression GROUP BY (et toutes les autres lignes sont mises dans une liste pour ce groupe). En Java, vous obtiendriez quelque chose comme : Map<String, List<Row>>. Si vous spécifiez une clause GROUP BY, alors vos lignes réelles ne contiennent que les colonnes du groupe, et non plus les autres colonnes, qui se trouvent maintenant dans cette liste. Ces colonnes dans la liste ne sont visibles que pour les fonctions d’agrégation qui peuvent opérer sur cette liste. Voir ci-dessous.
  • agrégations : Il est important de comprendre ceci. Peu importe où vous mettez votre fonction d’agrégation syntaxiquement (c’est-à-dire dans la clause SELECT, ou dans la clause ORDER BY), voici l’étape où les fonctions d’agrégation sont calculées. Juste après GROUP BY. (rappelez-vous : logiquement. Des bases de données astucieuses peuvent les avoir calculées avant, en fait). Cela explique pourquoi vous ne pouvez pas mettre une fonction d’agrégation dans la clause WHERE, car on ne peut pas encore accéder à sa valeur. La clause WHERE intervient logiquement avant l’étape d’agrégation. Les fonctions d’agrégation peuvent accéder aux colonnes que vous avez placées dans « cette liste » pour chaque groupe, ci-dessus. Après l’agrégation, « cette liste » disparaîtra et ne sera plus disponible. Si vous n’avez pas de clause GROUP BY, il y aura juste un grand groupe sans aucune clé, contenant toutes les lignes.
  • HAVING : … mais maintenant vous pouvez accéder aux valeurs des fonctions d’agrégation. Par exemple, vous pouvez vérifier que count(*) > 1 dans la clause HAVING. Parce que HAVING est après GROUP BY (ou implique GROUP BY), nous ne pouvons plus accéder aux colonnes ou aux expressions qui n’étaient pas des colonnes GROUP BY.
  • WINDOW : Si vous utilisez l’impressionnante fonction de fenêtre, c’est l’étape où ils sont tous calculés. Seulement maintenant. Et ce qui est cool, c’est que parce que nous avons déjà calculé (logiquement !) toutes les fonctions agrégées, nous pouvons imbriquer les fonctions agrégées dans les fonctions fenêtres. Il est donc parfaitement possible d’écrire des choses comme sum(count(*)) OVER () ou row_number() OVER (ORDER BY count(*)). Les fonctions fenêtres n’étant logiquement calculées que maintenant expliquent aussi pourquoi vous ne pouvez les mettre que dans les clauses SELECT ou ORDER BY. Elles ne sont pas disponibles pour la clause WHERE, ce qui était le cas auparavant. Notez que PostgreSQL et Sybase SQL Anywhere ont une clause WINDOW réelle !
  • SELECT : Enfin. Nous pouvons maintenant utiliser toutes les lignes qui sont produites à partir des clauses ci-dessus et créer de nouvelles lignes / tuples à partir d’elles en utilisant SELECT. Nous pouvons accéder à toutes les fonctions de fenêtre que nous avons calculées, à toutes les fonctions d’agrégation que nous avons calculées, à toutes les colonnes de regroupement que nous avons spécifiées, ou si nous n’avons pas regroupé/agrégé, nous pouvons utiliser toutes les colonnes de notre clause FROM. N’oubliez pas : Même s’il semble que nous agrégeons des choses à l’intérieur de SELECT, cela s’est produit il y a longtemps, et la douce fonction count(*) n’est rien de plus qu’une référence au résultat.
  • DISTINCT : Oui ! DISTINCT arrive après SELECT, même si elle est mise avant votre liste de colonnes SELECT, syntaxiquement parlant. Mais réfléchissez-y. C’est parfaitement logique. Sinon, comment pouvons-nous supprimer des lignes distinctes, si nous ne connaissons pas encore toutes les lignes (et leurs colonnes) ?
  • UNION, INTERSECT, EXCEPT : C’est une évidence. Un UNION est un opérateur qui relie deux sous-requêtes. Tout ce dont nous avons parlé jusqu’à présent était une sous-requête. Le résultat d’une union est une nouvelle requête contenant les mêmes types de lignes (c’est-à-dire les mêmes colonnes) que la première sous-requête. Habituellement. Parce que dans l’Oracle farfelu, l’avant-dernière sous-requête est la bonne pour définir le nom de la colonne. Base de données Oracle, le troll syntaxique 😉
  • ORDER BY : Il est totalement logique de reporter la décision d’ordonner un résultat jusqu’à la fin, parce que toutes les autres opérations pourraient utiliser des hashmaps, en interne, donc tout ordre intermédiaire pourrait être perdu à nouveau. Nous pouvons donc maintenant ordonner le résultat. Normalement, vous pouvez accéder à beaucoup de lignes à partir de la clause ORDER BY, y compris des lignes (ou des expressions) que vous n’avez pas SELECT. Mais lorsque vous avez spécifié DISTINCT, avant, vous ne pouvez plus ordonner par les lignes / expressions qui n’ont pas été sélectionnées. Pourquoi ? Parce que l’ordre serait tout à fait indéfini.
  • OFFSET : Ne pas utiliser le décalage
  • LIMIT, FETCH, TOP : Maintenant, les bases de données saines mettent la clause LIMIT (MySQL, PostgreSQL) ou FETCH (DB2, Oracle 12c, SQL Server 2012) à la toute fin, syntaxiquement. Autrefois, Sybase et SQL Server pensaient que ce serait une bonne idée d’avoir TOP comme mot-clé dans SELECT. Comme si l’ordre correct de SELECT DISTINCT n’était pas déjà assez confus.

Voilà, nous l’avons. C’est tout à fait logique. Et si jamais vous voulez faire quelque chose qui n’est pas dans le « bon ordre », l’astuce la plus simple est toujours de recourir à une table dérivée. Par exemple, lorsque vous voulez grouper sur une fonction de fenêtre:

Pourquoi cela fonctionne-t-il ? Parce que:

  • Dans le tableau dérivé, FROM se produit d’abord, et ensuite le WINDOW est calculé, puis le godet est SELECTed.
  • La SELECTextérieure peut maintenant traiter le résultat de ce calcul de la fonction fenêtre comme n’importe quelle table ordinaire dans la clause FROM, puis GROUP BYune colonne ordinaire, puis agréger, puis SELECT

Revisitons nos exemples originaux en expliquant pourquoi ils fonctionnent ou pourquoi ils ne fonctionnent pas.

Toujours penser à l’ordre logique des opérations

Si vous n’êtes pas un rédacteur SQL fréquent, la syntaxe peut en effet être déroutante. En particulier, les GROUP BY et les agrégations « infectent » le reste de la clause SELECT entière, et les choses deviennent vraiment bizarres. Face à cette bizarrerie, nous avons deux options :

  • S’énerver et hurler sur les concepteurs du langage SQL
  • Accepter notre sort, fermer les yeux, oublier la snytaxe et se souvenir de l’ordre des opérations logiques

Je recommande généralement la seconde solution, car les choses commencent alors à avoir beaucoup plus de sens, y compris le magnifique calcul du revenu quotidien cumulé ci-dessous, qui imbrique le revenu quotidien (SUM(amount) fonction agrégée) à l’intérieur du revenu cumulé (SUM(...) OVER (...) fonction fenêtre) :

SELECT payment_date, SUM(SUM(amount)) OVER (ORDER BY payment_date) AS revenueFROM paymentGROUP BY payment_date

… parce que les agrégations se produisent logiquement avant les fonctions de fenêtre.

Caveat : clause ORDER BY

Il y a quelques caveats autour de la clause ORDER BY, qui pourraient contribuer à une confusion supplémentaire. Par défaut, continuer à supposer que l’ordre logique des opérations est correct. Mais alors, il y a quelques cas spéciaux, en particulier:

  • En l’absence d’une clause DISTINCT
  • En l’absence d’opérations d’ensemble comme UNION

Vous pouvez référencer des expressions dans ORDER BY, qui ne sont pas projetées par SELECT. La requête suivante est parfaitement bien dans la plupart des bases de données:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Il y a une projection « virtuelle » / implicite ACTOR_ID, comme si nous avions écrit:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Mais ensuite, supprimé la colonne ACTOR_ID à nouveau du résultat. C’est très pratique, bien que cela puisse entraîner une certaine confusion sur la sémantique et l’ordre des opérations. Plus précisément, vous ne pouvez pas utiliser par exemple DISTINCT dans une telle situation. La requête suivante n’est pas valide:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Parce que, que se passe-t-il s’il y a deux acteurs du même nom mais avec des ID très différents ? L’ordre serait maintenant indéfini.

Avec les opérations ensemblistes, on comprend encore mieux pourquoi cela n’est pas autorisé :

SELECT first_name, last_nameFROM actorUNIONSELECT first_name, last_nameFROM customerORDER BY actor_id -- Oops

Dans ce cas, la colonne ACTOR_ID n’est pas présente sur la table CUSTOMER, donc la requête n’a aucun sens.

Lecture complémentaire

Vous voulez en savoir plus ? Nous vous proposons également de lire ces articles :

  • SQL GROUP BY et dépendances fonctionnelles : Une fonctionnalité très utile
  • Comment le GROUP BY SQL aurait dû être conçu – comme le GROUP BY implicite de Neo4j
  • Comment traduire le GROUP BY SQL et les agrégations en Java 8
  • Comprenez-vous vraiment les clauses GROUP BY et HAVING de SQL ?
  • GROUP BY ROLLUP / CUBE

.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.