PostgreSQL - wie soll ich first_value()?

Diese Antwort zeigt, wie die produzieren, High/Low/Open/Close-Werte aus einem ticker:
Abrufen Aggregate für beliebige Zeitintervalle

Ich versuche eine Lösung implementieren auf dieser Basis (S. 9.2), aber ich habe Mühe, den richtigen Wert für first_value().

So weit, ich habe versucht, zwei Fragen:

SELECT  
    cstamp,
    price,
    date_trunc('hour',cstamp) AS h,
    floor(EXTRACT(minute FROM cstamp) / 5) AS m5,
    min(price) OVER w,
    max(price) OVER w,
    first_value(price) OVER w,
    last_value(price) OVER w
FROM trades
Where date_trunc('hour',cstamp) = timestamp '2013-03-29 09:00:00'
WINDOW w AS (
    PARTITION BY date_trunc('hour',cstamp), floor(extract(minute FROM cstamp) / 5)
    ORDER BY date_trunc('hour',cstamp) ASC, floor(extract(minute FROM cstamp) / 5) ASC
    )
ORDER BY cstamp;

Hier ist ein Stück das Ergebnis:

        cstamp         price      h                 m5  min      max      first    last
"2013-03-29 09:19:14";77.00000;"2013-03-29 09:00:00";3;77.00000;77.00000;77.00000;77.00000

"2013-03-29 09:26:18";77.00000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.80000;77.00000
"2013-03-29 09:29:41";77.80000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.80000;77.00000
"2013-03-29 09:29:51";77.00000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.80000;77.00000

"2013-03-29 09:30:04";77.00000;"2013-03-29 09:00:00";6;73.99004;77.80000;73.99004;73.99004

Wie Sie sehen können, 77.8 ist nicht, was ich glaube, ist der richtige Wert für first_value() sollte 77.0.

Obwohl ich dies könnte durch eine unklare ORDER BY im WINDOW sein, also habe ich diese zu

ORDER BY cstamp ASC

aber dies scheint zu stören das PARTITION:

        cstamp         price      h                 m5  min      max      first    last
"2013-03-29 09:19:14";77.00000;"2013-03-29 09:00:00";3;77.00000;77.00000;77.00000;77.00000

"2013-03-29 09:26:18";77.00000;"2013-03-29 09:00:00";5;77.00000;77.00000;77.00000;77.00000
"2013-03-29 09:29:41";77.80000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.00000;77.80000
"2013-03-29 09:29:51";77.00000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.00000;77.00000

"2013-03-29 09:30:04";77.00000;"2013-03-29 09:00:00";6;77.00000;77.00000;77.00000;77.00000

da die Werte für max und letzten jetzt variieren innerhalb der partition.

Was mache ich falsch? Könnte mir jemand helfen, besser zu verstehen, die Beziehung zwischen PARTITION und ORDER innerhalb einer WINDOW?

Obwohl ich die Antwort haben, hier ist eine abgespeckte pg_dump, das wird niemandem gestatten, erstellen Sie die Tabelle. Das einzige, was anders ist, ist der name der Tabelle.

CREATE TABLE wtest (
    cstamp timestamp without time zone,
    price numeric(10,5)
);

COPY wtest (cstamp, price) FROM stdin;
2013-03-29 09:04:54 77.80000
2013-03-29 09:04:50 76.98000
2013-03-29 09:29:51 77.00000
2013-03-29 09:29:41 77.80000
2013-03-29 09:26:18 77.00000
2013-03-29 09:19:14 77.00000
2013-03-29 09:19:10 77.00000
2013-03-29 09:33:50 76.00000
2013-03-29 09:33:46 76.10000
2013-03-29 09:33:15 77.79000
2013-03-29 09:30:08 77.80000
2013-03-29 09:30:04 77.00000
\.

Es würde die Dinge einfacher, wenn Sie eine test-Tabelle, zum Beispiel als SQLfiddle (random Beispiel).
Sicher, dass die erste Bestellung durch alle Zeilen innerhalb der partition gleich. Damit können die Zeilen, Faust oder der Letzte. Jetzt die zweite ist die richtige. Es sieht für mich sollte es funktionieren. Es gibt etwas fehlt in Ihrem Bericht. @Erwin erzählte, das Sie produzieren, test-Daten, die reproduziert werden können.
Ich bin traurig über den fehlenden test-Tabelle. Ich habe einen am Ende in die Frage. Ich fürchte, ich fand SQLFiddle ein bisschen einschüchternd -- habe ich nicht bekommen, meine Füße ganz diesem nassen sehr oft.
BTW, ich habe nur messing mit Postgresql seit den letzten paar Tagen übersiedelt teilweise aus einer MySql-user. Ich bin wirklich beeindruckt.

InformationsquelleAutor Brent.Longborough | 2013-03-30

SQL Fiddle

Alle Funktionen, die Sie verwendet, wirken auf den Fenster-Rahmen, nicht auf die partition. Wenn weggelassen, der Rahmen ist Ende der aktuellen Zeile. Um den Fensterrahmen, um die ganze partition deklarieren Sie Sie in den Rahmen-Klausel (range...):

SELECT  
    cstamp,
    price,
    date_trunc('hour',cstamp) AS h,
    floor(EXTRACT(minute FROM cstamp) / 5) AS m5,
    min(price) OVER w,
    max(price) OVER w,
    first_value(price) OVER w,
    last_value(price) OVER w
FROM trades
Where date_trunc('hour',cstamp) = timestamp '2013-03-29 09:00:00'
WINDOW w AS (
    PARTITION BY date_trunc('hour',cstamp) , floor(extract(minute FROM cstamp) / 5)
    ORDER BY cstamp
    range between unbounded preceding and unbounded following
    )
ORDER BY cstamp;

Ich danke Ihnen sehr. Als Sie antworteten, stieß ich auf die frame-Klausel und es tut, in der Tat, Dinge reparieren. Was für ein Tag!

InformationsquelleAutor Clodoaldo Neto

9

Hier ist eine kurze Abfrage, um zu illustrieren das Verhalten:
```
select 
  v,
  first_value(v) over w1 f1,
  first_value(v) over w2 f2,
  first_value(v) over w3 f3,
  last_value (v) over w1 l1,
  last_value (v) over w2 l2,
  last_value (v) over w3 l3,
  max        (v) over w1 m1,
  max        (v) over w2 m2,
  max        (v) over w3 m3,
  max        (v) over () m4
from (values(1),(2),(3),(4)) t(v)
window
  w1 as (order by v),
  w2 as (order by v rows between unbounded preceding and current row),
  w3 as (order by v rows between unbounded preceding and unbounded following)
```
Die Ausgabe der obigen Abfrage können Sie hier sehen (SQLFiddle hier):
```
| V | F1 | F2 | F3 | L1 | L2 | L3 | M1 | M2 | M3 | M4 |
|--- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ----|
| 1 |  1 |  1 |  1 |  1 |  1 |  4 |  1 |  1 |  4 |  4 |
| 2 |  1 |  1 |  1 |  2 |  2 |  4 |  2 |  2 |  4 |  4 |
| 3 |  1 |  1 |  1 |  3 |  3 |  4 |  3 |  3 |  4 |  4 |
| 4 |  1 |  1 |  1 |  4 |  4 |  4 |  4 |  4 |  4 |  4 |
```
Paar Leute denken, die implizite frames, die angewendet werden, um die Fenster-Funktionen, die eine ORDER BY - Klausel. In diesem Fall, die Fenster sind standardmäßig auf den Rahmen ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Denken Sie an es auf diese Weise:
- Auf die Zeile mit v = 1 die bestellten Fenster-Rahmen überspannt v IN (1)
- Auf die Zeile mit v = 2 die bestellten Fenster-Rahmen überspannt v IN (1, 2)
- Auf die Zeile mit v = 3 die bestellten Fenster-Rahmen überspannt v IN (1, 2, 3)
- Auf die Zeile mit v = 4 die bestellten Fenster-Rahmen überspannt v IN (1, 2, 3, 4)
Wenn Sie verhindern möchten, dass Verhalten, haben Sie zwei Möglichkeiten:
- Verwenden Sie eine explizite ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Klausel für bestellt Fenster-Funktionen
- Verwenden Sie keine ORDER BY Klausel in diesen window-Funktionen, die es gestatten, unterlassen (wie MAX(v) OVER())
Mehr details erläutert werden, die in dieser Artikel über FÜHREN(), LAG(), FIRST_VALUE() und LAST_VALUE()

Sehr schöne demo! Vielleicht Namen, die Hinzugefügt max(v) over () m4 zu halten, die Symmetrie?
Eh... 😉 Gut, fair genug
Wir wollen ja nicht, dass meine .. uhm .. jeder OCD handeln, jetzt wir? 🙂
Nein, ich kann mit Euch fühlen. Ich bin der gleichen (meistens) 🙂
... Und danke für das edit 😉 ich hatte aktualisiert, die SQLFiddle, aber vergessen den link aktualisieren...

InformationsquelleAutor Lukas Eder
3

Ergebnis max() als Fenster-Funktion wird auf der Basis der frame-definition.

Die Standard-frame-definition (mit ORDER BY) vom Anfang des Frames bis zum letzten peer der aktuellen Zeile (einschließlich der aktuellen Zeile und ggf. mehrere Zeilen ranking ebenso nach ORDER BY). In der Abwesenheit von ORDER BY (wie in meiner Antwort auf die Sie sich beziehen), oder wenn ORDER BY behandelt jede Zeile in der partition gleich (so wie in deinem ersten Beispiel), werden alle Zeilen in der partition, sind peers, und max() produziert das gleiche Ergebnis für jede Zeile in der partition, effektiv unter Berücksichtigung alle Zeilen der partition.

Pro Dokumentation:

Den Standard-framing option ist RANGE UNBOUNDED PRECEDING, die die
gleiche wie RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Mit ORDER BY,
dieses setzt den Rahmen um alle Zeilen aus der partition starten
durch die aktuelle Zeile die Letzte peer. Ohne ORDER BY alle Zeilen der
partition enthalten sind in den Fensterrahmen, da alle Zeilen werden
Kollegen in der aktuellen Zeile.

Fett-Hervorhebung von mir.

Wäre die einfachste Lösung, um weglassen ORDER BY in dem Fenster definition, wie ich das in dem Beispiel demonstriert Sie sich beziehen.

Alle schmutzigen details über die frame Angaben im Kapitel Window Function Calls in der Anleitung.

Ohne eine Bestellung durch, wie eine erste und Letzte wirkliche Bedeutung?
Diese sind willkürlich pickt dann. Je nachdem, was der OP erreichen will, eine frame-definition wie in deiner Antwort, oder ein separates Fenster, definition oder einfach nur beliebige Favoriten (keine ORDER BY wie ich vermuten) wäre der Weg zu gehen.
Ja, eigentlich wollte ich das erste und Letzte in der Zeit nach den cstamp Spalte. Danke für Eure Hilfe.
"Die Standard-frame-definition vom Beginn des Rahmens bis zum letzten peer von der aktuellen Zeile," - ich denke, das ist so nicht ganz richtig. Es wäre besser zu sagen, dass bestellte Fenster haben einen Standard-frame von ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, d.h. die aktuelle Zeile enthalten ist. Siehe auch das Beispiel in meiner Antwort
Ich wollte nicht implizieren das die aktuelle Zeile wurde ausgeschlossen. Die "letzten peer der aktuellen Zeile" immer enthält die aktuelle Zeile. Ich habe ein bisschen um Missverständnisse zu vermeiden.

InformationsquelleAutor Erwin Brandstetter

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.