Requêtes

Requêtes de base

SELECT * FROM orders
WHERE amount > 50
ORDER BY amount DESC
LIMIT 10 OFFSET 5;

Agrégations

SELECT status, COUNT(*), AVG(amount), SUM(amount)
FROM orders
GROUP BY status
HAVING COUNT(*) > 5;

FILTER sur les agrégats

Appliquer un filtre par agrégat sans affecter les autres agrégats de la même requête :

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  SUM(amount) FILTER (WHERE amount > 100) AS high_value_total
FROM orders;

Fonctions de fenêtre

Les fonctions de fenêtre calculent des valeurs sur un ensemble de lignes liées à la ligne courante, sans regrouper les lignes comme GROUP BY.

Fonctions de classement

SELECT name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

Fonctions de décalage

SELECT name, salary,
  LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
  LEAD(salary, 1, 0) OVER (ORDER BY salary) AS next_salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

LAG(expr [, offset [, default]]) et LEAD(expr [, offset [, default]]) acceptent un décalage optionnel (par défaut 1) et une valeur par défaut (par défaut NULL).

Fonctions de valeur

SELECT name, department, salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary) AS lowest_paid,
  LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_paid,
  NTH_VALUE(name, 2) OVER (PARTITION BY department ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_lowest
FROM employees;

Fonction	Description
FIRST_VALUE(expr)	Valeur de expr à la première ligne du cadre de fenêtre
LAST_VALUE(expr)	Valeur de expr à la dernière ligne du cadre de fenêtre
NTH_VALUE(expr, n)	Valeur de expr à la n-ième ligne du cadre (base 1), ou NULL si pas de telle ligne

Le cadre par défaut est ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Pour LAST_VALUE et NTH_VALUE, vous voudrez généralement ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING pour voir la partition entière.

Fonctions de fenêtre avec agrégats

Toute fonction d’agrégation peut être utilisée avec OVER() :

SELECT name, department, salary,
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  AVG(salary) OVER () AS overall_avg,
  COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees;

Spécifications de cadre

Contrôlez quelles lignes au sein de la partition contribuent à une fonction de fenêtre avec agrégat :

SELECT name, salary,
  SUM(salary) OVER (ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(salary) OVER (ORDER BY salary
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM employees;

Bornes de cadre supportées :

• UNBOUNDED PRECEDING / UNBOUNDED FOLLOWING — début/fin de la partition
• CURRENT ROW — la ligne courante
• N PRECEDING / N FOLLOWING — N lignes avant/après la ligne courante

Sans clause de cadre, les fonctions de fenêtre avec agrégats calculent sur l’ensemble de la partition.

FILTER avec fonctions de fenêtre

La clause FILTER fonctionne avec les fonctions de fenêtre avec agrégats :

SELECT name, salary,
  SUM(salary) FILTER (WHERE salary > 70000)
    OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS running_high_earner_total
FROM employees;

Jointures

Jointures INNER, LEFT, RIGHT, FULL et CROSS :

SELECT o.id, o.amount, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Sous-requêtes et EXISTS

SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.amount > 100
);

Jointures LATERAL

Sous-requêtes corrélées dans FROM :

SELECT c.name, recent.amount
FROM customers c,
LATERAL (
  SELECT amount FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC LIMIT 1
) AS recent;

VALUES comme source

SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t (id, name);

Opérations ensemblistes

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

UNION, UNION ALL, INTERSECT et EXCEPT sont supportés.

Expressions de table communes (WITH)

Les CTE définissent des sous-requêtes nommées qui peuvent être référencées dans la requête principale, améliorant la lisibilité et permettant la réutilisation :

WITH active_users AS (
  SELECT id, name FROM users WHERE active = TRUE
),
user_orders AS (
  SELECT u.name, COUNT(*) AS order_count
  FROM active_users u
  INNER JOIN orders o ON u.id = o.user_id
  GROUP BY u.name
)
SELECT name, order_count FROM user_orders ORDER BY order_count DESC;

Les alias de colonnes peuvent être spécifiés : WITH t(x, y) AS (SELECT 1, 2).

Les CTE suivantes peuvent référencer les CTE précédentes. Un nom de CTE masque toute table portant le même nom.

CTE récursives

WITH RECURSIVE permet des requêtes itératives pour les données hiérarchiques, la traversée de graphes et la génération de séries :

-- Générer une série de nombres
WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

-- Traversée d'arbre
WITH RECURSIVE tree(id, name, depth) AS (
  SELECT id, name, 0 FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.depth + 1
  FROM categories c INNER JOIN tree t ON c.parent_id = t.id
)
SELECT name, depth FROM tree ORDER BY depth, name;

Le corps de la CTE récursive doit être un UNION ALL ou UNION d’une requête ancre (cas de base non récursif) et d’une requête récursive (qui référence le nom de la CTE). L’exécution s’arrête quand la requête récursive ne produit plus de nouvelles lignes, ou après 1000 itérations.

Expressions CASE

SELECT name,
  CASE WHEN amount > 100 THEN 'high' ELSE 'low' END AS tier
FROM orders;

Correspondance de motifs

SELECT * FROM products WHERE name LIKE '%phone%';
SELECT * FROM products WHERE name ILIKE '%Phone%';   -- insensible à la casse

BETWEEN, IN, ANY

SELECT * FROM orders WHERE amount BETWEEN 10 AND 100;
SELECT * FROM orders WHERE status IN ('pending', 'shipped');
SELECT * FROM orders WHERE status = ANY(ARRAY['pending', 'shipped']);

OVERLAPS

Teste si deux intervalles de dates/heures se chevauchent :

SELECT (DATE '2024-01-01', DATE '2024-01-31')
  OVERLAPS (DATE '2024-01-15', DATE '2024-02-15');

DISTINCT

SELECT DISTINCT category FROM products;

DISTINCT ON

Retourne une ligne par valeur distincte des expressions données. La première ligne de chaque groupe (selon ORDER BY) est conservée :

SELECT DISTINCT ON (department) department, name, salary
FROM employees
ORDER BY department, salary DESC;

Cela retourne l’employé le mieux payé par département.

EXPLAIN

Afficher le plan d’exécution de la requête :

EXPLAIN SELECT * FROM orders WHERE status = 'pending';

Constructeur ARRAY

SELECT ARRAY[1, 2, 3];