[MySQL] Virtuelle Abfrage mit fortlaufenden Daten erzeugen

[erik s.]

Hallo,

ich sitze gerade mal wieder über den schönen JOINs, die MySQL so zu bieten hat und wollte mittels eines reellen Beispiels üben. Nun tut sich mir aber bei meinem Beispiel gleich noch ein anderes Problem auf, dessen ich mich bisher auch noch nicht annehmen musste.

Mein Beispiel-Problem handelt von drei Tabellen: artikel, umsatz und vertreter. Allzu bekannt aus einigen Tutorials. Die Tabelle umsatz dient quasi als Zwischentabelle der Auflösung der m:n-Beziehung zwischen artikel und vertreter (ein Artikel kann von mehreren Vertretern verkauft worden sein, ein Vertreter kann aber auch mehrere Artikel verkauft haben).

artikel:
------------
id int(11)
bezeichnung varchar(50)
preis decimal(10,2)

umsatz:
------------
id int(11)
artikel_id int(11)
vertreter_id int(11)
menge int(11)
datum date

vertreter:
-------------
id int(11)
name varchar(50)

Als ersten Query habe ich einen erstellt, der mir heraussucht, an welchen Tagen welche Artikel nicht verkauft wurden. Funktioniert auch wirklich gut und ist logisch ;-)

		$sql = "SELECT DATE_FORMAT(E.datum, \"%d.%m.%Y\") AS datum,E.id,E.bezeichnung " .
			   "FROM " .
			   		"(SELECT C.datum, D.id, D.bezeichnung " .
			   		"FROM " .
			   			"(SELECT DISTINCT A.datum " .
				   			"FROM umsatz AS A) AS C," .
			   			"(SELECT B.id, B.bezeichnung " .
				   			"FROM artikel as B) AS D) AS E " .
			   "LEFT JOIN umsatz AS U " .
			   "ON E.datum = U.datum AND E.id = U.artikel_id " .
			   "WHERE U.datum IS NULL " .
			   "ORDER BY E.datum, E.id";

Nun kann es ja vorkommen, dass an manchen Tagen gar nichts verkauft wird, zu diesen Tagen gibt es dann auch logischerweise keine Datensätze in der umsatz-Tabelle (Feiertage vernachlässige ich der Einfachheit halber).

Wie kann ich die Abfrage

SELECT DISTINCT A.datum ...

also durch eine künstlich erzeugte ersetzen, die meinetwegen alle Daten vom 2010-01-01 bis 2010-06-30 als Zeilen enthält? Geht das überhaupt?

Zur Erläuterung der Abfrage oben:
Zuerst erzeuge ich eine Abfrage, in der jede mögliche Datum-Artikel-Kombination vorkommt (hier: natürlich nur die Daten, die in der umsatz-Tabelle stehen <- Problem). Dann kombiniere ich die Ergebnistabelle mittels LEFT JOIN mit der umsatz-Tabelle wobei hier die Datensätze mit Umsatz herausgefiltert werden. Was übrig bleibt sind die Datum-Artikel-Kombinationen, die quasi nicht stattgefunden haben.

Hoffe, irgendjemand versteht mich

Gruß,
Erik

EDIT: Ich sehe gerade, dass das Thema bei den Relationalen Datenbanksystemen besser aufgehoben wäre ...

 

[Yaslaw]

Ev. gibts noch was schöneres. Aber ich habe hier mal eine virtuelle Tabvelle für die Datum von Anfang 2010 bis Ende 2017 erstellt.

SELECT *
FROM
	(
		SELECT
			myYear,
			makedate(myYear, day_in_year) AS mydate
		FROM
			(
				SELECT
					@day_in_year := @day_in_year +1 AS day_in_year
				FROM
					(SELECT @day_in_year :=0) AS vars,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d1,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d2,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS d3,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS d4,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS d5
			) AS daysOfYear,
			(
				SELECT 2010 AS myYear UNION SELECT 2011 
				UNION SELECT 2012 UNION SELECT 2013
				UNION SELECT 2014 UNION SELECT 2015
				UNION SELECT 2016 UNION SELECT 2017) AS years
		WHERE 
			YEAR(makedate(myYear, day_in_year)) = myYear
	) AS dates
WHERE
	myYear=2011

Ev. lohnt es sich, diese Daten in eine Tabelle zu speichern.

Und nu zu der Erklärung.
Als erstes brauche ich einfach mal mehr als 365 Datensätze. Dies erreiche ich mit der Multiplikation der folgenden UNION-SELECTS

-- [VirtualRows]:
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d1,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d2,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS d3,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS d4,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS d5

Dieses Resultat wird durchnumeriert mittels der Variable @day_in_year

-- [DayNumbers]:
				SELECT
					@day_in_year := @day_in_year +1 AS day_in_year
				FROM
					(SELECT @day_in_year :=0) AS vars,
					[VirtualRows]

Diese Tagesnummern kombiniere ich mit einem UNION, der die Jahre beinhaltet und errechen die Datums.
Da meine Virtuelle Tabelle 432 Datensätze beinhaltet (4*4*3*3*3), erhalte ich pro Jahr auch Datums im nächsten Jahr:
makedate(2010, 400) ergibt ein Datum im Jahre 2011.
Darum prüfe ich am Schluss nochmals, ob das Jahr übereinstimmt. Da ich die Anzahl Tage nicht auf 365 beschränke, habe ich keine Probleme mit dem Schaltjahr. Das rechnet mir makedate() richtig aus.

-- [Datumliste]:
		SELECT
			myYear,
			makedate(myYear, day_in_year) AS mydate
		FROM
			([DayNumbers]) AS daysOfYear,
			(
				SELECT 2010 AS myYear UNION SELECT 2011 
				UNION SELECT 2012 UNION SELECT 2013
				UNION SELECT 2014 UNION SELECT 2015
				UNION SELECT 2016 UNION SELECT 2017) AS years
		WHERE 
			YEAR(makedate(myYear, day_in_year)) = myYear

Zuguter Letzt habe ich das ganze nochmals in ein SELECT gepackt um das Jahr auszuwählen. Dieses SELECT kann man sich aber auch sparen

SELECT *
FROM
	([Datumliste]) AS dates
WHERE
	myYear=2011

 

[erik s.]

Oha, schwere Kost zum frühen Morgen

Vielen Dank, werde mir das zu Gemüte führen und sehen, was ich daraus lernen kann. Von den UNION SELECTs wusste ich bis eben beispielsweise nichts.

Gruß,
Erik