如何在单个 SQL 查询中高效聚合多个数组？

单个查询中多次调用 array_agg()：聚合嵌套数组

在关系数据库领域，会出现多个数组的情况需要聚合在单个查询中。这可能是一项棘手的任务，尤其是在涉及联接时。

问题

尝试通过多个联接聚合来自不同表的数组时会遇到挑战。生成的数组可能包含不必要的重复和不一致。例如，您可能有一个包含多个地址（地址）和工作日（工作日）的员工表。

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

此查询会生成一组街道名称和工作日，但它会重复值

解决方案：先聚合

解决这个问题的关键是在执行连接之前聚合数组。通过首先聚合，可以防止行不必要地增加。

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;

在此查询中，地址聚合在子查询中完成，然后再与工作日表连接。这确保只有一组街道名称和工作日与每个员工关联。

或者：关联子查询和 JOIN LATERAL

对于员工的选择性过滤，关联子查询或 JOIN LATERAL（在 Postgres 9.3 或更高版本中）可以就业：

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 也可以在 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

这些查询将在结果中保留所有符合条件的员工。

以上是如何在单个 SQL 查询中高效聚合多个数组？的详细内容。更多信息请关注PHP中文网其他相关文章！