Newbie Problem mit korrelierten Subqueries

Hallo Forum,

vorweg muss ich wohl gleich eine Entschuldigung schicken. Ich bin völlig neu hier und belästige Euch gleich mit einer Frage. :frowning:

Ich habe leider mit Datenbanken normalerweise nicht viel zu tun und muss nur für ein paar Auswertungen auf einer großen Datenmenge eine postgreSQL DB bemühen. Ich habe mit meinem Problemchen, dass meine eigentliche Arbeit eigentlich nur am Rande berührt, nun schon einige Stunden verbracht und langsam aber sicher wirft das meinen Zeitplan ziemlich um.

Da mein Bemühen mit diesem Schwierigkeitsgrad wohl eher einmalig bleibt vorerst, lohnt es sich aber derzeit nicht sich noch weiter in die Thematik einzuarbeiten und so hoffe ich auf eine Plug&Play Lösung für einen verzweifelten Einsteiger.

So sieht meine Table aus:

Dabei handelt es sich um gemessene Fahrzeuge auf einer Autobahn.

Mein Problem:
Ich möchte die Zeitabstände von Fahrzeugen auf einer “lane” zueinander berechnen.

Dazu betrachte ich z.B. lane=1.
=> erste relevante Zeile ist die mit Num =3.
Nun muss mir die Abfrage die nächste relevante Zeile raussuchen ( in diesem Falle 5), timestep Zeile3 von timestep Zeile5 abziehen und diese Differenz in timedifference_lane von Zeile 5 (oder drei) eintragen.

Ich hoffe mein Problem ist klar.

Versucht habe ich es mit:
UPDATE “tb_NLDatensaetze” SET timedifference_lane =
(
SELECT
timestep - (select min(timestep) from “tb_NLDatensaetze” o where o.timestep>l.timestep and o.lane=1 LIMIT 100)
from “tb_NLDatensaetze” l
);

Wie ihr euch denken könnt, funktioniert das so nicht :frowning: :confused: :confused:


Danke für Hilfe und Verständnis!!

Ja, Problem is klar. Ich befürchte, Du bist bei der Polizei und wenn ich Dir jetzt helfe, dies zu lösen, laufe ich Gefahr, daß Du das irgendwann gegen mich einsetzt und ich eine Strafe bekomme. Also helfe ich Dir nicht.

Okay, Spaß beiseite :wink:

Ich denke, Du kommst am einfachsten mit einer Procedure. In einer äußeren Schleife ermittelst Du alle möglichen Vorkommen von ‘lane’. Es soll ja auch Autobahnen mit 3 oder mehr Spuren geben…

Für jede nun gefundene ermittelst Du alle Rows, geordnet nach timestamp. Durch diese wanderst Du durch und merkst Dir den PK des Vorgängers und berechnest den Zeitabstand und machst das passende Update. Das wäre relativ trivial mit plpgsql machbar.

Ich weiß, das ist nicht wirklich elegant, vielleicht hat jemand ja auch noch eine bessere Idee.

Vielleicht bekommt man das eleganter hin, wenn man eine temp. table erstellt und dazu eine Spalte mit einer Sequence füllt. Dann könnte man den PK des Vorgängers leichter ermitteln.

Falls Du das permanent machen willst, also beim Einfügen eines Datensatzes das immer gleich mit ausrechnen, wäre ein Trigger sinnvoll.
Achtung: Du erzeugst bei jedem Update einen toten Tupel, Du solltest also oft genug ein VACUUM machen.


Andreas

lool Nein, ich bin nicht bei der Polizei.

Was du beschreibst ist ist ein Progrämmchen, dass die DB durchrennt und die entsprechenden Daten manipuliert. So würde ich das wohl hinbekommen, aber leider kann ich derzeit mit keinem Code auf die DB zugreifen.

Dafür müsste ich mir noch anschauen wie ich z.B. mit C++ auf eine PG-DB Zugriff bekomme. Das wollte ich eigentlich vermeiden.

Gesucht ist also ein Query, das mir diese eine Berechnung ausführt.

Wenn Du mit normalem SQL zugreifen kannst, kannst Du auch eine plpgsql-Funktion schreiben.
Guggst Du: http://www.postgresql.org/docs/8.2/interactive/plpgsql.html

In SQL ansatzweise so:

test=# create table foo (id int, spur int, ts int, diff int);
-- der einfachkeit halber mit int und ein paar daten drin
test=# select * from foo;
 id | spur | ts | diff
----+------+----+------
  1 |    1 |  1 |
  2 |    1 |  3 |
  3 |    2 |  2 |
  4 |    1 |  6 |

-- eine sequence zur Hilfe
test=# create sequence s_foo;
CREATE SEQUENCE

-- eine temp-table
test=# create table foo_temp as select nextval('s_foo'), * from foo where spur = 1 order by ts;
SELECT
test=# select * from foo_temp ;
 nextval | id | spur | ts | diff
---------+----+------+----+------
       1 |  1 |    1 |  1 |
       2 |  2 |    1 |  3 |
       3 |  4 |    1 |  6 |
(3 rows)

-- differenz berechnen
test=# select a.*, b.ts-a.ts from foo_temp a, foo_temp b where b.nextval=a.nextval+1;
 nextval | id | spur | ts | diff | ?column?
---------+----+------+----+------+----------
       1 |  1 |    1 |  1 |      |        2
       2 |  2 |    1 |  3 |      |        3
(2 rows)

Du hast also an dieser Stelle in der letzten Spalte die Differenzen zum Nachfolger und auch die id’s. Daraus ein Update zu basteln überlasse ich Dir zur Übung :wink: