MySQL: Kann diese Query vereinfacht werden?

Sempervivum

Sempervivum

Erfahrenes Mitglied
Liebe Datenbankspezialisten,
bei Stackoverflow
Can my query using join and a subquery be simplified?
habe ich leider keine Lösung bekommen, daher versuche ich es hier.
Mit viel Googeln, Nachdenken und Versuch & Irrtum habe ich diese Abfrage gebaut:
Code: 
SELECT sq.movie_id,
       sq.movie_title,
       sq.number,
       sq.movie_year,
       coords.loc_country
FROM   (SELECT Count(*)            AS `number`,
               `coords`.`id_movie` AS movie_id,
               `movie`.`title`     AS movie_title,
               `movie`.`year`      AS movie_year
        -- possibly more info about the movie
        FROM   `coords`
               LEFT JOIN `movie`
                      ON `movie`.`id` = `coords`.`id_movie`
        GROUP  BY `coords`.`id_movie`
        ) AS sq
LEFT JOIN coords ON coords.id_movie = sq.movie_id
ORDER  BY sq.number DESC, sq.movie_title ASC
Sieht für mich ziemlich kompliziert aus und die Frage ist, ob man sie vereinfachen kann.
Die Tabelle movie enthält Daten wie Titel, Jahr, Genre etc. Die Drehorte stehen in einer anderen Tabelle coords. Ziel der Abfrage ist, die Filme nach Anzahl der Drehorte sortiert zu bekommen und zusätzlich eine Liste der Drehorte.
Mit einer kleinen Nachverarbeitung:
Code: 
$result = $pdo->query($sql);
$resultArr = [];
$movieId = -1;
while ($row = $result->fetchObject()) {
    if ($row->movie_id != $movieId) {
        $movieId = $row->movie_id;
        $resultArr[$movieId] = [
            'movie_title' => $row->movie_title,
            'movie_year' => $row->movie_year,
            'number' => $row->number,
            'drehorte' => [],
        ];
    }
    $resultArr[$movieId]['drehorte'][] = $row->loc_country;
}
sieht das Ergebnis dann so aus und das ist genau was gewünscht ist:
Code: 
array(632) {
  [881]=>
  array(4) {
    ["movie_title"]=>
    string(20) "Eddie Izzard: Circle"
    ["movie_year"]=>
    string(4) "1984"
    ["number"]=>
    string(1) "6"
    ["drehorte"]=>
    array(6) {
      [0]=>
      string(6) "Brazil"
      [1]=>
      string(5) "Kenya"
      [2]=>
      string(7) "Ukraine"
      [3]=>
      string(4) "Peru"
      [4]=>
      string(11) "Philippines"
      [5]=>
      string(8) "Slovenia"
    }
  }
  [171]=>
  array(4) {
    ["movie_title"]=>
    string(5) "Torso"
    ["movie_year"]=>
    string(4) "1923"
    ["number"]=>
    string(1) "6"
    ["drehorte"]=>
    array(6) {
      [0]=>
      string(11) "Philippines"
      [1]=>
      string(7) "Vietnam"
      [2]=>
      string(5) "China"
      [3]=>
      string(5) "Japan"
      [4]=>
      string(4) "Peru"
      [5]=>
      string(8) "Colombia"
    }
  }
  [266]=>
  array(4) {
    ["movie_title"]=>
    string(24) "Anton Chekhov's The Duel"
    ["movie_year"]=>
    string(4) "1939"
    ["number"]=>
    string(1) "5"
    ["drehorte"]=>
    array(5) {
      [0]=>
      string(5) "China"
      [1]=>
      string(6) "Brazil"
      [2]=>
      string(6) "Russia"
      [3]=>
      string(14) "Czech Republic"
      [4]=>
      string(6) "Mexico"
    }
  }
 
  usw.
Wie gesagt, die Frage ist, ob man die Abfrage vereinfachen kann.
 
Grundsätzlich, das SQL ist einfach und nicht komplizieret.
Aber, es nutzt eine MySQL-Spezifische Unschönheit aus. Jeder Feld das nicht im GROUP BY steht und keine Aggregationfunktion (Count, Max etc) hat wird automatisch dem GROUP BY zugerechnet. Jede andere DB läuft da auf ein Fehler, weil es einfach kein sauberer Code ist.

  1. Nenn das Feld nicht number. Ist wie name, text etc. ungeeignet.
  2. Arbeite mit Alias um nicht überall die Tabellennamen drin zu haben
  3. Ist der LEFT JOIN richtig? Du willst alle Coords, auch die, welche keinem Movie zugeordnet sind? Ich denke, der sollte umgekehrt sein. Die Movies sind Master.

Zur Vereinfachung. Mit COUNT() OVER() kannst du dein Total ermitteln. Using count(*) .. Over(*) in mysql
In deinem Fall nehmen wir mal alle Movie-Coord-Kombinationen und zählen dann die jeweiligen movie-Ids

Ungetestet
SQL: 
SELECT 
    COUNT(*) OVER(partition BY c.id_movie) AS coords_count,
    c.id_movie     AS movie_id,
    m.title     AS movie_title,
    m.year      AS movie_year,
    c.loc_country
FROM   
    movie m
    LEFT JOIN coords c ON m.id = c.id_movie
 
Danke für die Hinweise.
Grundsätzlich, das SQL ist einfach und nicht komplizieret.
Zum Vergrößern anklicken....
Das liegt im Auge des Betrachters. Mir, da kein SQL-Spezialist, kommt es kompliziert vor ;-)

  1. Nenn das Feld nicht number. Ist wie name, text etc. ungeeignet.
Zum Vergrößern anklicken....
Das stimmt natürlich, ist mir gar nicht aufgefallen.

  1. Ist der LEFT JOIN richtig? Du willst alle Coords, auch die, welche keinem Movie zugeordnet sind? Ich denke, der sollte umgekehrt sein. Die Movies sind Master.
Zum Vergrößern anklicken....
Darauf bin ich bei SO auch hingewiesen worden. Liegt definitiv an meinem Unwissen. Wenn ich mal etwas Muße habe, muss ich mich mit den Typen von Joins vertraut machen.

Die vereinfachte Version werde ich jetzt ausprobieren ...
 
... Hm, das Ergebnis sieht irgend wie merkwürdig aus:
Code: 
array(1368) {
  [0]=>
  array(10) {
    ["coords_count"]=>
    string(3) "369"
    [0]=>
    string(3) "369"
    ["movie_id"]=>
    NULL
    [1]=>
    NULL
    ["movie_title"]=>
    string(21) "Swiss Family Robinson"
    [2]=>
    string(21) "Swiss Family Robinson"
    ["movie_year"]=>
    string(4) "1983"
    [3]=>
    string(4) "1983"
    ["loc_country"]=>
    NULL
    [4]=>
    NULL
  }
  [1]=>
  array(10) {
    ["coords_count"]=>
    string(3) "369"
    [0]=>
    string(3) "369"
    ["movie_id"]=>
    NULL
    [1]=>
    NULL
    ["movie_title"]=>
    string(7) "Dollman"
    [2]=>
    string(7) "Dollman"
    ["movie_year"]=>
    string(4) "2003"
    [3]=>
    string(4) "2003"
    ["loc_country"]=>
    NULL
    [4]=>
    NULL
  }
  [2]=>
  array(10) {
    ["coords_count"]=>
    string(3) "369"
    [0]=>
    string(3) "369"
    ["movie_id"]=>
    NULL
    [1]=>
    NULL
    ["movie_title"]=>
    string(29) "What's in a Name (Pr?nom, Le)"
    [2]=>
    string(29) "What's in a Name (Pr?nom, Le)"
    ["movie_year"]=>
    string(4) "1922"
    [3]=>
    string(4) "1922"
    ["loc_country"]=>
    NULL
    [4]=>
    NULL
  }
  [3]=>
  array(10) {
    ["coords_count"]=>
    string(3) "369"
    [0]=>
    string(3) "369"
    ["movie_id"]=>
    NULL
    [1]=>
    NULL
    ["movie_title"]=>
    string(20) "Guest House Paradiso"
    [2]=>
    string(20) "Guest House Paradiso"
    ["movie_year"]=>
    string(4) "1987"
    [3]=>
    string(4) "1987"
    ["loc_country"]=>
    NULL
    [4]=>
    NULL
  }

...
Bei 1000 Filmen und 1000 Drehorten ist es eher unwahrscheinlich, dass eine Film 369 Drehorte hat. Und bei loc_country treten NULL-Werte auf.:rolleyes:
 
Zunächst hatte ich die NULL-Werte ausgefiltert, aber wenn ich auf RIGHT-Join umstelle und wie gewünscht sortiere
Code: 
SELECT
    COUNT(*) OVER(partition BY c.id_movie) AS coords_count,
    c.id_movie     AS movie_id,
    m.title     AS movie_title,
    m.year      AS movie_year,
    c.loc_country
FROM
    movie m
    RIGHT JOIN coords c ON m.id = c.id_movie
    -- WHERE c.loc_country IS NOT NULL
ORDER BY coords_count DESC
sieht das Ergebnis plausibel aus:
Code: 
array(1000) {
  [0]=>
  array(10) {
    ["coords_count"]=>
    string(1) "6"
    [0]=>
    string(1) "6"
    ["movie_id"]=>
    string(3) "171"
    [1]=>
    string(3) "171"
    ["movie_title"]=>
    string(5) "Torso"
    [2]=>
    string(5) "Torso"
    ["movie_year"]=>
    string(4) "1923"
    [3]=>
    string(4) "1923"
    ["loc_country"]=>
    string(5) "China"
    [4]=>
    string(5) "China"
  }
  [1]=>
  array(10) {
    ["coords_count"]=>
    string(1) "6"
    [0]=>
    string(1) "6"
    ["movie_id"]=>
    string(3) "881"
    [1]=>
    string(3) "881"
    ["movie_title"]=>
    string(20) "Eddie Izzard: Circle"
    [2]=>
    string(20) "Eddie Izzard: Circle"
    ["movie_year"]=>
    string(4) "1984"
    [3]=>
    string(4) "1984"
    ["loc_country"]=>
    string(4) "Peru"
    [4]=>
    string(4) "Peru"
  }
  [2]=>
  array(10) {
    ["coords_count"]=>
    string(1) "6"
    [0]=>
    string(1) "6"
    ["movie_id"]=>
    string(3) "171"
    [1]=>
    string(3) "171"
    ["movie_title"]=>
    string(5) "Torso"
    [2]=>
    string(5) "Torso"
    ["movie_year"]=>
    string(4) "1923"
    [3]=>
    string(4) "1923"
    ["loc_country"]=>
    string(5) "Japan"
    [4]=>
    string(5) "Japan"
  }
  [3]=>
  array(10) {
    ["coords_count"]=>
    string(1) "6"
    [0]=>
    string(1) "6"
    ["movie_id"]=>
    string(3) "881"
    [1]=>
    string(3) "881"
    ["movie_title"]=>
    string(20) "Eddie Izzard: Circle"
    [2]=>
    string(20) "Eddie Izzard: Circle"
    ["movie_year"]=>
    string(4) "1984"
    [3]=>
    string(4) "1984"
    ["loc_country"]=>
    string(6) "Brazil"
    [4]=>
    string(6) "Brazil"
  }
  [4]=>
  array(10) {
    ["coords_count"]=>
    string(1) "6"
    [0]=>
    string(1) "6"
    ["movie_id"]=>
    string(3) "881"
    [1]=>
    string(3) "881"
    ["movie_title"]=>
    string(20) "Eddie Izzard: Circle"
    [2]=>
    string(20) "Eddie Izzard: Circle"
    ["movie_year"]=>
    string(4) "1984"
    [3]=>
    string(4) "1984"
    ["loc_country"]=>
    string(11) "Philippines"
    [4]=>
    string(11) "Philippines"
  }
  [5]=>
  array(10) {
    ["coords_count"]=>
    string(1) "6"
    [0]=>
    string(1) "6"
    ["movie_id"]=>
    string(3) "171"
    [1]=>
    string(3) "171"
    ["movie_title"]=>
    string(5) "Torso"
    [2]=>
    string(5) "Torso"
    ["movie_year"]=>
    string(4) "1923"
    [3]=>
    string(4) "1923"
    ["loc_country"]=>
    string(11) "Philippines"
    [4]=>
    string(11) "Philippines"
  }
  [6]=>
  array(10) {
    ["coords_count"]=>
    string(1) "6"
    [0]=>
    string(1) "6"
    ["movie_id"]=>
    string(3) "171"
    [1]=>
    string(3) "171"
    ["movie_title"]=>
    string(5) "Torso"
    [2]=>
    string(5) "Torso"
    ["movie_year"]=>
    string(4) "1923"
    [3]=>
    string(4) "1923"
    ["loc_country"]=>
    string(4) "Peru"
    [4]=>
    string(4) "Peru"
  }
  [7]=>
  array(10) {
    ["coords_count"]=>
    string(1) "6"
    [0]=>
    string(1) "6"
    ["movie_id"]=>
    string(3) "881"
    [1]=>
    string(3) "881"
    ["movie_title"]=>
    string(20) "Eddie Izzard: Circle"
    [2]=>
    string(20) "Eddie Izzard: Circle"
    ["movie_year"]=>
    string(4) "1984"
    [3]=>
    string(4) "1984"
    ["loc_country"]=>
    string(5) "Kenya"
    [4]=>
    string(5) "Kenya"
  }
Aber es stimmt nicht mit dem meiner Version überein. Zu Fuß an einem Film untersuchen, was stimmt?
 
Mit dem RIGHT JOIN kehrst du weider um. Dann hast du die Movies, die keinen Ort haben nicht drin.
Wo hast du einen Null-Fehler bekommen?
Nimm als movie_id die ID der Tabelle Movie und nicht der coords.
Beim COUNT die c.id_movie über die m.id zählen. Also, zähle alle c.movie_id die nicht NULL sind für die m.id der Zeile

Für das andere Problem. Der ORDER BY muss nach m.id sein. sonst geht deine PHP-Logik nicht auf.

SQL: 
SELECT 
    COUNT(c.id_movie) OVER(partition BY m.id) AS coords_count,
    m.id         AS movie_id,
    m.title     AS movie_title,
    m.year      AS movie_year,
    c.loc_country
FROM   
    movie m
    LEFT JOIN coords c ON m.id = c.id_movie
ORDER BY m.id, coords_count
 
Jetzt scheint es perfekt zu sein, die selben beiden Filme haben jetzt mit 6 die höchste Anzahl an Drehorten.

Aber, wie ich sehe, für mich noch viel Lernstoff, einmal die Typen von Joins und diese Zeile muss ich noch verstehen:
COUNT(c.id_movie) OVER(partition BY m.id) AS coords_count
Ist das Standard-SQL oder spezifisch für MySQL?

Auf jeden Fall geniale Lösung!
 
Ist nicht standard. Aber auch Oracle etc. kennen sowas.
COUNT([zählfeld]) Zählt alle Nicht-Null Werte des Feldes. Im Normalfall im Zusammenhang mit einem GROUP BY. In deinem Fall nehem ich c_id_movie. DIeses Feld kann wegen dem LEFT JOIN den Wert NULL haben. Und zwar dann, wenn dem Movie kein ccord zugeteilt ist.
OVER(partition BY [gruppenfeld]) In dem Fall haben wir kein GROUP BY, da wir ja alle Zeilen ausgeben wollen. Mit dem OVER kann man dem COUNT angeben, über was er zählen soll. In unserem Fall über eine Partition (also Teilmenge des Ganzen), welche denselben Wert im gruppenfeld haben, wie die aktulle Zeile. Hier nehmen wir m.id, da dieses Feld sicher immer abgefüllt ist.

Schau dir dazu mal das Resultat des folgenden SQL in einem DB-Browser (zB. phpMyAdmin) an. Dann siehst du eher was es macht
SQL: 
SELECT
    m.movie_title,
    c.loc_country,
    m.id         AS mid,
    c.id_movie     AS cid,
    COUNT(c.id_movie) OVER(partition BY m.id) AS cnt
FROM 
    movie m
    LEFT JOIN coords c ON m.id = c.id_movie
ORDER BY m.id
[/icode]
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück