SQL Avancé : optimisation des bases de données

-- Clients sans aucune commande (anti-jointure)
SELECT c.id, c.nom
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
WHERE o.id IS NULL;

-- Variante avec NOT EXISTS (souvent plus rapide)
SELECT c.id, c.nom
FROM clients c
WHERE NOT EXISTS (
  SELECT 1 FROM commandes o WHERE o.client_id = c.id
);

SELECT COALESCE(a.id, b.id) AS id, a.donnee_source, b.donnee_cible
FROM source a
FULL OUTER JOIN cible b ON a.id = b.id;

-- Pour chaque client, les 3 dernières commandes
SELECT c.id, c.nom, d.id AS commande_id, d.total
FROM clients c
LEFT JOIN LATERAL (
  SELECT id, total
  FROM commandes
  WHERE client_id = c.id
  ORDER BY cree_le DESC
  LIMIT 3
) d ON true;

WITH top_clients AS (
  SELECT client_id, SUM(total) AS ca
  FROM commandes
  WHERE cree_le >= '2026-01-01'
  GROUP BY client_id
  ORDER BY ca DESC
  LIMIT 10
)
SELECT c.nom, t.ca
FROM top_clients t
JOIN clients c ON c.id = t.client_id;

-- Catégories en arborescence : parent_id pointe vers la catégorie parent
WITH RECURSIVE arbre AS (
  -- Cas de base : les racines (sans parent)
  SELECT id, parent_id, nom, 1 AS niveau
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Récursion : on prend les enfants des nœuds déjà trouvés
  SELECT c.id, c.parent_id, c.nom, a.niveau + 1
  FROM categories c
  JOIN arbre a ON c.parent_id = a.id
)
SELECT * FROM arbre ORDER BY niveau, nom;

-- Classement des commandes par client
SELECT
  client_id,
  id,
  total,
  ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY total DESC) AS rang
FROM commandes;

-- Évolution du CA mois par mois
SELECT
  mois,
  ca,
  LAG(ca) OVER (ORDER BY mois) AS ca_mois_precedent,
  ca - LAG(ca) OVER (ORDER BY mois) AS variation
FROM ca_mensuel;

-- Cumul du CA depuis le début de l'année
SELECT
  jour,
  ca_jour,
  SUM(ca_jour) OVER (ORDER BY jour) AS ca_cumule
FROM ca_journalier;

-- Moyenne glissante sur 7 jours
SELECT
  jour,
  ca_jour,
  AVG(ca_jour) OVER (
    ORDER BY jour
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moyenne_7j
FROM ca_journalier;

-- Sous-totaux par région ET par produit ET total général, en une passe
SELECT region, produit, SUM(ca) AS ca
FROM ventes
GROUP BY ROLLUP (region, produit);

-- Toutes les combinaisons (region, produit, region+produit, total)
SELECT region, produit, SUM(ca) AS ca
FROM ventes
GROUP BY CUBE (region, produit);

SELECT
  region,
  COUNT(*) FILTER (WHERE statut = 'PAYEE') AS payees,
  COUNT(*) FILTER (WHERE statut = 'ANNULEE') AS annulees,
  SUM(total) FILTER (WHERE statut = 'PAYEE') AS ca_paye
FROM commandes
GROUP BY region;

-- Cas typique : on filtre par client et on trie par date
CREATE INDEX idx_cmd_client_date
  ON commandes (client_id, cree_le DESC);

-- Cet index accélère :
SELECT * FROM commandes WHERE client_id = 42 ORDER BY cree_le DESC;
-- ET aussi : WHERE client_id = 42 (le préfixe gauche suffit)

-- Mais PAS : WHERE cree_le > '2026-01-01' tout seul
-- (la colonne client_id manque en préfixe)

-- Si 99% des commandes sont 'PAYEE', un index global est inutile.
-- Mais on cherche souvent les 1% qui restent :
CREATE INDEX idx_cmd_pending
  ON commandes (cree_le)
  WHERE statut = 'EN_ATTENTE';

-- Index minuscule, ultra rapide sur les requêtes filtrées par statut.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM commandes
WHERE client_id = 42 AND cree_le > NOW() - INTERVAL '30 days';

BEGIN;
  -- Verrouille la ligne jusqu'à COMMIT/ROLLBACK
  SELECT solde FROM comptes WHERE id = 1 FOR UPDATE;
  -- Calcul...
  UPDATE comptes SET solde = solde - 100 WHERE id = 1;
COMMIT;