SQL Quel Labeur

Comprendre la structure de ce que l'on voit est primordial. Cet article est né de ma frustration concernant les ressources pédagogiques du langage SQL.

Prérequis

• Vous êtes déjà familier avec un langage de programmation plus classique.
• Vous avez installé PostgreSQL et pgAdmin, ou tout autre moyen d'exécuter des requêtes SQL (rien ne vaut la pratique !).

Objectif

• Maîtriser la commande SQL select.
• Comprendre, raisonner sur et exprimer sans peine une majorité de selects.
• Pouvoir faire tous les exercices gratuits de SQL sur LeetCode.
• Savoir où trouver les réponses à ses questions dans la documentation de PostgreSQL.

Les mots-clés et identifiants

Les mots-clés (keywords) sont des mots réservés qui ont un sens bien précis dans le langage SQL. Si on fait le parallèle avec des langages de programmation plus classique, on peut dire que c'est l'équivalent des if, let, for, etc.

Les identifiants avec ou sans guillemet double (quoted or unquoted identifiers) représentent des noms de tables, de colonnes, de fonctions, etc. On peut également dire que c'est l'équivalent des variables, des fonctions, etc, dans nos langages plus classiques.

Les identifiants sans guillemet double (unquoted identifiers) et les mots-clés (keywords) sont incessibles à la casse (case insensitive).

SELECT MY_COL FROM MY_TABLE;
-- est équivalent à
select my_col from my_table;
-- est équivalent à
SELECT my_col FROM my_table;
-- est équivalent à
sELecT MY_coL FroM my_TAblE;

Une convention souvent utilisée est d'écrire les mots-clés en MAJUSCULE et les identifiants en minuscule.

SELECT my_col FROM my_table;

Mais ce n'est qu'une convention, qui trouve sans doute son origine dans l'époque à laquelle SQL est né. Une époque où les outils de développement n'étaient pas aussi perfectionnés que maintenant, et où la colorisation syntaxique n’était pas monnaie courante. Dans la suite de cet article, j'écrirai tout en minuscule.

Si on souhaite utiliser un mot-clé comme identifiant ou bien qu'il soit sensible à la casse, on peut utiliser les identifiants avec guillemet double.

-- on veut les valeurs de colonne « sEleCt » de la table « my_table ».
select "sEleCt" from my_table;

😮 : Les guillemet double ne servent pas à déclarer des chaînes de caractères.

Vous l'avez sans doute remarqué, les points-virgules séparent les commandes SQL. Dans la suite de cet article, je les omettrai quand ils ne seront pas nécessaires.

Reference :

• Lexical Structure

Vue d'ensemble d'une requête

L'objectif d'une requête (select) est de retourner une table avec un certain nombre de colonnes et de lignes, et dans un certain ordre. SQL est un langage de programmation déclarative, une requête est donc la description d'une table.

🤔 : Comment décririez-vous une table ?

Une requête minimale s'écrit « select select_list from table_expression ».

Cette requête décrit les deux dimensions d'une table que l'on souhaite récupérer :

• « select select_list » décrit les noms, les valeurs et la provenance de ses colonnes.
• « from table_expression » décrit le nombre et la provenance de ses lignes.

C'est en combinant la description des colonnes et des lignes que SQL nous génère notre table.

😮 : L'ordre des termes d'une requête SQL est trompeur. On pourrait naïvement se dire que « l'ordre d'exécution » de la requête est identique à celui de ses termes (de gauche à droite) mais ce n'est pas le cas.

La requête « select select_list from table_expression » peut être traduite par la phrase : Pour chaque ligne de la table « table_expression », voici comment extraire les colonnes « select_list ».

Ce qui donnerait en rust :

let mut table = vec![];
for row in table_expression.into_iter() {
  table.push(select_list(row));
}

Dans les faits, le moteur SQL est plus malin que ça, mais avoir à l'esprit « l'ordre d'exécution » va vous permettre de mieux visualiser vos requêtes. C'est d'autant plus crucial lorsqu'une requête devient complexe.

💡 : Une astuce que vous pouvez utiliser pour écrire vos requêtes est de commencer par le from (les lignes) plutôt que par le select (les colonnes). Demandez-vous quelles sont les lignes que vous voulez avoir dans votre table ?

La vrai requête minimale

Je vous ai un peu menti dans la section précédente, la requête minimale, dans PostgreSQL, s'écrit « select select_list ». Ce qui veut dire : « Je veux une table avec une ligne qui ne dépend pas d'une autre table, voici comment calculer ses colonnes ». Ça tombe bien. Ça va nous permettre de nous concentrer sur la description des colonnes.

Regardons de plus près la syntaxe de select_list. Si on développe select_list, la requête devient : « select value_expression as col_name, value_expression as col_name, value_expression as col_name, ... ».

Pour chaque colonne, nous avons une expression de valeur (value expression) qui indique comment calculer la valeur de la colonne et sa provenance, et, séparé par un « as », un nom de colonne.

select 1 as rank, 'Baba' as name

Chaque colonne possède un type de données bien précis qui peut être explicite ou implicite. SQL est un langage fortement typé.

Deux grands concepts

Il y a deux grands concepts à maîtriser pour se sentir libre d'exprimer ses idées avec SQL :

• les expressions de valeur (value expressions)

• les expressions de table (table expressions)

Les expressions de valeur

Une expression de valeur (value expression/scalar expression), aussi appelée expression, se comporte la plupart du temps comme les expressions des autres langages de programmation plus classiques.

Nous pouvons créer des expressions avec des littéraux (literals).

select 23 as a, 'Coucou' as b, 34.0 as c, true as d, null as e

Nous pouvons changer le type d'une expression en utilisant « cast (expression as type) » ou « expression::type ».

select cast ('2020-01-01' as date) as anniversaire, cast ('8.8.8.8' as inet) as adresse_ip;
-- or in PostgreSQL style
select '2020-01-01'::date as anniversaire, '8.8.8.8'::inet as adresse_ip;

Nous pouvons appeler des opérateurs ou des fonctions en utilisant respectivement « expression operator expression » ou « function_name(expression , expression, ...) ».

select 3 + 8 as ope, upper('coucou') as func;

😮 : L'ordre d'évaluation des sous expressions n'est pas définie contrairement à un langage classique ! Une fonction f appelée avec deux expressions a et b ne garantit pas que a sera évalué avant b ou que b sera évalué avant a. C'est d'autant plus important de le savoir lorsqu'on utilise des opérateurs sur les booléens comme « or » et « and ». Ils ne permettent pas de court-circuiter l'évaluation !

Nous pouvons construire des tableaux (arrays) en utilisant « array[expression, expression, ...] ».

select array[23, 76, 9] as mon_array;

Nous pouvons accéder à un élément d'un tableau.

select (array[23, 76, 9])[2] as ma_val;

😮 : Les indices commencent à 1 !

Nous pouvons construire des valeurs composites (row values/composite values) (pensez structure ou tuple) en utilisant « row(expression, expression, ...) ».

select row(1, 76.0, false) as my_record

Nous pouvons accéder à un champ d'une valeur composite.

select (row(1, 76.0, false)).f2 as my_field

Nous pouvons décomposer les champs d'une valeur composite.

select (row(1, 76.0, false)).*

Nous pouvons utiliser des parenthèses pour grouper des expressions ensemble.

select (1 + 3) * 4 as a, 1 + (3 * 4) as b

Pour résumer, une expression peut être :

• literals
• cast (expression as type)
• expression::type
• expression operator expression
• function_name(expression , expression, ...)
• array[expression, expression, ...]
• (expression)[one_based_index]
• row(expression, expression, ...)
• (expression).field_name
• (expression).*
• (expression)

Je vous invite à jouer avec les expressions, les types de données, les fonctions et les opérateurs avant de passer à la section suivante.

Vous trouverez en annexe quelques expressions qu'il est bon d'avoir en tête.

Reference :

• Value Expressions

• Data Types

• Functions and Operators

Les expressions de table

Dans la section précédente, nous avons vu comment, grâce à des expressions de valeur, décrire les colonnes d'une table. Mais les expressions de valeur ne se trouvent pas seulement dans la select_list d'un select. On les retrouve un peu partout dans les expressions de table (table expressions). C'est pourquoi, il faut bien être à l'aise avec elles avant de continuer la suite de cet article.

Reprenons le squelette d'une requête « select select_list from table_expression ».

Une expression de table commence toujours par le mot-clés « from ». Son but est de décrire le nombre et la provenance des lignes de notre future table.

Imaginons que nous avons dans notre base de données la table « pays » avec les valeurs suivantes :

💡: Pour remplir votre nouvelle instance de PostgreSQL afin de jouer avec des requêtes, vous pouvez demander à un LLM de vous générer les commandes ainsi que les fausses données.

Voici la requête pour indiquer que l'on souhaite que notre table ait le même nombre de lignes que la table « pays ».

select 47 as col1, 'Coucou' || ' tout le monde' as msg from pays

Nous avons bien la table que l'on a décrite ! Une table avec autant de ligne que la table « pays », deux colonnes, col1 et msg, avec comme valeur respectivement « 47 » et « Coucou tout le monde ».

Vous allez me dire que c'est totalement inutile. Et vous avez raison ! En effet, je ne vous ai pas tout dit sur les expressions de valeur. Une expression de valeur peut être une référence à une colonne de l'expression de table.

select pays.superficie as superficie, 'Coucou ' || lower(pays.nom) as msg from pays

C'est tout de suite plus utile ! Rappelez-vous « l'ordre d'exécution » de la requête :

let mut table = vec![];
for row in table_expression.into_iter() {
  table.push(select_list(row));
}

L'extraction des colonnes sera réalisée pour chaque ligne de la table décrite par l'expression de table.

Vous devriez maintenant mieux comprendre les interactions qu'il y a entre le « from » et le « select ». Entre la description des lignes et celle des colonnes.

On peut bien sûr simplifier la requête précédente.

select pays.superficie as superficie, 'Coucou ' || lower(pays.nom) as msg from pays;
-- est équivalent à
select pays.superficie, 'Coucou ' || lower(pays.nom) as msg from pays;
-- est équivalent à
select p.superficie, 'Coucou ' || lower(p.nom) as msg from pays as p;
-- est équivalent à
select superficie, 'Coucou ' || lower(nom) as msg from pays as p;
-- est équivalent à
select superficie, 'Coucou ' || lower(nom) as msg from pays;

Si l'on souhaite récupérer l'entièreté de la table « pays », on peut utiliser la requête suivante :

select pays.nom, pays.continent, pays.superficie, pays.population, pays.pid from pays

De la même manière, on peut la simplifier.

select pays.nom, pays.continent, pays.superficie, pays.population, pays.pid from pays;
-- est équivalent à
select p.nom, p.continent, p.superficie, p.population, p.pid from pays as p;
-- est équivalent à
select nom, continent, superficie, population, pid from pays as p;
-- est équivalent à
select nom, continent, superficie, population, pid from pays;
-- est équivalent à
select * from pays;
-- est équivalent à
select * from pays as p;
-- est équivalent à
select p.* from pays as p;
-- est équivalent à
select pays.* from pays;

Nous allons continuer à explorer les expressions de table. En plus du mot-clé « from », une expression de table peut utiliser les mots-clés « where », « group by » et « having ». C'est ce que nous allons voir dans la suite de cet article.

Le mot-clé where

Il peut arriver que seules quelques lignes de la table de « from » nous intéressent. Dans ce cas, nous utiliserons le mot-clé « where » de la manière suivante : « select select_list from from_item where condition ».

Le « where » va filtrer les lignes et ne garder que les lignes où l'expression de valeur « condition » est vraie.

😉 : Je vous avais dit que l'on retrouverait des expressions de valeur un peu partout.

Par exemple, pour n'utiliser que les lignes des pays qui se trouvent en Afrique, on utilisera la requête suivante :

select * from pays as p where lower(p.continent) = 'afrique'

Notre « ordre d'exécution » de la requête devient :

let mut table = vec![];
for row in from_item
  .into_iter()
  // Where
  .filter(|row| condition(row))
{
  table.push(select_list(row));
}

Le mot-clé group by

Il peut arriver qu'on ait envie de « grouper » des lignes ensemble, comme par exemple, n'avoir qu'une ligne par continent. Pour cela, on utilisera le mot-clé « group by » de la manière suivante : « select select_list from from_item group by grouping_element ».

😉 : grouping_element est aussi une expression de valeur.

Essayons la requête suivante :

select * from pays as p group by p.continent

Ah nous avons une erreur :

ERROR: column "p.nom" must appear in the GROUP BY clause or be used in an aggregate function

En effet, la requête que l'on vient de faire est équivalente à :

select p.nom, p.continent, p.superficie, p.population, p.pid from pays as p;

Et PostgreSQL nous dit qu'étant donné que l'on veut une seule ligne par continent, il ne sait pas quel nom utiliser. Pour l'Afrique, est-ce que l'on veut l'Algérie ou bien l'Angola ?

Pour réparer la requête, nous pouvons faire :

select p.continent from pays as p group by p.continent

C'est mieux, mais comment faire pour exploiter les autres colonnes ? Et bien, encore une fois, je ne vous ai pas tout dit sur les expressions de valeur. Une expression de valeur peut être une fonction d'agrégat (aggregate Function), ce sont des fonctions spéciales qui permettent justement de décrire comment passer d'une liste d'expressions de valeur à une seule expression de valeur.

Nous allons améliorer notre requête :

select
  p.continent,
  count(*) as nombre,
  sum(p.pid / 1_000_000) as somme,
  string_agg(lower(p.nom), ' ou ') as noms
from pays as p
group by p.continent

Les fonctions d'agrégat ont la même syntaxe que des fonctions normales, mais elles ne sont pas des expressions de valeur comme les autres. On ne peut pas avoir une fonction d'agrégat dans une autre fonction d'agrégat. Et on ne peut les trouver qu'à certains endroits dans une requête (dans le « select », le « having » ou le « order by »). Leur présence implique que l'on va grouper des lignes ensemble. Si elles sont utilisées sans « group by », cela veut dire que l'on va regrouper toutes les lignes de la table en une seule ligne.

select
  count(*) as nombre,
  sum(p.pid / 1_000_000) as somme,
  string_agg(lower(p.nom), ' ou ') as noms
from pays as p

Notre « ordre d'exécution » de la requête devient :

use std::collections::HashMap;
let mut map = HashMap::<Key, Vec<Row>>::new();
for row in from_item.into_iter() {
  let key = grouping_element(row);
  map.entry(key).or_default().push(row);
}

let mut table = vec![];
for (key, rows) in map.into_iter() {
  table.push(select_list(key, rows));
}

Ce que l'on remarque c'est que l'utilisation de la clause « group by » ou d'une fonction d'agrégat change la « signature » et la sémantique de la select_list. Elle passe d'une fonction qui prend une ligne, et renvoie une ligne, à une fonction qui prend une clé et des lignes, et renvoie une ligne.

// On passe de
fn select_list(row: Row) -> Row {}
// à
fn select_list(key: Key, rows: Vec<Row>) -> Row {}

Une autre façon de le voir c'est que l'on passe d'une fonction qui « map » les lignes à une fonction qui les « reduce ».

Le mot-clé having

Maintenant qu'on a « groupé » les lignes, il peut arriver que l'on veuille filtrer ces groupes. C'est là qu'intervient le mot-clé « having » de la manière suivante : « select select_list from from_item group by grouping_element having boolean_expression ».

😉 : boolean_expression est aussi une expression de valeur.

select
  p.continent,
  count(*) as nombre,
  sum(p.pid / 1_000_000) as somme,
  string_agg(lower(p.nom), ' ou ') as noms
from pays as p
group by p.continent
having sum(p.pid / 1_000_000) >= 20000

Notez que ce qui se trouve dans l'expression de valeur du « having » n'a pas besoin de se trouver dans le « select ».

select
  p.continent,
  string_agg(lower(p.nom), ' ou ') as noms
from pays as p
group by p.continent
having sum(p.pid / 1_000_000) >= 20000

Les expressions de valeur du « having » n'ont pas la même « signature » que les expressions de valeur du « where ».

fn where_condition(row: &Row) -> bool {}
fn having_condition(key: &Key, rows: &Vec<Row>) -> bool {}

Le « having » filtre les groupes de lignes là où le « where » filtre les lignes.

Notre « ordre d'exécution » de la requête devient :

use std::collections::HashMap;
let mut map = HashMap::<Key, Vec<Row>>::new();
for row in from_item.into_iter() {
  let key = grouping_element(row);
  map.entry(key).or_default().push(row);
}

let mut table = vec![];
for (key, rows) in map
  .into_iter()
  // Having
  .filter(|(key, rows)| having_condition(key, rows))
{
  table.push(select_list(key, rows));
}

Les jointures

Maintenant que nous avons vu les trois mots-clés optionnels « where », « group by » et « having », revenons sur le « from ».

Avec SQL, il va arriver très vite que l'on souhaite combiner plusieurs tables. Imaginons qu'en plus de la table « pays ».

Nous ayons la table « ventes ».

On voudrait savoir, par exemple, pour chaque continent le montant total des ventes ainsi que la quantité totale d'objets vendus.

Pour cela, on utilisera le mot-clé « join » de la manière suivante : « select select_list from table inner join table on join_condition ».

😉 : join_condition est aussi une expression de valeur.

Construisons la requête pas à pas.

select p.continent, v.montant, v.quantité from pays as p inner join ventes as v on p.nom = v.nom_pays

Le principe d'une expression de table est de décrire le nombre de lignes de notre table, mais elle permet aussi de décrire quelles seront les colonnes disponibles pour les expressions de valeur. Ici, on combine les tables « pays » et « ventes », on aura donc accès à toutes leurs colonnes dans les expressions de valeur de la requête.

La jointure que l'on vient de réaliser peut se dire en français : je veux toutes les combinaisons de lignes entre la table « pays » et la table « ventes » où le nom du pays est égale au nom du pays de la vente.

Nous pouvons omettre le « inner ».

select p.continent, v.montant, v.quantité from pays as p inner join ventes as v on p.nom = v.nom_pays;
-- est équivalent à
select p.continent, v.montant, v.quantité from pays as p join ventes as v on p.nom = v.nom_pays;

Vous me direz que nous n'avons pas terminé notre requête qui doit trouver pour chaque continent le montant total des ventes ainsi que la quantité totale d'objets vendus.

select p.continent, sum(v.montant) as montant, sum(v.quantité) as quantité
from pays as p inner join ventes as v on p.nom = v.nom_pays
group by p.continent

💡 : Les jointures font parties intégrante du « from ». Gardez bien en tête qu'une table expression est composée au maximum de quatre parties, « from », « where », « group by » et « having ».

💡: Également, lorsque vous écrivez une expression de table demandez-vous combien de lignes voulez-vous et quelles colonnes souhaitez-vous avoir à votre disposition dans les expression de valeur.

Il existe d'autres types de jointures.

On peut naturellement faire plusieurs jointures à la suite et utiliser des parenthèses pour les grouper à notre guise.

Reference :

• Table Expressions

Quelques mots-clés en plus

Le mot-clé order by

Ce mot-clé permet de décrire l'ordre dans lequel on souhaite recevoir les lignes. Si on ne le spécifie pas, le moteur SQL est libre de les retourner dans n'importe quel ordre.

select p.continent, p.nom
from pays as p
order by p.continent asc, p.nom desc;
-- est équivalent à
select p.continent, p.nom
from pays as p
order by p.continent, p.nom desc;

On a bien une table où les lignes sont classées par continent de A-Z puis par nom de Z-A.

Le mot-clé limit

Ce mot-clé permet de ne récupérer que les « x » premières lignes de la table. Il est souvent utilisé avec « order by ». En effet, pour savoir quelles seront les « x » premières lignes, il faut déjà que les lignes soient ordonnées, classées.

select p.continent, p.nom
from pays as p
order by p.continent asc, p.nom desc
limit 3

Le mot-clé offset

Ce mot-clé vient compléter le précédent, il permet de sauter les « x » premières lignes de la table. En le combinant avec « limit », on peut spécifier une plage de lignes que l'on souhaite avoir.

select p.continent, p.nom
from pays as p
order by p.continent asc, p.nom desc
offset 2
limit 3

Le mot-clé distinct

Enfin, le dernier mot-clé avant de tout récapituler, c'est « distinct ». Il permet de spécifier que l'on veut dédoublonner les lignes de la table.

select p.continent from pays as p;
-- dans les faits, un « select » est équivalent à un « select all »
select all p.continent from pays as p;

select distinct p.continent from pays as p

PostgreSQL offre aussi la possibilité de dédoublonner de manière plus spécifique grâce à « select distinct on (expression) ». Cependant, pour l'utiliser, il faudra impérativement spécifier un « order by expression » où les deux expressions sont identiques.

select p.continent, p.nom
from pays as p
order by p.continent asc, p.nom asc

select distinct on (p.continent) p.continent, p.nom
from pays as p
order by p.continent asc, p.nom asc

select distinct on (p.continent) p.continent, p.nom
from pays as p
order by p.continent asc, p.nom desc

On récapitule tout

Bien maîtriser les expressions de valeur et les expressions de table est très important pour se sentir à l'aise dans la rédaction d'une requête.

Voici à quoi ressemble la description la plus complexe que l'on puisse faire d'une table pour le moment :

-- On décrit les colonnes de la table. Il y en aura trois, col1, col2 et col3. Chacune a une expression de valeur qui indique comment la calculer. On veut dédoublonner les lignes.
select distinct expr as col1, expr as col2, expr as col3

-- On décrit les lignes de la table.

-- On aura accès dans les expressions de valeurs à toutes les colonnes de « a » et de « b ». La table aura autant de lignes qu'il y a de combinaisons entre « a » et « b » où la condition est vraie.
from a inner join b on expr

-- On réduit le nombre de lignes. On ne garde que les lignes où la condition est vraie.
where expr

-- On groupe les lignes en utilisant l'expression comme clé. Nous aurons autant de lignes qu'il y a de clés différentes.
group by expr

-- On réduit encore une fois le nombre de lignes, mais cette fois on travaille sur les lignes regroupées par la clause précédente.
having expr

-- On veut classer les lignes en fonction de l'expression.
order by expr asc

-- On veut sauter les 10 premières lignes.
offset 10

-- On veut que la table ait seulement 20 lignes.
limit 20

On remarque qu'il y a des expressions de valeur un peu partout dans notre requête. À chacun de ces endroits, nous pouvons mettre des expressions aussi complexes qu'on le souhaite. Attention tout de même à la particularité des fonctions d'agrégats.

L'ordre d'exécution de la requête est :

• From
• Where
• Group by
• Having
• Select
• Distinct
• Order by
• Offset
• Limit

En rust, la requête précédente donnerait naïvement :

use std::collections::HashMap;

// From
let mut from_item = vec![];

for row_a in a.into_iter() {
  for row_b in b.into_iter() {
    if on(row_a, row_b) {
      let row = row_a.clone();
      row.append(&mut row_b.clone());
      from_item.push(row);
    }
  }
}


let mut map = HashMap::<Key, Vec<Row>>::new();
for row in from_item
  .into_iter()
  // Where
  .filter(|row| where_condition(row))
{
  // Group by
  let key = grouping_element(row);
  map.entry(key).or_default().push(row);
}

let mut table = vec![];
for (key, rows) in map
  .into_iter()
  // Having
  .filter(|(key, rows)| having_condition(key, rows))
{
  // Select
  table.push(select_list(key, rows));
}

// Order by
table.sort();
// Distinct
table.dedup();
// Offset and Limit
let table = table.drain(10..10 + 20).as_slice().to_vec();

Le but d'une requête est de décrire les lignes et les colonnes de la table que l'on souhaite récupérer. Voici un récapitulatif des différentes actions que les mots-clés ont sur les lignes et les colonnes :

Visuellement, l'anatomie d'une requête ressemble à une table ! Avec les colonnes en haut (le select) suivi des lignes (tout le reste).

Nous allons continuer d'approfondir nos connaissances.

Reference :

• Select

Combiner plusieurs selects

Pour combiner plusieurs « selects », on utilisera les mots-clés « union », « intersect », « except ».

(select upper(p.continent) as txt from pays as p)
union
(select lower(p.nom) from pays as p);
-- est équivalent à
(select upper(p.continent) as txt from pays as p)
union distinct
(select lower(p.nom) from pays as p);

Où select statement est un « select » suivi optionnellement par « from », « where », « group by », « having », « order by », « offset » et « limit ».

Par défaut, utiliser « union », « intersect » ou « except » seul est équivalent à les utiliser avec le mot-clé « distinct ». C'est-à-dire que l'on va dédoublonner les lignes. Pour éviter cela, on pourra remplacer le mot-clé « distinct » par « all ». Pour « union », cela donnerait :

(select upper(p.continent) as txt from pays as p)
union
(select 'hello');
-- est équivalent à
(select upper(p.continent) as txt from pays as p)
union distinct
(select 'hello');

(select upper(p.continent) as txt from pays as p)
union all
(select 'hello')

On peut aussi spécifier l'ordre des lignes qui résultera de la combinaison des « selects » grâce à « order by », « offset » et « limit ».

(select upper(p.continent) as txt from pays as p)
union
(select 'hello')
order by txt
offset 2
limit 1

Les mots-clés « union », « intersect », « except » sont comme des opérateurs sur des « selects », on pourra utiliser des parenthèses pour les grouper.

Fonctions de fenêtrage

Les fonctions de fenêtrage (window functions) sont des fonctions qui vous permettent de faire des calculs en utilisant un ensemble de lignes qui sont liées à la ligne courante d'une certaine façon.

Toutes les fonctions d'agrégat peuvent être utilisées comme des fonctions de fenêtrage, mais il existe des fonctions spéciales, faites sur mesure pour cela.

Les fonctions de fenêtrage sont un concept un peu plus complexe que les fonctions d'agrégat car il va falloir définir une fenêtre de lignes, c'est-à-dire un ensemble de lignes, sur laquelle la fonction de fenêtrage sera exécutée.

Elles sont somme toute très pratiques et permettent de faire, entre autres, des moyennes mobiles ou bien des calculs qui dépendent des lignes « précédentes ».

Attention, la syntaxe pour définir des fenêtres est assez verbeuse. Je vous invite donc à regarder avec attention leur section dans la documentation de PostgreSQL pour découvrir pleinement toutes leurs possibilités.

Nous allons quand même voir quelques exemples.

select
  p.nom,
  p.superficie,
  string_agg(p.nom, ' < ') over (order by p.superficie rows between 1 preceding and 1 following) as équation
from pays as p
order by p.nom;
-- est équivalent à
select
  p.nom,
  p.superficie,
  string_agg(p.nom, ' < ') over ma_fenêtre as équation
from pays as p
window ma_fenêtre as (order by p.superficie rows between 1 preceding and 1 following)
order by p.nom;

On repère que l'on utilise une fonction d'agrégat pour la troisième colonne « equation ». Il y a cependant le mot-clé « over » qui indique qu'il s'agit en fait d'une fonction de fenêtrage, et qui par la même occasion, indique soit la description de la fenêtre de lignes, soit son nom.

La fenêtre que l'on vient d'utiliser peut se lire comme : Ordonne les lignes par superficie croissante, puis pour chaque ligne de ma table, les lignes de la fenêtre seront la précédente, l'actuelle et la suivante.

Nous avons donc pour chaque ligne, une équation avec le nom du pays de la ligne encadré par le pays qui a une superficie un tout petit peu plus petite, et le pays qui a une superficie un tout petit peu plus grande.

Pour complexifier un peu plus la requête, on pourrait vouloir avoir la même équation mais en prenant en compte uniquement les pays d'un même continent. Il faut donc que l'on rajoute le mot-clé « partition by » à la définition de la fenêtre.

select
  p.nom,
  p.superficie,
  string_agg(p.nom, ' < ') over ma_fenêtre as équation
from pays as p
window ma_fenêtre as (partition by p.continent order by p.superficie rows between 1 preceding and 1 following)
order by p.nom

Pour finir, nous allons utiliser la fonction de fenêtrage « lag » pour calculer des ratios entre deux lignes qui se suivent.

select
  v.*,
  lag(v.quantité * v.montant) over ma_fenêtre as pre,
  v.quantité * v.montant as act,
  round((v.quantité * v.montant)::numeric / (lag(v.quantité * v.montant) over ma_fenêtre)::numeric, 2) as croissance
from ventes as v
window ma_fenêtre as (partition by v.nom_pays order by v.nom_pays, v.montant rows between 1 preceding and current row)
order by v.nom_pays, v.montant

Combiner des requêtes

Il arrive parfois que l'on ait besoin de faire une ou plusieurs requêtes avant de pouvoir faire la requête de la table que l'on souhaite avoir. Pour cela, nous avons plusieurs moyens d'utiliser des sous-requêtes dans une requête.

Les sous-requêtes scalaires

Dans toutes les expressions de valeur nous pouvons utiliser des sous-requêtes scalaire, c'est-à-dire des sous-requêtes qui renvoient une table avec une colonne et une seule ligne. Une telle table sera alors équivalente à un scalaire.

select 2 * 4 + (select sum(v.quantité) from ventes as v) - 3 as col

En effet, la sous-requête renvoie bien une seule ligne et une seule colonne.

select sum(v.quantité) from ventes as v

La création de tableaux avec une sous-requête

Une sous-requête qui ne possède qu'une seule colonne peut être utilisée pour créer un tableau. On utilisera la syntaxe suivante : « array(subquery) ».

select
  p.nom,
  array(
    select v.montant::text || ' x ' || v.quantité
    from ventes as v
    where v.nom_pays = p.nom
  ) as toutes
from pays as p

En effet, la sous-requête renvoie bien une seule colonne.

select v.montant::text || ' x ' || v.quantité as ventes
from ventes as v
where v.nom_pays = 'Angola'

Les sous-requêtes dans « from »

En mettant entre parenthèse une requête, nous pouvons l'utiliser à la place d'un nom de table dans le « from » de notre requête principale.

select sous_table.*
from
  (
    select v.nom_pays, sum(v.montant * v.quantité) as total
    from ventes as v
    group by v.nom_pays
  ) as sous_table
where sous_table.total < 100_000::money

select v.nom_pays, sum(v.montant * v.quantité) as total
from ventes as v
group by v.nom_pays

Les expressions de table commune

Les expressions de table commune (Common Table Expressions) ou CTE sont des sous-requêtes que l'on peut définir avant la requête principale. C'est un peu comme des variables que l'on pourra utiliser pour calculer des tables intermédiaires ou simplifier notre requête.

La requête précédente peut être écrite :

with
  sous_table as (
    select v.nom_pays, sum(v.montant * v.quantité) as total
    from ventes as v
    group by v.nom_pays
  )
select sous_table.*
from sous_table
where sous_table.total < 100_000::money

Toutes ces variables pourront être utilisées dans les « from » des sous-requêtes suivantes et de la requête principale.

On peut avoir autant de variables que l'on veut, voici un autre exemple :

with
  montant_max as (select max(v.montant) from ventes as v),
  quantité_max as (select max(v.quantité) from ventes as v)
select
  v.nom_pays,
  round((v.montant * 100 / (select * from montant_max))::numeric, 2) as montant_ratio,
  round((v.quantité * 100 / (select * from quantité_max))::numeric, 2) as quantité_ratio
from ventes as v

Dans les cas où une requête est sous la forme « select * from ma_table », on peut la simplifier par le « table ma_table ». Attention, le mot-clé « table » ne sert qu'à ça. Il ne peut pas être utilisé avec les autres mots-clés (« where », « group by », etc).

with
  montant_max as (select max(v.montant) from ventes as v),
  quantité_max as (select max(v.quantité) from ventes as v)
select
  v.nom_pays,
  round((v.montant * 100 / (table montant_max))::numeric, 2) as montant_ratio,
  round((v.quantité * 100 / (table quantité_max))::numeric, 2) as quantité_ratio
from ventes as v

Bravo à vous 👏🎉🥳

Félicitation à vous d'être arrivé jusqu'à la fin de cet article. Vous devriez pouvoir faire tous les exercices de LeetCode sur SQL sans peine en quelques jours. J'espère aussi que vous avez maintenant une meilleure connaissance du langage SQL et que vous vous sentez à l'aise dans l'expression de vos idées.

Il existe deux ressources que je vous recommande chaudement pour approfondir vos connaissances ! C'est la référence de PostgreSQL sur les commandes SQL et la documentation de PostgreSQL sur le langage SQL.

Évidemment, si vous utilisez une autre base de données SQL, je vous invite à aller chercher l'information à la source directement, c'est-à-dire dans la documentation de votre base de données. Savoir naviguer en leur sein est important.

Annexe 1 : Quelques expressions qu'il est bon d'avoir en tête

Voici une liste d'expressions non exhaustive que j'ai trouvé utile pour résoudre les problèmes de LeetCode.

Ces expressions illustrent bien la variété des syntaxes que l'on peut utiliser pour appeler ce qui de fait se comporte comme une fonction. Tantôt avec des parenthèses, tantôt avec des mots-clés et parfois avec les deux en même temps. Il y a aussi des inclassables comme la fonction « unnest » qui permet de transformer un tableau en plusieurs lignes...

SQL nous montre ici tous les avantages qu'un Domain-Specific Language (DSL) peut nous apporter mais aussi tous ses inconvénients. En quelques lignes de code, nous pouvons exprimer des idées complexes qui nous prendraient normalement beaucoup de temps à écrire dans un langage classique. Notre productivité en est décuplée. Mais très vite, on aura envie d'avoir plus d'expressivité, de pouvoir composer, tester et organiser son code, en somme d'avoir accès à un « vrai » langage de programmation. Prendre SQL pour ce qu’il est, avec ses défauts et ses qualités, est sans doute la recette pour s’épanouir avec lui.