如何避免 PostgreSQL 中多个嵌套 `array_agg()` 调用产生意外结果？

避免在 PostgreSQL 查询中使用多个嵌套聚合

单个查询中的多个 array_agg() 调用可能会导致意外结果，如提供的示例。此问题是由于连接具有多行的表而有效地创建笛卡尔积而引起的。要纠正此问题，请考虑以下策略：

先聚合，稍后加入

在连接之前在子查询中分别聚合每个表中的数据。这可确保您聚合一组唯一的行：

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
) e
JOIN workingdays wd ON wd.employeeid = e.id
GROUP BY e.id, e.name, e.age;

相关子查询或 JOIN LATERAL

使用相关子查询或 JOIN LATERAL 聚合每行的数据单独，允许选择性过滤器：

相关子查询：

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';

JOIN LATERAL：

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';

这些方法可以防止不必要的行重复并提供所需的数组聚合结果。

以上是如何避免 PostgreSQL 中多个嵌套 `array_agg()` 调用产生意外结果？的详细内容。更多信息请关注PHP中文网其他相关文章！