Warum führen mehrere SQL LEFT JOINs zu falschen Besuchszahlen?

Ungenaue Besuchszahlen mit mehreren SQL LEFT JOINs verstehen

In diesem Artikel wird ein häufiges SQL-Abfrageproblem untersucht: das Erhalten falscher Besuchszahlen bei Verwendung mehrerer LEFT JOIN-Vorgänge. Ziel ist es, Benutzerdaten abzurufen, darunter Kontostand, Besuche in Lebensmittelgeschäften und Besuche auf dem Fischmarkt. Eine naive Abfrage führt oft zu überhöhten Ergebnissen (z. B. „1“, „12“, „12“).

Das Problem ergibt sich aus der Ausführung von LEFT JOINs von links nach rechts. Jeder nachfolgende Join vervielfacht die Zeilenanzahl, wenn in den verbundenen Tabellen mehrere übereinstimmende Einträge vorhanden sind. Wenn beispielsweise drei Lebensmitteltische einem Benutzer zugeordnet werden, entstehen drei Zeilen. Ein anschließender Join mit vier Fischmarkttabellen erweitert dies dann auf zwölf Zeilen, was zu ungenauen Besuchszahlen führt.

Die Lösung: Voraggregation mit Unterabfragen

Die Lösung besteht darin, die Besuchszahlen für jede Tabelle vorab zu aggregieren, bevor sie mit der Tabelle users zusammengeführt werden. Dies verhindert das Problem der Zeilenmultiplikation. Die korrigierte Abfrage verwendet Unterabfragen, um dies zu erreichen:

<code class="language-sql">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;</code>

Umgang mit fehlenden Werten mit COALESCE

Um Fälle, in denen ein Benutzer keinen Besuch an einem bestimmten Standort hat, ordnungsgemäß zu behandeln, kann die Funktion COALESCE NULL-Werte durch 0 (oder einen anderen gewünschten Wert) ersetzen:

<code class="language-sql">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;</code>

Diese verbesserte Abfrage spiegelt genau die Anzahl der Lebensmittel- und Fischmarktbesuche für jeden Benutzer wider. Durch die Voraggregierung der Daten vermeiden wir die Fallstricke kaskadierender LEFT JOINs und stellen genaue Ergebnisse sicher.

Das obige ist der detaillierte Inhalt vonWarum führen mehrere SQL LEFT JOINs zu falschen Besuchszahlen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!