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:

<code class="language-sql">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
...</code>

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

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

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()):

<code class="language-sql">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</code>

Demo

PostgreSQL 9.4 oder höher

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

<code class="language-sql">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</code>

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:

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr)</code>

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

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr)</code>

Der Einfachheit halber können Sie die Standardspaltennamen verwenden:

<code class="language-sql">SELECT id, a, ordinality
FROM   tbl, unnest(arr) WITH ORDINALITY a</code>

Kann weiter vereinfacht werden:

<code class="language-sql">SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a</code>

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:

<code class="language-sql">SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM  (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t</code>

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

<code class="language-sql">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</code>

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

<code class="language-sql">SELECT id, arr[nr] AS elem, nr
FROM  (SELECT *, generate_subscripts(arr, 1) AS nr FROM tbl) t</code>

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:

<code class="language-sql">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'</code>

Die geänderte Funktion lautet wie folgt:

<code class="language-sql">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
...</code>

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

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

Ausgabe

<code class="language-sql">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</code>

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!