SQL で複数の左結合が間違ったカウント結果を引き起こす原因と、これを修正するにはどうすればよいですか?

不正なカウント結果を引き起こす SQL 複数左結合のトラップと解決策

SQL を使用する場合、複数の左結合が正しく処理されないと、予期しない結果が発生する可能性があります。次のクエリを考えてみましょう:

SELECT
    t1."id" AS "User ID",
    t1.account_balance AS "Account Balance",
    count(t2.user_id) AS "# of grocery visits",
    count(t3.user_id) AS "# of fishmarket visits"
FROM users t1
LEFT OUTER JOIN grocery t2 ON (t2.user_id=t1."id")
LEFT OUTER JOIN fishmarket t3 ON (t3.user_id=t1."id")
GROUP BY t1.account_balance,t1.id
ORDER BY t1.id

このクエリは、users テーブル内の各ユーザーの 2 つの関連テーブル grocery と fishmarket への訪問数をカウントしようとします。ただし、左結合の性質により、不正確な結果が生成されます。

左結合に関する誤解

SQL では、結合は左から右に実行されます。このクエリでは、最初に users と grocery の間の左結合が実行されます。これにより、各ユーザー レコードが対応する食料品購入レコードに関連付けられます。次に、最初の結合結果と fishmarket の間で 2 番目の左結合が実行されます。これは、食料品の購入記録を持つ各ユーザー レコードが、対応する魚市場の購入記録とさらに結合されることを意味します。

加算ではなく乗算

この順次処理の意図しない結果として、grocery と fishmarket からのアクセス数が加算されるのではなく乗算されてしまいます。たとえば、ユーザーが食料品店への訪問が 3 回、魚市場への訪問が 4 回あった場合、クエリでは予想される 7 回ではなく 12 回の訪問が返されます。

解決策: 集計にサブクエリを使用する

これを修正するには、テーブルに結合する前に訪問数の集計 (カウント) を実行する必要があります。これはサブクエリを使用して実現できます:

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;

この変更されたクエリは、サブクエリ内の訪問数を集計し、集計結果を users テーブルと結合して、訪問数が誤って増加しないようにします。

以上がSQL で複数の左結合が間違ったカウント結果を引き起こす原因と、これを修正するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。