Sous-requêtes et agrégations

Dans le chapitre précédent, on a vu Les jointures : combiner les données. Tu sais croiser des tables. Maintenant, on attaque les outils qui font la différence entre un débutant et un praticien efficace.

🎯 Objectif : Maîtriser les opérations ensemblistes (UNION, INTERSECT, EXCEPT), les vues SQL pour simplifier les requêtes complexes, et les index pour optimiser les performances.

Opérations ensemblistes

Les opérations ensemblistes combinent les résultats de deux requêtes SELECT. Condition : les deux requêtes doivent avoir le même nombre de colonnes avec des types compatibles.

UNION : fusionner deux résultats

UNION combine les résultats et supprime les doublons. UNION ALL garde tout — et c’est plus rapide car il n’y a pas de déduplication.

-- Tous les hôtes (serveurs + conteneurs) dans un même résultat
SELECT hostname AS name, ip_address, 'server' AS type
FROM servers
UNION
SELECT container_name, container_ip, 'container'
FROM containers;

-- UNION ALL : plus rapide, garde les doublons
SELECT hostname, environment FROM servers
UNION ALL
SELECT hostname, environment FROM archived_servers;

INTERSECT et EXCEPT

INTERSECT retourne les lignes présentes dans les deux requêtes. EXCEPT retourne celles de la première absentes de la seconde.

-- Services déployés à la fois en prod ET en staging
SELECT name FROM services WHERE environment = 'production'
INTERSECT
SELECT name FROM services WHERE environment = 'staging';

-- Services en prod mais PAS en staging (risque de divergence)
SELECT name FROM services WHERE environment = 'production'
EXCEPT
SELECT name FROM services WHERE environment = 'staging';

💡 Cas d’usage DevOps typique : comparer les services entre environnements. Si un service est en production mais absent du staging, c’est un risque — impossible de tester les mises à jour avant la prod.

⚠️ EXCEPT s’appelle MINUS dans Oracle. Le reste de la syntaxe est identique. PostgreSQL et MySQL utilisent EXCEPT.

Vues SQL : des requêtes réutilisables

Une vue est une requête sauvegardée qui se comporte comme une table virtuelle. Elle ne stocke aucune donnée — elle recalcule le résultat à chaque interrogation.

Créer et utiliser une vue

-- Dashboard serveurs : jointure complexe encapsulée
CREATE VIEW v_server_dashboard AS
SELECT s.hostname, s.ip_address, s.environment, s.status,
       s.cpu_cores, s.ram_gb,
       d.name AS datacenter, d.location,
       COUNT(svc.service_id) AS nb_services,
       COUNT(CASE WHEN a.resolved_at IS NULL THEN 1 END) AS open_alerts
FROM servers s
LEFT JOIN datacenters d ON s.datacenter_id = d.datacenter_id
LEFT JOIN services svc ON s.server_id = svc.server_id
LEFT JOIN alerts a ON s.server_id = a.server_id
GROUP BY s.server_id, s.hostname, s.ip_address, s.environment,
         s.status, s.cpu_cores, s.ram_gb, d.name, d.location;

-- Utilisation : aussi simple qu'une table
SELECT * FROM v_server_dashboard
WHERE environment = 'production' AND open_alerts > 0;

-- Modifier ou supprimer
CREATE OR REPLACE VIEW v_server_dashboard AS SELECT ...;
DROP VIEW IF EXISTS v_server_dashboard;

🔥 Les vues sont un outil de simplification puissant. Au lieu de copier-coller une requête de 20 lignes dans chaque script, tu crées une vue et tu la requêtes en une ligne. C’est le DRY appliqué au SQL.

Les vues servent aussi à la sécurité : tu peux exposer une vue qui ne montre que certaines colonnes, sans donner accès à la table complète. Un analyste voit les statistiques, pas les données personnelles.

Index et performances

Comment fonctionne un index

Sans index, PostgreSQL doit scanner toutes les lignes d’une table pour trouver celles qui matchent ton WHERE — c’est un full table scan. Un index crée une structure de recherche optimisée (un B-tree par défaut) qui pointe directement vers les bonnes lignes.

L’analogie : un livre de 500 pages sans index te force à lire chaque page pour trouver un sujet. Avec un index alphabétique en fin de livre, tu trouves la page en 5 secondes.

-- Index simple sur une colonne fréquemment filtrée
CREATE INDEX idx_servers_environment ON servers(environment);

-- Index composite : utile quand tu filtres sur deux colonnes
CREATE INDEX idx_servers_env_status ON servers(environment, status);

-- Index unique : garantit l'unicité en plus de la performance
CREATE UNIQUE INDEX idx_servers_hostname ON servers(hostname);

-- Index partiel (PostgreSQL) : indexe seulement un sous-ensemble
CREATE INDEX idx_alerts_open ON alerts(server_id, severity)
WHERE resolved_at IS NULL;

Quand créer (et ne pas créer) un index

Bon candidat : colonnes dans WHERE, colonnes de jointure (clés étrangères), colonnes de ORDER BY, colonnes avec beaucoup de valeurs distinctes.

Pas besoin : tables de moins de 1000 lignes, colonnes booléennes (trop peu de valeurs distinctes), colonnes rarement filtrées.

⚠️ L’ordre des colonnes dans un index composite compte. L’index (environment, status) accélère WHERE environment = 'production' et WHERE environment = 'production' AND status = 'running', mais pas WHERE status = 'running' seul — la première colonne doit être dans le filtre.

EXPLAIN : diagnostiquer les performances

-- Voir le plan d'exécution d'une requête
EXPLAIN ANALYZE
SELECT s.hostname, d.name
FROM servers s
JOIN datacenters d ON s.datacenter_id = d.datacenter_id
WHERE s.environment = 'production';

Le résultat montre : quels index sont utilisés (ou ignorés), combien de lignes sont scannées, et le temps réel d’exécution. Si tu vois Seq Scan sur une grosse table avec un WHERE, c’est le signe qu’un index manque.

💡 Règle pragmatique : ajoute des index sur les requêtes lentes que tu exécutes souvent. Ne mets pas d’index partout — chaque index ralentit les écritures (INSERT, UPDATE, DELETE) et consomme de l’espace disque.

Cas entreprise : tableau de bord opérationnel

Un SRE construit un dashboard pour l’astreinte. Il combine vues, jointures et opérations ensemblistes pour donner une vision complète de l’infrastructure.

-- Vue résumé par datacenter
CREATE VIEW v_ops_summary AS
SELECT d.name AS datacenter,
       COUNT(DISTINCT s.server_id) AS nb_servers,
       COUNT(DISTINCT svc.service_id) AS nb_services,
       SUM(s.cpu_cores) AS total_cpus,
       SUM(s.ram_gb) AS total_ram,
       COUNT(DISTINCT CASE WHEN a.resolved_at IS NULL
             THEN a.alert_id END) AS open_alerts
FROM datacenters d
LEFT JOIN servers s ON d.datacenter_id = s.datacenter_id
LEFT JOIN services svc ON s.server_id = svc.server_id
LEFT JOIN alerts a ON s.server_id = a.server_id
GROUP BY d.datacenter_id, d.name;

-- Timeline unifiée des événements (déploiements + alertes)
SELECT 'deploy' AS event_type, svc.name AS source,
       dep.deploy_time AS event_time
FROM deployments dep
JOIN services svc ON dep.service_id = svc.service_id
UNION ALL
SELECT 'alert', s.hostname, a.created_at
FROM alerts a
JOIN servers s ON a.server_id = s.server_id
ORDER BY event_time DESC
LIMIT 20;

🎯 La vue v_ops_summary remplace un dashboard custom. Un SELECT * FROM v_ops_summary donne l’état complet de l’infra en une requête. L’astreinteur voit immédiatement quel datacenter a des alertes ouvertes et où sont concentrées les ressources.

Pièges et bonnes pratiques

⚠️ Les erreurs classiques à éviter :

• UNION au lieu de UNION ALL — Si tu n’as pas besoin de dédupliquer, utilise UNION ALL. La déduplication force un tri coûteux sur de gros résultats.
• Trop d’index — Chaque index ralentit les écritures. Sur une table avec beaucoup d’INSERT (logs, métriques), chaque index supplémentaire impacte les performances.
• Index ignoré — PostgreSQL choisit le plan le plus efficace. Sur une petite table ou un filtre peu sélectif, il peut ignorer ton index et faire un scan complet. C’est normal.
• Vues imbriquées — Une vue qui appelle une autre vue qui appelle une troisième vue. Le plan d’exécution devient illisible et les performances imprévisibles. Limite l’imbrication à un niveau.
• Oublier EXPLAIN — Avant d’ajouter un index, vérifie d’abord avec EXPLAIN ANALYZE que ta requête est réellement lente et qu’un scan complet est bien le problème.

💡 Astuce PostgreSQL : CREATE INDEX CONCURRENTLY crée un index sans bloquer les écritures sur la table. Indispensable en production quand tu ne peux pas te permettre un downtime.

Résumé

🔥 Ce qu’on a couvert : les opérations ensemblistes (UNION / UNION ALL pour fusionner, INTERSECT pour les éléments communs, EXCEPT pour les différences), les vues pour encapsuler les requêtes complexes et simplifier le quotidien, et les index pour accélérer les lectures — avec EXPLAIN ANALYZE pour diagnostiquer avant d’optimiser.

Avec ces 4 chapitres SQL, tu as les bases solides : requêtes simples, agrégation, jointures, sous-requêtes, vues et optimisation. Le SQL que tu écriras en entreprise utilise exactement ces concepts — la maîtrise vient avec la pratique sur des données réelles.

💡 À retenir : une vue simplifie, un index accélère, EXPLAIN diagnostique. Ces trois outils transforment un développeur qui “fait du SQL” en un praticien qui écrit des requêtes efficaces et maintenables.