SQL Group by Date: Fehlende Werte

gaunt

Lt. Commander
Registriert
Aug. 2007
Beiträge
2.014
Hi
ich bräuchte mal einen Tipp:
Ich Frage Ereignisse mit einem Timestamp ab. Der wird in Tage, Stunden oder was auch immer formatiert und groupiert. Die Ereignisse kann ich counten.
Aber:
Gibt es in einer Stunde kein Ereignis gibts auch keine Zeile.
PHP:
select
	date_format(ts, '%Y %j %H') as stunde
	, count(*) anzahlEreignisse
from tabelle 
where 
	ts > date_sub(now(), interval 1 day)
group by stunde

Hat einer eine Idee wie ich die "fehlenden" Werte durch Zeilen mit Anzahl 0 bekommen kann?
Ich kann leider nichts Programmieren oder stored Procedure verwenden.

THX
 
Was willst du mit deiner Abfrage denn erreichen? Willst du für die Einträge in deiner Tabelle eine 0 ausgegeben haben, die du durch deine Where-Bedingung ausschließt?

Es wäre evtl. ein Beispiel deiner Tabelle interessant und welches Ergebnis du mit deiner Abfrage erreichen willst.
 
Ist:
Stunde | Wert
1|1
2|2
4|4
5|2
...|

Soll:
Stunde | Wert
1|1
2|2
3|0
4|4
5|2
...|
 
Du kannst bei deinem Query nichts zählen was nicht da ist ;) Würde anders gehn, ist aber auf Datenbankseite keine gute Idee. Machs in der Programmierung der Webseite, indem du einen passenden Key=Stunde, Value=Wert Array aufbaust.

Edit: Bei der Gelegenheit, hast du einen Key auf ts? Falls nicht unbedingt anlegen.
 
Zuletzt bearbeitet:
Also zu lösen wäre das folgendermaßen:

Code:
select
    abschnitte.abschnitt as stunde,
    count(ts) anzahlEreignisse
from tabelle,
     (select rownum as abschnitt from tabelle where rownum < 25) abschnitte
where 
    ts(+) > date_sub(now(), interval 1 day)
    and abschnitte.abschnitt = date_format(ts(+) , '%Y %j %H')
group by stunde

Ich hab das jetzt mal in Oracle-Schreibweise angepaßt. Leider fehlt mir die Zeit, mich in den SQL-Dialekt einzuarbeiten, den Du verwendest. Sorry dafür.
Die Abfrage funktioniert jedenfalls bei mir.

Zur Erläuterung:
Man braucht einfach nur 24 fortlaufende Zahlen. Diese erhält man günstigerweise aus der gleichen Tabelle, sofern mindestens 24 Datensätze insgesamt vorhanden sind, indem man einfach pro Datensatz die Datensatznummer (rownum) zurückliefert. Diese 24 fortlaufenden Zahlen werden mit der ursprünglichen Tabelle über die aus dem Timestamp extrahierte Stunde verknüpft - und zwar per OUTER JOIN (wird in Oracle kurz mit dem (+) nach der Spalte gekennzeichnet, die auch leer sein kann weil die Bedingung nicht zutrifft), sodass die 24 fortlaufenden Datensätze IMMER erscheinen und nur diejenigen aus der Log-Tabelle, die stundenmäßig passen.
Wichtig ist noch, dass man nicht Count(*) schreibt, sonst zählt er IMMER mindestens 1. Count(spaltenname) zählt nur die Spalten, die einen Wert <> NULL besitzen, d.h. beim Zutreffen des Outer-Joins gefüllt sind (hier also nur die Pflichtspalten benutzen, die nicht NULL sein dürfen).
Wichtig auch noch, dass das Einschränken der Zielwerte aus der Tabelle (Timestamp vom aktuellen Tag) auch in den Outer-Join einbezogen ist, sonst klappt das nicht - womöglich ist das in einer anderen SQL-Notation anders gehandhabt. Sollte nur ein Auge drauf geworfen werden.
Sollten noch Verständnisprobleme auftreten, dann einfach fragen.
 
@Enigma
Wenn ich coden könnte wäre es simple. Mir steht aber leider keine Programmiersprache zur Verfügung:-(
Ich hab nichtmal schreibrechte in der DB.
Geht um eine reine statistische Auswertung.

@MistaJack
Der Ansatz ist gut. Leider gibt es rownum unter mysql nicht. Aber das kann man so simulieren:
Code:
SELECT @rownum := @rownum + 1 ROWNUM FROM (SELECT @rownum := 0) r, tabelle limit 1,100
Damit kann ich eine Zahlenreihe erzeugen. Also kann ich die Stunden eines Tages oder die Tage eines Jahres abfragen. Aber in kombination? Also über 2 Jahre hinweg an allen Tagen die Stunden?
Ich muss mich da morgen nochmal drann machen. Heute nichtmehr.
 
gaunt schrieb:
@MistaJack
Der Ansatz ist gut. Leider gibt es rownum unter mysql nicht. Aber das kann man so simulieren:
...
Damit kann ich eine Zahlenreihe erzeugen. Also kann ich die Stunden eines Tages oder die Tage eines Jahres abfragen. Aber in kombination? Also über 2 Jahre hinweg an allen Tagen die Stunden?
Ich muss mich da morgen nochmal drann machen. Heute nichtmehr.

Naja, ok, zugegebenermaßen kenne ich mich mit MySQL eher weniger aus.


Wieso für jeden Tag über 2 Jahre hinweg?
Die Bedingung
Code:
ts > date_sub(now(), interval 1 day)
selektiert doch sowieso nur Datensätze des aktuellen Tages, soweit ich sie verstehe?

Wie auch immer kannst Du ja eine zweite Zahlenreihe über die letzten ~730 Tage hinzufügen und das kartesische Produkt ausnutzen. Hoffentlich stimmt dann die Performance noch.
 
Zurück
Oben