Comment agréger efficacement plusieurs tableaux dans une seule requête SQL ?

Appels multiples array_agg() dans une seule requête : agrégation de tableaux imbriqués

Dans le domaine des bases de données relationnelles, la situation se présente lorsque plusieurs tableaux doivent être regroupés dans une seule requête. Cela peut être une tâche délicate, surtout lorsque des jointures sont impliquées.

Le problème

Le défi survient lorsque l'on tente d'agréger des tableaux de différentes tables via plusieurs jointures. Le tableau résultant peut contenir des doublons et des incohérences inutiles. Par exemple, vous pouvez avoir un tableau d'employés avec plusieurs adresses (address) et jours ouvrables (workingdays).

SELECT name, age, array_agg(ad.street), arrag_agg(wd.day)
FROM employees e
JOIN address ad ON e.id = ad.employeeid
JOIN workingdays wd ON e.id = wd.employeeid
GROUP BY name, age

Cette requête génère un tableau de noms de rues et de jours ouvrables, mais elle duplique les valeurs plusieurs fois.

La solution : agréger d'abord

La clé pour résoudre ce problème est d'agréger les tableaux avant d'effectuer la jointure. En agrégeant d'abord, vous évitez que les lignes ne se multiplient inutilement.

SELECT e.id, e.name, e.age, e.streets, array_agg(wd.day) AS days
FROM (
   SELECT e.id, e.name, e.age, array_agg(ad.street) AS streets
   FROM   employees e 
   JOIN   address  ad ON ad.employeeid = e.id
   GROUP  BY e.id  -- PK covers whole row
   ) e
JOIN   workingdays wd ON wd.employeeid = e.id
GROUP  BY e.id, e.name, e.age;

Dans cette requête, l'agrégation des adresses se fait dans une sous-requête avant de la joindre à la table des jours ouvrables. Cela garantit qu'un seul ensemble de noms de rues et de jours ouvrables est associé à chaque employé.

Alternative : sous-requêtes corrélées et JOIN LATERAL

Pour un filtrage sélectif sur les employés, corrélés les sous-requêtes ou JOIN LATERAL (dans Postgres 9.3 ou version ultérieure) peuvent être employé :

SELECT name, age
    , (SELECT array_agg(street) FROM address WHERE employeeid = e.id) AS streets
    , (SELECT array_agg(day) FROM workingdays WHERE employeeid = e.id) AS days
FROM   employees e
WHERE  e.namer = 'peter';  -- very selective

JOIN LATERAL peut également être utilisé dans Postgres :

SELECT e.name, e.age, a.streets, w.days
FROM   employees e
LEFT   JOIN LATERAL (
   SELECT array_agg(street) AS streets
   FROM   address
   WHERE  employeeid = e.id
   GROUP  BY 1
   ) a ON true
LEFT   JOIN LATERAL (
   SELECT array_agg(day) AS days
   FROM   workingdays
   WHERE  employeeid = e.id
   GROUP  BY 1
   ) w ON true
WHERE  e.name = 'peter';  -- very selective

Ces requêtes conserveront tous les employés éligibles dans le résultat.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!