Analyse de données avec les fonctions de fenêtre SQLite

Introduction

Dans ce laboratoire, vous explorerez l'analyse de fenêtrage (window analytics) SQLite, en vous concentrant sur le classement des lignes (ranking rows) et le calcul des totaux cumulés (running totals). Vous apprendrez à utiliser les fonctions de fenêtrage (window functions) pour effectuer des calculs sur des ensembles de lignes qui sont liées à la ligne actuelle.

Plus précisément, vous utiliserez la fonction ROW_NUMBER() pour attribuer un rang unique à chaque ligne en fonction du montant des ventes. Vous apprendrez également à calculer les totaux cumulés et à partitionner les données pour une analyse plus avancée. Ce laboratoire fournit une introduction pratique aux fonctions de fenêtrage dans SQLite.

Skills Graph

Créer une base de données et une table de ventes

Dans cette première étape, vous allez créer une base de données SQLite nommée sales.db et une table nommée sales pour stocker les données de ventes. Cette table comprendra des colonnes pour l'ID du produit, le nom du produit et le montant des ventes.

Ouvrez votre terminal dans la VM LabEx. Votre chemin d'accès par défaut est /home/labex/project.

Pour commencer, créez la base de données sales.db et ouvrez l'outil de ligne de commande SQLite en exécutant la commande suivante :

sqlite3 sales.db

Cette commande crée le fichier de base de données et ouvre l'interpréteur (shell) SQLite, où vous pouvez exécuter des commandes SQL. Vous verrez une invite de commande comme celle-ci :

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Ensuite, créez la table sales avec les colonnes suivantes : product_id, product_name et sales_amount. Entrez la commande SQL suivante à l'invite sqlite> et appuyez sur Entrée :

CREATE TABLE sales (
    product_id INTEGER,
    product_name TEXT,
    sales_amount INTEGER
);

Cette commande configure la table sales où :

• product_id est un entier représentant l'identifiant unique de chaque produit.
• product_name est un champ texte stockant le nom du produit.
• sales_amount est un entier représentant le montant des ventes pour le produit.

Vous ne verrez aucune sortie si la commande s'exécute correctement.

Insérer des exemples de données dans la table Sales

Maintenant que vous avez créé la table sales, ajoutons-y des exemples de données. Nous allons insérer six enregistrements représentant différents produits et leurs montants de ventes.

Insérez les enregistrements suivants dans la table sales en exécutant ces commandes une par une à l'invite sqlite> :

INSERT INTO sales (product_id, product_name, sales_amount) VALUES
(1, 'Laptop', 1200),
(2, 'Keyboard', 75),
(3, 'Mouse', 25),
(4, 'Monitor', 300),
(5, 'Headphones', 100),
(6, 'Webcam', 50);

Ces commandes ajoutent six lignes à la table sales. Chaque ligne représente un produit avec son ID, son nom et son montant de ventes.

• INSERT INTO sales (product_id, product_name, sales_amount) spécifie que vous insérez des données dans les colonnes product_id, product_name et sales_amount de la table sales.
• VALUES (1, 'Laptop', 1200) fournit les valeurs à insérer pour chaque enregistrement.

Pour confirmer que les données ont été ajoutées correctement, exécutez cette commande pour afficher tous les enregistrements de la table :

SELECT * FROM sales;

Résultat attendu :

1|Laptop|1200
2|Keyboard|75
3|Mouse|25
4|Monitor|300
5|Headphones|100
6|Webcam|50

Cette sortie affiche le product_id, le product_name et le sales_amount pour chaque enregistrement. La commande SELECT * récupère toutes les colonnes de la table spécifiée.

Classer les lignes avec ROW_NUMBER()

Dans cette étape, vous apprendrez à utiliser la fonction de fenêtre ROW_NUMBER() pour attribuer un rang unique à chaque ligne en fonction du sales_amount (montant des ventes). Ceci est utile pour identifier les produits les plus vendus.

La fonction ROW_NUMBER() attribue un entier unique à chaque ligne dans une partition d'un ensemble de résultats. Le rang est déterminé par l'ordre spécifié dans la clause ORDER BY.

Exécutez la requête suivante à l'invite sqlite> :

SELECT
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales;

Cette requête calcule le rang de chaque produit en fonction de son sales_amount par ordre décroissant.

• ROW_NUMBER() OVER (ORDER BY sales_amount DESC) attribue un rang à chaque ligne en fonction du sales_amount, le montant des ventes le plus élevé recevant un rang de 1.
• ORDER BY sales_amount DESC spécifie que le classement doit être basé sur le sales_amount par ordre décroissant.

Résultat attendu :

Laptop|1200|1
Monitor|300|2
Headphones|100|3
Keyboard|75|4
Webcam|50|5
Mouse|25|6

Comme vous pouvez le constater, la colonne sales_rank contient maintenant le rang de chaque produit en fonction de son sales_amount, le montant des ventes le plus élevé (Laptop) recevant un rang de 1.

Calculer les totaux cumulés (Running Totals)

Dans cette étape, vous apprendrez à calculer les totaux cumulés (sommes cumulatives) en utilisant les fonctions de fenêtre. Les totaux cumulés sont utiles pour suivre la somme des valeurs sur une période de temps ou sur un ensemble de lignes.

Pour calculer les totaux cumulés, vous utilisez la fonction SUM() avec la clause OVER() et une clause ORDER BY pour spécifier l'ordre dans lequel la somme est calculée.

Tout d'abord, ajoutons une colonne sale_date (date de vente) à la table sales et remplissons-la avec des exemples de dates. Exécutez les commandes suivantes à l'invite sqlite> :

ALTER TABLE sales ADD COLUMN sale_date DATE;

UPDATE sales SET sale_date = '2023-01-01' WHERE product_name = 'Laptop';
UPDATE sales SET sale_date = '2023-01-05' WHERE product_name = 'Keyboard';
UPDATE sales SET sale_date = '2023-01-10' WHERE product_name = 'Mouse';
UPDATE sales SET sale_date = '2023-01-15' WHERE product_name = 'Monitor';
UPDATE sales SET sale_date = '2023-01-20' WHERE product_name = 'Headphones';
UPDATE sales SET sale_date = '2023-01-25' WHERE product_name = 'Webcam';

Ces commandes ajoutent une colonne sale_date à la table sales et mettent à jour la table avec des exemples de dates pour chaque produit.

Maintenant, calculons le total cumulé de sales_amount au fil du temps, trié par sale_date. Exécutez la requête suivante :

SELECT
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM
    sales;

Cette requête calcule le total cumulé de sales_amount au fil du temps, trié par sale_date.

• SUM(sales_amount) OVER (ORDER BY sale_date) calcule la somme cumulative de sales_amount jusqu'à chaque sale_date.
• ORDER BY sale_date spécifie que le total cumulé doit être calculé en fonction de la sale_date par ordre croissant.

Résultat attendu :

2023-01-01|Laptop|1200|1200
2023-01-05|Keyboard|75|1275
2023-01-10|Mouse|25|1300
2023-01-15|Monitor|300|1600
2023-01-20|Headphones|100|1700
2023-01-25|Webcam|50|1750

La colonne running_total (total cumulé) affiche la somme cumulative de sales_amount jusqu'à chaque sale_date. Par exemple, le total cumulé au '2023-01-15' est de 1600, ce qui est la somme des ventes de '2023-01-01', '2023-01-05', '2023-01-10' et '2023-01-15'.

Partitionner les données pour l'analyse

Dans cette étape, vous apprendrez à partitionner les données en utilisant la clause PARTITION BY dans les fonctions de fenêtre. Le partitionnement vous permet de diviser vos données en groupes logiques, puis d'effectuer des calculs indépendamment dans chaque groupe.

Ajoutons une colonne product_category (catégorie de produit) à notre table sales. Exécutez les commandes suivantes à l'invite sqlite> :

ALTER TABLE sales ADD COLUMN product_category TEXT;

UPDATE sales SET product_category = 'Electronics' WHERE product_name IN ('Laptop', 'Monitor', 'Headphones', 'Webcam');
UPDATE sales SET product_category = 'Accessories' WHERE product_name IN ('Keyboard', 'Mouse');

Ces commandes ajoutent une colonne product_category à la table sales et mettent à jour la table avec des catégories pour chaque produit.

Maintenant, utilisons PARTITION BY pour calculer le total cumulé de sales_amount dans chaque product_category. Exécutez la requête suivante :

SELECT
    product_category,
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_total_by_category
FROM
    sales;

Cette requête calcule le total cumulé de sales_amount dans chaque product_category, trié par sale_date.

• PARTITION BY product_category divise les données en partitions en fonction de la product_category.
• SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) calcule la somme cumulative de sales_amount dans chaque product_category, triée par sale_date.

Résultat attendu :

Accessories|2023-01-05|Keyboard|75|75
Accessories|2023-01-10|Mouse|25|100
Electronics|2023-01-01|Laptop|1200|1200
Electronics|2023-01-15|Monitor|300|1500
Electronics|2023-01-20|Headphones|100|1600
Electronics|2023-01-25|Webcam|50|1650

La colonne running_total_by_category (total cumulé par catégorie) affiche la somme cumulative de sales_amount dans chaque product_category, triée par sale_date. Notez que le total cumulé redémarre pour chaque catégorie.

Résumé

Dans ce labo, vous avez appris à utiliser les fonctions de fenêtre (window functions) dans SQLite pour effectuer une analyse de données avancée. Vous avez commencé par créer une table sales et à insérer des exemples de données. Ensuite, vous avez utilisé la fonction ROW_NUMBER() pour classer les produits en fonction de leur montant de vente. Vous avez également appris à calculer les totaux cumulés (running totals) à l'aide de la fonction SUM() avec la clause OVER() et à partitionner les données à l'aide de la clause PARTITION BY pour effectuer des calculs au sein de groupes logiques. Ces compétences fournissent une base pour des tâches d'analyse de données plus complexes dans SQLite.