Tabelle(n daten) in subquery direkt erzeugen
am 07.07.2007 21:03:38 von Frank Arthur
Ich wollte eine Abfrage machen, die mir Werte gruppiert nach
Wochentagen ausgibt.
SELECT COUNT(*) AS DbCount
, DAYOFWEEK(`DbDate`) AS DbDayOfWeek
FROM t1
GROUP BY DAYOFWEEK(`DbDate`)
(Der Query ist nur so aus dem Kopf dahingeschrieben.)
Das Problem ist, dass es durchaus möglich ist, dass nicht für alle Tage
der Woche (Mo - So) Daten in der Datenbank sind. Ich möchte jetzt aber,
dass ich auch Daten für solche Wochentage bekommen, von denen keine
Daten in der Datanbank sind, dann aber eben mit der Anzahl 0 (null).
Ich könnte jetzt eine Temporäre Tabelle erstellen und dort die Werte 1
bis 7 eintragen. Dann mache ich einen Join auf diese Tabelle und habe,
was ich will.
CREATE TEMPORARY TABLE `tmp` (
`DbDayOfWeek` INT NOT NULL
);
INSERT INTO `tmp` VALUES (1, 2, 3, 4, 5, 6, 7);
SELECT COUNT(t1*) AS DbCount
, DAYOFWEEK(t1.`DbDate`) AS DbDayOfWeek
FROM tmp
LEFT JOIN t1 ON tmp.DbDayOfWeek = DAYOFWEEK(t1.`DbDate`)
GROUP BY tmp.DbDayOfWeek
Jetzt meine Frage:
Dieses Erstellen der Temporären Tabelle ist mir doch etwas umständlich,
gibt es nicht die Möglichkeit die Daten, die ja doch sehr einfach sind,
on the fly in einem subquery zu erstellen. Etwa so:
SELECT DbDayOfWeek FROM (1, 2, 3, 4, 5, 6, 7)
Dann wäre mein Query wesentlich eleganter:
SELECT COUNT(t1*) AS DbCount
, DAYOFWEEK(ti.`DbDate`) AS DbDayOfWeek
FROM (SELECT DbDayOfWeek FROM (1, 2, 3, 4, 5, 6, 7)) AS tmp
LEFT JOIN t1 ON tmp.DbDayOfWeek = DAYOFWEEK(t1.`DbDate`)
GROUP BY tmp.DbDayOfWeek
Re: Tabelle(n daten) in subquery direkt erzeugen
am 07.07.2007 22:47:22 von Frank Arthur
> begin Frank Arthur wrote:
>> Das Problem ist, dass es durchaus möglich ist, dass nicht für alle Tage
>> der Woche (Mo - So) Daten in der Datenbank sind. Ich möchte jetzt aber,
>> dass ich auch Daten für solche Wochentage bekommen, von denen keine
>> Daten in der Datanbank sind, dann aber eben mit der Anzahl 0 (null).
>
>> Ich könnte jetzt eine Temporäre Tabelle erstellen und dort die Werte 1
>> bis 7 eintragen. Dann mache ich einen Join auf diese Tabelle und habe,
>> was ich will.
>
>
>> Jetzt meine Frage:
>> Dieses Erstellen der Temporären Tabelle ist mir doch etwas umständlich,
>> gibt es nicht die Möglichkeit die Daten, die ja doch sehr einfach sind,
>> on the fly in einem subquery zu erstellen. Etwa so:
Andreas Kretschmer said:
>
> Andere Systeme haben für sowas eine Funktion generate_series(), die man
> dafür so verwenden kann:
>
> test=*# select * from werte;
>
> test=*# select s.datum, sum(b.val)
> from (select (current_date-(s||'days')::interval)::date as datum from
> generate_series(0,10)s) s
> left join werte b
> using (datum)
> group by s.datum
> order by 1;
>
> Man kann das sogar in MySQL nachbasteln, XL zeigte dies mal hier.
> Obiges noch mit coalesce zur Ausgabe von 0 statt NULL zu erweitern
> überlasse ich zur Übung.
Das hilft mir nicht weiter. Hast du eine konkretere Implementierung für MySQL?
Nachtrag
Folgendermaßen habe ich das mit temporärer Tabelle implementiert:
CREATE TEMPORARY TABLE `tmp` ( `IsoDayOfWeek` INT NOT NULL );
INSERT INTO `tmp` VALUES (1), (2), (3), (4), (5), (6), (7);
SELECT COUNT(`TrainingData`.`Date`) AS `Count`
, `tmp`.`IsoDayOfWeek`
FROM `tmp`
LEFT JOIN `TrainingData`
ON `tmp`.`IsoDayOfWeek` = ((DAYOFWEEK(`TrainingData`.`Date`) + 5)
% 7) + 1
GROUP BY `tmp`.`IsoDayOfWeek`
ORDER BY `tmp`.`IsoDayOfWeek`
Das Ergebnis ist dann
Count | IsoDayOfWeek
------+-------------
6 | 1
3 | 2
11 | 3
4 | 4
0 | 5
9 | 6
5 | 7
Re: Tabelle(n daten) in subquery direkt erzeugen
am 09.07.2007 15:23:44 von Daniel Fischer
Frank Arthur!
> Ich könnte jetzt eine Temporäre Tabelle erstellen und dort die Werte 1
> bis 7 eintragen. Dann mache ich einen Join auf diese Tabelle und habe,
> was ich will.
Auf die Schnelle fällt mir auch nur das hier ein :)
SELECT * FROM
(SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) days,
...
WHERE n=...
Gruß
Daniel