Yaslaw.Info

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======[MySQL] Virtuelle Tabelle mit allen Daten im Jahr======
Im Tutorial-Forum kam die Frage auf, wie man eine Virtuelle Tabelle mit Allen Datums eines Jahres erstellen kann.
Hier mein Lösungsvorschlag:
=====Lösung=====
Ev. gibts noch was schöneres. Aber ich habe hier mal eine virtuelle Tabelle für die Datums von diesem Jahr -10/+17 erstellt und zum Schluss als Beispiel auf dieses Jahr eingeschränkt.
<code sql>
SELECT *
FROM
	(
		-- [Datumsliste]
		SELECT
			myYear,
			makedate(years.myYear, daysOfYear.day_in_year) AS mydate
		FROM
			(
				-- [Tagesnummern]
				-- Zahlen von 1 bis 400
				SELECT
					@day_in_year := @day_in_year +1 AS day_in_year
				FROM
					(SELECT @day_in_year :=0) AS vars,
					-- [VirtualRows1]
					-- 400 Zeilen
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS d1,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS d2,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d3,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d4
					-- [/VirtualRows1]
				-- [/Tagesnummern]
			) AS daysOfYear,
			(
				-- [Jahreszahlen]
				-- Zahlen von 'Aktuelles Jahr'-10 bis 'Aktuelles Jahr' +17
				SELECT
					@year := @year+1 AS myYear
				FROM
					(SELECT @year := YEAR(NOW()) -10) AS vars,    
					-- [VirtualRows2]
					-- 27 Zeilem
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y1,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y2,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y3					
					-- [/VirtualRows2]
				-- [/Jahreszahlen]
			) AS years
		WHERE
			YEAR(makedate(years.myYear, daysOfYear.day_in_year)) = years.myYear
		-- [/Datumsliste]
	) AS dates
WHERE
	myYear=2011;
</code>

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

=====Erklärung=====
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
<code sql>
-- [VirtualRows1]
-- 400 Zeilen
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS d1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS d2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d3,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d4
-- [/VirtualRows1]</code>
Dieses Resultat wird durchnummeriert mittels der Variable @day_in_year
<code sql>
-- [Tagesnummern]
-- Zahlen von 1 bis 400
SELECT
	@day_in_year := @day_in_year +1 AS day_in_year
FROM
	(SELECT @day_in_year :=0) AS vars,
	[VirtualRows1]
-- [/Tagesnummern]
</code>

Als zweite Quelle erstelle ich 27 Zelen für die Jahreszahlen
<code sql>
-- [VirtualRows2]
-- 27 Zeilem
(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y3					
-- [/VirtualRows2]
</code>

Auch diese kombiniere werden durchnummeriert. Ich fange aber nicht bei 0 an, sondern bei 'aktuelles Jahr minus 10'
<code sql>
-- [Jahreszahlen]
-- Zahlen von 'Aktuelles Jahr'-10 bis 'Aktuelles Jahr' +17
SELECT
	@year := @year+1 AS myYear
FROM
	(SELECT @year := YEAR(NOW()) -10) AS vars,    
	[VirtualRows2]					
-- [/Jahreszahlen]
</code>

Diese Tagesnummern kombiniere ich mit den Jahren und errechnen die Datums.
Da meine Virtuelle Tabelle 400 Datensätze beinhaltet (5x5x4x4), 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.
<code sql>
-- [Datumsliste]
SELECT
	myYear,
	makedate(years.myYear, daysOfYear.day_in_year) AS mydate
FROM
	[Tagesnummern]  AS daysOfYear,
	[Jahreszahlen] AS years
WHERE
	YEAR(makedate(years.myYear, daysOfYear.day_in_year)) = years.myYear
-- [/Datumsliste]
</code>

Das wars auch schon.


===== Möglicher Nutzen =====
==== Eine Tabelle erstellen mit allen Tagen von 2013 und 2014 ====
Dazu wende ich das Obige 2 mal an. Beim ersten mal mit einem CREATE_TABLE für das 
Jahr 2013 und einmal als INSERT INTO für das Jahr 2014.
<code sql>-- Tagestabelle mit den Jahren 2013 und 2014
-- 2013
CREATE TABLE tbl_dates AS
SELECT *
FROM
	(
		-- [Datumsliste]
		SELECT
			myYear,
			makedate(years.myYear, daysOfYear.day_in_year) AS mydate
		FROM
			(
				-- [Tagesnummern]
				-- Zahlen von 1 bis 400
				SELECT
					@day_in_year := @day_in_year +1 AS day_in_year
				FROM
					(SELECT @day_in_year :=0) AS vars,
					-- [VirtualRows1]
					-- 400 Zeilen
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS d1,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS d2,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d3,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d4
					-- [/VirtualRows1]
				-- [/Tagesnummern]
			) AS daysOfYear,
			(
				-- [Jahreszahlen]
				-- Zahlen von 'Aktuelles Jahr'-10 bis 'Aktuelles Jahr' +17
				SELECT
					@YEAR := @YEAR+1 AS myYear
				FROM
					(SELECT @YEAR := YEAR(NOW()) -10) AS vars,    
					-- [VirtualRows2]
					-- 27 Zeilem
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y1,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y2,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y3					
					-- [/VirtualRows2]
				-- [/Jahreszahlen]
			) AS years
		WHERE
			YEAR(makedate(years.myYear, daysOfYear.day_in_year)) = years.myYear
		-- [/Datumsliste]
	) AS dates
WHERE
	myYear=2013;
	
-- 2014	
INSERT INTO  tbl_dates (myYear, mydate)
SELECT *
FROM
	(
		-- [Datumsliste]
		SELECT
			myYear,
			makedate(years.myYear, daysOfYear.day_in_year) AS mydate
		FROM
			(
				-- [Tagesnummern]
				-- Zahlen von 1 bis 400
				SELECT
					@day_in_year := @day_in_year +1 AS day_in_year
				FROM
					(SELECT @day_in_year :=0) AS vars,
					-- [VirtualRows1]
					-- 400 Zeilen
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS d1,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS d2,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d3,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d4
					-- [/VirtualRows1]
				-- [/Tagesnummern]
			) AS daysOfYear,
			(
				-- [Jahreszahlen]
				-- Zahlen von 'Aktuelles Jahr'-10 bis 'Aktuelles Jahr' +17
				SELECT
					@YEAR := @YEAR+1 AS myYear
				FROM
					(SELECT @YEAR := YEAR(NOW()) -10) AS vars,    
					-- [VirtualRows2]
					-- 27 Zeilem
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y1,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y2,
					(SELECT 1 UNION SELECT 2 UNION SELECT 3) AS y3					
					-- [/VirtualRows2]
				-- [/Jahreszahlen]
			) AS years
		WHERE
			YEAR(makedate(years.myYear, daysOfYear.day_in_year)) = years.myYear
		-- [/Datumsliste]
	) AS dates
WHERE
	myYear=2014;
	
COMMIT;</code>


{{tag>MySQL Date}}
Yaslaw.Info

User Tools

Site Tools

Page Tools
