Wie erhalte ich die Elementnummer, wenn ich die Funktion unnest() von PostgreSQL verwende?

PostgreSQL unnest()Funktionen und Elementnummern

Frage

Wenn Sie auf eine Spalte stoßen, die durch Trennzeichen getrennte Werte enthält, bietet die Funktion unnest() eine Möglichkeit, diese Werte zu extrahieren:

myTable
id | elements
---+------------
1  |ab,cd,efg,hi
2  |jk,lm,no,pq
3  |rstuv,wxyz

select id, unnest(string_to_array(elements, ',')) AS elem
from myTable

id | elem
---+-----
1  | ab
1  | cd
1  | efg
1  | hi
2  | jk
...

Möglicherweise möchten Sie jedoch auch die Elementnummer im folgenden Format angeben:

id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...

Das ultimative Ziel besteht darin, die ursprüngliche Position jedes Elements in der Quellzeichenfolge zu ermitteln, ohne Fensterfunktionen wie row_number() oder rank() zu verwenden, da diese Funktionen immer 1 zurückgeben, wahrscheinlich weil sich alle Elemente in derselben Zeile der Quelle befinden Tisch.

Lösung

PostgreSQL 14 oder höher

Für durch Kommas getrennte Zeichenfolgen verwenden Sie string_to_table() anstelle von unnest(string_to_array()):

SELECT t.id, a.elem, a.nr
FROM   tbl t
LEFT   JOIN LATERAL string_to_table(t.elements, ',') WITH ORDINALITY AS a(elem, nr) ON true

Demo

PostgreSQL 9.4 oder höher

Für Funktionen, die eine Sammlung zurückgeben, verwenden Sie WITH ORDINALITY:

SELECT t.id, a.elem, a.nr
FROM   tbl AS t
LEFT   JOIN LATERAL unnest(string_to_array(t.elements, ',')) WITH ORDINALITY AS a(elem, nr) ON true

LEFT JOIN ... ON true Stellt sicher, dass alle Zeilen aus der linken Tabelle beibehalten werden, unabhängig davon, ob der rechte Tabellenausdruck Zeilen zurückgibt.

Da LEFT JOIN ... ON true alle Zeilen behält, kann alternativ eine prägnantere Version der Abfrage verwendet werden:

SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr)

Für tatsächliche Arrays (arr sind Array-Spalten) kann eine prägnantere Form verwendet werden:

SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr)

Der Einfachheit halber können Sie die Standardspaltennamen verwenden:

SELECT id, a, ordinality
FROM   tbl, unnest(arr) WITH ORDINALITY a

Kann weiter vereinfacht werden:

SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a

Dieses endgültige Formular gibt alle Spalten von tbl zurück. Natürlich kann die explizite Angabe von Spaltenaliasen und tabellenqualifizierten Spalten die Übersichtlichkeit verbessern.

a wird sowohl als Tabellenalias als auch als Spaltenalias (für die erste Spalte) verwendet, und der Standardname der angehängten Ordinalspalte ist ordinality.

PostgreSQL 8.4 - 9.3

Verwenden Sie row_number() OVER (PARTITION BY id ORDER BY elem), um Zahlen basierend auf der Sortierreihenfolge (statt der Ordnungsposition) zu erhalten:

SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM  (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t

Obwohl dies im Allgemeinen funktioniert und bei einfachen Abfragen keine Fehler beobachtet wurden, garantiert PostgreSQL nicht die Reihenfolge von Zeilen ohne ORDER BY. Das aktuelle Verhalten ist das Ergebnis von Implementierungsdetails.

Um sicherzustellen, dass die durch Leerzeichen getrennte Seriennummer der Elemente in der Zeichenfolge :

ist

SELECT id, arr[nr] AS elem, nr
FROM  (
   SELECT *, generate_subscripts(arr, 1) AS nr
   FROM  (SELECT id, string_to_array(elements, ' ') AS arr FROM tbl) t
   ) sub

Für tatsächliche Arrays kann eine einfachere Version verwendet werden:

SELECT id, arr[nr] AS elem, nr
FROM  (SELECT *, generate_subscripts(arr, 1) AS nr FROM tbl) t

PostgreSQL 8.1 - 8.4

Da in den PostgreSQL-Versionen 8.1 bis 8.4 einige Funktionen fehlen, wie z. B. RETURNS TABLE, generate_subscripts(), unnest() und array_length(), kann eine benutzerdefinierte SQL-Funktion mit dem Namen f_unnest_ord verwendet werden:

CREATE FUNCTION f_unnest_ord(anyarray, OUT val anyelement, OUT ordinality integer)
  RETURNS SETOF record
  LANGUAGE sql IMMUTABLE AS
'SELECT [i], i - array_lower(,1) + 1
 FROM   generate_series(array_lower(,1), array_upper(,1)) i'

Die geänderte Funktion lautet wie folgt:

myTable
id | elements
---+------------
1  |ab,cd,efg,hi
2  |jk,lm,no,pq
3  |rstuv,wxyz

select id, unnest(string_to_array(elements, ',')) AS elem
from myTable

id | elem
---+-----
1  | ab
1  | cd
1  | efg
1  | hi
2  | jk
...

Diese Erweiterungsfunktion f_unnest_ord_idx gibt zusätzliche idx Spalten zurück. Vergleichen Sie:

id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...

Ausgabe

SELECT t.id, a.elem, a.nr
FROM   tbl t
LEFT   JOIN LATERAL string_to_table(t.elements, ',') WITH ORDINALITY AS a(elem, nr) ON true

Das obige ist der detaillierte Inhalt vonWie erhalte ich die Elementnummer, wenn ich die Funktion unnest() von PostgreSQL verwende?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!