Mittelwert kriege ich hin - aber min() und max()?

Hallo zusammen

Wieder mal eine Anfängerfrage… Ich habe folgende Tabelle von Messwerten (VALUE_NU), welche alle 10 Minuten anfallen (REFERENCE_TS):

 "REFERENCE_TS";"INSTALLATION_ID";"PARAMETER_ID";"MEAS_CAT_ID";"LOAD_TS";"VALUE_NU";"TIME_GRANULARITY_TX"
"2016-04-02 10:10:00";1;1;1;"2016-05-15 14:19:51";14.42;"T"
"2016-04-02 10:20:00";1;1;1;"2016-05-15 14:19:51";14.14;"T"
"2016-04-02 10:30:00";1;1;1;"2016-05-15 14:19:51";14.24;"T"
"2016-04-02 10:40:00";1;1;1;"2016-05-15 14:19:51";13.98;"T"
"2016-04-02 10:50:00";1;1;1;"2016-05-15 14:19:51";14.42;"T"
"2016-04-02 11:00:00";1;1;1;"2016-05-15 14:19:51";14.54;"T"
....

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?

Danke für jeden Hinweis im Voraus!

Humpe

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…

Beste Grüsse, Humpe

yw

Hallo zusammen,

Ich muss diesen Thread nochmals ausgraben, da mein Problem offensichtlich doch noch nicht ganz gelöst ist.

Zur Veranschaulichung nochmals einige Testdaten:

 "REFERENCE_TS";"INSTALLATION_ID";"PARAMETER_ID";"MEAS_CAT_ID";"LOAD_TS";"VALUE_NU";"TIME_GRANULARITY_TX"
"2016-04-03 22:40:00";1;2;1;"2016-05-31 21:35:51";12.9;"T"
"2016-04-03 22:50:00";1;2;1;"2016-05-31 21:35:51";13;"T"
"2016-04-03 23:00:00";1;2;1;"2016-05-31 21:35:51";12.9;"T"
"2016-04-03 23:10:00";1;2;1;"2016-05-31 21:35:51";12.9;"T"
"2016-04-03 23:20:00";1;2;1;"2016-05-31 21:35:51";12.8;"T"
"2016-04-03 23:30:00";1;2;1;"2016-05-31 21:35:51";13.2;"T"
"2016-04-03 23:40:00";1;2;1;"2016-05-31 21:35:51";12.7;"T"
"2016-04-03 23:50:00";1;2;1;"2016-05-31 21:35:51";12.8;"T"
"2016-04-04 00:00:00";1;2;1;"2016-05-31 21:35:51";-112.7;"T"
"2016-04-04 00:10:00";1;2;1;"2016-05-31 21:35:51";12.4;"T"
"2016-04-04 00:20:00";1;2;1;"2016-05-31 21:35:51";12.2;"T"
"2016-04-04 00:30:00";1;2;1;"2016-05-31 21:35:51";12.3;"T"
"2016-04-04 00:40:00";1;2;1;"2016-05-31 21:35:51";12.4;"T"
"2016-04-04 00:50:00";1;2;1;"2016-05-31 21:35:51";12.5;"T"
"2016-04-04 01:00:00";1;2;1;"2016-05-31 21:35:51";12.5;"T"

Bei den 10 Minuten-Daten handelt es sich um das Minimum der vorangehenden 10 Minuten. Aus diesen Werten möchte ich das Tagesminimum ermitteln.

Für die Kontrolle des Statements, welches ich aufgrund des Hinweises von akretschmer gebastelt habe, wurde ein Wert auffällig präpariert:

“2016-04-04 00:00:00”;1;2;1;“2016-05-31 21:35:51”;-112.7;“T”

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;

Hier mein Resultat:

"min_temp_day";"min_date"
12.6;"2016-04-03"
-112.7;"2016-04-04"
4.6;"2016-04-05"
-0.2;"2016-04-06"

–> 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!

Beste Grüsse, Humpe

Hallo,

für das grouping möchtest du ja zuerst die 10 Minuten abziehen und dann davon den Tag haben. Also:

select date( date('2016-04-04 00:00:00') - interval '10 minutes');
    date    
------------
 2016-04-03
(1 row)

Bedeute statt:

date("REFERENCE_TS") as min_date

dann das:

date( date("REFERENCE_TS") - interval '10 minutes') as min_date

Gruß

Hallo fraster,

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.

Stimmen meine Überlegungen?

Viele Grüsse, Humpe

Hallo Humpe,

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:

select date( timestamp '2016-04-04 00:00:00' - interval '10 minutes') min_date;
  min_date  
------------
 2016-04-03
(1 row)

Und vom 04.04.2016 0:10 der 04.04.2016 sein:

select date( timestamp '2016-04-04 00:10:00' - interval '10 minutes') min_date;
  min_date  
------------
 2016-04-04
(1 row)

Und so hast du es auch in deinem Select.

Gruß frastr