SQL-Select-Statement

[wKovacs]

Hi,

ich quäl mich schon seit einigen Stunden mit dem folgenden Problem. Das sollte doch nicht so schwer sein, doch komme ich einfach nicht auf die Lösung. Vermutlich ist es einfach zu lange her, das ich mit SQL hantiert habe.

Ich habe ein Tabelle die sieht ungefähr so aus:

|id|user_id|plan_id|monday|tuesday|wednesday|thursday|friday |1 |0 |17 |m3 |m2 |m2 || |2 |15 |17 |m1 |m2 |m3 |m4 |mZ |3 |16 |17 |m2 |m3 |m4 |m5 |mZ |4 |17 |17 |m1 |m1 |m1 || |5 |18 |17 |m5 ||m5 |m5 |mZ |6 |19 |17 |m4 |m4 |m4 |m4 |mZ |7 |20 |17 |m5 |m4 |m4 |m3 |m2 |8 |21 |17 |||m4 |m4 |m4 |9 |22 |17 |m4 |m3 ||| |10 |23 |17 |m3 |m2 |m3 || |11 |24 |17 |m4 |m3 |m3 ||

In einer zweiten Tabelle stehen alle Optionen für die Spalten der Wochentage

|options | |m1 |m2 |m3 |m4 |m5 |m6 |mZ

Ich benötige eine Tabelle die mir für jede der Optionen die Anzahl pro Wochentag ausgibt. In etwa:

|option|monday|tuesday|wednesday|thursday|friday |m1|2|1|1|0|0 |m2|1|3|1|0|0 |m3|2|3|3|1|0 |m4|3|2|4|3|1 |m5|2|0|1|1|0 |m6|0|0|0|0|0 |mZ|0|0|0|0|4

Sollte das wirklich so viel schwieriger sein, oder stelle ich mich grad einfach viel zu dumm an?

Kann mir jemand die richtige Richtung weisen?

Regards

wKovacs

[dedlfix]

Tach!

Kann mir jemand die richtige Richtung weisen?

Erstmal klein anfangen. In welche Teilaufgaben kann man denn die Aufgabe zerlegen? Die einfachste ist, eine Liste der Options zu bekommen, denn das ist sowohl ein Teil, der im Ergebnis stehen soll, als auch die Grundlage für die Ergebnismenge, zu der dann die zusätzlichen Daten ermittelt werden sollen.

SELECT options FROM zweite_Tabelle

Der nächste Schritt muss für "m1" die Anzahl aller Montagswerte, die Anzahl aller Dienstagswerte, und so weiter ermitteln. Und das in gleicher Weise für jeden Eintrag auf der ersten Teil-Query. Man könnte das mit 5 korrelierten Subselects machen, für jeden Wochentag eins.

SELECT option,
  (SELECT COUNT(monday) FROM ein_Tabelle WHERE monday = zT.option) monday, 
  (SELECT COUNT(tuesday) FROM ein_Tabelle WHERE tuesday = zT.option) tuesday,
  ... 
FROM zweite_Tabelle zT

Funktioniert, fertig. Zeig es nur niemandem, es sieht nicht besonders toll aus.

Kann man die Subquerys nicht irgendwie in eine zusammenfassen? Gruppieren der "ein Tabelle" nach options geht nicht, weil du für jeden Wochentag eine eigene Gruppierung brauchst, was 5 Abfragen ergibt. 5 Abfragen haben wir schon in der ersten Lösung, und noch dazu einfacher als mit Gruppierung. Hmm, fällt mir jetzt auch nichts schöneres ein. Vielleicht doch Lösung Nummer eins?

dedlfix.

[Matthias Apsel]

Hallo dedlfix,

Hmm, fällt mir jetzt auch nichts schöneres ein. Vielleicht doch Lösung Nummer eins?

Da bin ich aber froh. Das hätte ich mich nie getraut zu posten.

Bis demnächst
Matthias

[wKovacs]

Vielen Dank für die Hilfe!

Etwas in der Art hatte ich schon im Kopf, doch habe es zu meiner endlosen Schande nicht zum laufen bekommen. Ich rede mir jetzt ein, das mich mein Unterbewusstsein blockiert hat, da die Lösung wirklich nicht "schön" ist, aber am Ende zählt, das es funzt!

Danke nochmal

wKovacs

PS: Die Wochentage stehen auch in einer Tabelle (ähnlich der Optionen) kann man daraus was machen, um die Lösung eleganter zu bekommen?

[dedlfix]

Tach!

PS: Die Wochentage stehen auch in einer Tabelle (ähnlich der Optionen) kann man daraus was machen, um die Lösung eleganter zu bekommen?

Nicht in der Form, weil du in einer Schleife Querys erzeugen müsstest. Du braucht für jede Spalte der Ergebniszelle eine Query.

Eine andere Form der Ergebnisdarstellung, wo du für jede Kombination aus Option und Wochentag eine eigene Ergebniszeile bekommst, kannst du damit auch nicht erzeugen, weil die erste Tabelle bezüglich der Wochentage nicht normalisiert ist. Du kommst nicht von Wert auf Spaltenname, ohne ein Statement zusammenzubauen. Geht prinzipiell mit PREPARE, ist aber unverhältnismäßig aufwendig. Wenn diese Wochentage hingegen statt eigener Spalten nur ein Wert innerhalb einer Spalte Wochentag wären, dann hättest du zwar 5 mal mehr Zeilen, aber dann kann man das im Sinne der eben genannten anderen Ergebnisdarstellung auch einfacher abfragen.

dedlfix.

[wKovacs]

sry, wenn ich jetzt dumm frage: "hä?" Ich hab echt zu lang nicht mit SQL gespielt, stelle ich fest!

Du meinst eine Tabelle in der Form?

|id|user_id|plan_id|day|option |1|1|1|monday|m1 |2|1|1|thusday|m2 |3|1|1|friday|mZ

Wie würde dann die Abfrage aussehen?

Da ich die Datenbank unter meiner Kontrolle habe, kann ich alles so hinbasteln, wie ich es haben möchte. Welche der Lösungen wäre dann die zu Bevorzugende in Hinblick auf Perfomance(die Abfrage würde maximal zweimal die Woche laufen) und Speicherverbrauch?

Regards

wKovacs

[dedlfix]

Tach!

Du meinst eine Tabelle in der Form?

Ja.

Wie würde dann die Abfrage aussehen?

Formuliere die Anforderung in natürlicher Sprache, aber präzise. Da sollte dann sowas rauskommen:

"Für jeden Wochentag und jede Option brauche ich die Anzahl der Vorkommen."

Das ergibt ein gruppierendes Statement

SELECT day, option, COUNT() FROM eine_Tabelle GROUP BY day, option

Das wäre eigentlich schon. Es braucht noch nicht mal eine Verknüpfung zur den Tabellen der Optionen oder Tage, es sei denn, du musst daraus noch Informationen in die Ergebnismenge bringen.

dedlfix.

[wKovacs]

Vielen Dank!

Mit GROUP BY stand ich schon immer auf Kriegsfuß, keine Ahnung warum... So schwer sollte das doch dann auch wieder nicht sein Oo

Welche Version wäre in Hinblick eines vernünftigen Datenbankentwurfs der "Richtige"?, und warum? Will's ja A richtig machen und im Idealfall B was lernen!

Regards

wKovacs

[Matthias Apsel]

Hallo wKovacs,

Welche Version wäre in Hinblick eines vernünftigen Datenbankentwurfs der "Richtige"?, und warum? Will's ja A richtig machen und im Idealfall B was lernen!

Die Grundlage ist immer eine Normalisierung. Entscheide das zunächst für deine Daten. https://www.tinohempel.de/info/info/datenbank/normalisierung.htm

Bis demnächst
Matthias

[wKovacs]

Vorrausgesetzt ich verstehe das Normalisieren korrekt, sind beide Tabellenversionen in der 3ten Normalform. Alle Attribute sind atomar. Jedes Attribut hängt direkt vom Primärschlüssel ab. Es gibt keine Transitionen.

Ok, ich gestehe in Entwurf 1 bilden tatsächlich user_id und plan_id zusammen einen eindeutigen Schlüssel und die Spalte id kann entfallen. Doch abgesehen davon sind beide Entwürfe in der 3ten Normalform und ich wüssten keinen Grund die eine der anderen vorzuziehen.

Regards

wKovacs

[dedlfix]

Tach!

Ok, ich gestehe in Entwurf 1 bilden tatsächlich user_id und plan_id zusammen einen eindeutigen Schlüssel und die Spalte id kann entfallen.

Nun, die Wochentage sind gleichartige Daten, die können in eine gemeinsame Spalte. Damit ergibt sich dann auch, dass der Primärschlüssel um den Wochentag erweitert werden muss.

Bildlich gesprochen wiederholst du dich nebeneinander, aber Wiederholungen ist Aufgabe der Datensätze. Also wenn du einen gemeinsamen Begriff für mehrere Spalten finden kannst, dann ist das in der Regel die bessere Spalte.

dedlfix.

[wKovacs]

Nun, die Wochentage sind gleichartige Daten, die können in eine gemeinsame Spalte. Damit ergibt sich dann auch, dass der Primärschlüssel um den Wochentag erweitert werden muss.

Ok, dieser Aussage kann ich schwerlich widersprechen! Damit ergibt sich (für mich) das ich einen Fehler in meinem Entwurf habe. Und sowas kann ich auf gar keinen Fall so stehen lassen. Dann mal Ärmel hochgekrempelt und ausmisten Oo. Sind glücklicherweise nur 3 Stellen in meinem aktuellen Code.

Vielen Dank für die Aufklärung

wKovacs

[Rolf B]

Hallo Matthias,

genau, und das ursprüngliche Design verletzt die 1. Normalform: Relationen müssen frei von Wiederholungsgruppen sein. Die 5 Spalten mit den Wochentagen sind eine Wiederholungsgruppe - und die Strafe folgt sofort: Redundanz im SQL Statement bei der Summierung.

Beim Normalisieren muss man aber aufpassen: die plan_id ist keine Wiederholungsgruppe. Man muss die Tabelle 1 daher in 2 Tabellen teilen:

Tabelle 1a: id, user_id, plan_id
Tabelle 1b: id, wochentag, option

Darf ich übrigens die Frage stellen, warum es eine id und einer user_id gibt? Kann es zu einer user_id mehrere Zeilen in Tabelle 1 geben? Kann sich in einem existierenden Satz der Tabelle 1 die User-ID nochmal ändern? Wenn beides NEIN ist, könnte man die User-ID vermutlich zum Primary Key erheben und die ID-Spalte weglassen.

Eine separate Tabelle mit Wochentagen macht nur Sinn, wenn den Wochentagen noch weitere Attribute zugeordnet sind. Als reine Wertetabelle ist sie weniger hilfreich - selbst Papst Gregor hat damals die Wochentage nicht verändert. Für eine Übersetzung ist es wurscht, ob ich "monday" auf "Lundi" mappe oder die Wochentag-ID 1.

Das Ausgeben der Zieltabelle ist dann das Bilden einer Pivot-Tabelle, und das ist mit SQL ursprünglich nicht vorgesehen. Sofern der DB-Server keine Pivot-Funktion hat, muss man das von Hand machen, und das geht auch ohne Subselects. Allerdings nicht dynamisch; man muss schon alle Spalten kennen die man braucht. Selbst DB-Server wie MS-SQL, der Sprachelemente zur Pivot-Bildung hat, benötigen diese Information noch. Pivot-Bildung mit dynamischer Spaltenanzahl kenne ich nur aus Excel.

Das Standardpattern für Pivot-Bildung in SQL verwendet CASE Statements. Wenn man Tabelle 1 in table_1a und table_1b aufgeteilt hat, kann man so abfragen:

select option
     , sum(case table_1b.wochentag = 'monday' then 1 end) as monday
     , sum(case table_1b.wochentag = 'tuesday' then 1 end) as tuesday
     , sum(case table_1b.wochentag = 'wednesday' then 1 end) as wednesday
     , sum(case table_1b.wochentag = 'thursday' then 1 end) as thursday
     , sum(case table_1b.wochentag = 'friday' then 1 end) as friday
from options join table_1b ON options = table1b.option 
order by options

Wirklich schöner ist das auch nicht, aber es ersetzt 5 Subselects durch einen Join und könnte daher performanter sein. Muss man messen - und bei kleinen Datenmengen wird es sich nicht auswirken.

Die Alternative besteht darin, die Pivot-Bildung nicht im SQL durchzuführen, sondern einfach nur, wie schon von dedlfix gezeigt, einen GROUP BY zu machen und die Verteilung auf die Wochentage in der Software auszuführen, die das SQL Ergebnis verarbeitet. Eine solche Software könnte auch die Wochentag-Tabelle verwenden, um den SQL-Teil mit den SUM-Zeilen automatisch zu generieren.

Rolf

[wKovacs]

Darf ich übrigens die Frage stellen, warum es eine id und einer user_id gibt? Kann es zu einer user_id mehrere Zeilen in Tabelle 1 geben? Kann sich in einem existierenden Satz der Tabelle 1 die User-ID nochmal ändern? Wenn beides NEIN ist, könnte man die User-ID vermutlich zum Primary Key erheben und die ID-Spalte weglassen.

Ein User hat die Möglichkeit für jeden vorliegenden Plan pro Wochentag eine oder keine Option zu wählen. Also kommt eine user_id durchaus mehrfach in der Tabelle vor. Doch ist eine Kombination aus user_id und plan_id ein Primärschlüssel.

Als ich damals mit SQL anfing, hatte ich einen "Lehrer" der empfahl immer einen einfachen Primärschlüssel anzulegen, anstatt einen kombinierten zu verwenden. Hab damals nicht weiter drüber nachgedacht, da ich genug damit zu tun hatte, alles andere zu verstehen. Sollte ich mir wohl abgewöhnen, denn einen Sinn kann ich grad auch nicht erkennen....

[pl]

Die Alternative besteht darin, die Pivot-Bildung nicht im SQL durchzuführen, sondern einfach nur, wie schon von dedlfix gezeigt, einen GROUP BY zu machen und die Verteilung auf die Wochentage in der Software auszuführen, die das SQL Ergebnis verarbeitet. Eine solche Software könnte auch die Wochentag-Tabelle verwenden, um den SQL-Teil mit den SUM-Zeilen automatisch zu generieren.

Die Software in Perl implementiert wäre ein Hash (PHP assoz. Array) mit 2 Schlüsseln, da wird beim Durchgang durch das Abfrageergebnis einfach nur $pivot{$col}{$opt}++ angewiesen. Danach greifst Du nur noch in den Hash z.B. mit $pivot{monday}{m1} und hast die Anzahl. Ein group by in der Abfrage ist nicht erforderlich.

MfG

[Rolf B]

Hallo pl,

natürlich kann man auch das Zählen in der nachgelagerten Auswertung übernehmen, aber die Strategie beim Einsatz eines SQL Servers ist doch eigentlich, ihm solche Trivialitäten zu überlassen.

Meine Vermutung wäre, dass der SQL Server schneller damit fertig ist, zu zählen und das Zählergebnis zu senden, als die ungezählten Rohdaten in dein Programm zu schicken. Ein GROUP BY frißt wenig bis gar kein Brot, wenn nach Schlüsselspalten gruppiert wird, die einen clustering index bilden. Glaube ich jedenfalls. Gruppiert man nach beliebigen Spalten, muss der Server natürlich umsortieren und da kann das selber addieren in einer Hashtable effizienter sein.

Ich habe jetzt aber keine Lust, das zu benchmarken 😀. Vielleicht bildet der SQL Server ja auch eine Hashtable.

Rolf

[pl]

Grundsätzlich: Ja.

Aber angesichts des bischen Code

my $cols = q(monday,tuesday,wednesday,thursday,friday);
my $spread = $dbh->selectall_arrayref(
    "SELECT $cols FROM spread",
    { Slice => {} }
);

my %pivot = ();
foreach my $r( @$spread ){
    $pivot{$_}{$r->{$_}}++ for split /,/, $cols;
}

# ie. $pivot{monday}{m1}

würde ich den Fakt, daß es auch mit native SQL gehen müsste ziemlich schnell vergessen 😉

PS: Statistiken zur Verteilung von Viren hab ich früher immer so erstellt. Woher die Daten kommen und was das für welche sind, ist ja auch egal. Die Auswertung meiner in Excel geführten Kaffeekasse sah genauso aus.

[dedlfix]

Tach!

Welche Version wäre in Hinblick eines vernünftigen Datenbankentwurfs der "Richtige"?, und warum? Will's ja A richtig machen und im Idealfall B was lernen!

Ein kategorisches Richtig oder Falsch gibts beim Programmieren meistens nicht. Aber wenn man die Lösungsmöglichkeiten aus dem Erfahrungsschatz miteinander vergleicht, bekommt eine mehr Vorteilspunkte als andere, und das wird dann die "richtige" sein - solange bis sich rausstellt, dass man den einen entscheidenden Nachteil nicht auf dem Schirm hatte.

Also, wenn man solche Krücken wie Lösung Nummer eins braucht, bei der man die Statements 1 zu 0,99+Spaltenname für alle Spalten wiederholen muss, dann hat man vermutlich was falsch gemacht und das Potential, eine Normalisierung einzubauen.

Damit ändert sich auch das zuerst angestrebte Abfrageergebnis. Vermutlich wolltest du das dann auch so tabellarisch ausgeben, also ein Datensatz ergibt eine Zeile mit den Werten der Wochentage als Spalten. Mit dem normalisierten Tabellendesign ändert sich die Ergebnismenge. Was bisher größtenteils sozusagen nebeneinander stand, steht nun untereinander. Aber das ist auch kein Problem, das man nicht mit einem Gruppenwechsel gelöst bekommt.

dedlfix.

[pl]

Mahlzeit.

Kann es sein daß Deine Tabelle bereits selbst das Ergebnis einer Abfrage ist? Jedenfalls sieht das mächtig nach einer Pivot-Tabelle aus. Beschreib doch mal kurz wie diese Daten erhoben werden.

MfG

[wKovacs]

Hi,

die Daten stehen genau wie oben gezeigt in der Tabelle (einfach SELECT * FROM table).

User können von verschiedenen Plänen für jeden Wochentag eine (von 7) oder keine Option auswählen.

Doch wie ich in einem anderen Post schrieb, habe ich diese Tabelle korrekt normalisiert wodurch sich obige Fragestellung auch erledigt hat.

Regards

wKovacs

[pl]

Doch wie ich in einem anderen Post schrieb, habe ich diese Tabelle korrekt normalisiert wodurch sich obige Fragestellung auch erledigt hat.

Meine Frage war, wie die Daten erhoben werden. Also wo sie herkommen und wie sie in die Tabelle reinkommen. MfG, schönen Sonntag.

[wKovacs]

Die User sehen eine Tabelle in der sie einen Radio-Button für die gewählte Option wählen

||m1|m2|m3|m4|m5|m6|mZ |Montag|o|o|o|o|o|o|o |Dienstag|o|o|o|o|o|o|o |Mittwoch|o|o|o|o|o|o|o |Donnerstag|o|o|o|o|o|o|o |Freitag|o|o|o|o|o|o|o

In den Zellen stehen noch die Informationen zu der jeweiligen Option, weshalb ich diese Art der Darstellung gewählt habe.

Für mich ist nur die gewählte Option interessant, da diese den Preis bestimmt. Wie diese Option genau aussieht (da sie sich von Tag zu Tag ändert) ist mir egal.

[pl]

Danke für die Info. Ich nehme an, daß diese Erhebung wöchentlich stattfindet und somit kumulativ ist.

Schöne Grüße.

[wKovacs]

wöchentlich: JA

kumulativ: Ich benötige diese Zusammenstellung jeweils pro Woche und als monatliche Zusammenfassung.