Perfectionnez votre maîtrise de SQL

La compagnie DVD Rental se retrouve confrontée à un problème concernant le format des dates dans sa base de données. Rodolphe vous contacte pour vous demander de normaliser ces formats et ainsi éviter les erreurs liées aux différentes façons dont les dates sont stockées et utilisées dans les requêtes. Votre mission ? Manipuler ces données avec précision pour garantir que les dates soient cohérentes et faciles à utiliser.

Maîtrisez les fonctions de base associées aux colonnes de types VARCHAR

Avant de plonger dans la normalisation des dates, il est important de maîtriser les fonctions de manipulation de chaînes de caractères, souvent utilisées pour nettoyer et formater les données dans PostgreSQL.

Le type VARCHAR (Variable Character) en SQL est utilisé pour stocker des chaînes de caractères de longueur variable. Contrairement à CHAR, qui a une longueur fixe, VARCHAR permet de définir une longueur maximale, mais les données stockées n'occupent que l'espace nécessaire. Par exemple, si vous définissez un champ VARCHAR(255), cela signifie que la colonne peut contenir jusqu'à 255 caractères, mais si la chaîne insérée ne fait que 10 caractères, elle n'occupera que l'espace de ces 10 caractères. Ce type est idéal pour des champs comme les noms, prénoms, ou adresses e-mail, où la longueur peut varier, tout en limitant la taille maximale pour optimiser le stockage.

PostgreSQL propose de nombreuses fonctions prédéfinies qui vous permettent de transformer et d'analyser les données dans des colonnes de tous types. Nous allons nous concentrer sur celles de type VARCHAR. Pour commencer, voyons comment appeler et utiliser ces fonctions dans une requête SQL. Un exemple classique pourrait être l'utilisation de CONCAT pour combiner plusieurs chaînes de caractères.

Pour ce faire, vous allez intégrer la fonction dans la clauseSELECT, de la même manière que vous le feriez avec une colonne.

SELECT
CONCAT(first_name, last_name) AS full_name
FROM
customer

Dans ce cas précis, il serait pertinent d'ajouter un espace entre le prénom et le nom pour plus de lisibilité. Heureusement, vous pouvez utiliser la fonctionCONCAT_WS, qui permet de combiner plusieurs chaînes de caractères tout en insérant un séparateur.

SELECT
CONCAT_WS(' ', first_name, last_name) AS full_name
FROM
customer

Vous pouvez également enchaîner plusieurs fonctions, par exemple en supprimant les espaces vides, puis en calculant la longueur de la chaîne de caractères :

SELECT
last_name,
LENGTH(TRIM(last_name))
FROM
customer

Voici quelques-unes des fonctions les plus utiles lorsque vous travaillez avec des colonnes de type VARCHAR :

Manipulez les formats de dates

Au fil des semaines, Sofia, la CEO de DVD Rental a remarqué que certaines analyses sur les périodes de location, les dates de retour et les comportements d'emprunt des clients étaient difficiles à interpréter en raison de formats de dates incohérents. Que ce soit pour savoir combien de films ont été loués sur une période donnée, calculer la durée moyenne des locations, ou encore analyser les périodes de pics de demande, les dates sont au cœur de nombreuses questions essentielles pour l’entreprise.

Imaginez qu'un client se présente pour une réclamation concernant des frais de retard, ou que la CEO vous demande un rapport sur les pics de location lors des vacances scolaires. Chaque fois, vous aurez besoin de manipuler et de formater les dates correctement pour obtenir les réponses précises.

Passons donc maintenant à la manipulation des types de données temporelles, indispensables pour répondre aux demandes de Sofia et Rodolphe.

Comprenez les différents formats

Il existe plusieurs formats de stockage de dates dans PostgreSQL, chacun ayant ses particularités :

Maintenant que vous maîtrisez les différents formats, passons à leur utilisation en tenant compte des fuseaux horaires et des fonctions de dates courantes.

Utilisez les fonctions de dates courantes

Utilisez des fonctions commeNOW()ouCURRENT_TIMESTAMPpour obtenir la date et l'heure actuelles, ou alorsCURRENT_DATEpour obtenir la date actuelle.

SELECT
NOW(),
CURRENT_TIMESTAMP,
CURRENT_DATE

Il est également possible de définir un fuseau horaire par défaut qui sera ensuite utilisé à chaque fois que vous manipulez les dates. Pour ce faire utilisez la requête suivante :

SET timezone = 'America/New_York';

Les fuseaux horaires peuvent devenir complexes, notamment dans des applications internationales.  Pour vérifier le fuseau actuellement utilisé dans votre base, utilisez la commandeSHOW TIMEZONE :

SHOW TIMEZONE;

Vous pouvez rétablir votre fuseau horaire d'origine, qui est le suivant :

SET timezone = 'Etc/UTC';

Prenons l'exemple suivant, dans lequel nous souhaitons ajouter une colonne created_at dans une table rentals, afin de stocker l'heure précise de chaque nouvelle location. Nous allons définir cette colonne en tant que typeTIMESTAMPTZ, ce qui permet de prendre automatiquement en compte le fuseau horaire, tout en utilisant la fonctionCURRENT_TIMESTAMPpour enregistrer l'heure actuelle :

ALTER TABLE rentals
ADD COLUMN created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP;

Ici, la colonne created_at enregistrera automatiquement l'heure exacte de chaque nouvel enregistrement avec le fuseau horaire du serveur. Cela évite d'oublier de renseigner cette information manuellement à chaque nouvelle location. Si vous voulez définir une valeur par défaut lors de la création d'une nouvelle table, vous pouvez utiliser cette syntaxe :

CREATE TABLE rentals (
rental_id SERIAL PRIMARY KEY,
customer_id IN,
film_id INT,
rental_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Maintenant que vous comprenez les fuseaux horaires et les fonctions de dates, voyons comment extraire et transformer ces données pour des analyses plus précises.

Utilisez les fonctions pour récupérer et transformer les dates

PostgreSQL propose plusieurs fonctions pour manipuler les dates. Voici un résumé des fonctions essentielles que vous utiliserez dans ce cours :

SELECT
rental_date,
EXTRACT(YEAR FROM rental_date) AS YEAR,
EXTRACT(MONTH FROM rental_date) AS MONTH,
EXTRACT(DAY FROM rental_date) AS DAY,
DATE_TRUNC('year', rental_date) AS YEAR_TRUNC,
DATE_TRUNC('month', rental_date) AS MONTH_TRUNC,
rental_date + INTERVAL '7 days' AS PLUS_7DAYS
FROM
rental;

Utilisez les tableaux comme type dans vos colonnes

Après avoir résolu les problèmes liés aux dates, Sofia la CEO de DVD Rental vous contacte par Slack avec une nouvelle demande :

Salut, Nous avons une nouvelle idée pour améliorer l'expérience utilisateur. Nous aimerions stocker plusieurs numéros de téléphone pour chaque client et enregistrer leurs catégories de films préférées afin de personnaliser les recommandations.

Tu peux regarder comment intégrer ça dans la base de données ? Merci !

Pour répondre à ce besoin, vous allez devoir utiliser les tableaux dans PostgreSQL, un type de colonne qui permet de stocker plusieurs valeurs au sein d'un seul enregistrement, ce qui est idéal pour cette situation.

Comprenez et utilisez les tableaux dans vos colonnes

Prenons l’exemple de la table customer. Actuellement, un client ne peut avoir qu'un seul numéro de téléphone enregistré (aujourd’hui il est stocké dans la tableaddress), mais dans la réalité, les clients peuvent avoir plusieurs numéros (portable, fixe, professionnel). Pour éviter de créer de nouvelles colonnes pour chaque numéro, vous pouvez utiliser un tableau pour stocker plusieurs numéros dans une seule colonne.

Ainsi, au lieu d’avoir une seule valeur pour le téléphone, vous créerez une colonne phone_numbers de type tableau pour chaque client :

ALTER TABLE customer ADD COLUMN phone_numbers TEXT[];

De la même manière, pour capturer les catégories de films préférées d’un client (par exemple, "Action", "Comédie", "Drame"), vous pouvez utiliser une colonne favorite_categories :

ALTER TABLE customer ADD COLUMN favorite_categories TEXT[];

Modifiez et manipulez les tableaux

Une fois que vous avez défini vos colonnes de type tableau, la CEO vous demande de commencer à enregistrer ces informations. Pour cela, vous devrez savoir ajouter et supprimer des éléments dans ces tableaux.

Par exemple, supposons qu’un client a trois numéros de téléphone : un portable, un fixe, et un numéro professionnel. Vous pouvez insérer ces numéros dans la colonne phone_numbers de cette façon :

UPDATE customer
SET phone_numbers = ARRAY['+1 212 555 1212', '+1 212 526 2365', '+1 212 526 1718']
WHERE
customer_id = 1;

Si ce client change de numéro et souhaite supprimer son fixe, vous pouvez utiliser la fonctionARRAY_REMOVE()pour modifier son tableau de numéros :

UPDATE customer
SET phone_numbers = ARRAY_REMOVE(phone_numbers, '+1 212 526 1718') WHERE
customer_id = 1;

Vous pouvez également ajouter une nouvelle catégorie de films préférée pour un client en utilisantARRAY_APPEND():

UPDATE customer
SET favorite_categories = ARRAY_APPEND(favorite_categories, 'Horreur') WHERE
customer_id = 1;
UPDATE customer
SET favorite_categories = ARRAY_APPEND(favorite_categories, 'Action') WHERE
customer_id IN (1,7,8);

Grâce à ces fonctions, vous pourrez facilement gérer les modifications dans les tableaux, sans avoir à réorganiser toute votre base de données.

Recherchez dans un tableau

Le DBA vous envoie ensuite une nouvelle requête : il veut savoir quels clients ont "Action" comme catégorie préférée. Pour cela, vous devez rechercher un élément spécifique dans les tableaux de la colonne favorite_categories.

En utilisant la fonctionARRAY_POSITION(), vous pouvez identifier les clients qui ont "Action" dans leur liste de catégories : 

SELECT
customer_id,
first_name,
last_name
FROM
customer
WHERE
ARRAY_POSITION(favorite_categories, 'Action') IS NOT NULL;

Une autre façon de faire est d’utiliser la fonctionANY():

SELECT
customer_id,
first_name,
last_name
FROM
customer
WHERE
'Action' = ANY(favorite_categories);

Cela vous permet d'extraire les clients qui préfèrent les films d'action et d'utiliser ces informations pour des campagnes marketing ciblées.

Étendez vos tableaux

Enfin, pour une analyse plus détaillée, la CEO vous demande un rapport sur les numéros de téléphone de tous les clients, mais chaque numéro doit être affiché sur une ligne distincte.

La fonctionUNNEST()vous permet de découper un tableau en plusieurs lignes distinctes, ce qui est parfait pour cette situation. Par exemple, pour obtenir une liste de tous les numéros de téléphone de chaque client :

SELECT
customer_id,
UNNEST(phone_numbers) AS phone_number
FROM
customer;

Cela transformera chaque élément du tableau phone_numbers en une nouvelle ligne, permettant d’afficher tous les numéros de téléphone de chaque client dans un format plus simple à analyser.

Et si on vérifiait que tout était clair avec un cas pratique ?

À vous de jouer

Contexte

Sofia souhaite analyser les habitudes de location des clients et, dans cette démarche, elle aimerait porter une attention particulière aux clients dont le mode de contact préféré est le SMS. Chaque client peut avoir plusieurs modes de contact (téléphone, email, etc.), et il est crucial de comprendre ces préférences pour optimiser les interactions. Ainsi, vous devrez manipuler les dates de location tout en utilisant un tableau pour enregistrer les différents modes de contact des clients.

Consignes

Dates :

• Ajoutez une nouvelle colonne dans la table rental pour indiquer si une location a été effectuée durant les heures d'ouverture de DVD Rental (de 9h à 18h)

• Utilisez la fonctionEXTRACTouDATE_TRUNCpour vérifier l'heure de la location et insérer une valeur booléenne (TRUE/FALSE).

Tableaux :

• Ajoutez une colonne de type tableau dans la table customer pour enregistrer les différents modes de contact utilisés par chaque client (par exemple, {Email, SMS, Téléphone}).

• Insérez plusieurs modes de contact pour certains clients.

• Effectuez une requête pour rechercher les clients qui ont "SMS" comme mode de contact préféré.

Vérifiez votre travail à l’aide de cet exemple de corrigé

Si vous êtes coincé, la solution est disponible ci-dessous.

Dans cette vidéo :

• Vous avez ajouté une colonne dans la table rental pour indiquer si une location a été effectuée durant les heures d'ouverture, en utilisant les fonctionsEXTRACTouDATE_TRUNC

• Vous avez inséré une colonne de type tableau dans la table customer pour enregistrer plusieurs modes de contact pour chaque client.

• Vous avez exécuté une requête pour identifier les clients ayant "SMS" comme mode de contact préféré.

En résumé

• Vous avez exploré les différents formats de dates pris en charge par PostgreSQL, ainsi que les fonctions associées, telles queEXTRACT,DATE_TRUNC etINTERVAL.

• Vous avez compris comment filtrer et manipuler les dates pour des analyses précises, notamment en utilisant les fuseaux horaires.

• Vous avez découvert comment utiliser des tableaux comme types de colonnes pour stocker des informations multiples liées à un même enregistrement.

• En vous basant sur des exemples concrets, vous avez appris à ajouter, modifier et rechercher des éléments dans des tableaux, ce qui vous permet de mieux gérer des données variées, comme les modes de contact des clients.

Après avoir maîtrisé les dates et les tableaux, il est temps de plonger dans les métadonnées et les statistiques de votre base de données, afin de permettre à DVD Rental de suivre efficacement ses tables et d'optimiser ses performances.