Arbeiten mit Zeitreihen

[LordExcalibur]

Hallo,

ich bin gerade dabei eine Datenbank zu entwerfen in der Zeitreihen von Verfügbarkeiten gehalten werden.

Es gibt mehrere Anlagen die in der Tabelle Assets gespeichert sind.
Jede Anlage hat bestimmte Verfügbarkeiten welche durch Leistungen (Zahl) ausgedrückt werden.

Die Tabelle Assets sieht wie folgt aus:

AssetID
AssetName
... weitere Asset Parameter


Die Tabelle Availability habe ich bisher so angelegt:

AvailabilityID
AssetID
AvailabilityDate
PowerAvailable


Das ganze sieht dann so aus, dass in der Tabelle Availability folgende Datensätze stehen

AvailabilityID | Asset ID | AvailabilityDate | PowerAvailable
1 | 1 | 13.4.2016 00:00 | 10
2 | 1 | 13.4.2016 4:00 | 5
3 | 1 | 13.4.2016 12:00 | 15

Das ganze wird so interpretiert, dass zwischen 0 und 4 Uhr 10MW Leistung zur verfügung stehen, zwischen 4 und 12 Uhr 5 MW und ab 12 Uhr 15MW (open end).

Nun möchte ich eine Abfrage erstellen, welche mir die Verfügbarkeitsdaten als Zeitreihe ausgibt. Dabei sollte optimalerweise die Rasterung flexibel wählbar sein.
Das Ergebnis sollte für ein 60min Raster etwa so aussehen:

AssetName | Date | PowerAvailable
Asset 1| 13.4.2016 00:00 | 10
Asset 1| 13.4.2016 01:00 | 10
Asset 1| 13.4.2016 02:00 | 10
Asset 1| 13.4.2016 03:00 | 10
Asset 1| 13.4.2016 04:00 | 5
Asset 1| 13.4.2016 05:00 | 5
......


Frage:
Wie lässt sich das realisieren? Ist die von mir vorgesehene Datenstruktur dafür geeignet oder gibt es für diesen Fall bessere/günstigere Datenstrukturen?

Viele Grüße
Sebastian

 

[akretschmer]

Wie lässt sich das realisieren? Ist die von mir vorgesehene Datenstruktur dafür geeignet oder gibt es für diesen Fall bessere/günstigere Datenstrukturen?

Ist Access verhandelbar? Meine Lösung mit PostgreSQL:

test=*# select * from availability ;
 id |  ts  | power
----+---------------------+-------
  1 | 2016-04-13 00:00:00 |  10
  1 | 2016-04-13 04:00:00 |  5
  1 | 2016-04-13 12:00:00 |  15
(3 rows)

Ich bastle mir einen VIEW:

test=*# create view stunden as select '2016-04-13'::timestamp + s * ('1 hour')::interval stunde from generate_series(0,23) s;
CREATE VIEW
test=*# select * from stunden ;
  stunde   
---------------------
 2016-04-13 00:00:00
 2016-04-13 01:00:00
 2016-04-13 02:00:00
 2016-04-13 03:00:00
 2016-04-13 04:00:00
 2016-04-13 05:00:00
 2016-04-13 06:00:00
 2016-04-13 07:00:00
 2016-04-13 08:00:00
 2016-04-13 09:00:00
 2016-04-13 10:00:00
 2016-04-13 11:00:00
 2016-04-13 12:00:00
 2016-04-13 13:00:00
 2016-04-13 14:00:00
 2016-04-13 15:00:00
 2016-04-13 16:00:00
 2016-04-13 17:00:00
 2016-04-13 18:00:00
 2016-04-13 19:00:00
 2016-04-13 20:00:00
 2016-04-13 21:00:00
 2016-04-13 22:00:00
 2016-04-13 23:00:00
(24 rows)

Und frage ab:

test=*# select s.stunde, av.* from stunden s left join (select id, tsrange(ts, lead(ts) over (partition by id order by ts)), power from availability) av on (s.stunde <@ av.tsrange);  stunde  | id |  tsrange  | power
---------------------+----+-----------------------------------------------+-------
 2016-04-13 00:00:00 |  1 | ["2016-04-13 00:00:00","2016-04-13 04:00:00") |  10
 2016-04-13 01:00:00 |  1 | ["2016-04-13 00:00:00","2016-04-13 04:00:00") |  10
 2016-04-13 02:00:00 |  1 | ["2016-04-13 00:00:00","2016-04-13 04:00:00") |  10
 2016-04-13 03:00:00 |  1 | ["2016-04-13 00:00:00","2016-04-13 04:00:00") |  10
 2016-04-13 04:00:00 |  1 | ["2016-04-13 04:00:00","2016-04-13 12:00:00") |  5
 2016-04-13 05:00:00 |  1 | ["2016-04-13 04:00:00","2016-04-13 12:00:00") |  5
 2016-04-13 06:00:00 |  1 | ["2016-04-13 04:00:00","2016-04-13 12:00:00") |  5
 2016-04-13 07:00:00 |  1 | ["2016-04-13 04:00:00","2016-04-13 12:00:00") |  5
 2016-04-13 08:00:00 |  1 | ["2016-04-13 04:00:00","2016-04-13 12:00:00") |  5
 2016-04-13 09:00:00 |  1 | ["2016-04-13 04:00:00","2016-04-13 12:00:00") |  5
 2016-04-13 10:00:00 |  1 | ["2016-04-13 04:00:00","2016-04-13 12:00:00") |  5
 2016-04-13 11:00:00 |  1 | ["2016-04-13 04:00:00","2016-04-13 12:00:00") |  5
 2016-04-13 12:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 13:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 14:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 15:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 16:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 17:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 18:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 19:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 20:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 21:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 22:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
 2016-04-13 23:00:00 |  1 | ["2016-04-13 12:00:00",)  |  15
(24 rows)

Oder, um es so zu haben wie Du es willst:

test=*# select s.stunde, av.power from stunden s left join (select id, tsrange(ts, lead(ts) over (partition by id order by ts)), power from availability) av on (s.stunde <@ av.tsrange);
  stunde  | power
---------------------+-------
 2016-04-13 00:00:00 |  10
 2016-04-13 01:00:00 |  10
 2016-04-13 02:00:00 |  10
 2016-04-13 03:00:00 |  10
 2016-04-13 04:00:00 |  5
 2016-04-13 05:00:00 |  5
 2016-04-13 06:00:00 |  5
 2016-04-13 07:00:00 |  5
 2016-04-13 08:00:00 |  5
 2016-04-13 09:00:00 |  5
 2016-04-13 10:00:00 |  5
 2016-04-13 11:00:00 |  5
 2016-04-13 12:00:00 |  15
 2016-04-13 13:00:00 |  15
 2016-04-13 14:00:00 |  15
 2016-04-13 15:00:00 |  15
 2016-04-13 16:00:00 |  15
 2016-04-13 17:00:00 |  15
 2016-04-13 18:00:00 |  15
 2016-04-13 19:00:00 |  15
 2016-04-13 20:00:00 |  15
 2016-04-13 21:00:00 |  15
 2016-04-13 22:00:00 |  15
 2016-04-13 23:00:00 |  15
(24 rows)

Oder ohne dem View:

test=*# select s.stunde, av.power from (select '2016-04-13'::timestamp + s * ('1 hour')::interval stunde from generate_series(0,23)s) s left join (select id, tsrange(ts, lead(ts) over (partition by id order by ts)), power from availability) av on (s.stunde <@ av.tsrange);
  stunde  | power
---------------------+-------
 2016-04-13 00:00:00 |  10
 2016-04-13 01:00:00 |  10
 2016-04-13 02:00:00 |  10
 2016-04-13 03:00:00 |  10
 2016-04-13 04:00:00 |  5
 2016-04-13 05:00:00 |  5
 2016-04-13 06:00:00 |  5
 2016-04-13 07:00:00 |  5
 2016-04-13 08:00:00 |  5
 2016-04-13 09:00:00 |  5
 2016-04-13 10:00:00 |  5
 2016-04-13 11:00:00 |  5
 2016-04-13 12:00:00 |  15
 2016-04-13 13:00:00 |  15
 2016-04-13 14:00:00 |  15
 2016-04-13 15:00:00 |  15
 2016-04-13 16:00:00 |  15
 2016-04-13 17:00:00 |  15
 2016-04-13 18:00:00 |  15
 2016-04-13 19:00:00 |  15
 2016-04-13 20:00:00 |  15
 2016-04-13 21:00:00 |  15
 2016-04-13 22:00:00 |  15
 2016-04-13 23:00:00 |  15
(24 rows)

test=*#

 

[ukulele]

Auch mit MSSQL würde man das ganz gut hinbekommen oder eben wie beschrieben mit PostgreSQL. Unter Access käme mir nur eine fixe Tabelle mit Uhrzeiten und eine mit Datumswerten in den Sinn die dann gejoint werden. Ist aber nicht so elegant.

 

[LordExcalibur]

Access ist nicht fix. Posgres geht auch. Ich habe deinen Ansatz gerade mal implementiert. Funktioniert super, obwohl ich die Abfrage nicht 100%ig verstehe. Kannst du das nochmal näher erläutern?

Wie würde ich es hinbekommen die Auswertung nur nach einem Asset zu machen, wenn mehrere Verfügbarkeiten in der Ausgangstabelle stehen? Wie lassen sich die power Werte als Summe in der Abfrage anzeigen?

 

[akretschmer]

welchen Teil hast denn nicht verstanden? generate_series() erzeugt eine Serie von Daten, hier um Zeitpunkte zu ermitteln. Daraus generiere ich dann wieder TIMESTAMPRANGES, mit Hilfe einer Window-Funktion lead(), die den Datensatz des nächsten Records liefert. Das ist alles nix komplexes, nur eine Anwendung diverser Techniken, die PG halt kann.