postgresql unnest () función y número de elemento

pregunta

Cuando se encuentra una columna que contiene valores separados, la función unnest () proporciona una forma de extraer estos valores:

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
...

Sin embargo, es posible que desee incluir números de elementos también, en el formato de la siguiente manera:

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

el objetivo final es obtener la posición original de cada elemento en la cadena de origen sin usar funciones de ventana como row_number () o rank () ), porque estas funciones siempre regresan 1, lo que probablemente se debe a que todos los elementos están en la misma fila en la tabla fuente.

Solución

Postgresql 14 o más tarde

para cadenas separadas por comas, use string_to_table () en lugar de 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

Manifestación

Postgresql 9.4 o más tarde

para funciones que devuelven conjuntos, use con 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

izquierda unirse ... en true Asegúrese de que todas las filas en la mesa izquierda se conserven independientemente de si la expresión de la tabla correcta devuelve alguna fila.

o, ya que izquierda se une ... en true retiene todas las filas, se puede usar una versión de consulta más limpia:

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

para matrices reales ( arr es una columna de matriz), se puede usar una forma más simple:

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

Para simplificar, se puede usar el nombre de columna predeterminado:

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

también se puede simplificar más:

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

Este formulario final devuelve todas las columnas de tbl . Por supuesto, especificar explícitamente alias de columna y columnas calificadas para la tabla pueden mejorar la claridad.

a se usa como un alias de tabla y un alias de columna (para la primera columna), y el nombre predeterminado de la columna de secuencia adjunta es ordinality .

Postgresql 8.4 - 9.3

use row_number () over (Partition by Id Order by Elem) Obtenga el número de acuerdo con el orden de clasificación (no la posición de número):

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

Si bien esto es generalmente válido y no se observan fallas en consultas simples, PostgreSQL no garantiza el orden de las filas sin orden por . El comportamiento actual es el resultado de los detalles de implementación.

para asegurarse de que el espacio separe el número de secuencia del elemento en la string : : ]

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

real , se puede usar una versión más simple:

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

devuelve tabla , generate_subscripts () , unnest () y array_length () ], puede usar una función personalizada SQL llamada sql llamada sql SQL Called SQL Called Sql 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 devuelve la columna adjunta idx . Comparar:

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

Producción]

 id | arr | Val | Ordinalidad | 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