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