SQL 結合で複数の `array_agg()` 呼び出しを使用するときに行の乗算を回避するにはどうすればよいですか?

単一クエリでの複数の array_agg() 呼び出しの解決

配列を含む複数の結合を使用する場合、それらの結合が次のクエリに与える影響を理解することが重要です。結果。この場合、元のクエリは各従業員の住所と勤務日の両方の配列を抽出しようとします。ただし、複数の結合による行の予期しない乗算により、誤った集計が行われます。

解決策 1: 最初に集計し、後で結合

これを解決するには、次のような効果的なアプローチがあります。結合する前にサブクエリ内のデータを集計します。集計を結合から分離することで、行の乗算の問題が回避されます。

SELECT e.name, e.age, ad.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;

解決策 2: 相関サブクエリまたは 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';  -- very selective

代わりに、Postgres 9.3 以降で 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';  -- very selective

これらの代替アプローチにより、条件を満たすすべての従業員が結果に保持され、以前の結合が回避されます。行の乗算の問題

以上がSQL 結合で複数の `array_agg()` 呼び出しを使用するときに行の乗算を回避するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。