PostgreSQL で LEFT JOIN が間違ったカウントを返すのはなぜですか?

PostgreSQL LEFT JOIN: 不正なカウント結果への対処

PostgreSQL で LEFT JOIN を使用して組織ごとのエントリをカウントすると、カウントが 0 より大きい組織のみが返される状況が発生する可能性があります。 これは、結合されたテーブルに一致するエントリがあるかどうかに関係なく、すべての組織を含める必要がある LEFT JOIN の予期される動作と矛盾します。

LEFT JOIN クエリの修正

この問題は通常、フィルタリング条件が正しく設定されていないことが原因で発生します。 次の改訂されたクエリは、正しいアプローチを示しています:

<code class="language-sql">SELECT
  o.name AS organisation_name,
  COUNT(e.id) AS total_used
FROM
  organisations o
LEFT JOIN
  exam_items e
  ON e.organisation_id = o.id
  AND e.item_template_id = #{sanitize(item_template_id)}
  AND e.used
GROUP BY
  o.name
ORDER BY
  o.name;</code>

重要な変更には、e.used 条件を WHERE 句 (結合の後 のフィルターとして機能する) から ON 句 (結合条件として機能する) に移動することが含まれます。 ）。これにより、すべての条件を満たす一致する行のみが結合自体に含まれるようになり、カウントがゼロの組織が意図せずフィルタリングされるのを防ぎます。

最適化されたアプローチ: 事前集計

特に大規模なデータセットを扱う場合のパフォーマンスを向上させるには、次の代替クエリを検討してください。

<code class="language-sql">SELECT
  o.id,
  o.name AS organisation_name,
  COALESCE(e.total_used, 0) AS total_used
FROM
  organisations o
LEFT JOIN
  (
    SELECT
      organisation_id AS id,
      COUNT(*) AS total_used
    FROM
      exam_items
    WHERE
      item_template_id = #{sanitize(item_template_id)}
      AND used
    GROUP BY
      1
  ) AS e
  USING (id)
ORDER BY
  o.name,
  o.id;</code>

このメソッドは、まずサブクエリ内のカウントを集計し、次に結合を実行します。 この事前集計により、特に exam_items 行のかなりの部分がカウントに含まれる場合、効率が大幅に向上します。 COALESCE 関数は、total_used が NULL (一致するエントリがない組織の場合) の場合を処理し、一貫性を保つために 0 に置き換えます。

以上がPostgreSQL で LEFT JOIN が間違ったカウントを返すのはなぜですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。