如何在單一 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中文網其他相關文章！