単一の 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;

このクエリでは、アドレスの集計は、営業日テーブルと結合する前にサブクエリで行われます。これにより、各従業員に関連付けられる番地名と営業日は 1 組のみになります。

代わりに: 相関サブクエリと 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 中国語 Web サイトの他の関連記事を参照してください。