postgresql unstest () Funktion und Elementnummer

Frage

Wenn eine Spalte mit getrennten Werten 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
...

Sie möchten jedoch auch Elementnummern in das Format wie folgt einfügen:

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

Das ultimative Ziel ist es, die ursprüngliche Position jedes Elements in die Quellzeichenfolge zu erhalten, ohne Fensterfunktionen wie row_number () oder Rank () )), weil diese Funktionen immer zurückgeben, was wahrscheinlich darauf zurückzuführen ist, dass alle Elemente in der Ausgangstabelle in derselben Zeile liegen.

Lösung

postgresql 14 oder später

für Komma-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 später

für Funktionen, die Sätze zurückgeben, verwenden Sie mit Ordinalität :

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

links join ... on true stellen Sie sicher, dass alle Zeilen in der linken Tabelle erhalten bleiben, unabhängig davon, ob der Ausdruck der rechten Tabelle alle Zeilen zurückgibt.

oder, da links join ... on true behält alle Zeilen, kann eine sauberere Abfrageversion 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 ist eine Array -Spalte), kann eine einfachere Form verwendet werden:

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

Der Einfachheit halber kann der Name der Standardspalte verwendet werden:

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

kann auch weiter vereinfacht werden:

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

Diese endgültige Form gibt alle Spalten von tbl zurück. Natürlich kann die explizit Angabe von Spalten-Alias ​​und Tabellenqualifizierungen die Klarheit verbessern.

a wird sowohl als Tabellenalias als auch als Spalte alias (für die erste Spalte) verwendet, und der Standardname der angehängten Sequenzspalte ist ordinalität .

postgresql 8.4 - 9.3

use row_number () over (Partition nach ID -Bestellung von Elem) Holen Sie sich die Nummer gemäß der Sortierordnung (nicht der Nummer der Nummer):

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 gültig ist und in einfachen Abfragen keine Fehler beobachtet werden, garantiert PostgreSQL die Reihenfolge der Zeilen ohne order von . Das aktuelle Verhalten ist das Ergebnis von Implementierungsdetails.

um sicherzustellen, dass der Raum die Sequenznummer des Elements in der string : : ] trennt

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

tatsächliche Array kann eine einfachere Version verwendet werden:

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

table , generate_subscripts () , unnest () und array_length () , Sie können eine benutzerdefinierte SCL -Funktion benennen, Sie können eine benutzerdefinierte SCL -Funktion verwenden, Sie können eine benutzerdefinierte SCL -Funktion verwenden. f_unnest_ord :

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

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

f_unnest_ord_idx gibt die angehängte idx Spalte zurück. Vergleichen:

SELECT id, arr, (rec).*
FROM  (
   SELECT *, f_unnest_ord_idx(arr) AS rec
   FROM  (
      VALUES
        (1, '{a,b,c}'::text[])  --  short for: '[1:3]={a,b,c}'
      , (2, '[5:7]={a,b,c}')
      , (3, '[-9:-7]={a,b,c}')
      ) t(id, arr)
   ) sub

Ausgabe]

 id | arr | val | Ordinalität | IDX
---- ------------------ ------ ------------ -----
  1 | {a, b, c} | a | 1 | 1
  1 | {a, b, c} | B | 2 | 2
  1 | {a, b, c} | c | 3 | 3
  2 | [5: 7] = {a, b, c} | a | 1 | 5
  2 | [5: 7] = {a, b, c} | B | 2 | 6
  2 | [5: 7] = {a, b, c} | c | 3 | 7
  3 | [-9: -7] = {a, b, c} | a | 1 | -9
  3 | [-9: -7] = {a, b, c} | B | 2 | -8
  3 | [-9: -7] = {a, b, c} | c | 3 | -7