Ce guide fait suite au Tutoriel sur l’Explorateur de données - Partie 1 - Écrire votre première requête.
Ce guide fait suite au Maintenant que vous avez vu comment l’Explorateur de données fonctionne en pratique, parlons de quelques bases SQL qui vous seront utiles lors de vos aventures d’exploration de données.
Instructions SQL
Toutes les actions que vous devrez effectuer sur la base de données de Discourse s’effectuent à l’aide d’instructions SQL.
selectest identique àSELECT, mais dans ce tutoriel, et par bonne pratique, nous écrirons tous les mots-clés SQL en majuscules.
Voici certaines des instructions SQL les plus courantes que vous pourriez utiliser. Notez que nous en avons déjà utilisé certaines dans notre requête d’exemple précédente :
- SELECT : Utilisé pour sélectionner des données dans une base de données. Les données retournées sont stockées dans une table de résultats, appelée jeu de résultats.
SELECT column1, column2, ...
FROM table_name;
- WHERE : Utilisé pour filtrer les enregistrements.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
- ORDER BY : Utilisé pour trier le jeu de résultats par ordre croissant ou décroissant.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
- COUNT : Retourne le nombre de lignes correspondant à un critère spécifié.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
- GROUP BY : Souvent utilisé avec des fonctions d’agrégation (COUNT, MAX, MIN, SUM, AVG) pour regrouper le jeu de résultats par une ou plusieurs colonnes.
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
- HAVING : Utilisé pour filtrer les résultats d’une opération GROUP BY. La clause HAVING est utilisée en SQL car le mot-clé WHERE ne peut pas être utilisé avec des fonctions d’agrégation (Ex :
COUNT(),MAX(),MIN(),SUM(),AVG()).
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
- IN : Utilisé pour vérifier si une valeur est présente dans un ensemble de valeurs ou retournée par une sous-requête. C’est une abréviation pour plusieurs conditions
OR, rendant vos requêtes SQL plus concises et plus faciles à lire.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
PostgreSQL
Discourse utilise actuellement PostgreSQL version 13 pour sa base de données, un système de gestion de base de données relationnelle (SGBDR) open source qui met l’accent sur l’extensibilité et la conformité SQL.
Pour une compréhension détaillée de PostgreSQL, vous pouvez consulter la Documentation officielle de PostgreSQL. En particulier, les sections sur les Requêtes, les Types de données et les Fonctions seront particulièrement pertinentes lors de l’écriture de requêtes SQL.
Tables de base de données
La base de données Discourse est composée de tables. Les tables sont la manière fondamentale de stocker des données dans un système de gestion de base de données relationnelle.
Chaque table dans Discourse est identifiée par un nom (par exemple posts ou topics), et chaque table contient des enregistrements (lignes) avec des données. Les colonnes dans les tables sont couramment appelées champs.
L’Explorateur de données liste les 9 tables les plus importantes en premier dans le panneau d’édition de requête de l’interface utilisateur, et vous pouvez voir la structure et les types de toutes les colonnes des tables en un clic :
Schéma de base de données
Dans PostgreSQL, ainsi que dans d’autres bases de données relationnelles, les concepts de clé primaire et de clé étrangère sont fondamentaux. Ils sont utilisés pour établir des relations entre les tables et assurer la cohérence et l’intégrité des données.
Clés primaires
Une clé primaire est une colonne ou un ensemble de colonnes dans une table qui identifie de manière unique chaque ligne de cette table. Aucune deux lignes dans une table ne peuvent avoir la même valeur de clé primaire. Vous ne pouvez pas avoir une valeur de clé primaire nulle, et la valeur doit être unique. La clé primaire est utilisée pour indexer les données dans la table, rendant la récupération des données beaucoup plus rapide. Dans l’explorateur de schéma de Discourse, la clé primaire d’une table est toujours listée en premier.
Exemple : La clé primaire de la table posts est le champ id.
Clés étrangères
Une clé étrangère est une colonne ou un ensemble de colonnes dans une table utilisé pour établir un lien entre les données de deux tables. Elle agit comme une référence croisée entre les tables car elle fait référence à la clé primaire d’une autre table, établissant ainsi un lien entre elles. La table contenant la clé étrangère est appelée table enfant, et la table contenant la clé primaire est appelée table référencée ou table parent. Dans l’explorateur de schéma de Discourse, une clé étrangère est indiquée par le texte fkey suivi du nom de la table parente.
Exemple : Dans la table posts, user_id est une clé étrangère de la table users.
Utilisation des clés primaires et étrangères
Lorsque vous écrivez des requêtes dans l’Explorateur de données, vous pouvez utiliser ces clés primaires et étrangères pour JOINDRE des tables ensemble et obtenir des données plus complexes. Par exemple, si vous vouliez trouver tous les messages publiés par un utilisateur particulier, vous pourriez écrire une requête comme celle-ci :
SELECT p.id, p.created_at, p.raw
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.username = 'username_here'
Dans cette requête, nous utilisons la clé étrangère user_id dans la table posts pour joindre avec la clé primaire id dans la table users. Cela nous permet de trouver tous les messages publiés par un utilisateur particulier.
JOINTURES SQL
Comme mentionné précédemment, l’instruction JOIN est utilisée pour combiner des lignes de deux ou plusieurs tables, sur la base d’une colonne apparentée entre elles.
Il est important de noter qu’il existe plusieurs types d’instructions JOIN SQL, chacun servant un objectif différent :
-
INNER JOIN : Le mot-clé
INNER JOINsélectionne les enregistrements qui ont des valeurs correspondantes dans les deux tables. Il retourne les lignes des deux tables où il y a une correspondance. UtilisezINNER JOINlorsque vous ne souhaitez retourner que les enregistrements où il y a une correspondance dans les deux tables. LorsqueJOINest utilisé sans spécifier le type de jointure (commeLEFT,RIGHTouFULL), il se comporte par défaut comme unINNER JOIN. -
LEFT (OUTER) JOIN : Le mot-clé
LEFT JOINretourne tous les enregistrements de la table de gauche (table1), et les enregistrements correspondants de la table de droite (table2). Le résultat est NULL du côté droit s’il n’y a pas de correspondance. UtilisezLEFT JOINlorsque vous voulez retourner tous les enregistrements de la table de gauche et les enregistrements correspondants de la table de droite. S’il n’y a pas de correspondance, le résultat est NULL du côté droit. -
RIGHT (OUTER) JOIN : Le mot-clé
RIGHT JOINretourne tous les enregistrements de la table de droite (table2), et les enregistrements correspondants de la table de gauche (table1). Le résultat est NULL du côté gauche lorsqu’il n’y a pas de correspondance. UtilisezRIGHT JOINlorsque vous voulez retourner tous les enregistrements de la table de droite et les enregistrements correspondants de la table de gauche. S’il n’y a pas de correspondance, le résultat est NULL du côté gauche. -
FULL (OUTER) JOIN : Le mot-clé
FULL JOINretourne tous les enregistrements lorsqu’il y a une correspondance dans la table de gauche (table1) ou de droite (table2). UtilisezFULL JOINlorsque vous voulez retourner tous les enregistrements lorsqu’il y a une correspondance dans l’une des tables.
Vous pouvez également combiner plusieurs instructions JOIN pour joindre plusieurs tables ensemble. Par exemple, nous pouvons joindre la table topic_tags avec la table topics, puis joindre la table tags avec la table topic_tags pour accéder aux noms des tags associés à un sujet.
SELECT
t.id AS topic_id,
tg.name as tag_name
FROM
topics t
JOIN topic_tags tt ON t.id = tt.topic_id
JOIN tags tg ON tg.id = tt.tag_id
Instructions WITH et sous-requêtes
L’instruction SQL WITH, également appelée Expression de Table Commune (CTE), est utilisée pour créer un jeu de résultats temporaire qui peut être référencé dans une autre instruction SELECT. Cela peut être particulièrement utile lors de l’utilisation de requêtes SQL complexes, car cela peut aider à les simplifier, les rendant plus faciles à lire et à maintenir.
Voici un exemple de base d’une instruction WITH :
WITH post_counts AS (
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id
)
SELECT u.username, post_counts.post_count
FROM users u
JOIN post_counts ON u.id = post_counts.user_id
ORDER BY post_counts.post_count DESC;
Dans cette requête, l’instruction WITH crée une table temporaire post_counts qui contient l’ID de chaque utilisateur et leur nombre total de messages. La requête principale joint ensuite cette table avec la table users pour obtenir les noms d’utilisateurs associés à chaque ID, et trie le résultat par nombre de messages dans l’ordre décroissant.
Cette requête retournerait le nombre de messages publiés par chaque utilisateur.
Les sous-requêtes sont des requêtes imbriquées à l’intérieur d’une autre requête. Une sous-requête peut être utilisée à l’intérieur d’instructions SELECT ou à l’intérieur d’une autre sous-requête. Une sous-requête peut retourner un ensemble d’enregistrements, un seul enregistrement ou une seule valeur.
Voici un exemple de sous-requête :
SELECT u.username
FROM users u
WHERE u.id IN (
SELECT p.user_id
FROM posts p
GROUP BY p.user_id
HAVING COUNT(p.id) > 100
)
Dans cet exemple, la sous-requête (SELECT p.user_id FROM posts p GROUP BY p.user_id HAVING COUNT(p.id) > 100) sélectionne le user_id de la table posts pour les utilisateurs qui ont publié plus de 100 messages. La requête principale récupère ensuite le username de la table users pour ces user_id.
Cette requête retournerait une liste de noms d’utilisateurs pour les utilisateurs qui ont publié plus de 100 messages.