JOIN zweier Tabellen liefert komische Daten

pagemaster1979

Neuer Benutzer
Beiträge
4
Hallo,

ich muss zugeben, dass ich kein SQL Profi bin obwohl ich versuche alles selbst herauszufinden, komme ich nicht mehr weiter. Folgendes Problem:

Ich habe 2 Tabellen jeweils mit daten und einem timestamp wann die daten erfasst wurden.

Folgende Abfragen einzeln führen pro tabelle zu dem gewünschten ergebniss:

Gibt mir jeweils Datum, Wochentag, und die Anzahl der Zaehlungen aus:

SELECT date(timestamp) as Datum, CASE DATE_FORMAT(timestamp,'%w')
WHEN 0 THEN 'Sonntag'
WHEN 1 THEN 'Montag'
WHEN 2 THEN 'Dienstag'
WHEN 3 THEN 'Mittwoch'
WHEN 4 THEN 'Donnerstag'
WHEN 5 THEN 'Freitag'
WHEN 6 THEN 'Samstag'
ELSE 'fehler' END as Wochentag,
sum(zaehlerstand)/100 as m³
FROM gaszaehler
GROUP BY Datum

Gleiches für für temperatur:
SELECT date(timestamp) as Datum, CASE DATE_FORMAT(timestamp,'%w')
WHEN 0 THEN 'Sonntag'
WHEN 1 THEN 'Montag'
WHEN 2 THEN 'Dienstag'
WHEN 3 THEN 'Mittwoch'
WHEN 4 THEN 'Donnerstag'
WHEN 5 THEN 'Freitag'
WHEN 6 THEN 'Samstag'
ELSE 'fehler' END as Wochentag,
ROUND( avg(temperatur),2) as 'AVG Temp',
ROUND( max(temperatur),2) as 'MAX Temp'
FROM temperatur
where luftfeuchtigkeit < '100'
GROUP BY Datum

Nun ist das Ziel aber die temperatuten des jeweiligen Tages und den Zaehlerstand zusammenzuführen.

Wenn ich das über einen JOIN mache
SELECT date(gaszaehler.timestamp) as Datum, CASE DATE_FORMAT(gaszaehler.timestamp,'%w')
WHEN 0 THEN 'Sonntag'
WHEN 1 THEN 'Montag'
WHEN 2 THEN 'Dienstag'
WHEN 3 THEN 'Mittwoch'
WHEN 4 THEN 'Donnerstag'
WHEN 5 THEN 'Freitag'
WHEN 6 THEN 'Samstag'
ELSE 'fehler' END as Wochentag,
sum(zaehlerstand)/100 as m³,
ROUND( avg(temperatur),2) as 'AVG Temp',
ROUND( max(temperatur),2) as 'MAX Temp'
FROM gaszaehler
JOIN temperatur
ON date(gaszaehler.timestamp) = date(temperatur.timestamp)
GROUP BY Datum'

Stimmen temperaturen und speziell auch der zaehlerstand nicht mehr. Der Zaehlerstand ist deutlich zu hoch, da ich vermute, dass jedem timetamp des datums alle alle anderen passenden timestamps zugeordnet werden.

Ist relativ schwer zu erklären.

Ich habe auch Screens der Abfragen inkl. Ergebniss dran.

Die Geschwindigkeit der Abfrage wäre nicht super relevant. Eig. möchte ich "nur" das Ergebnis der ersten beiden Abfragen (gaszaehler und temperatur) zusammenführen.

Hat jemand eine Idee?

Vielen Dank schon mal.

VG Mario
 

Anhänge

  • gaszaehler.PNG
    gaszaehler.PNG
    14 KB · Aufrufe: 6
  • temperatur.PNG
    temperatur.PNG
    18,5 KB · Aufrufe: 3
  • gas_temp_zusammen.PNG
    gas_temp_zusammen.PNG
    22,4 KB · Aufrufe: 6
Werbung:
Du summierst Zählerstände? Wie viele Zählerstände und Messungen hast Du jeweils pro Tag?

Zeig mal Deine Tabellen mit einigen Datensätzen, und was Du exakt erwartest.
 
Anbei zwei Auschnitte der Rohdatentabellen.

Es sind nicht wirklich zaehlerstaender, sondern immer wenn der takt erfasst wird (alle 0,01m³)
schreibe ich einen Eintrag in eine Tabelle.

Die temperatur wird alle 5min aufgezeichnet, sind also 288 Einträge pro Tag.
Und beim gaszaehler schwankt es etwas aber ich sage mal ca. 400 pro Tag.

Ich hätte gerne etwas in der Art:

Datum Wochentag m³ AVG Temp MAX Temp


Aber mit korrekten werten, nicht so wie bei meinen JOIN. Anhand der zwei einzel abfragen wäre das:



Datum Wochentag m³ AVG Temp MAX Temp
2017 10 28 Samstag 3,0000 10,38 11,40
2017 10 29 Sonntag 1,8600 8,86 11,10
 

Anhänge

  • temp einzeln.PNG
    temp einzeln.PNG
    21,5 KB · Aufrufe: 3
  • gas einzeln.PNG
    gas einzeln.PNG
    16,2 KB · Aufrufe: 3
Punkt 1 ist, daß MySQL logische Fehler beim GROUP BY nicht erkennt. (es müssen alle Spalten des Resultates entweder aggregiert oder gruppiert sein). Dies ist bei Dir nicht der Fall, obwohl es in diesem speziellen Fall, wo die Wochentagsspalte eine Funktion der Datumsspalte ist, wohl egal wäre.

Code:
test=*# \d t1
  Tabelle »public.t1«
 Spalte |  Typ  | Attribute
--------+-----------------------------+-----------
 datum  | timestamp without time zone |
 val  | integer  |

test=*# \d t2
  Tabelle »public.t2«
 Spalte |  Typ  | Attribute
--------+-----------------------------+-----------
 datum  | timestamp without time zone |
 val  | integer  |

test=*# select t1.datum, extract(dow from t1.datum), count(t1.*), sum(t2.val) from t1 join t2 on t1.datum::date=t2.datum::date group by t1.datum;
 datum | date_part | count | sum
-------+-----------+-------+-----
(0 Zeilen)

test=*# select t1.datum, extract(dow from t2.datum), count(t1.*), sum(t2.val) from t1 join t2 on t1.datum::date=t2.datum::date group by t1.datum;
FEHLER:  Spalte »t2.datum« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
ZEILE 1: select t1.datum, extract(dow from t2.datum), count(t1.*), su...
  ^
test=*# select t2.datum, extract(dow from t2.datum), count(t1.*), sum(t2.val) from t1 join t2 on t1.datum::date=t2.datum::date group by t1.datum;
FEHLER:  Spalte »t2.datum« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
ZEILE 1: select t2.datum, extract(dow from t2.datum), count(t1.*), su...
  ^
test=*# select t2.datum, extract(dow from t2.datum), count(t2.*), sum(t2.val) from t1 join t2 on t1.datum::date=t2.datum::date group by t1.datum;
FEHLER:  Spalte »t2.datum« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
ZEILE 1: select t2.datum, extract(dow from t2.datum), count(t2.*), su...
  ^
test=*# select t2.datum, extract(dow from t2.datum), count(t2.*), sum(t2.val) from t1 join t2 on t1.datum::date=t2.datum::date group by t2.datum;
 datum | date_part | count | sum
-------+-----------+-------+-----
(0 Zeilen)
[/code

Ich habe hier immer expliziet die Quelltabelle angegeben, das, passiert, wenn ich dies nicht mache:

[code]
test=*# select t2.datum, extract(dow from t2.datum), count(t2.*), sum(t2.val) from t1 join t2 on t1.datum::date=t2.datum::date group by datum;
FEHLER:  Spaltenverweis »datum« ist nicht eindeutig
ZEILE 1: ... t1 join t2 on t1.datum::date=t2.datum::date group by datum;


Also, ich sehe mindestens 3 Möglichkeiten für Dich:

  • spezifiziere überall exakt die Tabelle
  • lasse die Berechnung des Wochentages weg
  • wirf MySQL in die Tonne und nehme was richtiges


Andreas
 
Danke für die Rückmeldung. Hab ich so versucht, klappt aber leider nicht:


SELECT date(gaszaehler.timestamp) as Datum,
sum(gaszaehler.zaehlerstand)/100 as m³,
ROUND( avg(temperatur.temperatur),2) as 'AVG Temp',
ROUND( max(temperatur.temperatur),2) as 'MAX Temp'
FROM gaszaehler
JOIN temperatur
ON date(gaszaehler.timestamp) = date(temperatur.timestamp)
Group by Datum;

Ergebniss ist identisch, nur ohne Wochentag. Oder ich verstehe ich immer noch nicht (ist vermutlich so) :(
 
Ja, Baume, Wald, etc.

Mit

Code:
test=*# select * from t1;
  datum  | val
---------------------+-----
 2017-10-29 00:00:00 |  1
 2017-10-29 00:00:00 |  1
 2017-10-29 00:00:00 |  1
 2017-10-29 00:00:00 |  1
 2017-10-30 00:00:00 |  1
 2017-10-30 00:00:00 |  1
(6 Zeilen)

test=*# select * from t2;
  datum  | val
---------------------+-----
 2017-10-29 00:00:00 |  10
 2017-10-29 00:00:00 |  12
 2017-10-29 00:00:00 |  15
 2017-10-30 00:00:00 |  20
(4 Zeilen)

bekommst Du bei Deiner Abfrage mehr als gewollt ist::

Code:
test=*# select * from t1 inner join t2 on t1.datum::date=t2.datum::date ;
  datum  | val |  datum  | val
---------------------+-----+---------------------+-----
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  10
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  12
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  15
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  10
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  12
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  15
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  10
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  12
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  15
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  10
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  12
 2017-10-29 00:00:00 |  1 | 2017-10-29 00:00:00 |  15
 2017-10-30 00:00:00 |  1 | 2017-10-30 00:00:00 |  20
 2017-10-30 00:00:00 |  1 | 2017-10-30 00:00:00 |  20
(14 Zeilen)

Was Du willst ist vermutlich das:

Code:
test=*# select
  x.datum
  , x.count as einheiten
  , y.min as min
  , y.max as mx
  , y.avg as avg
from (
  select
  datum
  , count(1)
  from t1 group by datum
  ) x
inner join (
  select
  datum
  , min(val)
  , max(val)
  , avg(val)
  from t2 group by datum
  ) y
on x.datum=y.datum
order by x.datum;
  datum  | einheiten | min | mx |  avg   
---------------------+-----------+-----+----+---------------------
 2017-10-29 00:00:00 |  4 |  10 | 15 | 12.3333333333333333
 2017-10-30 00:00:00 |  2 |  20 | 20 | 20.0000000000000000
(2 Zeilen)

test=*#
 
Werbung:
Danke für deine Hilfe. Habs leider so nicht hinbekommen. Warum kann ich nicht wirklich sagen. Habs dann über eine temporäre Tabelle gelöst. Sicher nicht der beste Weg aber die Daten passen. Vielleicht hilfts ja jemandem:


CREATE TEMPORARY TABLE temptmp ENGINE=INNODB
SELECT date(datum) as Datum2,
#CASE DATE_FORMAT(datum,'%w')
# WHEN 0 THEN 'Sonntag'
# WHEN 1 THEN 'Montag'
# WHEN 2 THEN 'Dienstag'
# WHEN 3 THEN 'Mittwoch'
# WHEN 4 THEN 'Donnerstag'
# WHEN 5 THEN 'Freitag'
# WHEN 6 THEN 'Samstag'
# ELSE 'fehler' END as Wochentag,
ROUND( avg(temp),2) as 'AVGTemp',
ROUND( max(temp),2) as 'MAXTemp',
ROUND( min(temp),2) as 'MINTemp'
FROM temperatur
where luftfeuchtigkeit < '100'
GROUP BY Datum2;

SELECT date(datum) as Datum2,
CASE DATE_FORMAT(datum,'%w')
WHEN 0 THEN 'Sonntag'
WHEN 1 THEN 'Montag'
WHEN 2 THEN 'Dienstag'
WHEN 3 THEN 'Mittwoch'
WHEN 4 THEN 'Donnerstag'
WHEN 5 THEN 'Freitag'
WHEN 6 THEN 'Samstag'
ELSE 'fehler' END as Wochentag,
ROUND(sum(zaehlerstand)/100,2) as m³,
MINTemp, AVGTemp, MAXTemp
FROM gaszaehler
JOIN temptmp ON temptmp.Datum2 = date(gaszaehler.datum)
GROUP BY temptmp.Datum2;
DROP TABLE `temptmp`;
 
Zurück
Oben