Qu'est-ce que le SQL avancé ?

Les compétences SQL avancées vous laissent-elles perplexes ? Quelles sont-elles ? Cet article explique ce que peut signifier le SQL avancé, notamment tel que nous l'utilisons sur LearnSQL.fr.

Je suis sûr que vous rencontrez très souvent les expressions " compétences SQL avancées " ou " sujets SQL avancés ". Vous lisez un article sur le SQL avancé et vous êtes satisfait de la facilité avec laquelle ces sujets avancés semblent être abordés. Puis vous discutez avec quelqu'un et vous voyez qu'il considère tout ce que vous savez comme des connaissances SQL de base. Comment vous définissez-vous ? Vous considérez-vous comme un utilisateur de SQL de base, intermédiaire ou avancé ?

SQL avancé C'est partout

SQL avancé est partout. En tout cas, le mot "avancé" l'est. Il est utilisé très couramment par les apprenants et les utilisateurs de SQL. Vous pouvez le trouver dans les descriptions de cours SQL, dans les offres d'emploi et dans les questions d'entretien d'embauche. Il est présent dans la littérature SQL. Vous l'entendez lorsque des collègues discutent au travail. Il est présent dans de nombreux articles qui tentent de définir ce qu'est le SQL avancé.

En fait, je n'essaie pas de vous donner une définition du SQL avancé. J'essaie de vous dire autre chose : il n'y a pas de définition unique du SQL avancé ! Et vous devriez arrêter de la chercher. Que devriez-vous faire à la place ?

Accepter l'incohérence

C'est bien cela ! Vous devez accepter que le terme "SQL avancé" soit utilisé de manière incohérente. Sa signification varie en fonction du contexte et de la personne qui l'utilise.

Il est tout à fait logique que le SQL avancé comprenne une chose pour quelqu'un qui rédige des rapports SQL et quelque chose de totalement différent pour quelqu'un qui engage un analyste de données. Un développeur de logiciels aura sûrement une autre définition de ce que comprend le SQL avancé.

Vous voyez le tableau. SQL avancé ne peut pas avoir une seule définition. Lorsque vous lisez des articles sur les compétences SQL avancées, vous devez toujours tenir compte du contexte, de la personne qui parle et de son public.

Que pourrait inclure SQL avancé?

Par exemple, il y a une discussion très intéressante sur le SQL avancé sur Stack Overflow.

La discussion a été lancée par une personne à la recherche d'un emploi dans le domaine du SQL, qui a remarqué qu'il existe de nombreux emplois nécessitant des "connaissances avancées en SQL". L'utilisateur demande ce qu'il faut attendre de ce type d'emploi. Quelles connaissances sont considérées comme avancées ?

La première réponse donne un extrait de code assez long comme mesure des connaissances avancées. Même s'il est assez long, il n'est pas si compliqué. Selon cette réponse, le SQL avancé couvre la sélection de colonnes, les fonctions d'agrégation comme MIN() et MAX(), l'instruction CASE WHEN, JOINs, la clause WHERE, GROUP BY, la déclaration de variables et les sous-requêtes.

En revanche, la réponse suivante considère que la plupart de ces sujets sont au mieux basiques ou intermédiaires. Cet utilisateur estime que les sujets SQL avancés comprennent les fonctions, les procédures stockées, les requêtes hiérarchiques, les déclencheurs, les indices, la modélisation des données (formes normales, clés primaires et étrangères, contraintes de table), les transactions et bien plus encore. Ceci est beaucoup plus proche de ma définition du SQL avancé et de ce que j'ai appris dans les cours de SQL. Cependant, il s'agissait d'un programme destiné aux administrateurs de bases de données ; il est compréhensible que ces connaissances soient considérées comme avancées. Certains spécialistes du reporting et analystes de données n'auront peut-être jamais besoin d'utiliser de telles choses.

Il est intéressant de noter que, parfois, le site JOINs est considéré comme avancé alors que l'écriture de procédures stockées est encore considérée comme une connaissance de base. Je peux comprendre pourquoi car un utilisateur fait allusion au problème avec JOINs. Même si elles sont généralement considérées comme des connaissances de base, de nombreux utilisateurs de SQL apprennent des sujets beaucoup plus avancés avant de vraiment comprendre JOINs. C'est ainsi que les connaissances de base deviennent facilement des connaissances avancées. Il n'est pas rare de trouver quelqu'un qui utilise des fonctions flashy, des triggers, etc., sans savoir comment écrire un simple JOIN.

Qu'est-ce que SQL avancé à LearnSQL.com ?

Avant d'expliquer ce qu'est le SQL avancé, il est essentiel de savoir ce qu'il n'est pas. Lorsque vous regardez nos cours et articles, le SQL de base/intermédiaire est tout ce qui est en SQL-92. (Voici l'histoire et les détails des normes SQL si vous êtes intéressé à en savoir plus). Cela inclut :

• Tous les types de JOINs
• Fonctions d'agrégation
• GROUP BY
• HAVING
• Les sous-requêtes
• Les opérations sur les ensembles (UNION, UNION ALL, INTERSECT, MINUS)

Vous devez être familier avec ces sujets si vous prétendez connaître SQL. Ce sont des choses que vous devez comprendre avant de passer à des sujets plus avancés.

En général, nous considérons trois sujets comme du "SQL avancé" :

• Les fonctions de fenêtre
• Expressions de table communes (CTE)
• GROUP BY les extensions (ROLLUP, CUBE et GROUPING SETS).

Toute personne souhaitant apprendre (ou pratiquer) ces trois sujets devrait consulter notre piste SQL avancé . Bien entendu, il ne s'agit pas du seul cours de SQL avancé disponible ; nous avons déjà passé en revue d'excellents cours de SQL avancé d'autres plateformes. Pour l'instant, examinons un exemple de chacun de ces sujets.

Fonctions de fenêtrage

Les fonctions de fenêtre SQL vous permettent d'effectuer des opérations qui sont souvent nécessaires pour créer des rapports, par exemple classer des données, calculer des totaux courants et des moyennes mobiles, trouver la différence entre les lignes, etc. En outre, vous pouvez également diviser les données en fenêtres, ce qui vous permet d'effectuer des opérations sur des sous-ensembles de données plutôt que sur les données dans leur ensemble. Vous pouvez en apprendre davantage à ce sujet dans notre cours Fonctions de fenêtrage .

Voyons un exemple. Ce code montre la différence entre les nombres annuels de voitures vendues, en fonction de la marque (c'est-à-dire la marque de voiture) :

SELECT	car_make,
		cars_sold,
		year,
		cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff
FROM cars_sale;

Pour obtenir cette information, vous devez d'abord sélectionner les colonnes que vous voulez dans le résultat : car_make, cars_sold, year. Pour obtenir la différence annuelle, soustrayez la vente de l'année précédente de celle de l'année en cours : cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff. Ici, cars_sold signifie les ventes de l'année en cours. La fonction LAG() vous permet d'extraire des données de la ligne précédente. La clause OVER indique qu'il s'agit d'une fonction de fenêtre. Vient ensuite la clause PARTITION BY, qui sert à définir la fenêtre (sous-ensemble de données) que nous voulons utiliser. Dans ce cas, il s'agit de car_make; cela signifie que la fonction calculera la différence de vente uniquement pour une marque de voiture spécifique. Lorsqu'elle rencontrera une autre marque de voiture, la fonction se réinitialisera et recommencera à calculer la différence de vente.

Enfin, l'opération est classée par année en ordre croissant. Pourquoi ? La fonction LAG() nous permet d'obtenir les données de la ligne précédente. Ainsi, si cette opération est effectuée pour chaque année dans l'ordre croissant, l'"année précédente" signifiera la ligne précédente. C'est exactement ce dont nous avons besoin.

Jetez un coup d'oeil au résultat :

Vous avez vu la valeur NULL dans la première ligne ? C'est parce que 2015 est la première année ; il n'y a pas de données précédentes qui peuvent en être déduites. Si vous suivez les résultats, vous verrez que chaque ligne correspond à la différence entre les ventes annuelles de la ligne actuelle et celles de la ligne précédente. Lorsque vous atteignez la ligne où Renault commence, il y a à nouveau un NULL. C'est ce que font les fonctions de fenêtre : elles travaillent sur des données situées dans une fenêtre donnée. J'ai défini la fenêtre en fonction de car_make, de sorte que la fonction de fenêtre se réinitialise lorsque nous obtenons une nouvelle valeur dans cette colonne. C'est logique. Pourquoi déduirais-je les ventes de Renault des ventes de Nissan ? Je veux le faire pour chaque marque de voiture séparément.

Expressions de table communes (CTE)

Les CTE vous permettront d'écrire des requêtes complexes sans utiliser de sous-requêtes, ce qui vous permettra de conserver un code simple et direct. Ils vous donnent la possibilité de produire des rapports complexes rapidement et efficacement. Elles vous permettent également d'effectuer certains calculs que vous ne pourriez pas faire autrement.

Vous vous demandez peut-être ce qu'est une expression de table commune ? Il s'agit d'un résultat temporaire que vous pouvez utiliser dans l'instruction SELECT. Il fonctionne comme une table temporaire - vous pouvez le joindre à d'autres tables, à d'autres CTE ou à lui-même.

Ils peuvent être utiles si vous devez, par exemple, établir un rapport sur le temps passé sur un projet particulier. D'un côté, il y a une table contenant des données sur la date à laquelle chaque employé a travaillé sur ce projet. Il y a également l'heure de début et l'heure de fin. De l'autre côté, il y a un tableau contenant les noms des employés. Vous devez produire un tableau indiquant le nom de chaque employé et son temps moyen passé sur ce projet.

Voici comment le CTE peut vous aider :

WITH time_worked AS (
	SELECT	employee_id,
			end_time - start_time AS time
FROM project_timesheet
)
SELECT	e.first_name,
		e.last_name,
		AVG (tw.time) AS avg_time_worked
FROM employee e 
LEFT JOIN time_worked tw 
ON e.id = tw.employee_id
GROUP BY e.first_name, e.last_name;

Comment fonctionne ce CTE ? Chaque CTE s'ouvre par la clause WITH. Ensuite, vous devez donner un nom à votre CTE ; dans ce cas, il s'agit de time_worked. Ensuite, vous écrivez une instruction SELECT. Ici, je vais utiliser le CTE pour calculer le temps de travail de chaque employé à chaque fois qu'il a travaillé sur le projet. J'ai besoin du CTE parce que cette information n'est pas indiquée explicitement dans la table ; je n'ai que start_time et end_time. Pour calculer le temps moyen travaillé, la première étape consiste à obtenir le temps travaillé. C'est pourquoi ce CTE déduit le start_time du end_time et affiche le résultat dans la colonne time. Les données sont extraites du tableau project_timesheet.

Maintenant que j'ai écrit le CTE, je peux l'utiliser dans la prochaine déclaration SELECT. Tout d'abord, je vais récupérer le prénom et le nom de famille dans le tableau. employee. Ensuite, je vais utiliser la fonction AVG() sur la colonne time du CTE time_worked. Pour ce faire, j'ai utilisé la fonction LEFT JOIN - et je l'ai utilisée exactement comme je le ferais avec n'importe quelle autre table. Enfin, les données sont regroupées par nom et prénom des employés.

Le résultat est un petit tableau comme celui-ci :

Si les CTE vous intéressent, imaginez ce que vous serez en mesure de faire après avoir suivi notre cours sur Requêtes récursives . Ah oui, je n'ai pas mentionné qu'un CTE peut être récursif, ce qui signifie qu'il se référence lui-même. Ce faisant, il renvoie le sous-résultat et répète le processus jusqu'à ce qu'il renvoie le résultat final. Si les CTE peuvent être non récursifs, il n'existe pas de requêtes récursives qui ne soient pas des CTE. Si vous voulez apprendre les requêtes récursives, il est indispensable de connaître les CTE.

Extensions GROUP BY

Les extensions de SQL GROUP BY vous offrent des possibilités supplémentaires de regroupement des données. En retour, cela peut augmenter la complexité de votre analyse de données et des rapports que vous créez.

Il existe trois extensions GROUP BY:

• ROLLUP
• CUBE
• GROUPING SETS

Contrairement à GROUP BY, ROLLUP vous permet de regrouper les données en plusieurs ensembles de données et d'agréger les résultats à différents niveaux. En termes simples, vous pouvez utiliser ROLLUP pour calculer des totaux et des sous-totaux, comme dans les tableaux croisés dynamiques d'Excel.

L'extension CUBE est similaire, mais il y a une différence cruciale. CUBE génère des sous-totaux pour chaque combinaison des colonnes spécifiées.

Enfin, il y a GROUPING SETs. Un ensemble de regroupement est un ensemble de colonnes que vous utilisez dans la clause GROUP BY. Vous pouvez connecter différentes requêtes contenant GROUP BY si vous utilisez UNION ALL. Toutefois, plus le nombre de requêtes est élevé, plus la situation devient compliquée. Vous pouvez obtenir le même résultat, mais avec des requêtes beaucoup plus soignées, en utilisant GROUPING SETS.

Laissez-moi vous montrer comment fonctionne ROLLUP. Supposons que vous travaillez pour un magasin de guitares qui possède plusieurs sites. Vous aurez parfois besoin de créer un rapport indiquant le nombre total de guitares que vous avez en stock. Voici une requête qui vous permettra de le faire au niveau du fabricant, du modèle et du magasin :

SELECT	manufacturer,
		model,
		store,
		SUM(quantity) AS quantity_sum
FROM guitars
GROUP BY ROLLUP (manufacturer, model, store)
ORDER BY manufacturer;

Cela n'a pas l'air compliqué. Il s'agit d'une simple instruction SELECT qui vous donnera les colonnes manufacturer, model, et store de la table guitars. J'ai utilisé la fonction d'agrégation SUM() pour obtenir la quantité. Puis j'ai écrit GROUP BY suivi immédiatement de ROLLUP. Les données seront regroupées selon les colonnes entre parenthèses. Enfin, le résultat est ordonné par le fabricant.

Que retournera cette requête ? Jetez-y un coup d'oeil :

Il devrait être plus facile de comprendre ce que j'entends par différents niveaux de regroupement. Un petit conseil avant de continuer : Chaque fois que vous voyez une valeur NULL, il s'agit d'un sous-total. Jetons un coup d'œil au tableau. D'abord, il y a 9 Fender Jazzmasters à Amsterdam. Ensuite, il y a 32 Fender Jazzmasters à New York. La quantité totale est de 41, ce qui est indiqué dans la ligne :

La valeur NULL signifie que les données sont regroupées au niveau du magasin. Le résultat est le suivant : "il y a 41 Fender Jazzmasters au total, à la fois à New York et à Amsterdam". Le même calcul est effectué pour tous les autres modèles Fender, c'est-à-dire Stratocaster et Telecaster. Ensuite, il y a cette ligne :

Qu'est-ce que ça veut dire ? Cela signifie qu'il y a au total 592 Fender des trois modèles dans les deux magasins.

Le même principe s'applique à Gibson. La quantité de guitares à Amsterdam et à New York est d'abord indiquée pour le modèle. Ensuite, on fait un sous-total en additionnant les quantités des deux magasins. Ceci est fait pour les trois modèles Gibson : ES-335, Les Paul, et SG. Ensuite, il y a une ligne qui montre le nombre total des trois modèles de guitares Gibson dans les deux magasins (comme pour les Fender) :

Enfin, il y a une ligne montrant le nombre total de guitares, quel que soit le magasin, le fabricant de guitare ou le modèle :

Je suis sûr que vous voulez maintenant savoir comment fonctionnent les CUBE et GROUPING SETS. Pour cela, je vous recommande de jeter un coup d'œil au cours sur les extensions GROUP BY.

Ces sujets avancés sont quelque chose que les analystes de données utiliseront très souvent. J'ai donc préparé quelques constructions SQL pour mes collègues analystes de données. Si vous vous intéressez à la finance, voici quelques requêtes SQL avancées pour l'analyse financière.

Vous considérez-vous comme un utilisateur de SQL avancé?

Comment vous sentez-vous maintenant ? Je vous ai mis en confiance ? Si vous connaissez déjà les fonctions de fenêtre SQL, les CTE et les extensions GROUP BY, vous pouvez vous vanter de vos compétences SQL avancées.

Ou peut-être ai-je fait exactement le contraire ? Peut-être ai-je ébranlé votre confiance en réalisant que vous ne connaissez rien aux sujets avancés dont j'ai parlé dans cet article.

Ne vous inquiétez pas ! Quel que soit le groupe auquel vous appartenez, il existe LearnSQL.fr cours qui vous aideront à renforcer vos connaissances et vos compétences. Vous voulez apprendre les fonctions des fenêtres ? Aucun problème - consultez notre cours Fonctions de fenêtrage . Les CTE vous intéressent ? Vous pouvez les apprendre et les pratiquer dans notre cours Requêtes récursives . Vous souhaitez tirer le meilleur parti de GROUP BY ? Notre cours GROUP BY Extensions in SQL est fait pour vous.