✔ SQL + Analytische Funktion

[Dextrose]

Hallo, ich bin SQL-Anfänger und steht vor folgender Aufgabe:

SQL Plus

Aus gegebener Tabelle möchte ich nun die Trafficwerte nach fortlaufender Stunde summieren.

Die vollen Stunde habe ich schon hinbekommen. (Also alle Trafficwerte von 1-2 Uhr, 2-3 Uhr usw.)

Nun sollte allerdings noch summiert werden von
00:15-01:15, 01:15-02:15 usw.
00:30-01:30, 01:30-02:30 usw.
00:45-01:45, 01:45:02:45 usw.

Später noch Gruppiert nach ID und Tag, aber das dürfte ich selbst hinbekommen. Achso, es können auch 3 Abfragen sein...

Ich habe gehört, so etwas wäre mti einer Analytischen Funktion zu lösen sein. Leider weiss ich nicht wie.

Vielen Dank für Hilfe

Gruß,
Dex


Ausschnitt der Tabelle

ID	Timestamp	Traffic
1	19.04.2006 00:00	100
1	19.04.2006 00:15	100
1	19.04.2006 00:30	100
1	19.04.2006 00:45	100
1	19.04.2006 01:00	100
1	19.04.2006 01:15	100
1	19.04.2006 01:30	100
1	19.04.2006 01:45	100
1	19.04.2006 02:00	100
1	19.04.2006 02:15	100
1	19.04.2006 02:30	100
1	19.04.2006 02:45	100
2	19.04.2006 14:45	100
2	19.04.2006 15:00	100

 

[Dextrose]

Hier mein Ansatz, der aber bestimmt komplett falsch ist. Also bitte davon nicht irritieren lassen...

SELECT ID, TIMESTAMP
SUM(TRAFFIC) OVER (PARTITION BY TIMESTAMP
                  ORDER BY TIMESTAMP
                  RANGE BETWEEN 1 PRECEDING
                  AND 3 FOLLOWING)"SUMME TRAFFIC"
FROM TABELLE
ORDER BY ID

 

[Exceptionfault]

Versuchs mal so:

SQL> SELECT ID, TS AS VON,
  2             TS + 1 / 24 BIS,
  3             SUM( TR ) OVER (
  4              PARTITION BY ID
  5              ORDER     BY TS
  6              ROWS      BETWEEN CURRENT ROW AND 3 FOLLOWING
  7  ) AS TRAFFIC
  8  FROM TRAFFIC;

             ID VON                 BIS                         TRAFFIC
--------------- ------------------- ------------------- ---------------
              1 19.04.2006 00:00:00 19.04.2006 01:00:00             400
              1 19.04.2006 00:15:00 19.04.2006 01:15:00             400
              1 19.04.2006 00:30:00 19.04.2006 01:30:00             400
              1 19.04.2006 00:45:00 19.04.2006 01:45:00             400
              1 19.04.2006 01:00:00 19.04.2006 02:00:00             400
              1 19.04.2006 01:15:00 19.04.2006 02:15:00             400
              1 19.04.2006 01:30:00 19.04.2006 02:30:00             400
              1 19.04.2006 01:45:00 19.04.2006 02:45:00             400
              1 19.04.2006 02:00:00 19.04.2006 03:00:00             400
              1 19.04.2006 02:15:00 19.04.2006 03:15:00             300
              1 19.04.2006 02:30:00 19.04.2006 03:30:00             200
              1 19.04.2006 02:45:00 19.04.2006 03:45:00             100
              2 19.04.2006 14:45:00 19.04.2006 15:45:00             200
              2 19.04.2006 15:00:00 19.04.2006 16:00:00             100

14 Zeilen ausgewõhlt.

 

[Jochen_Schneider]

Und ich dachte ich hätte SQL verstanden

Respekt für diese Lösung! BTW: Kann man diese Abfrage auch unter mySQL einsetzen?

 

[Exceptionfault]

Kann man diese Abfrage auch unter mySQL einsetzen?

Hmm, ich denke nicht dass mySQL das kann. Gehört zu den Analytischen Funktionen von Oracle. Soweit ist mySQL dann doch noch nicht ganz

Zur Erklärung:
Der folgende Teil sollte soweit klar sein. Das TS + 1/24 bedeutet einfach addiere zur Spalte TS eine volle Stunde. ( Der Wert 1 entspr. beim Date Datentyp 1 Tag )

SELECT ID, TS AS VON, TS + 1 / 24 BIS, SUM( TR )

Anstatt jetzt mit einem GROUP BY zu arbeiten bilden wir uns Partitionen, das ist im Grunde nichts anderes als Töpfe nach denen wir die Daten trennen. In diesem Fall nach ID. Die Sortierung innerhalb der Partition soll natürlich nach Tag/Uhrzeit erfolgen.

OVER ( PARTITION BY ID ORDER BY TS

Ziel ist es jetzt unser SUM( TR ) einzuschränken. Im Beispiel wollen wir die Summe des Traffics von allen Sätzen innerhalb einer Stunde, das sind immer 4 Sätze bei unseren Daten.

ROWS      BETWEEN CURRENT ROW AND 3 FOLLOWING

Problematisch wird es hier, falls wir mal mehr oder weniger Sätze pro Stunde kriegen, dann passt nämlich unser "Window" mit den 4 Sätzen nicht mehr. In dem Fall wäre es besser das Statement von "ROWS" auf "RANGE" umzuschreiben:

RANGE      BETWEEN CURRENT ROW AND  1/24-1/(1440*60) FOLLOWING

1/24-1/(1440*60) Sind genau 59 min und 59 Sek. D.h. es würden tatsächlich alle Sätze aufsummiert die innerhalb einer Stunde anfallen.

 

[Dextrose]

Super, vielen Dank!