Expressions de table courantes

Manipulation de données en SQL

Mona Khalil

Data Scientist, Greenhouse Software

Lors de l'ajout de sous-requêtes…

La complexité des requêtes augmente rapidement
- Il peut être difficile de suivre les informations

Solution : Expressions de table courantes

Expressions de table courantes

Expressions de table courantes (CTE)

Table déclarée avant la requête principale
Nommées et mentionnées plus loin dans l’instruction FROM

Configuration des CTE

WITH cte AS (
    SELECT col1, col2
    FROM table)

SELECT
    AVG(col1) AS avg_col
FROM cte;

Utiliser 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       |

À placer au début

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

À placer au début

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

Montrer les 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       |

Montrer toutes les CTE

WITH s1 AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10),
s2 AS (                              -- New subquery
  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         -- New column
FROM country AS c
INNER JOIN s1
ON c.id = s1.country_id
INNER JOIN s2                        -- New join
ON c.id = s2.country_id
GROUP BY country;

Pourquoi utiliser les CTE ?

Exécutée une fois
- La CTE est ensuite stockée en mémoire
- Améliore les performances des requêtes
Amélioration de l'organisation des requêtes
Référencement d'autres CTE
Référence à elle-même (SELF JOIN)

Passons à la pratique !

Manipulation de données en SQL