SQL WITH

Parfois, vous êtes confronté à une question complexe qui ne peut pas être facilement résolue avec une seule instruction SQL.

Vous pourriez essayer d'arriver à la réponse en une seule passe en utilisant plusieurs vues en ligne ou de multiples sous-requêtes, mais cela rendra probablement votre SQL difficile à comprendre, et certaines variétés de SQL, telles que Hive SQL, ne permettent pas plus d'une sous-requête.

Une deuxième approche consiste à décomposer la complexité en plusieurs étapes différentes. Une façon de faire cela est de créer plusieurs tables, chaque table stockant les résultats de chaque étape. C'est souvent une bonne stratégie, car cela facilite la débogage et rend plus simple le suivi de l'exécution du code.

Cependant, l'effet secondaire indésirable est que vous devez vous rappeler de supprimer ces tables une fois que vous avez terminé votre analyse ; sinon, ces tables restent dans la base de données, créant ainsi des problèmes de gestion de la base de données.

L'alternative à la création de plusieurs tables est d'utiliser la clause WITH en SQL.

Syntaxe

La syntaxe de base de la clause WITH est la suivante:

L'utilisation de la clause WITH est très similaire à la création de tables. Lorsque vous créez une table, vous lui donnez un nom. De la même manière, lorsque vous utilisez la clause WITH, vous lui donnez également un nom, et ce nom agit essentiellement comme un nom de table dans la déclaration SQL principale.

Étant donné que WITH ne crée ni table ni vue, l'objet associé à l'instruction WITH disparaît après l'exécution de l'instruction SQL principale, et il n'y a rien à nettoyer.

Vous pouvez également avoir plusieurs clauses WITH. La syntaxe est la suivante :

Pour avoir plusieurs clauses WITH, vous n'avez pas besoin de spécifier WITH plusieurs fois. Au lieu de cela, après que la première clause WITH est terminée, ajoutez une virgule, puis vous pouvez spécifier la clause suivante en commençant par <nom_de_requête> suivi de COMME. Il n'y a pas de virgule entre la dernière clause WITH et la requête SQL principale.

Exemples

Nous utilisons la table suivante dans nos exemples.

Table Store_Sales

Store_Name	Sales
Los Angeles	15000
San Diego	2500
New York	3000
Boston	7000

Exemple 1 : Utilisation de WITH dans une instruction SELECT

Supposons que nous voulons répertorier tous les magasins ayant un chiffre d'affaires supérieur à la moyenne. Pour ce faire, nous pouvons utiliser la déclaration WITH suivante :

Résultat:

La déclaration WITH calcule le montant moyen des ventes (qui est de 6 875 $), puis la requête SQL principale retourne simplement toutes les lignes où la valeur de la colonne Ventes est supérieure à cette moyenne.

Un SQL équivalent utilisant une vue en ligne serait:

De même, cela peut être réalisé via le SQL suivant en utilisant une sous-requête:

Notez que la version avec sous-requête ne fonctionne pas dans Hive SQL en raison de la manière dont Hive SQL prend en charge les sous-requêtes.

Exemple 2 : Utilisation de WITH dans une instruction CREATE TABLE

Nous pouvons également utiliser WITH avec une instruction CREATE TABLE. Disons que nous voulons créer une table en utilisant la clause WITH de l'exemple précédent, nous entrerions :

La partie WITH de la requête intervient après CREATE TABLE AS. Ne placez pas la clause WITH au début. La requête ci-dessous générera une erreur: