Geburtstage der letzten und kommenden 3 Tage
Melvin Cowznofski
- datenbank
Hallo,
ich habe heute Früh irgendetwas bzgl. MySQL gegoogelt und bin dabei zufällig auf eine Seite gestoßen, wo jemand das Problem hatte, aus einer Tabelle einer MySQL Datenbank, in der es eine Spalte mit Geburtstagen im DATE Format gibt, sich alle Leute anzeigen lassen zu wollen, deren Geburtstag zwischen den letzten 3 Tagen und den kommenden 3 Tagen liegt. (Ich gehe davon aus, dass durch eine Eingabevalidierung sichergestellt ist, dass in der Geburtstagsspalte ein Datum aus der Vergangenheit stehen _muss_!)
Ich selbst brauche so was momentan nicht aber mich hat es trotzdem gereizt, mich mit dieser Aufgabe zu beschäftigen. Nach ca. 10 Stunden Googeln, Manual Lesen und meist erfolglosem Herumprobieren habe ich nun nicht nur brennende Augen, sondern auch ein Ergebnis, das ich hier vorstellen möchte. Einerseits, weil ich da Fragen dazu habe, andererseits, weil ich gerne wissen würde, was Ihr von meiner Lösung haltet. Kann ja leicht möglich sein, dass es eine bessere/simplere Lösung gibt.
Nach längerem Googeln fand ich als erste brauchbare Idee diese Lösung. Hier stehe ich leider vor dem Problem, dass ich auch selbst nach stundenlangem Nachdenken den Block […]AS days\_to\_birthday
nicht mal ansatzweise verstehe.
Also habe ich weitergesucht und fand dann auf dieser Seite folgenden Lösungsvorschlag für diese Art von Fragestellung:
This was a little tricky, but here is my solution:
SELECT
birthday,
-- Shift the birthday into the current year
CAST(CONCAT(YEAR(NOW()), '-', MONTH(birthday), '-', DAY(birthday)) AS DATE) AS BirthdayInCurrentYear
FROM Club
WHERE CAST(CONCAT(YEAR(NOW()), '-', MONTH(birthday), '-', DAY(birthday)) AS DATE)
BETWEEN NOW() AND NOW() + INTERVAL 30 DAY
Was mir hier nicht gefällt ist, dass eine virtuelle Spalte „BirthdayInCurrentYear“ angelegt wird aber dann nicht mit dierser Spalte gearbeitet wird, sondern die Berechnung für die Spaltenwerte ein 2. Mal im Query steht.
Ich habe deshalb versucht, ob nicht irgendetwas mit [...] WHERE BirthdayInCurrentYear […] möglich ist. Aber es kommt dann immer zu einer Fehlermeldung, dass es so eine Spalte nicht gibt. So bin ich dann auf jetzt nicht mehr nachvollziehbaren Umwegen darauf gekommen, dass eine Weiterverarbeitung zwar nicht mit WHERE funktioniert, aber mit HAVING. (Etwas, das ich bis dato nicht mal gekannt habe.)
Und somit komme ich, nachdem ich mich den ganzen Tag damit beschäftigt habe, auf folgende Lösung:
SELECT m.id, m.name, m.familienname, m.geburtsdatum,
CAST(CONCAT(YEAR(NOW()), '-', MONTH(m.geburtsdatum), '-', DAY(m.geburtsdatum)) AS DATE) AS BirthdayInCurrentYear,
DATEDIFF(CAST(CONCAT(YEAR(NOW()), '-', MONTH(m.geburtsdatum), '-', DAY(m.geburtsdatum)) AS DATE),NOW()) AS differenz
FROM mitglieder AS m
HAVING BirthdayInCurrentYear BETWEEN NOW() - INTERVAL 4 DAY AND NOW() + INTERVAL 3 DAY
ORDER BY BirthdayInCurrentYear ASC
Es werden, wie gewünscht, alle Personen aufgelistet, die ihren Geburtstag innerhalb der Zeitspanne heute - 3 Tage und heute + 3 Tage haben. Zusätzlich liedert der Query die virtuelle Spalte "differenz", in der die Tage seit bzw. bis zum Geburtstag stehen bzw. eine 0, wenn die Person heute Geburtstag hat.
Ewig gedauert hat es, bis ich drauf gekommen bin, dass es für die Geburtstage der vergangenen 3 Tage nötig ist, NOW() - INTERVAL 4 DAY zu schreiben. Zuerst fehlten nämlich immer die Personen mit Geburtstag vor genau 3 Tagen. Meine Versuche haben dann gezeigt, dass NOW() - INTERVAL x DAY alle Personen mit Geburtstagen in den letzten x-1 Tagen ausgibt.
Wie gesagt, ich habe ein funktionierendes Ergebnis. Und was ich noch habe, sind jetzt mehrere Fragen, weil ich so manches nicht nachvollziehen kann:
1.) Wieso kann ich eine virtuell erstellte Spalte nicht mit WHERE weiterverarbeiten, mit HAVING aber schon?
2.) Wieso greift NOW() + INTERVAL 3 DAY für die kommenden 3 Tage, aber NOW() - INTERVAL 3 DAY nur für die letzten 2 Tage?
3.) Bevor ich die Lösung mit dem BETWEEN hatte, habe ich auch in die Richtung ([…] > NOW() - INTERVAL 3 DAY ) AND ([…] < NOW() + INTERVAL 3 DAY ) herumexperimentiert. Da gab es aber nur Fehlermeldungen und nie ein Ergebnis. _Gäbe_ es denn einen Weg in die Richtung „größer als heute – 3 und kleiner als heute + 3“? (Abgesehen davon, dass die Lösung mit BETWEEN eh kürzer ist.)
4.) Was haltet Ihr generell von meiner Lösung? Ist das OK so oder völlig unbrauchbar? Ist das erste verlinkte Beispiel besser und wenn ja, warum?
5.) Und meine letzte Frage: Auch wenn meine Lösung funktioniert, würde ich trotzdem gerne den […]AS days\_to\_birthday
Block der zuerst gefundenen Lösung verstehen. Kann mir das vielleicht wer ausdeutschen, was in diesem Teil der Query geschieht? Unter anderem verstehe ich die Stelle mit dem „ , 1, 0))“ nicht.
Ich möchte mich jetzt schon bedanken, falls sich wer dieser Sache annimmt und mir ein Feedback gibt!
Mit lieben Grüßen
Melvin Cowznofski
Tach!
(Ich gehe davon aus, dass durch eine Eingabevalidierung sichergestellt ist, dass in der Geburtstagsspalte ein Datum aus der Vergangenheit stehen _muss_!)
(Ist im Prinzip egal, wenn es nur um die Geburtstagsanzeige und zum Beispiel keine rechtlich relevanten Dinge geht. Dann wird eben zum minus soundosovielten Geburtstag gratuliert.)
Hier stehe ich leider vor dem Problem, dass ich auch selbst nach stundenlangem Nachdenken den Block […]AS
days\_to\_birthday
nicht mal ansatzweise verstehe.
Nach AS steht ein Aliasname, der zum Beispiel als "Feldname" in der Ergebnismenge auftaucht. Ansonsten steht da nämlich exakt das was im SELECT steht: ein Feldname oder der komplette Ausdruck.
Was mir hier nicht gefällt ist, dass eine virtuelle Spalte „BirthdayInCurrentYear“ angelegt wird aber dann nicht mit dierser Spalte gearbeitet wird, sondern die Berechnung für die Spaltenwerte ein 2. Mal im Query steht.
Der Aliasname wird nach der Berechnung vergeben und im WHERE ist er noch gar nicht vorhanden. Es gibt eine Reihenfolge, in der die Klauseln ausgewertet werden. Aliasnamen sind immer erst nach dem Abarbeiten der jeweiligen Klausel für die anderen Klauseln verfügbar. Aliasnamen im SELECT können im ORDER BY verwendet werden. Im HAVING auch, aber das sollte man nicht als WHERE-Ersatz missbrauchen, weil das unnötig Ressourcen verbraucht (wenn es der Optimizer nicht wegoptimiert).
FROM -> WHERE -> GROUP BY -> SELECT -> HAVING -> ORDER BY -> LIMIT
Das SELECT steht üblicherweise vorn, schmuggelt sich aber in der Abarbeitungsreihenfolge dazwischen. Ansonsten ist die Reihenfolge so, wie man die Klauseln notieren muss. Das heißt, im WHERE kann man auf Aliasnamen (und Berechnungsergebnisse) für Tabellen zugreifen und nicht auf die im SELECT.
Mal angenommen, du hast keine Berechnung sondern einen einfachen Vergleich im WHERE, dann kann unter Umständen ein Index zum schnelleren Finden der Datensätze verwendet werden. Sortierst du erst im HAVING die ungewünschten Datensätze weg, dann arbeitest du bereits mit den Daten der Ergebnismenge und nicht mehr mit den Daten aus der Tabelle. Dann hat das SELECT vielleicht viele Berechnungen (für die anderen Felder) umsonst angestellt, deren Datensätze das WHERE gar nicht erst durchgelassen hätte.
Weiterhin definiert der SQL-Standard, dass HAVING sich nur auf GROUP-BY-Spalten und Aggregatfunktionen beziehen darf. MySQL ist etwas freier und gestattet das HAVING auch ohne die Anwendung von GROUP BY. Woanders bekommst du sowas als Fehler abgelehnt.
2.) Wieso greift NOW() + INTERVAL 3 DAY für die kommenden 3 Tage, aber NOW() - INTERVAL 3 DAY nur für die letzten 2 Tage?
SELECT NOW() - INTERVAL 3 DAY lieferte eben 2013-08-30 18:57:28. Das ist größer als der 2013-08-30 (00:00:00) und somit ist der 30.8. nicht mehr im Intervall.
4.) Was haltet Ihr generell von meiner Lösung? Ist das OK so oder völlig unbrauchbar? Ist das erste verlinkte Beispiel besser und wenn ja, warum?
Es gibt eine deutlich einfachere, wenn man den 29. Februar außen vor lässt. DAYOFYEAR() spart eine ganze Menge Funktionsaufrufe.
Ob ein Schaltjahr ist, lässt sich mit MySQL nicht einfach herausfinden. Deswegen würde ich Schaltjahre ignorieren (dann ist da eben ein Versatz um einen Tag drin) oder sie außerhalb SQLs berücksichtigen, in der Form, dass ich die Tagesnummern für die obere und untere Grenze in die Query reinreiche und nicht in MySQL ermittle.
5.) [...] Unter anderem verstehe ich die Stelle mit dem „ , 1, 0))“ nicht.
Die relevante Stelle sieht gekürzt so aus:
... + IF(x > y, 1, 0)
1 und 0 sind Parameter der IF-Funktion. Das IF() liefert abhängig vom Vergleichsergebnis eine 1 oder 0 und die wird zum vorhergehenden Teilausdruck addiert.
dedlfix.
Hallo dedlfix!
Nach AS steht ein Aliasname, der zum Beispiel als "Feldname" in der Ergebnismenge auftaucht. Ansonsten steht da nämlich exakt das was im SELECT steht: ein Feldname oder der komplette Ausdruck.
Wofür das AS steht, war mir schon klar. Den Block davor habe ich nicht verstanden.
FROM -> WHERE -> GROUP BY -> SELECT -> HAVING -> ORDER BY -> LIMIT
_Das_ ist sehr interessant! Vielen Dank für diese Info!
Der Aliasname wird nach der Berechnung vergeben und im WHERE ist er noch gar nicht vorhanden.
Das verstehe ich jetzt, wenn ich mir die Abarbeitungsfolge, die Du geschrieben hast, ansehe.
Es gibt eine Reihenfolge, in der die Klauseln ausgewertet werden. Aliasnamen sind immer erst nach dem Abarbeiten der jeweiligen Klausel für die anderen Klauseln verfügbar.
Also, wenn ich mir die Abarbeitungsfolge ansehe, erst nach dem SELECT, sprich im HAVING, und im ORDER BY.
Aliasnamen im SELECT können im ORDER BY verwendet werden. Im HAVING auch
Na dann verstehe ich das ja richtig.
aber das sollte man nicht als WHERE-Ersatz missbrauchen, weil das unnötig Ressourcen verbraucht (wenn es der Optimizer nicht wegoptimiert).
Das verstehe ich nicht so ganz. Was wäre denn ein _simples_ Beispiel für so einen unötigen WHERE-Ersatz? Anders gefragt: Kann man den Unterschied/Verwendungszweck bei WHERE und HAVING irgendwie verständlich erklären?
Das heißt, im WHERE kann man auf Aliasnamen (und Berechnungsergebnisse) für Tabellen zugreifen und nicht auf die im SELECT.
Also das ist mir jetzt _völlig_ unverständlich! Was meinst Du mit "Aliasnamen (und Berechnungsergebnisse) für Tabellen"?
SELECT preis, (preis * 2) AS doppelter_preis FROM katalog
Hier ist "doppelter_preis" ein Aliasname einer virtuellen Spalte. Also einer Spalte, die es in der Datenbank eigentlich gar nicht real gibt. Gefüllt mit berechneten Werten. Der Aliasnahme entstammt dem SELECT, kann also, wie wir vorher festgestellt haben, nur im HAVING und im ORDER BY verwendet werden. Und was wäre im Gegensatz dazu jetzt ein "Aliasnamen (und Berechnungsergebnis) für Tabellen", auf den ich laut Deinem Satz auch im WHERE zugreifen kann?
Mal angenommen, du hast keine Berechnung sondern einen einfachen Vergleich im WHERE, dann kann unter Umständen ein Index zum schnelleren Finden der Datensätze verwendet werden. Sortierst du erst im HAVING die ungewünschten Datensätze weg, dann arbeitest du bereits mit den Daten der Ergebnismenge und nicht mehr mit den Daten aus der Tabelle. Dann hat das SELECT vielleicht viele Berechnungen (für die anderen Felder) umsonst angestellt, deren Datensätze das WHERE gar nicht erst durchgelassen hätte.
Ich bilde mir zwar ein, das zumindest teilweise zu verstehen, wenn ich dabei auf die von Dir gepostete Avarbeitungsfolge schaue, aber so ganz klar ist mir die Sache nicht. Kannst Du das evt. an Hand eines ganz trivialen Beispiels verständlich machen?
Es gibt eine deutlich einfachere, wenn man den 29. Februar außen vor lässt. DAYOFYEAR() spart eine ganze Menge Funktionsaufrufe.
_Das_ war ursprünglich das Erste, woran ich gedacht habe und meine ersten Lösungsversuche gingen auch in diese Richtung. Ich fand das allerdings unbrauchbar, weil die Tage nach dem 28. Februar in den Schlatjahren nicht der selbe "day of year" sind und somit wurden zB. die Schaltjahr-Geburtstage bei den Geburtstagen vor 3 Tagen nicht mitangezeigt. Nein, das DAYOFYEAR() bringt in diesem Fall nichts. Aber meine finale Lösung funktioniert eh wunderbar.
Die relevante Stelle sieht gekürzt so aus:
... + IF(x > y, 1, 0)
1 und 0 sind Parameter der IF-Funktion. Das IF() liefert abhängig vom Vergleichsergebnis eine 1 oder 0 und die wird zum vorhergehenden Teilausdruck addiert.
Danke, jetzt ist es klar!
Mit lieben Grüßen
Melvin Cowznofski
Tach!
[HAVING] sollte man nicht als WHERE-Ersatz missbrauchen, weil das unnötig Ressourcen verbraucht (wenn es der Optimizer nicht wegoptimiert).
Das verstehe ich nicht so ganz. Was wäre denn ein _simples_ Beispiel für so einen unötigen WHERE-Ersatz? Anders gefragt: Kann man den Unterschied/Verwendungszweck bei WHERE und HAVING irgendwie verständlich erklären?
Statt Beispiel formuliere ich das mal so: HAVING dient dazu, ein Ergebnis zu filtern, das erst nach dem Gruppieren entsteht. Das geht im WHERE ja noch nicht. Alles andere kann im WHERE gefiltert werden. Ein HAVING-"Missbrauch" liegt also dann vor, wenn man es ohne Gruppierung verwendet.
Das heißt, im WHERE kann man auf Aliasnamen (und Berechnungsergebnisse) für Tabellen zugreifen und nicht auf die im SELECT.
Also das ist mir jetzt _völlig_ unverständlich! Was meinst Du mit "Aliasnamen (und Berechnungsergebnisse) für Tabellen"?
Aliasnamen für Tabellen sollte klar sein. Berechnungsergebnisse sollte hier eine Subquery sein, die eine Ergebnismenge ähnlich einer Tabelle liefert.
SELECT preis, (preis * 2) AS doppelter_preis FROM katalog
Hier ist "doppelter_preis" ein Aliasname einer virtuellen Spalte. Also einer Spalte, die es in der Datenbank eigentlich gar nicht real gibt. Gefüllt mit berechneten Werten. Der Aliasnahme entstammt dem SELECT, kann also, wie wir vorher festgestellt haben, nur im HAVING und im ORDER BY verwendet werden. Und was wäre im Gegensatz dazu jetzt ein "Aliasnamen (und Berechnungsergebnis) für Tabellen", auf den ich laut Deinem Satz auch im WHERE zugreifen kann?
SELECT preis, (preis * 2) AS doppelter_preis FROM katalog k WHERE k.produktgruppe = 'foo'
In dem Beispiel ist der Tabellenalias k verzichtbar. Interessanter wird er erst wenn mehrere Tabellen gejoint werden (und man nicht immer den kompletten Tabellennamen hinschreiben will).
SELECT * FROM table1 t1 LEFT JOIN (SELECT * FROM table2) t2 WHERE t2.id IS NULL
Das ist ein Beispiel für eine Subquery. Hier ist die Subquery zwar nicht sinnvoll, weil sie direkt durch den table2 ersetzt werden kann, aber du musst dir vorstellen, dass das da eine komplexe Abfrage ist, die man nicht über Join- und Where-Bedingungen abbilden kann. Eine Subquery im FROM jedenfalls verlangt immer einen Alias, auch wenn man ihn später nicht verwendet.
Mal angenommen, du hast keine Berechnung sondern einen einfachen Vergleich im WHERE, dann kann unter Umständen ein Index zum schnelleren Finden der Datensätze verwendet werden. Sortierst du erst im HAVING die ungewünschten Datensätze weg, dann arbeitest du bereits mit den Daten der Ergebnismenge und nicht mehr mit den Daten aus der Tabelle. Dann hat das SELECT vielleicht viele Berechnungen (für die anderen Felder) umsonst angestellt, deren Datensätze das WHERE gar nicht erst durchgelassen hätte.
Ich bilde mir zwar ein, das zumindest teilweise zu verstehen, wenn ich dabei auf die von Dir gepostete Avarbeitungsfolge schaue, aber so ganz klar ist mir die Sache nicht. Kannst Du das evt. an Hand eines ganz trivialen Beispiels verständlich machen?
SELECT * FROM foo WHERE id = ?
Ein Index auf id kann verwendet werden.
SELECT * FROM foo WHERE a + b = ?
SELECT * FROM foo WHERE bar(a) = ?
Es gibt keine berechneten Indexe in MySQL, also muss hier ein Full-Table-Scan ausgeführt werden, weil erst nach dem Berechnen von a+b feststeht, ob der Datensatz selektiert werden soll. Dasselbe gilt für bar(a), das muss auch erst für jeden Datensatz einzeln berechnet werden.
Bei Datumsfilterungen ist es immer von Vorteil, wenn man das Datumsfeld direkt angeben kann und nicht mit einer Funktion darauf zugreift. Das geht natürlich nicht, wenn man nur Teile vom Datum braucht und das Selektionskriterium nicht auf vollständige Datümer umformulieren kann.
Ich weiß nicht, ob MySQL bei einem HAVING im Missbrauchsfall einen Index verwendet, bei "ordentlicher" Verwendung nach einem GROUP BY auf ein Aggregationsergebnis ist das jedenfalls nicht möglich.
Es gibt eine deutlich einfachere, wenn man den 29. Februar außen vor lässt. DAYOFYEAR() spart eine ganze Menge Funktionsaufrufe.
[...] Nein, das DAYOFYEAR() bringt in diesem Fall nichts.
... wenn die Lösung rein datenbankbasiert sein soll.
dedlfix.
Moin
5.) Und meine letzte Frage: Auch wenn meine Lösung funktioniert, würde ich trotzdem gerne den […]AS
days\_to\_birthday
Block der zuerst gefundenen Lösung verstehen. Kann mir das vielleicht wer ausdeutschen, was in diesem Teil der Query geschieht? Unter anderem verstehe ich die Stelle mit dem „ , 1, 0))“ nicht.
DATEDIFF(`geburtstag` + INTERVAL
(YEAR(CURRENT_DATE) - YEAR(`geburtstag`) +
IF(DATE_FORMAT(CURRENT_DATE, "%m%d") >
DATE_FORMAT(`geburtstag`, "%m%d"), 1, 0))
YEAR, CURRENT_DATE) AS `days_to_birthday`
Diese Funktion muss man von innen nach außen lesen (wie jede Funktion)
nehmen wir zuerst
DATE_FORMAT(CURRENT_DATE, "%m%d")
diese Funktion gibt ein Datumsfeld formatiert zurück (hier als %m%d (Monat im Format 00-12 und Tag des Monats als 00-31)
Es geht weiter
IF(DATE_FORMAT(CURRENT_DATE, "%m%d") >
DATE_FORMAT(`geburtstag`, "%m%d"), 1, 0)
es wird hier das aktuelle ebenso formatierte aAtum mit dem ausgelesenen formatierten verglichen. Wenn das aktuelle Datum größer als das ausgelesene (nur von Monat und Tag her), dann soll eine 1 zurückgegeben werden ansonsten eine 0
Und weiter im Text:
`geburtstag` +INTERVAL
(YEAR(CURRENT_DATE) - YEAR(`geburtstag`) +
IF(DATE_FORMAT(CURRENT_DATE, "%m%d") >
DATE_FORMAT(`geburtstag`, "%m%d"), 1, 0)) YEAR
hier ersetze ich mal mit 1 oder o des vorherigen Blocks ** 1|0 **
`geburtstag` + INTERVAL (YEAR(CURRENT_DATE) - YEAR(`geburtstag`) + ** 0|1** ) YEAR
Hier der nächste Geburtstag des Users endweder auf dieses Jahr (Wennn er noch folgt) oder aufs nächste Jahr (wenn im aktuellen Jahr bereits vorbei) gesetzt.
Und als letztes noch die Datediff-Funktion
DATEDIFF(`geburtstag` + INTERVAL
(YEAR(CURRENT_DATE) - YEAR(`geburtstag`) +
IF(DATE_FORMAT(CURRENT_DATE, "%m%d") >
DATE_FORMAT(`geburtstag`, "%m%d"), 1, 0))
YEAR, CURRENT_DATE)
hier wird die Differenz an Tagen zwischen dem errechneten nächsten Geburtstag und dem aktuellen Datum berechnet "DATEDIFF ( errechneter nächster geburtstag , aktuelles Datum)".
Und so erhält man eine Anzahl an Tagen zum nächsten Geburtstag als days\_to\_birthday
.
Gruß Bobby
Hallo Bobby!
Danke für die Zerlegung des Codes und die Erklärung, welche mir das Ganze etwas leichter machte, die Sache zu verstehen. Ich habe trotzdem mehrere Tage gebraucht, bis mir halbwegs klar war, was hier passiert. Deshalb auch erst jetzt meine Antwort.
Etwas habe ich allerdings verändert. Zunächst bin ich draufgekommen, dass man den Teil mit dem +0 oder 1 gar nicht wirklich braucht, wenn man sich die Geburtstage des Zeitraums -3 Tage bis heute bis +3 Tage mit der Anzahl der Differenztage zu heute ausgeben lassen möchte.
Außerdem finde ich die ursprüngliche Sortierung nach "daystobirthday" nicht sehr optimal. Ich will ja nicht wissen, dass ein User in 364 Tagen den nächsten Geburtstag hat. Ich will wissen, ober ihn heute, in 1,2 oder 3 Tagen oder vor 1, 2 oder 3 Tagen gehabt hat und danach möchte ich auch die Ausgabe reihen.
Deshalb schaut mein Query in der aktuellen, finalen Version so aus:
SELECT m.name,
m.familienname,
m.geburtsdatum,
DATEDIFF(m.geburtsdatum+INTERVAL(YEAR(NOW())-YEAR(m.geburtsdatum))YEAR,NOW()) AS differenz
FROM mitglieder AS m
HAVING differenz BETWEEN -3 AND 3
ORDER BY differenz ASC
Kurz, simpel und nun auch für mich verständlich. Man kann natürlich eine weitere virtuelle Spalte schaffen und zusätzlich auch noch das momentane Alter ausgeben.
Somit habe ich eine Lösung, die ich auch verstehe. Danke nochmals für Deine Antwort!
Mit lieben Grüßen
Melvin Cowznofski