Regroupement de données SQLite

Introduction

Dans ce laboratoire, vous apprendrez à résumer et à analyser des données dans SQLite en utilisant des fonctions d'agrégation et des clauses de regroupement. Vous explorerez COUNT et SUM pour les calculs, regrouperez les données par colonnes uniques, filtrerez les groupes avec HAVING, et trierez la sortie groupée. Cette expérience pratique vous fournira des compétences essentielles en manipulation de données SQLite.

Skills Graph

Créer la table Orders et insérer des données

Dans cette étape, vous allez créer une base de données nommée sales.db et une table orders à l'intérieur. Vous insérerez ensuite des exemples de données dans la table. Cette table sera utilisée tout au long du laboratoire pour pratiquer les techniques de regroupement de données.

Tout d'abord, 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 connectez-vous à celle-ci en utilisant la commande suivante :

sqlite3 sales.db

Cette commande ouvrira l'interpréteur (shell) SQLite, et vous verrez une invite de commande comme sqlite>.

Ensuite, créez la table orders avec des colonnes pour order_id, customer_id, product_name, quantity et price. Exécutez la commande SQL suivante :

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_name TEXT,
    quantity INTEGER,
    price REAL
);

Cette commande crée la table orders avec les colonnes et les types de données spécifiés. La colonne order_id est définie comme clé primaire (primary key).

Maintenant, insérez des exemples de données dans la table orders. Exécutez les instructions INSERT suivantes une par une :

INSERT INTO orders (customer_id, product_name, quantity, price) VALUES
(1, 'Laptop', 1, 1200.00),
(1, 'Mouse', 2, 25.00),
(2, 'Keyboard', 1, 75.00),
(2, 'Monitor', 1, 300.00),
(3, 'Laptop', 1, 1200.00),
(3, 'Headphones', 1, 100.00),
(1, 'Keyboard', 1, 75.00);

Ces commandes insèrent sept lignes de données dans la table orders, représentant différentes commandes client.

Pour vérifier que les données ont été insérées correctement, vous pouvez exécuter une simple requête SELECT :

SELECT * FROM orders;

Cette commande affichera toutes les lignes et colonnes de la table orders.

Agrégation avec COUNT et SUM

Dans cette étape, vous utiliserez les fonctions d'agrégation COUNT et SUM pour effectuer des calculs sur les données de la table orders. Les fonctions d'agrégation vous permettent de résumer les données de plusieurs lignes en un seul résultat.

Vous devriez toujours être connecté à la base de données sales.db depuis l'étape précédente. Si ce n'est pas le cas, reconnectez-vous en utilisant :

sqlite3 sales.db

Tout d'abord, utilisons la fonction COUNT pour déterminer le nombre total de commandes dans la table. Exécutez la commande SQL suivante :

SELECT COUNT(*) FROM orders;

Cette requête renverra le nombre total de lignes dans la table orders, ce qui représente le nombre total de commandes.

La fonction COUNT(*) compte toutes les lignes de la table, que certaines colonnes contiennent ou non des valeurs NULL.

Ensuite, utilisons la fonction SUM pour calculer la quantité totale de tous les produits commandés. Exécutez la commande SQL suivante :

SELECT SUM(quantity) FROM orders;

Cette requête renverra la somme de la colonne quantity pour toutes les lignes de la table orders.

La fonction SUM additionne les valeurs de la colonne spécifiée.

Enfin, calculons le revenu total généré par toutes les commandes. Exécutez la commande SQL suivante :

SELECT SUM(quantity * price) FROM orders;

Cette requête multiplie les colonnes quantity et price pour chaque ligne, puis additionne les résultats, ce qui vous donne le revenu total.

Grouper par colonnes uniques

Dans cette étape, vous apprendrez à utiliser la clause GROUP BY pour regrouper les lignes en fonction des valeurs d'une ou plusieurs colonnes. Ceci est souvent utilisé conjointement avec des fonctions d'agrégation pour calculer des statistiques récapitulatives pour chaque groupe.

Vous devriez toujours être connecté à la base de données sales.db depuis l'étape précédente. Si ce n'est pas le cas, reconnectez-vous en utilisant :

sqlite3 sales.db

Regroupons la table orders par customer_id et comptons le nombre de commandes pour chaque client. Exécutez la commande SQL suivante :

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;

Cette requête renverra le customer_id et le nombre de commandes (order_count) pour chaque client unique. La clause GROUP BY customer_id indique à SQLite de regrouper les lignes en fonction des valeurs de la colonne customer_id. La fonction COUNT(*) compte ensuite le nombre de lignes dans chaque groupe.

Ensuite, regroupons la table orders par product_name et calculons la quantité totale commandée pour chaque produit. Exécutez la commande SQL suivante :

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name;

Cette requête renverra le product_name et la quantité totale commandée (total_quantity) pour chaque produit unique. La clause GROUP BY product_name indique à SQLite de regrouper les lignes en fonction des valeurs de la colonne product_name. La fonction SUM(quantity) calcule ensuite la somme de la colonne quantity pour chaque groupe.

Enfin, regroupons la table orders par customer_id et calculons le revenu total généré par chaque client. Exécutez la commande SQL suivante :

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id;

Cette requête renverra le customer_id et le revenu total (total_revenue) généré par chaque client.

Appliquer HAVING aux groupes

Dans cette étape, vous apprendrez à utiliser la clause HAVING pour filtrer les groupes après qu'ils ont été créés par la clause GROUP BY. La clause HAVING est similaire à la clause WHERE, mais elle opère sur des groupes plutôt que sur des lignes individuelles.

Vous devriez toujours être connecté à la base de données sales.db depuis l'étape précédente. Si ce n'est pas le cas, reconnectez-vous en utilisant :

sqlite3 sales.db

Regroupons la table orders par customer_id et comptons le nombre de commandes pour chaque client. Ensuite, nous utiliserons la clause HAVING pour filtrer les résultats afin d'inclure uniquement les clients qui ont passé plus d'une commande. Exécutez la commande SQL suivante :

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;

Cette requête renverra le customer_id et le nombre de commandes (order_count) pour chaque client qui a passé plus d'une commande. La clause GROUP BY customer_id regroupe les lignes par customer_id, et la clause HAVING COUNT(*) > 1 filtre les groupes pour n'inclure que ceux où le nombre de commandes est supérieur à 1.

Ensuite, regroupons la table orders par product_name et calculons la quantité totale commandée pour chaque produit. Ensuite, nous utiliserons la clause HAVING pour filtrer les résultats afin d'inclure uniquement les produits où la quantité totale commandée est supérieure à 1. Exécutez la commande SQL suivante :

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name HAVING SUM(quantity) > 1;

Cette requête renverra le product_name et la quantité totale commandée (total_quantity) pour chaque produit où la quantité totale commandée est supérieure à 1.

Enfin, regroupons la table orders par customer_id et calculons le revenu total généré par chaque client. Ensuite, nous utiliserons la clause HAVING pour filtrer les résultats afin d'inclure uniquement les clients qui ont généré plus de 1000 $ de revenus. Exécutez la commande SQL suivante :

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id HAVING SUM(quantity * price) > 1000;

Cette requête renverra le customer_id et le revenu total (total_revenue) généré par chaque client qui a généré plus de 1000 $ de revenus.

Trier la sortie groupée

Dans cette étape, vous apprendrez à utiliser la clause ORDER BY pour trier la sortie d'une requête qui inclut la clause GROUP BY. Le tri de la sortie groupée peut faciliter l'analyse et la compréhension des données.

Vous devriez toujours être connecté à la base de données sales.db depuis l'étape précédente. Si ce n'est pas le cas, reconnectez-vous en utilisant :

sqlite3 sales.db

Regroupons la table orders par customer_id et comptons le nombre de commandes pour chaque client. Ensuite, nous utiliserons la clause ORDER BY pour trier les résultats par ordre décroissant en fonction du nombre de commandes. Exécutez la commande SQL suivante :

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count DESC;

Cette requête renverra le customer_id et le nombre de commandes (order_count) pour chaque client, triés par ordre décroissant en fonction de order_count. La clause GROUP BY customer_id regroupe les lignes par customer_id, et la clause ORDER BY order_count DESC trie les résultats par ordre décroissant en fonction de l'alias order_count.

Ensuite, regroupons la table orders par product_name et calculons la quantité totale commandée pour chaque produit. Ensuite, nous utiliserons la clause ORDER BY pour trier les résultats par ordre croissant en fonction du nom du produit. Exécutez la commande SQL suivante :

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name ORDER BY product_name ASC;

Cette requête renverra le product_name et la quantité totale commandée (total_quantity) pour chaque produit, triés par ordre croissant en fonction de product_name.

Enfin, regroupons la table orders par customer_id et calculons le revenu total généré par chaque client. Ensuite, nous utiliserons la clause ORDER BY pour trier les résultats par ordre décroissant en fonction du revenu total. Exécutez la commande SQL suivante :

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id ORDER BY total_revenue DESC;

Cette requête renverra le customer_id et le revenu total (total_revenue) généré par chaque client, triés par ordre décroissant en fonction de total_revenue.

Pour quitter l'interpréteur de commandes (shell) SQLite, exécutez :

.exit

Résumé

Dans ce TP (travaux pratiques), vous avez appris à utiliser des fonctions d'agrégation comme COUNT et SUM pour synthétiser des données dans SQLite. Vous avez créé une base de données sales.db avec une table orders et inséré des exemples de données. Vous avez ensuite utilisé COUNT(*) pour déterminer le nombre total de commandes et SUM(quantity * price) pour calculer le revenu total. Vous avez également appris à regrouper des données à l'aide de la clause GROUP BY, à filtrer des groupes à l'aide de la clause HAVING et à trier la sortie à l'aide de la clause ORDER BY. Ces compétences fournissent une base solide pour l'analyse de données dans SQLite.