複数の SQL LEFT JOIN によって不正確な訪問数が生成されるのはなぜですか?

複数の SQL LEFT JOIN による不正確な訪問数について理解する

この記事では、SQL クエリの一般的な問題、つまり複数の LEFT JOIN 操作を使用したときに誤った訪問数が取得される問題について調査します。 目標は、アカウント残高、食料品店への訪問、魚市場への訪問などのユーザー データを取得することです。 単純なクエリでは、多くの場合、誇張された結果 (例: "1"、"12"、"12") が生成されます。

この問題は、LEFT JOIN の左から右への実行に起因します。 結合されたテーブルに複数の一致するエントリがある場合、後続の結合ごとに行数が増加します。 たとえば、3 つの食料品テーブルを 1 人のユーザーに結合すると、結果は 3 行になります。 その後、4 つの魚市場テーブルと結合すると、これが 12 行に拡張され、訪問数が不正確になります。

解決策: サブクエリによる事前集計

この解決策には、users テーブルに結合する前に、各テーブルの訪問数を事前に集計することが含まれます。これにより、行の乗算の問題が回避されます。 修正されたクエリでは、サブクエリを使用してこれを実現します。

SELECT u.id, u.account_balance, g.grocery_visits, f.fishmarket_visits
FROM users u
LEFT JOIN (
   SELECT user_id, COUNT(*) AS grocery_visits
   FROM grocery
   GROUP BY user_id
   ) g ON g.user_id = u.id
LEFT JOIN (
   SELECT user_id, COUNT(*) AS fishmarket_visits
   FROM fishmarket
   GROUP BY user_id
   ) f ON f.user_id = u.id
ORDER BY u.id;

COALESCE による欠損値の処理

ユーザーが特定の場所を訪問していない場合を適切に処理するために、COALESCE 関数は NULL 値を 0 (またはその他の任意の値) に置き換えることができます。

SELECT u.id, u.account_balance, COALESCE(g.grocery_visits, 0) AS grocery_visits, COALESCE(f.fishmarket_visits, 0) AS fishmarket_visits
FROM users u
LEFT JOIN (
   SELECT user_id, COUNT(*) AS grocery_visits
   FROM grocery
   GROUP BY user_id
   ) g ON g.user_id = u.id
LEFT JOIN (
   SELECT user_id, COUNT(*) AS fishmarket_visits
   FROM fishmarket
   GROUP BY user_id
   ) f ON f.user_id = u.id
ORDER BY u.id;

この改善されたクエリは、各ユーザーの食料品店や魚市場の訪問回数を正確に反映します。 データを事前に集計することで、カスケード LEFT JOIN の落とし穴を回避し、正確な結果を保証します。

以上が複数の SQL LEFT JOIN によって不正確な訪問数が生成されるのはなぜですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。