Datenbank - Zeilenvergleich innerhalb einer Tabelle

[Matayus]

Moin moin,

ich hab da ein Problem und hoffe Ihr könnt mir helfen.
Ich habe eine Tabelle in der die einzelnen Spalten verglichen werden sollen.

Ich habe Handelsware und ziehe mir alle 15 Minuten die aktuellen Preise von einer bestimmten Website per Schnittstelle in meine Datenbank. Beim Import habe ich zusätzlich zur Zeit auch die Spalte Datensatz angefügt. Ich glaube damit ist es eindeutiger zu vergleichen, da ich bei der Zeit manchmal Abweichungen habe. Und so habe ich feste Werte für jeden neuen Eintrag in meiner Tabelle.

Nun möchte ich die Preise untereinander vergleichen.
Habe dazu ein Anhang mit Bild angefügt zur besseren Verständnis.
Der letzte Datensatz dient als Hauptpreis (im Bild "Datensatz 9" - 16 Uhr mit 0,0440).
Diese Spalte möchte ich mit dem vorherigen Datensätzen vergleichen
(im Bild "Datensatz 8" - 15:45 Uhr und "Datensatz 7 - 15:30 usw).

Es reichen die letzten 10 Datensätze die miteinander vergleichen werden, dabei ist der letzte Datensatz quasi der Hauptsatz (also 100%).




Hintergrund: Ich möchte wissen, wann der Spread > 10 % ist.
Und falls ja, soll das Ergebnis mir mitteilen, dass in meinem Beispiel, der Datensatz 5 (was von der Zeit ca. 2 Stunden waren) einen Spread von 15,45 % hatte.

Ich hoffe Ihr versteht was ich meine.
Vielen Dank schon mal im Voraus

Beste Grüße, Mata

 

[akretschmer]

also die 2 Spread-Spalten sind nicht in der Tabelle, die willst Du aber berechnen, oder?

test=*# select * from matayus ;
 id |        zeit         | preis  
----+---------------------+--------
  1 | 2019-09-24 14:00:00 | 0.0356
  2 | 2019-09-24 14:15:00 | 0.0360
  3 | 2019-09-24 14:30:00 | 0.0365
  4 | 2019-09-24 14:45:00 | 0.0370
  5 | 2019-09-24 15:00:00 | 0.0372
  6 | 2019-09-24 15:15:00 | 0.0402
  7 | 2019-09-24 15:30:00 | 0.0420
  8 | 2019-09-24 15:45:00 | 0.0432
  9 | 2019-09-24 16:00:00 | 0.0440
(9 rows)

test=*# with tmp as (select *, (1 - (preis / first_value(preis) over (order by zeit desc)))*100 as spread2  from matayus order by id) select *, case when spread2 > 10 then 'größer 10' else 'kleiner 10' end from tmp;
 id |        zeit         | preis  |         spread2         |    case   
----+---------------------+--------+-------------------------+------------
  1 | 2019-09-24 14:00:00 | 0.0356 | 19.09090909090909090900 | größer 10
  2 | 2019-09-24 14:15:00 | 0.0360 | 18.18181818181818181800 | größer 10
  3 | 2019-09-24 14:30:00 | 0.0365 | 17.04545454545454545500 | größer 10
  4 | 2019-09-24 14:45:00 | 0.0370 | 15.90909090909090909100 | größer 10
  5 | 2019-09-24 15:00:00 | 0.0372 | 15.45454545454545454500 | größer 10
  6 | 2019-09-24 15:15:00 | 0.0402 |  8.63636363636363636400 | kleiner 10
  7 | 2019-09-24 15:30:00 | 0.0420 |  4.54545454545454545500 | kleiner 10
  8 | 2019-09-24 15:45:00 | 0.0432 |  1.81818181818181818200 | kleiner 10
  9 | 2019-09-24 16:00:00 | 0.0440 |  0.00000000000000000000 | kleiner 10
(9 rows)

test=*#

Also so?

 

[Matayus]

Ja genau so ! Perfekt das ging ja mega schnell. Danke DIR !!

Was ist den der Befehl um die einzelnen Zeilen miteinander zu gleichen ?

Wenn ich den Case erweitern möchte um >15 und > 20 , muss ich dann den Grenzbereich vom ersten case auf 10-14 machen ?
Also mit einem between Befehl ? Quasi:
case when spread2 between 10 and 14 then 'größer 10' else case when spread2 between 15 and 19 then 'größer 15' else end

 

[akretschmer]

Was ist den der Befehl um die einzelnen Zeilen miteinander zu gleichen ?

das erfolgt über Window-Funktionen, hier first_value(...) over (...). Eine Dokumentation darüber findest Du hier: PostgreSQL: Documentation: 12: 3.5. Window Functions

 

[Matayus]

Merciiiii, das hilft mir enorm !!!

Bezüglich des case und dem Grenzbreich müsste doch das hier klappen oder ?

case
when spread2 between 10 and 14 then 'größer 10'
when spread2 between 14 and 19 then 'größer als 15'
when spread2 > 20 then 'größer als 20'
else 'kleiner 10'
end
from tmp

 

[akretschmer]

joa, sollte klappen.

 

[Matayus]

Danke Top.

Eine letzte Sache:
Möglich den Datenbereich einzuschränken ? Ich möchte zB. das nur die letzten 10 oder 15 Datensätze der Tabelle miteinander verglichen/herangezogen werden.
Alles was drüber liegt, wäre zu alt.

Wäre das:
(select Top 15 *, (1 - (preis / first_value(preis)……..

??

 

[akretschmer]

na sicher, mit Window-Funktionen geht das.

 

[Matayus]

Super vielen Dank für Deine Hilfe !

 

[akretschmer]

hier mal ne ganz kleine Demo. Via sum() und der Definition eines Windows von 1 Zeiler vorher bis 1 Zeile nachher summiere ich die Zahlen, die generate_series() mir vorher geliefert hat.

test=*# with zahlen as (select * from generate_series(1,20)s) select *, sum(s) over (rows between 1 preceding and 1 following) from zahlen;
 s  | sum
----+-----
  1 |   3
  2 |   6
  3 |   9
  4 |  12
  5 |  15
  6 |  18
  7 |  21
  8 |  24
  9 |  27
 10 |  30
 11 |  33
 12 |  36
 13 |  39
 14 |  42
 15 |  45
 16 |  48
 17 |  51
 18 |  54
 19 |  57
 20 |  39
(20 rows)

test=*#

 

[Matayus]

Wenn man die Befehle kennt, ist es ganz easy. Aber erstmal die Definition zu treffen ist manchmal schwerer als man denkt.
Ich habe solange das Netz durchsucht.... Tausend dank an Dich. Du hast es echt drauf