Expressions de table communes

Manipulation de données en SQL

Mona Khalil

Data Scientist, Greenhouse Software

En ajoutant des sous-requêtes...

La complexité des requêtes augmente vite !
- L'information devient difficile à suivre

Solution : expressions de table communes !

Expressions de table communes

Expressions de table communes (CTE)

Table déclarée avant la requête principale
Nommée et référencée plus tard dans FROM

Configurer des CTE

WITH cte AS (
    SELECT col1, col2
    FROM table)

SELECT
    AVG(col1) AS avg_col
FROM cte;

Prendre une sous-requête dans FROM

SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10) AS s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

La placer au début

(
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

La placer au début

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

Afficher la CTE

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)
SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

Afficher toutes les CTE

WITH s1 AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10),
s2 AS (                              -- Nouvelle sous-requête
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) <= 1
)
SELECT
  c.name AS country,
  COUNT(s1.id) AS high_scores,
  COUNT(s2.id) AS low_scores         -- Nouvelle colonne
FROM country AS c
INNER JOIN s1
ON c.id = s1.country_id
INNER JOIN s2                        -- Nouvelle jointure
ON c.id = s2.country_id
GROUP BY country;

Pourquoi utiliser des CTE ?

Exécutée une seule fois
- La CTE est ensuite gardée en mémoire
- Améliore la performance de la requête
Mieux organiser les requêtes
Référencer d'autres CTE
Se référencer elle-même (SELF JOIN)

Passons à la pratique !

Manipulation de données en SQL