Astuces et bonnes pratiques SQL

(github.com/ben-n93)

7 points par GN⁺ 2024-09-26 | 2 commentaires | Partager sur WhatsApp

Table des matières

Formatage / lisibilité

Utiliser des virgules en tête de ligne pour séparer les champs
Utiliser une valeur factice dans la clause WHERE
Indenter correctement le code
Envisager les CTE pour écrire des requêtes complexes

Fonctionnalités utiles

Convertir les types de données avec l’opérateur ::
Utiliser les anti-joins
Utiliser QUALIFY pour filtrer les fonctions de fenêtre
Il est possible d’utiliser GROUP BY avec la position des colonnes

Pièges à éviter

Faire attention à l’utilisation de NOT IN avec des valeurs NULL
Renommer les champs calculés pour éviter les ambiguïtés
Indiquer à quelle table appartient chaque colonne
Comprendre l’ordre d’exécution
Ajouter des commentaires dans le code
Lire toute la documentation

Formatage / lisibilité

Utiliser des virgules en tête de ligne pour séparer les champs

Dans la clause SELECT, les virgules en tête de ligne permettent de distinguer clairement les nouvelles colonnes
Elles fournissent un repère visuel qui permet de voir facilement si une virgule manque

SELECT
  employee_id,
  employee_name,
  job,
  salary
FROM employees;

Utiliser une valeur factice dans la clause WHERE

Une valeur factice dans la clause WHERE permet d’ajouter et de retirer dynamiquement des conditions

SELECT *
FROM employees
WHERE 1=1 -- valeur factice
  AND job IN ('Clerk', 'Manager')
  AND dept_no != 5;

Indenter correctement le code

L’indentation améliore la lisibilité du code et le rend plus facile à comprendre pour ses collègues comme pour soi-même plus tard

-- mauvais exemple :
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

-- bon exemple :
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

Envisager les CTE pour écrire des requêtes complexes

Au lieu d’imbriquer des vues inline, on peut utiliser des common table expressions (CTE) pour améliorer la lisibilité et l’organisation du code

-- utilisation de vues inline :
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM
  (SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id) AS cs
INNER JOIN
  (SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id) AS vhs
ON cs.movie_id = vhs.movie_id;

-- utilisation de CTE :
WITH cinema_sales AS (
  SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id
),
vhs_sales AS (
  SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id
)
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM cinema_sales AS cs
INNER JOIN vhs_sales AS vhs
ON cs.movie_id = vhs.movie_id;

Fonctionnalités utiles

Convertir les types de données avec l’opérateur `::`

Dans certains SGBDR, l’opérateur :: permet de convertir une valeur vers un autre type de données

SELECT CAST('5' AS INTEGER); -- utilisation de la fonction CAST
SELECT '5'::INTEGER; -- utilisation de la syntaxe ::

Utiliser les anti-joins

Les anti-joins sont très utiles pour renvoyer les lignes présentes dans une seule table
On peut aussi utiliser une sous-requête, mais un anti-join est généralement plus rapide

-- anti-join :
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

-- sous-requête :
SELECT
  *
FROM video_content
WHERE series_id NOT IN (SELECT DISTINCT series_id FROM archive);

-- sous-requête corrélée :
SELECT
  *
FROM video_content
WHERE NOT EXISTS (
  SELECT 1
  FROM archive a
  WHERE a.series_id = vc.series_id
);

-- EXCEPT :
SELECT series_id
FROM video_content
EXCEPT
SELECT series_id
FROM archive;

Utiliser `QUALIFY` pour filtrer les fonctions de fenêtre

QUALIFY permet de filtrer le résultat des fonctions de fenêtre
C’est utile pour réduire le nombre de lignes de code

-- avec QUALIFY :
SELECT
  product,
  market,
  SUM(revenue) AS market_revenue
FROM sales
GROUP BY product, market
QUALIFY DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) <= 10
ORDER BY product, market_revenue;

-- sans QUALIFY :
SELECT
  product,
  market,
  market_revenue
FROM (
  SELECT
    product,
    market,
    SUM(revenue) AS market_revenue,
    DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) AS market_rank
  FROM sales
  GROUP BY product, market
)
WHERE market_rank <= 10
ORDER BY product, market_revenue;

Il est possible d’utiliser `GROUP BY` avec la position des colonnes

On peut utiliser la position des colonnes au lieu de leur nom dans GROUP BY ou ORDER BY
C’est utile pour les requêtes temporaires, mais en code de production il faut toujours référencer les noms de colonnes

SELECT
  dept_no,
  SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_no est la première colonne de la clause SELECT
ORDER BY 2 DESC;

Pièges à éviter

Faire attention à l’utilisation de `NOT IN` avec des valeurs `NULL`

NOT IN ne fonctionne pas en présence de valeurs NULL
Il faut utiliser NOT EXISTS à la place

INSERT INTO departments (id)
VALUES (1), (2), (NULL);

-- ne fonctionne pas à cause de la valeur NULL
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);

-- solution
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

Renommer les champs calculés pour éviter les ambiguïtés

Renommer un champ calculé avec le nom d’une colonne existante peut entraîner un comportement inattendu

INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);

-- la fonction de fenêtre classe le produit 'Robot' à la première place
SELECT
  product,
  CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
  RANK() OVER (ORDER BY revenue DESC)
FROM products;

Indiquer à quelle table appartient chaque colonne

Dans les requêtes complexes, préciser à quelle table appartient chaque colonne facilite le suivi des problèmes

SELECT
  vc.video_id,
  vc.series_name,
  metadata.season,
  metadata.episode_number
FROM video_content AS vc
INNER JOIN video_metadata AS metadata
ON vc.video_id = metadata.video_id;

Comprendre l’ordre d’exécution

Le conseil le plus important pour apprendre SQL est de comprendre l’ordre d’exécution
Une fois cet ordre compris, la manière d’écrire des requêtes change complètement

Ajouter des commentaires dans le code

Il faut ajouter des commentaires qui expliquent le pourquoi du code
Ses collègues comme soi-même dans le futur vous en remercieront

SELECT
  video_content.*
FROM video_content
LEFT JOIN archive -- le nouveau CMS ne peut pas gérer le format vidéo d’archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

Lire toute la documentation

Lire la documentation dans son ensemble permet d’éviter des problèmes inattendus
Cela ne prend que quelques minutes et peut aider à résoudre des problèmes imprévus

-- en lisant davantage la documentation, le problème de NULL aurait pu être évité
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);

-- il est possible d’utiliser la fonction GREATEST_IGNORE_NULLS
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);

Le résumé de GN⁺

Cet article présente diverses astuces et bonnes pratiques pour écrire du SQL de manière plus efficace et plus lisible
Il contient de nombreuses informations utiles aussi bien pour les débutants en SQL que pour les data analysts expérimentés
En particulier, l’utilisation des CTE pour les requêtes complexes, des anti-joins et de QUALIFY sera très utile en pratique
Il est important de prendre l’habitude de comprendre l’ordre d’exécution du SQL, d’ajouter des commentaires dans le code et de lire attentivement la documentation
Parmi les autres outils aux fonctionnalités similaires, on peut citer PostgreSQL, MySQL et Oracle

2 commentaires

hiyama 2024-09-26

Toutes les virgules en tête ont été réécrites avec des virgules en fin de ligne dans ce post. Dans l’original, elles sont saisies en tête.

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt; 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt;= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;

GN⁺ 2024-09-26

Avis Hacker News

Nécessité de bien comprendre le serveur de base de données et de vérifier souvent le plan d’exécution des requêtes
- EXISTS est souvent plus rapide que IN
- NOT EXISTS et EXCEPT se comportent différemment dans la gestion des valeurs NULL
- Il est recommandé d’utiliser des colonnes de sous-requêtes plutôt que des jointures de tables
- Il faut éviter les scans de table et ajouter des index
- Pour le filtrage par expression, il est possible d’utiliser des colonnes calculées et des index
- UNION ALL peut être plus rapide que OR
- Il est possible de forcer l’ordre de filtrage via des JOIN sur des sous-requêtes
Conseils pour les tâches complexes en procédures stockées
- Copier les tables permanentes dans des tables temporaires et filtrer uniquement les lignes nécessaires
- Manipuler les tables temporaires
- Mettre à jour les tables permanentes dans une transaction, avec rollback en cas d’erreur
- Prudence lors du travail sur des tables distantes ; il est recommandé de copier d’abord dans des tables temporaires
- Les plans d’exécution peuvent devenir confus, donc mieux vaut découper le travail en petites étapes
- Toujours vérifier le plan d’exécution
Avis sur la lisibilité du code
- Les deux premiers exemples sacrifient la lisibilité au profit de la facilité d’écriture
- Dans le dernier exemple, l’indentation n’apporte pas grand-chose
Proposition d’utiliser la syntaxe FROM-first et de piping en SQL
- L’expérience avec Kusto query language a représenté une grande avancée
Conseil sur les Anti Join
- Utilisation recommandée de EXISTS, avantageuse pour vérifier l’existence de lignes dans des sous-requêtes basées sur des conditions
Avantages de la virgule en tête de ligne dans les instructions SELECT
- Possibilité de commenter chaque ligne individuellement
- Lisibilité améliorée grâce à l’indentation du code
Dans MSSQL, il est recommandé d’utiliser /* */ plutôt que -- pour les commentaires
- Car le query store enregistre les requêtes sans retours à la ligne
Utilisation recommandée des fonctions de fenêtre
Débat autour de l’utilisation de 1=1 dans la clause WHERE
Présentation d’AI2sql
- Possibilité de générer des requêtes SQL à partir de prompts en anglais courant
- Utile pour rédiger des requêtes complexes

Astuces et bonnes pratiques SQL

Table des matières

Formatage / lisibilité

Fonctionnalités utiles

Pièges à éviter

Formatage / lisibilité

Utiliser des virgules en tête de ligne pour séparer les champs

Utiliser une valeur factice dans la clause WHERE

Indenter correctement le code

Envisager les CTE pour écrire des requêtes complexes

Fonctionnalités utiles

Convertir les types de données avec l’opérateur ::

Utiliser les anti-joins

Utiliser QUALIFY pour filtrer les fonctions de fenêtre

Il est possible d’utiliser GROUP BY avec la position des colonnes

Pièges à éviter

Faire attention à l’utilisation de NOT IN avec des valeurs NULL

Renommer les champs calculés pour éviter les ambiguïtés

Indiquer à quelle table appartient chaque colonne

Comprendre l’ordre d’exécution

Ajouter des commentaires dans le code

Lire toute la documentation

Le résumé de GN⁺

À lire aussi

2 commentaires

Avis Hacker News

Convertir les types de données avec l’opérateur `::`

Utiliser `QUALIFY` pour filtrer les fonctions de fenêtre

Il est possible d’utiliser `GROUP BY` avec la position des colonnes

Faire attention à l’utilisation de `NOT IN` avec des valeurs `NULL`