Ich kriege es hin, für jeden Tag den Durchschnitt für PARAMETER_ID = 1 zu rechnen:
SELECT
avg("VALUE_NU") AS avg_temp_day,
date("REFERENCE_TS") as avg_date
FROM "DATA"."DATA_ORIG"
WHERE
date("REFERENCE_TS") BETWEEN '2016-04-01' AND '2016-04-30'
AND
"PARAMETER_ID" = '1'
GROUP BY
avg_date
ORDER BY
avg_date;
Mühe habe ich, wenn ich das tägliche Minimum von PARAMETER_ID = 2 und das tägliche Maximum für PARAMETER_ID = 3 zu rechnen versuche. Folgende Gründe:
PARAMETER_ID = 1 sind Momentanwerte --> Das VALUE_NU, welches dem REFERENCE_TS “2016-05-31 00:00:00” zugeordnet wird, gehört zum 31.05.2016. Für PARAMETER_ID = 1 geht der Tag von YYYY-MM-DD 00:00:00 bis YYYY-MM-DD 23:50:00.
PARAMETER_ID = 2 sind Minimalwerte über die vergangenen 10 Minuten --> Das VALUE_NU, welches dem REFERENCE_TS “2016-05-31 00:00:00” zugeordnet wird, gehört zum 30.05.2016. Für PARAMETER_ID = 2 geht der Tag von YYYY-MM-DD 00:10:00 bis YYYY-MM-DD+1 00:00:00
Analoges gilt für PARAMETER_ID = 3, nur dass es sich um Maximalwerte handelt
Folgende Frage:
Wie baue ich den obigen Select um, damit ich über einen definierten Zeitraum hinweg das tägliche Minimum (bzw. das tägliche Maximum) berechnen kann?
addiere im Where bzw. date(…) einen positiven oder negativen Offset von 1 Minute oder so zum jeweiligen Timestamp dazu, damit Du diesen in den richtigen Tagesslot verschiebst.
Danke für die rasche Antwort, die Lösung funktioniert!
Hier mein angepasstes Statement:
SELECT
avg("VALUE_NU") AS avg_temp_day,
date("REFERENCE_TS") as avg_date
FROM "DATA"."DATA_ORIG"
WHERE
date("REFERENCE_TS" + interval '10 minutes') BETWEEN '2016-04-01' AND '2016-05-06'
AND
"PARAMETER_ID" = '1'
GROUP BY
avg_date
ORDER BY
avg_date;
Ich überlege mir nun aber, ob ich diesem Kniff konzeptionell aus dem Weg gehen soll und periodenbezogene Parameter (also Mittelwert, Maximum, Minimum etc.) konsequent mit REFERENCE_TS = Beginn des Bezugszeitraums ablegen soll. Bei 10 min-Werten mag die praktische Relevanz akademisch sein. Bei Monats- oder Jahresmittelwerten macht das schon mehr aus und es scheint mir eleganter, wenn ich das Grundgerüst einer Abfrage nicht abhängig von der Granularität des Zeitraums (10 min, Tag, Monat, Jahr) variieren muss…
Dieser Wert von -112.7 müsste als Tagesminimum des 3. April auftauchen, da er sich auf die 10 Minuten vor Mitternacht bezieht. Hier das Statement, welches ich absetze:
SELECT
min("VALUE_NU") AS min_temp_day,
date("REFERENCE_TS") as min_date
FROM "DATA"."t_data_orig"
WHERE
(date("REFERENCE_TS") + interval '10 minutes') BETWEEN '2016-04-03' AND '2016-05-05'
AND
"PARAMETER_ID" = '2'
GROUP BY
min_date
ORDER BY
min_date;
–> Der Wert von -112.7 wird trotz 10minütigem Zeitversatz bei der WHERE-Anweisung dem 04. April zugewiesen, was eben nicht genau passieren sollte…
Ich würde gerne nachvollziehen, was passiert und was angepasst werden muss, damit der richtige Zeitraum nach dem Minimum abgegrast wird… Vielen Dank für Eure Hinweise!
Danke auch an Dich für den Hinweis! Damit und mit try&error bin ich nun darauf gestossen, dass folgender Select das Richtige liefern müsste:
SELECT
min("VALUE_NU") AS min_temp_day,
date("REFERENCE_TS" - interval '10 minutes') as min_date
FROM "DATA"."t_data_orig"
WHERE
date("REFERENCE_TS") BETWEEN '2016-04-02' AND '2016-05-05'
AND
"PARAMETER_ID" = '2'
GROUP BY
min_date
ORDER BY
min_date;
Mit
date( date("REFERENCE_TS") - interval '10 minutes') as min_date
würde ich ja von jedem reference_ts das Datum rauspicken, davon 10 Minuten abziehen und würde so beim Vortages um 23:50 landen. Davon wiederum wird das Datum herausgepickt. Somit würde jeder reference_ts und nicht nur der um 00:00 dem Vortag zusgeschlagen.
sorry, ja ich muss mich berichtigen. So dürfte es passen wie du es jetzt geschrieben hast.
Wenn ich dich richtig verstanden habe, möchtest du das min() und max() des Tages ermitteln. Wobei der Timestamp sich auf die vergangen 10 Minuten bezieht. Daher die Werte um 00:00 gehören noch zum Vortag.
Die Werte um 00:10 sind die ersten Werte des Tages, Die um 00:00 des Folgetages die letzten.
Die Gruppierung ist der ermittelte Tag ohne Timestamp (die Funktion date() trennt dabei das Datum von der Uhrzeit)!
Also muss der errechnet Wert vom 04.04.2016 0:00 der 03.04.2016 sein: