View: Daten aggregieren

[RalphM2]

Servus Zusammen!

Ich habe eine einfach Tabelle, mit leider "zu vielen" Daten.
Die Tabelle besteht aus den Spalten Zeitstempel und min. einem Messwert (MW1). Es wird praktisch alle paar Sekunden ein neuer Datensatz in diese Tabelle geschrieben.

Ich würde jetzt gerne eine View erstellen, welche die Daten aggregiert. Also aus bestimmten Zeitintervallen (z.B. 5 Min.) nur den Max-Wert liefert.

Eine einfach Abfrage wäre dies hier:
select max(mw1) as MW1 from messwert
where
zeitstempel >= '03.04.2008 01:00'
AND
zeitstempel < '03.04.2008 01:05'

Wie bekomme ich das hin, dass die View mir dies automatisch für "alle 5 Min." liefert?

Die verwendete DB ist Oracle 10gR2.

[Falk Prüfer]

Hallo,

für einen "Minutentakt" könnte man das mit einer normalen Gruppierung machen.
[highlight=sql]
select round(zeitstempel, 'MI') intervall, max(mw1) as MW1
from messwert
group by round(zeitstempel, 'MI')
[/highlight]

Für deinen 5 Minutentakt muß der Zeitstempel halt passend umgerechnet werden. Z.B. mit TO_CHAR auseinander nehmen und auf 5 Min. gerundet wieder zusammenbauen.

Gruß Falk

[JenneB]

Hi,

ich habe mal ein wenig rum gespielt und eine etwas umständliche Lösung gefunden. Das ganze geht mit Sicherheit auch einfacher und performanter. Das ist aber denke ich erstmal egal.

Ich habe mir als erstes mal eine Tabelle gebaut, die in etwa das beinhaltet was du hast: Eine Zahl und ein Datum. Ich habe 10 Minuten lang, jede Minute das Datum und den jeweiligen Zähler in die Tabelle geschrieben:

dummy01 ddummy01
0	04.07.2008 07:30:35
1	04.07.2008 07:31:35
2	04.07.2008 07:32:35
3	04.07.2008 07:33:35
4	04.07.2008 07:34:35
5	04.07.2008 07:35:35
6	04.07.2008 07:36:35
7	04.07.2008 07:37:35
8	04.07.2008 07:38:35
9	04.07.2008 07:39:35
10	04.07.2008 07:40:35

Dann habe ich mir das Datum ohne Sekunden und der 10er Minuten Stelle ausgeben lassen:

substr(to_char(ddummy01,'YYYY:MM:DD:HH24:MI'),1,15)

Ergebnis

2008:07:04:07:3
2008:07:04:07:4

Die ist dann der erste Punkt nach dem du gruppieren musst.
Jetzt willst du aber alle 5 nicht alle 10 Minuten geliefert bekommen. Jetzt haben wir noch ein kleines Problem. Du musst dir die 10er Stelle der Minute ausgeben lassen und prüfen ob sie zwischen 0-4 oder 5-9 liegt. Dafür setzt du eine 1 oder eine 2:

decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),0,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),1,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),2,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),3,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),4,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),5,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),6,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),7,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),8,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),9,2))))))))))

Hiernach musst du ebenfalls gruppieren.

Der gesamte Select würde dann etwa so aussehen:

select 
substr(to_char(ddummy01,'MI'),1,1) ||
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),0,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),1,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),2,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),3,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),4,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),5,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),6,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),7,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),8,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),9,2)))))))))),
max(dummy01)
from jb_dummy
group by 
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),0,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),1,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),2,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),3,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),4,1,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),5,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),6,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),7,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),8,2,
decode(to_number(substr(to_char(ddummy01,'mi'),2,1)),9,2)))))))))),
substr(to_char(ddummy01,'YYYY:MM:DD:HH24:MI'),1,15),
substr(to_char(ddummy01,'MI'),1,1)

und das Ergebnis so:

31	4
32	9
41	10

So erhältst du den MAX-Eintrag aus jeweils 5 Minuten!

Gruß

Jens

[RalphM2]

Lösung

Vielen Dank für Eure Antworten.

Ich bin jetzt noch auf eine weitere Lösung gestossen, mit einer Funktion (numtodsinterval), die ich irgendwie verdrängt hatte.

select trunc(zeitstempel,'HH24') + numtodsinterval(trunc(to_char(zeitstempel,'MI') / 5) * 5,'MINUTE'),
max(MW1)
from messwert
group by trunc(zeitstempel,'HH24') + numtodsinterval(trunc(to_char(zeitstempel,'MI') / 5) * 5,'MINUTE');

[ralfb]

Hallo,

wie Falk schon sagte, kann man so etwas machen:

ALTER session SET nls_date_format='dd.mm.yyyy hh24:mi';
WITH daten AS
(SELECT SYSDATE+LEVEL/24/60 zeit, LEVEL wert FROM dual
 CONNECT BY LEVEL < 16)
SELECT zeit
      ,wert
FROM daten
;

WITH daten AS
(SELECT SYSDATE+LEVEL/24/60 zeit, LEVEL wert FROM dual
 CONNECT BY LEVEL < 16)
SELECT TO_DATE(TO_CHAR(zeit,'dd.mm.yyyy hh24')
||TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(zeit,'mi'))/5)*5,'99'),'dd.mm.yyyy hh24:mi') x
      ,sum(wert) summe
FROM daten
GROUP BY TO_DATE(TO_CHAR(zeit,'dd.mm.yyyy hh24')
||TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(zeit,'mi'))/5)*5,'99'),'dd.mm.yyyy hh24:mi')
ORDER BY 1
;

Ein Beispielergebnis wäre dann:

ZEIT                   WERT
04.07.2008 10:56          1
04.07.2008 10:57          2
04.07.2008 10:58          3
04.07.2008 10:59          4
04.07.2008 11:00          5
04.07.2008 11:01          6
04.07.2008 11:02          7
04.07.2008 11:03          8
04.07.2008 11:04          9
04.07.2008 11:05         10
04.07.2008 11:06         11
04.07.2008 11:07         12
04.07.2008 11:08         13
04.07.2008 11:09         14
04.07.2008 11:10         15

15 Zeilen ausgewählt.

X                     SUMME
04.07.2008 10:55         10
04.07.2008 11:00         35
04.07.2008 11:05         60
04.07.2008 11:10         15

Ich habe hier mal die Summe genommen, weil man da besser sieht, was für Sätze angefasst werden. Für die eigentliche Aufgabenstellung muss da natürlich ein Max statt des Sum stehen.

Gruß

Ralf