Probleme bei der Formulierung zweier Abfragen.

[Cor Angar]

Ich stecke wieder einmal fest und hoffe auf Hilfe. War fleißig am SQL lernen. Vieles klappt schon wie es soll. Bei zwei Abfragen brauch ich dennoch etwas Unterstützung.

Abfrage 1:

Funktioniert soweit.

Aber... mit den zwei CASE Anweisungen erzeuge ich die Felder points_for_qualifying_pos und points_for_race_pos. Die zwei Felder würde ich gerne noch in einen Rutsch addieren lassen um mir eine weitere Abfrage zu ersparen. Wenn ich allerdings nach der zweiten Case Anweisung

,
points_for_qualifying_pos + points_for_race_pos AS target_3

hinzufüge, will MySQL (unter phpMyAdmin) die zwei Felder nicht finden.

Komplette Abfrage:

SELECT a.driver_id, a.pos AS pos_qualifying, b.pos AS pos_race, c.driver_name,

(CASE
WHEN a.pos = 1 THEN d.scoring_points_pos_1 / 2
WHEN a.pos = 2 THEN d.scoring_points_pos_2 / 2
WHEN a.pos = 3 THEN d.scoring_points_pos_3 / 2
WHEN a.pos = 4 THEN d.scoring_points_pos_4 / 2
WHEN a.pos = 5 THEN d.scoring_points_pos_5 / 2
WHEN a.pos = 6 THEN d.scoring_points_pos_6 / 2
WHEN a.pos = 7 THEN d.scoring_points_pos_7 / 2
WHEN a.pos = 8 THEN d.scoring_points_pos_8 / 2
WHEN a.pos = 9 THEN d.scoring_points_pos_9 / 2
WHEN a.pos = 10 THEN d.scoring_points_pos_10 / 2
WHEN a.pos = 0 THEN d.scoring_points_pos_nc / 2
ELSE 0
END) AS points_for_qualifying_pos,

(CASE
WHEN b.pos = 1 THEN d.scoring_points_pos_1
WHEN b.pos = 2 THEN d.scoring_points_pos_2
WHEN b.pos = 3 THEN d.scoring_points_pos_3
WHEN b.pos = 4 THEN d.scoring_points_pos_4
WHEN b.pos = 5 THEN d.scoring_points_pos_5
WHEN b.pos = 6 THEN d.scoring_points_pos_6
WHEN b.pos = 7 THEN d.scoring_points_pos_7
WHEN b.pos = 8 THEN d.scoring_points_pos_8
WHEN b.pos = 9 THEN d.scoring_points_pos_9
WHEN b.pos = 10 THEN d.scoring_points_pos_10
WHEN b.pos = 0 THEN d.scoring_points_pos_nc
ELSE 0
END) AS points_for_race_pos

FROM calc_data d, driver c

RIGHT JOIN singapore_qualifying a ON (c.driver_id = a.driver_id)
RIGHT JOIN singapore_race_result b ON (c.driver_id = b.driver_id)

ORDER BY points_for_qualifying_pos DESC

Abfrage 2:

Es handelt sich um eine Abfrage dreier Tabellen.

Die drei Tabellen habe ich hier nachgebaut und mit Probedaten befüllt.
http://sqlfiddle.com/#!9/9a8cc70/3


Übersicht der Tabellen:

Tabelle driver:

driver_id	...	driver_name	real_team_membership	...
1	...	d1	t1	...
2	...	d2	t6	...
3	...	d3	t8	...
...	...	...	...	...

Tabelle singapore_race_result

driver_id	pos	...
...	...	...

Tabelle calc_data (Dient nur zur globalen Vergabe von Werten für diverse Berechnungen und Bewertungen):

id	winner_points	...
1	7	...

Die Abfrage:

• Ich will per Abfrage alle vorhandenen Fahrer auflisten.
• In einem Fahrerteam sind immer 2 bis 3 Fahrer vorhanden .Die Tabelle "driver" zeigt die Teamzugehörigkeit unter "real_team_membership". Der Teamfahrer der aus seinen jeweiligen Team die beste (niedrigste Zahl aber größer als 0. Bei 0 ist er ausgefallen) Rennposition erzielt (Tabelle singapore_race_result, Spalte pos), soll die "winner_points" aus der Tabelle "calc_data" bekommen. Der Rest bekommt 0 Punkte.

Ergebnis:

driver_id	driver_name	real_team_membership	pos	points
2	d2	t6	4	7
3	d3	t8	2	7
4	d4	t6	0	0
...	...	...	...	...

Bislang arbeite ich mit Einzelabfragen für jedes Team und füge die Punkte in php ein. Nachdem ich nun aber eine komplette Auflistung in Tabellenform an anderer Stelle benötige, will ich mir gerne ein view erzeugen, wo alle Daten Griffbereit sind.

Hier noch meine alte Abfrage die jedes Team getrennt behandelt und schlicht den besten Teamfahrer ermittelt.

SELECT driver.driver_id, driver.driver_name, singapore_race_result.pos FROM driver
LEFT JOIN singapore_race_result ON driver.driver_id = singapore_race_result.driver_id
WHERE real_team_membership = 't1' AND pos >= 1 ORDER BY pos
LIMIT 1

Seit nun über 8 Stunden scheitere ich kläglich an einer vernünftigen MySQL Lösung.

[hellbringer]

Kaputtes Tabellendesign. Spalten sollten nicht nummeriert werden. Du willst hier nicht mehrere Spalten sondern mehrere Zeilen. Ist das Tabellendesign korrekt, ist die Abfrage auch gleich deutlich einfacher (und kürzer)

[Cor Angar]

hm, ich nehme an es geht um die Tabelle calc_data? Die scoring_points_pos_x Werte in eine eigene Tabelle legen und zu einer Spalte zusammenfügen?

[hellbringer]

Ja, quasi -> scoring_points: id, calc_data_id, pos, points

[Cor Angar]

Die pos kann ich nicht zusammen mit den scoring_points setzen. Die scoring_points dienen für unterschiedlichste Berechnungen und sollen an einer Stelle global zur Verfügung stehen.
Die pos (Platzierung/Ranking/Ergebnis was auch immer gerade präsentiert werden soll) wird in vielen Tabellen verwendet. Gibt ja schließlich nicht nur ein Rennen und ein Qualifying. Auch kommen noch Dinge wie fastest lap, fastest pit stop, etc. hinzu.
Daher gibt es Tabellen die mit Rohdaten gefüllt sind. Tabelle -> rennstrecke_rennen bzw. rennstrecke_qualifying und weiteres.
Und eben einmal die Tabelle calc_data wo die Zahlen für die unterschiedlichsten Berechnungen drin stehen. Wichtig an der Stelle ist zu wissen dass die scoring_points nicht nur für dieses Beispiel Verwendung finden und diese eventuell noch angepasst werden müssen. Somit auch nur einmal vorkommen sollen.
Aus den Abfragen erzeuge ich dann views die möglichst fertige Ergebnisse beinhalten sollen, da ich die Daten an unterschiedlichen Stellen brauche (einmal Tabelle, dann wieder für ein Dropdown, schließlich eine weitere Abfragen usw.) und mir Code und Server Resourcen sparen möchte. Muss ja nicht sein, dass jeder Zugriff die immer selben Berechnungen erneut durchführt.

[hellbringer]

Die pos kann ich nicht zusammen mit den scoring_points setzen. Die scoring_points dienen für unterschiedlichste Berechnungen und sollen an einer Stelle global zur Verfügung stehen.

Ist halt murks.

Muss ja nicht sein, dass jeder Zugriff die immer selben Berechnungen erneut durchführt.

Dafür gibts Query Cache und Materialized Views.

[Cor Angar]

Wenn ich 20 Rennen, Qualifyings und noch zahlreiche weitere Sachen habe, wo die scoring_points sammt anderen points auf alle Fahrer verteilt werden, ergeben sich hunderte Stellen wo alleine diese scoring_points Verwendung finden. Wenn ich dich richtig verstanden habe, soll ich die vielen Positionen und die scoring_points zusammenwürfeln, was zahlreiche andere Unübersichtlichkeiten schaffen würde. Mir erschließt sich derweil der Sinn noch nicht, eine etwas kürzere und dann natürlich auch eine leichtere Abfrage derart vielen Problemen vorzuziehen. Zumal es eine klare Zielsetzung ist, dass solche Rechendaten unkomplitziert, zentral und schnell angepasst werden können. Redundanz kostet schließlich nicht nur Zeit sondern erhöht auch die Fehleranfälligkeit.
Die Spalte winner_points hätte dann wie alle anderen Rechenspalten nicht einmal den Zielwert sondern eben Zielwert * Fahreranzahl. Und die Fahreranzahl ändert sich regelmäßig. Bin mir auch nicht sicher ob jeder Wert überhaupt bei 0 landet wenn ein neuer Fahrer hinzukommt. Da könnten sich auch individuell, situationsbedingt Boni und Mali auftun.

Dafür gibts Query Cache und Materialized Views.

Dachte views sind genau dafür da? Erzählt zumindest der hier:
https://www.youtube.com/watch?v=luO2MWjUqe4

[hellbringer]

Was ist "dafür"?

Ich seh nur, dass du mit nummerierten Spalten herumjonglierst, obwohl das komplett unnötig ist. Wenn Daten sauber vorliegen (würden), dann gibt es das Problem gar nicht.

[Cor Angar]

Ja aber das endet damit, so ich das richtig sehe, dass die Punkte alle manuell vergeben werden müssen. Das soll natürlich nicht so sein. Das View sollte sich hingegen selbstständig anpassen, wenn die Rohdaten gesetzt oder auch geändert werden und sämtliche vorkommende Punkte automatisch nach vorliegenden Formeln vergeben und bei Veränderung von Rechenwerten ebenfalls vollautonom arbeiten.

[hellbringer]

Ja aber das endet damit, so ich das richtig sehe, dass die Punkte alle manuell vergeben werden müssen.

Was meinst du mit "manuell"?

Das soll natürlich nicht so sein. Das View sollte sich hingegen selbstständig anpassen, wenn die Rohdaten gesetzt oder auch geändert werden und sämtliche vorkommende Punkte automatisch nach vorliegenden Formeln vergeben und bei Veränderung von Rechenwerten ebenfalls vollautonom arbeiten.

Bei "Rohdaten" klingelts bei mir. Was du brauchst ist kein View, sondern ein Import-Script, das die Daten korrekt abspeichert. Ein View ist nicht für sowas gedacht.

[Cor Angar]

Ja, das wäre eine Möglichkeit. Ich bastel gerade noch an Abfrage 2 rum.
Das lass ich mir durch den Kopf gehen und werde die Möglichkeit mit anderen besprechen.

[Cor Angar]

Bin nun dabei die Datenbank wie empfohlen umzubauen und die Datensätze mittels Script zusammenzustellen. Eine Frage ist dabei noch aufgekommen.
Mein Plan ist es für jede Rennstrecke eine Tabelle anzulegen (um die 20), in der alle Fahrerergebnisse und Punkte der jeweiligen Strecke eingetragen werden. Qualifying, Rennen,... schnellste Runde, Zeit,....
Wenn ich nun von allen Streckentabellen, deren Struktur ident sein wird, eine bestimmte Spalte summieren will, also bsp. von allen 20 Tabellen die Gesamtsumme der Spalte race_points. Wie sähe da eine Abfrage aus. Mache ich das mit vielen JOIN, kann ich da zahlreiche UNION aneinander reihen, oder läuft das komplett anders?

[protestix]

Das läuft anders.
Warum willst du 20 Tabellen anlegen?
Eine Tabelle für Rennstrecken, eine für Teams, eine für Fahrer und eine für Rennen sollte doch ausreichend sein.
Von mir aus noch eine mit Resultate.

[Cor Angar]

Ja, das mache ich bereits. Eine Tabelle wo alle Streckendaten erfasst werden. Eine andere mit Fahrerdaten gespeichert werden und so weiter.
Ich stelle mir nun die Frage wie ich am besten die Ergebnisse speichern soll, also die Fahrerleistungen auf den jeweiligen Strecken und wie ich entsprechend dieser Leistungen Punkte einrechnen soll.

Beispiel einer Punktekategorie: Fahrer A aus Team A fährt die schnellste Runde gemessen an allen vorhandenen Teamfahrern (selbes Team). Dafür gibt es eine vordefinierte Anzahl von Teampunkten die vergeben werden sollen.
Nun will ich nicht nur wissen wie viele Punkte ein Fahrer in welcher Kategorie auf einer bestimmten Strecke erzielt hat, sondern ein Gesamtergebnis einer Kategorie ermitteln können.

Mich treibt schlicht die Frage wie ich die Datenbank am besten aufbaue. Zuerst hatte ich die zu vergebenen Punkte sowie die Rohdaten in einer eigenen Tabelle abgelegt und Ergebnisabfragen zur Punktevergabe in ein View gespeichert (Siehe Abfrage 1 und 2).
Nun versuche ich den vorgeschlagenen Ansatz umzusetzen und die Punkte bereits in php bei der Eingabe zu errechnen und sie in der db zu speichern. Nach meiner derzeitigen Vorstellung resultiert dies in einer Ergebnistabelle zu jeder Strecke. Daher die Frage wie man bei so vielen Tabellen am besten zu einer Summe kommt. Will ja auch nicht 20 Tabellen anlegen und dann erst draufkommen, dass die Idee nicht so toll war.

[LudwigBr]

Wenn du mehrere Tabellen mit gleichem Schema erstellst hast du in den allermeisten Fällen etwas falsch gemacht.
Du solltest dich vermutlich etwas unter dem Stichwort "Normalisierung" belesen.
Stattdessen brauchst du dann eine weitere Spalte als Unterscheidungsmerkmal.

Beispiel einer Punktekategorie: Fahrer A aus Team A fährt die schnellste Runde gemessen an allen vorhandenen Teamfahrern (selbes Team). Dafür gibt es eine vordefinierte Anzahl von Teampunkten die vergeben werden sollen.
Nun will ich nicht nur wissen wie viele Punkte ein Fahrer in welcher Kategorie auf einer bestimmten Strecke erzielt hat, sondern ein Gesamtergebnis einer Kategorie ermitteln können.

Ohne das böse zu meinen, aber solche Abstrakten Beispiele bringen (fast) keinem was. Stattdessen solltest du ein (reales) Beispiel mit Werten liefern und welches Ergebnis dazu passt. Wenn schon eine Tabellenstruktur steht am besten mit selbiger in einem sqlfiddle(.com).