Tableaux croisés dynamiques SQL : Tutoriel étape par étape

Vous êtes un analyste de données et vous souhaitez améliorer vos compétences ? Ou peut-être souhaitez-vous découvrir les outils dont vous avez besoin pour réussir dans ce rôle ? Aujourd'hui, nous allons nous pencher sur l'un des outils les plus importants pour les analystes de données : le tableau croisé dynamique SQL.

Un tableau croisé dynamique SQL est un outil essentiel pour organiser et résumer rapidement des données. En quelques lignes de code seulement, il aide les analystes à identifier rapidement et efficacement les modèles et les tendances dans les données. Pour ce faire, il suffit de faire pivoter ou tourner les lignes et les colonnes du tableau et d'appliquer un calcul agrégé aux données sous-jacentes.

Si vous êtes novice en matière de création de rapports SQL, consultez notre Création de rapports basiques en SQL cours. Vous apprendrez à écrire des requêtes complexes en SQL. Parmi les nombreuses fonctions enseignées dans ce cours figure la syntaxe CASE WHEN, essentielle pour construire des tableaux croisés dynamiques.

Avant d'aborder les tableaux croisés dynamiques, n'oubliez pas de conserver notre Aide-mémoire SQL pour l'analyse des données à portée de main. Vous pourrez l'utiliser pour vous rafraîchir la mémoire sur certaines des fonctions que nous allons aborder.

Comment les tableaux croisés dynamiques SQL fonctionnent-ils en analyse de données ?

Les tableaux croisés dynamiques SQL sont un outil utile pour réorganiser, résumer et analyser rapidement de grandes quantités d'informations. Vous avez peut-être déjà travaillé avec des outils tels que Microsoft Excel et Google Sheets, qui intègrent la fonctionnalité de tableau croisé dynamique, ce qui vous permet d'effectuer facilement de telles transformations.

Prenons un exemple. Imaginez que vous disposiez d'un tableau affichant les données relatives à la diffusion en continu de la musique d'un artiste sur plusieurs plateformes musicales et dans plusieurs pays.

Tableau :

La présentation de ces données rend les comparaisons difficiles, en particulier lorsque le nombre de pays et de plateformes augmente.

Imaginons maintenant que nous puissions structurer les données de manière à ce que les flux de chaque pays soient ventilés par plateforme et totalisés collectivement. Nous pourrions alors répondre à des questions telles que "Quelle est la plateforme la plus populaire dans chaque pays ?" et "Sur quelle plateforme et quel pays devrions-nous concentrer nos efforts ?

Résultat :

Le tableau nouvellement formaté facilite grandement la comparaison des flux de l'artiste entre les plateformes et les pays. Vous pouvez maintenant voir en un coup d'œil quelle plateforme fonctionne le mieux dans chaque pays - Spotify en France et en Irlande, et SoundCloud en Allemagne. Le nouveau format permet d'accélérer l'analyse et la compréhension, en affichant les performances géographiques et spécifiques à chaque plateforme sans avoir à fouiller manuellement dans les lignes de données.

Les ingrédients d'un tableau croisé dynamique SQL

Commençons par décomposer les éléments constitutifs du tableau croisé dynamique en les définissant à l'aide d'un exemple. Nous utiliserons le même tableau que dans la section précédente pour illustrer notre propos :

• Identifiant de ligne : Il s'agit du premier ingrédient ; c'est une colonne sélectionnée dans votre ensemble de données pour représenter les lignes du tableau croisé dynamique (c'est-à-dire les enregistrements individuels). Dans l'exemple ci-dessus, nous avons utilisé la colonne platform comme identifiant de ligne, de sorte que chaque ligne contiendra les détails d'une plate-forme.
• Identifiant de colonne : Le deuxième ingrédient est une colonne qui est sélectionnée pour être la partie verticale du tableau croisé dynamique. Dans notre exemple, nous avons utilisé la colonne country comme identifiant de colonne ; les pays seront répertoriés horizontalement, comme des en-têtes en haut du tableau de résultats.
• Agrégation : Maintenant que nous disposons des parties verticale et horizontale de notre tableau, l'agrégation est l'ingrédient qui détermine comment les données sous-jacentes de l'identifiant de ligne et de colonne interagissent. Les fonctions d'agrégation telles que SUM(), AVG() et MAX() sont couramment utilisées. Dans notre exemple, nous avons utilisé SUM() pour calculer le nombre total de flux pour chaque plateforme dans chacun des pays.
• Tableau croisé dynamique : Le tableau croisé dynamique est la dernière étape. Il résume les données saisies en faisant pivoter les lignes en colonnes à l'aide d'une fonction d'agrégation. En résumant efficacement les données, le tableau croisé dynamique nous aide à visualiser ce que nous analysons sous une forme plus structurée. Dans l'exemple ci-dessus, nous voyons que le résultat final de nos entrées affiche les flux pour chaque plateforme dans les pays énumérés dans notre tableau original.

Tableaux croisés dynamiques natifs

Selon la base de données que vous utilisez, il se peut qu'elle dispose d'un tableau croisé dynamique natif . Cela signifie essentiellement qu'il existe une fonction intégrée que la base de données utilise pour créer des tableaux croisés dynamiques SQL. SQL Server est un excellent exemple de base de données offrant une fonction PIVOT().

Construisons un tableau croisé dynamique à l'aide de la fonction SQL Server PIVOT() pour résumer des données en continu. Les données que nous utiliserons sont stockées dans un tableau appelé streams_table:

Query :

SELECT platform, France, Ireland, Germany
FROM
(
    SELECT platform, country, streams
    FROM streams_table
) AS SourceTable
PIVOT
(
    SUM(streams)
    FOR country IN (France, Ireland, Germany)
) AS PivotTable;

Décomposons cette requête. Nous commencerons par la sous-requête.

1. Définir le tableau source

SELECT platform, country, streams
FROM streams_table

Les lignes 4 à 6 (immédiatement après la parenthèse ouverte) définissent le site SourceTable à l'aide d'une sous-requête. La table source comporte trois colonnes indiquées dans l'instruction SELECT (platform, country et streams) qui proviennent de l'instruction initiale streams_table.

2. Remodeler les données à l'aide d'opérations pivotantes

SUM(streams)
FOR country IN (France, Ireland, Germany)

Ensuite - entre les parenthèses de la fonction PIVOT() - nous définissons notre PivotTable en appliquant la fonction d'agrégation SUM() à la colonne streams. Cette fonction additionne le nombre de flux pour chaque plateforme. Nous spécifions ensuite la colonne sur laquelle pivoter : country.

Notez que les valeurs des pays sont données sans guillemets. Notez également que dans SQL Server, les noms de colonnes qui contiennent des espaces ou des caractères spéciaux ou qui commencent par un nombre doivent être placés entre crochets. Si nous avions une colonne contenant les années, elle ressemblerait à ceci :

SUM(streams)
FOR stream_year IN ([2022], [2023])

3. Sélection des colonnes finales

SELECT platform, France, Ireland, Germany

Enfin, nous retournons au début de la requête et sélectionnons les colonnes pour notre sortie. Cela inclut la colonne platform et les colonnes nouvellement formées pour chaque pays.

Résultat :

Tous les langages SQL ne disposent pas de fonctions PIVOT() intégrées. Lorsque cette fonction n'est pas disponible dans votre base de données, il existe une autre technique simple que nous pouvons appliquer pour la contourner. Explorons-la.

Création de tableaux croisés dynamiques SQL sans tables PIVOT() natives

Comme nous l'avons déjà mentionné, toutes les bases de données, y compris les plus connues comme MySQL et PostgreSQL, ne disposent pas de capacités de pivot natives. Vous vous souvenez peut-être que nous avons abordé la syntaxe CASE WHEN dans notre introduction. En utilisant cette syntaxe, nous pouvons obtenir exactement le même résultat !

MySQL et PostgreSQL étant des bases de données très répandues, la technique CASE WHEN est une méthode largement utilisée pour créer des tableaux croisés dynamiques. Voyons quelques exemples :

Exemple 1 : pivoter des données en continu

Nous utiliserons le même exemple pour montrer comment l'approche CASE WHEN peut produire les mêmes résultats :

Requête :

SELECT platform,
    SUM(CASE WHEN country = 'France' THEN streams END) AS france_streams,
    SUM(CASE WHEN country = 'Ireland' THEN streams END) AS ireland_streams,
    SUM(CASE WHEN country = 'Germany' THEN streams END) AS germany_streams
FROM streams_table
GROUP BY platform;

Les tableaux croisés dynamiques SQL qui en résultent indiquent le nombre total de flux par plate-forme pour les pays énumérés.

• Identifiant de ligne : Étant donné que nous sélectionnons la colonne platform, chaque plateforme apparaît comme une colonne dans le résultat. Vous remarquerez également que nous incluons platform dans notre GROUP BY pour séparer nos plateformes en groupes.
• Identifiant de colonne : Pour spécifier les pays dans les colonnes verticales, nous utilisons la structure CASE WHEN country = 'France'. Si le flux provient de France, l'instruction CASE WHEN renvoie les flux. Il est ensuite additionné à l'aide de la fonction SUM(). Si le flux ne provient pas de France, l'instruction CASE WHEN renvoie NULL et les valeurs ne sont pas prises en compte dans la somme. La requête passe alors à l'instruction CASE WHEN suivante. Voir notre article sur l'utilisation de CASE WHEN dans GROUP BY pour une explication plus détaillée.
• Agrégation : Comme précédemment, nous utilisons SUM() comme fonction d'agrégation. La logique de notre instruction de cas stipule que les flux sont accumulés si le pays est le même que le pays donné ; sinon, rien n'est accumulé.

Utilisons le même tableau pour la démonstration, mais inversons les identifiants des lignes et des colonnes et voyons ce qui se passe. T

Requête :

SELECT country,
    SUM(CASE WHEN platform = 'Apple Music' THEN streams END) AS apple_music_streams,
    SUM(CASE WHEN platform = 'SoundCloud' THEN streams END) AS soundcloud_streams,
    SUM(CASE WHEN platform = 'Spotify' THEN streams END) AS spotify_streams
FROM streams_table
GROUP BY country;

Résultat :

Comme vous pouvez le constater, le résultat de l'agrégation reste le même. Le seul changement réside dans la manière dont les données sont affichées :

• Identifiant de ligne : Cette fois-ci, nous utilisons country comme identifiant de ligne, de sorte que chaque pays apparaît comme une ligne horizontale dans le résultat. Nous incluons également country dans la clause GROUP BY pour nous assurer que les résultats sont correctement regroupés par pays, ce qui donne des lignes distinctes pour le total des données en continu de chaque pays.
• Identifiant de colonne : Nous avons modifié notre identification de colonne en plateforme, de sorte que les plateformes apparaissent sous forme de colonnes verticales dans le résultat. Cette modification est effectuée à l'aide de l'option SUM(CASE WHEN platform =...)
• Agrégation : Comme pour la dernière requête, les résultats numériques sont identiques car nous avons utilisé la même méthode d'agrégation (SUM()).

Pour plus d'exemples de l'agrégation CASE WHEN avec SUM(), consultez notre article Comment utiliser CASE WHEN avec SUM() en SQL.

Exemple 2 : Pivoter les données des clients

Voyons maintenant comment les tableaux croisés dynamiques peuvent nous aider à découvrir des segments de marché cibles.

Le tableau croisé dynamique customer_table décrit les données de vente des clients avec quatre colonnes : customer, age_category, country et purchases:

Requête :

SELECT age_category,
    SUM(CASE WHEN country = 'France' THEN purchases END) AS France,
    SUM(CASE WHEN country = 'Spain' THEN purchases END) AS Spain,
    SUM(CASE WHEN country = 'Italy' THEN purchases END) AS Italy
FROM customer_table
GROUP BY age_category;

Résultat :

Les tableaux croisés dynamiques SQL résultants montrent les achats totaux par catégorie d'âge en France, en Espagne et en Italie où :

• L'identifiant de la ligne est la colonne age_category.
• L'identifiant de la colonne est country, spécifiant France, Spain et Italy.
• La fonction d'agrégation est SUM().

Exemple 3 : données d'opérations pivotantes

Voyons maintenant un exemple de la façon dont l'utilisation de tableaux croisés dynamiques SQL peut permettre de découvrir des goulets d'étranglement ou des erreurs potentielles dans les opérations.

Le tableau ci-dessous, cake_baking_datadécrit les données d'une boulangerie à l'aide de quatre colonnes : order_number, stage, order_day et mins_taken:

Tableau :

Supposons que nous souhaitions connaître la durée moyenne de chaque tâche pour chaque jour. Voici la requête que nous utiliserions.

Requête :

SELECT 
    order_day,
    AVG(CASE WHEN stage = 'Mixing' THEN mins_taken END) AS avg_mixing_time,
    AVG(CASE WHEN stage = 'Baking' THEN mins_taken END) AS avg_baking_time,
    AVG(CASE WHEN stage = 'Decorating' THEN mins_taken END) AS avg_decorating_time
FROM cake_baking_data
GROUP BY order_day;

Résultat :

Le tableau croisé dynamique qui en résulte montre les temps moyens par jour de commande pour le mélange, la cuisson et la décoration :

• L'identificateur de ligne est la colonne order_day.
• L'identificateur de colonne est stage, en spécifiant mixing, baking, ou decorating.
• La fonction d'agrégation est AVG().

Comme vous pouvez le constater, la solution de contournement CASE WHEN vous permet de rester dans les bases de données populaires MySQL et PostgreSQL et d'obtenir les mêmes tableaux croisés dynamiques SQL.

Un dernier mot sur les tableaux croisés dynamiques SQL

En résumé, il est clair que les tableaux croisés dynamiques SQL sont si importants. La possibilité de pivoter les données est un outil important dans votre boîte à outils d'analyse de données. La possibilité de transformer et de résumer rapidement et efficacement des ensembles de données vous permet de découvrir des tendances et de prendre des décisions plus judicieuses.

Dans cet article, nous nous sommes principalement concentrés sur le pivotement pour les analystes de données. Si une carrière dans l'analyse de données vous intéresse, je vous recommande de consulter notre article 25 questions d'entretien SQL pour les analystes de données. Il vous aidera à comprendre ce que vous devez savoir pour réussir dans ce domaine.

La meilleure façon d'améliorer votre SQL est de pratiquer, pratiquer, pratiquer ! Nous recommandons notre cours Création de rapports basiques en SQL Vous pourrez non seulement mettre en pratique ce que nous avons appris aujourd'hui, mais aussi apprendre à créer des rapports pertinents. Jetez-y un coup d'œil et continuez à développer vos compétences en matière de tableaux croisés dynamiques SQL !