MySQL - Abfrage Datum, Ergebnisse mit Wert 0 anzeigen

[SkiD]

Hallo,

ich habe eine Anfrage, welche zu einem bestimmten Datumsinterval alle verschiedenen Einträge nach Klasse soritiert und zählt. D.h. ich bekomme ein Ergebnisse für das Interval vom Jahr 2000 bis 2005 welches ungefähr so aussieht:

2000 12
2001 3
2002 5
2003 8
2004 9
2005 1

Das Problem an der Anfrage ist jedoch, wenn für ein bestimmtes Jahr keine Einträge gefunden werden, wird keine 0 ausgegeben. Bspw müsste folgendes Ergebnis:

2000 12
2001 3
2003 8

... so aussehen, wenn für 2002, 2004 und 2005 nichts gefunden wurde:

2000 12
2001 3
2002 0
2003 8
2004 0
2005 0

Kann mir jemand dabei helfen das Anfrageergebnis so zu entwerfen ?

Meine derzeitige Anfrage sieht so aus:
SELECT DATE_FORMAT(datum, '%Y'), klasse, COUNT(datum) AS cnt FROM tabelle WHERE DATE_FORMAT(datum, '%Y') >= '2000' AND DATE_FORMAT(datum, '%Y') <= '2005' AND klasse like '01' GROUP BY DATE_FORMAT(datum, '%Y'), klasse;

Danke und beste Grüße,
SkiD.

[dedlfix]

Hi!

Das Problem an der Anfrage ist jedoch, wenn für ein bestimmtes Jahr keine Einträge gefunden werden, wird keine 0 ausgegeben.

Das Thema gabs vor kurzem erst, aber ich finde den Thread grad nicht.

Daten, die nicht vorhanden sind, können auch nicht im Abfrageergebnis auftauchen. Man kann beim Auswerten im abfragenden Programm die Lücke feststellen und 0-Zeilen zur Ausgabe bringen.

Eine Lösung allein im DBMS ist zum Beispiel eine Stored Procedure, die für das Abfrageintervall eine temporäre Tabelle mit jedem gewünschten Ergebnis (Jahr in deinem Fall) erstellt. An diese temporäre Tabelle kann man mit einen Left Join die eigentlichen Daten verknüpfen. Für die nicht vorhandenen Jahre ergibt das dann NULL, das man gegebenenfalls mit einer der Funktionen mit NULL im Namen oder COALESCE() zu einer Interger-Null bekommt.

Diese temporäre Tabelle kann man auch mit dem abfragenden Programm generieren, was aber aufwendiger ist, weil CREATE TABLE und ein INSERT-Statement zusammengebaut und zum DBMS gesendet werden müssen. Da kommt man günstiger, beim Auswerten auf Lücken zu testen.

Lo!

[Sven Rautenberg]

Moin!

Das Problem an der Anfrage ist jedoch, wenn für ein bestimmtes Jahr keine Einträge gefunden werden, wird keine 0 ausgegeben.

Kann mir jemand dabei helfen das Anfrageergebnis so zu entwerfen ?

Dein Problem ist mit SQL nicht zu lösen. SQL kann nur Vorhandenes auswerten. Anhand welcher Handreichungen sollte es denn wissen, welche nichtvorhandenen Werte du erwartest?

Meine derzeitige Anfrage sieht so aus:
SELECT DATE_FORMAT(datum, '%Y'), klasse, COUNT(datum) AS cnt FROM tabelle WHERE DATE_FORMAT(datum, '%Y') >= '2000' AND DATE_FORMAT(datum, '%Y') <= '2005' AND klasse like '01' GROUP BY DATE_FORMAT(datum, '%Y'), klasse;

Die einfachste Lösung ist, in der abfragenden Sprache ein Raster für die zu erwartenden Abfrageergebnisse vorzubereiten, mit 0 vorzubelegen, und alle gefundenen SQL-Ergebnisse dann dort einzusortieren.

- Sven Rautenberg

[Matti Mäkitalo]

Hi,

den Hinweis mit der (temporären) Tabelle, welche alle (relevanten) Jahreszahlen enthält, hat dir dedlfix bereits gegeben.

Meine derzeitige Anfrage sieht so aus:
SELECT DATE_FORMAT(datum, '%Y'), klasse, COUNT(datum) AS cnt FROM tabelle WHERE DATE_FORMAT(datum, '%Y') >= '2000' AND DATE_FORMAT(datum, '%Y') <= '2005' AND klasse like '01' GROUP BY DATE_FORMAT(datum, '%Y'), klasse;

Habe gerade kein MySQL zur Hand, um es auszuprobieren, aber aus dem Bauch heraus würde ich sagen, dass du das Statement schneller machen könntest.

klasse like '01' ist ggü. einem einfachen Vergleich langsamer, ersetze also like durch =, wenn du ohnehin keinen Platzhalter verwendest.

Das Problem liegt darin, dass MySQL keine Indize auf "berechneten Spalten" legen kann. In deinem Fall wäre diese Spalte DATE_FORMAT(datum, '%Y').
Du kannst hier also keinen Index einsetzen, was die Abfrage langsam macht.

Die Lösung ist eigentlich recht einfach:
Du machst nicht den Vergleich mit den Jahreszahlen, sondern mit den Randdaten. Suchst du also etwa alle Einträge zwischen dem (und inklusive) 2005, dann kannst du vergleichen, ob die Spalte datum zwischen 2000-01-01 00:00:00 und 2006-01-01 00:00:00 liegt.

Nebenbei kannst du dann auch sehr bequem den BETWEEN ... AND -Operator nutzen, so dass obiges Statement so aussieht:

  
SELECT  
  DATE_FORMAT(datum, '%Y'),  
  klasse,  
  COUNT(datum) AS cnt  
FROM tabelle  
WHERE datum BETWEEN '2000-01-01' AND '2006-01-01' -- DateTime-Spalte angenommen, 0 Uhr wird automatisch ergänzt  
AND klasse = '01'  
GROUP BY DATE_FORMAT(datum, '%Y'), klasse;

Bis die Tage,
Matti