Verwendung einer Ringdatenstruktur in Fensterfunktionen
Ich habe Daten, die in einer Ringstruktur angeordnet sind (oderRingpuffer), das heißt, es kann ausgedrückt werden als Sequenzen, die durchlaufen: :
Ich möchte eine Fensterabfrage erstellen, mit der die Elemente "Lag" und "Lead" in einem Dreipunkt-Array kombiniert werden können, aber ich kann es nicht herausfinden. Zum Beispiel ist in Teil 1 eines 5-teiligen Rings die Verzögerungs- / Führungssequenz 5-1-2 oder in Teil 4 3-4-5.
Hier ist eine Beispieltabelle von zwei Ringen mit unterschiedlicher Anzahl von Teilen (immer mehr als drei pro Ring):
create table rp (ring int, part int);
insert into rp(ring, part) values(1, generate_series(1, 5));
insert into rp(ring, part) values(2, generate_series(1, 7));
Hier ist eine fast erfolgreiche Abfrage:
SELECT ring, part, array[
lag(part, 1, NULL) over (partition by ring),
part,
lead(part, 1, 1) over (partition by ring)
] AS neighbours
FROM rp;
ring | part | neighbours
------+------+------------
1 | 1 | {NULL,1,2}
1 | 2 | {1,2,3}
1 | 3 | {2,3,4}
1 | 4 | {3,4,5}
1 | 5 | {4,5,1}
2 | 1 | {NULL,1,2}
2 | 2 | {1,2,3}
2 | 3 | {2,3,4}
2 | 4 | {3,4,5}
2 | 5 | {4,5,6}
2 | 6 | {5,6,7}
2 | 7 | {6,7,1}
(12 rows)
Das einzige, was ich tun muss, ist das zu ersetzenNULL
mit dem Endpunkt jedes Rings, der der letzte Wert ist. Jetzt zusammen mitlag
undlead
Fensterfunktionen gibt es einelast_value
Funktion das wäre ideal. Diese können jedoch nicht verschachtelt werden:
SELECT ring, part, array[
lag(part, 1, last_value(part) over (partition by ring)) over (partition by ring),
part,
lead(part, 1, 1) over (partition by ring)
] AS neighbours
FROM rp;
ERROR: window function calls cannot be nested
LINE 2: lag(part, 1, last_value(part) over (partition by ring)) ...
Aktualisieren. Danke an @ Justins Vorschlag zu nutzencoalesce
Verschachtelung von Fensterfunktionen vermeiden. Darüber hinaus wurde von zahlreichen Leuten darauf hingewiesen, dass erste / letzte Werte eine explizite benötigenorder by
auf der Ringfolge, die zufällig istpart
für dieses Beispiel. So randomisieren Sie die Eingabedaten ein wenig:
create table rp (ring int, part int);
insert into rp(ring, part) select 1, generate_series(1, 5) order by random();
insert into rp(ring, part) select 2, generate_series(1, 7) order by random();