単一の SQL クエリで複数の `array_agg()` 呼び出しを結合するときに冗長データを回避するにはどうすればよいですか?

単一のクエリで複数の array_agg() 呼び出しを結合する

クエリで、複数の array_agg() 呼び出しが冗長を返すという問題が発生しました。データ。複数の結合により重複行が存在すると、望ましくない結果が生じます。これを解決するには、最初から行の乗算を回避するいくつかのアプローチを検討できます。

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

1 つの効果的な方法は、結合を実行する前にサブクエリ内のデータを削除します。これにより、各従業員レコードが一意になることが保証され、行の重複が防止されます。変更されたクエリは次のようになります。

SELECT e.id, 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

もう 1 つのアプローチは、PostgreSQL 9.3 で相関サブクエリまたは 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

JOINラテラル:

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 サイトの他の関連記事を参照してください。