array - PostgreSQL unnest() con número de elemento

string to array postgresql

Question

Cuando tengo una columna con valores separados, puedo usar la función unnest() :

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

¿Cómo puedo incluir números de elementos? Es decir:

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

Quiero la posición original de cada elemento en la cadena fuente. Lo he intentado con las funciones de ventana ( row_number() , rank() etc.) pero siempre obtengo 1 . Tal vez porque están en la misma fila de la tabla de origen?

Sé que es un mal diseño de mesa. No es mío, solo estoy tratando de arreglarlo.

Answer 1

Postgres 9.4 o posterior

Úselo WITH ORDINALITY para las funciones de devolución de conjuntos:

Cuando una función en la cláusula FROM está sufijada con WITH ORDINALITY , se WITH ORDINALITY una columna bigint a la salida que comienza desde 1 y se incrementa en 1 para cada fila de la salida de la función. Esto es más útil en el caso de establecer funciones de retorno como UNNEST() .

En combinación con la función LATERAL en la página 9.3+ , y de acuerdo con este hilo en pgsql-hackers , la consulta anterior ahora se puede escribir como:

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 conserva todas las filas en la tabla de la izquierda, incluso si la expresión de tabla a la derecha no devuelve filas. Si eso no te preocupa, puedes usar esta forma, por lo demás equivalente, menos detallada con un CROSS JOIN LATERAL implícito:

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

O más simple si está basado en una matriz real ( arr es una columna de matriz):

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

O incluso, con una sintaxis mínima:

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

a es automáticamente un alias de tabla y columna. El nombre predeterminado de la columna de ordinalidad añadida es ordinality . Pero es mejor (más seguro, más limpio) agregar alias explícitos de columna y columnas de clasificación de tabla.

Postgres 8.4 - 9.3

Con row_number() OVER (PARTITION BY id ORDER BY elem) obtiene los números de acuerdo con el orden de clasificación, no el número ordinal de la posición ordinal original en la cadena.

Simplemente podría omitir la ORDER BY :

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 normalmente funciona y nunca lo he visto romper en consultas simples, PostgreSQL no afirma nada sobre el orden de las filas sin ORDER BY . Sucede que funciona debido a un detalle de implementación.

Para garantizar números ordinales de elementos en la cadena separada por espacios en blanco:

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;

O más simple si está basado en una matriz real :

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

Respuesta relacionada en dba.SE:

• ¿Cómo preservar el orden original de los elementos en una matriz sin par?

Postgres 8.1 - 8.4

Ninguna de estas características está disponible todavía: RETURNS TABLE , generate_subscripts() , unnest() , unnest() .
Pero esto funciona:

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'';

Tenga en cuenta en particular, que el índice de matriz puede diferir de las posiciones ordinales de los elementos. Considere esta demostración con una función extendida :

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'';

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;

 id |       arr       | val | ordinality | 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

Comparar:

• Normalice los subíndices de la matriz para una matriz de 1 dimensión, de modo que comiencen con 1

Answer 2

unnest2() como ejercicio

Las versiones anteriores a la pg v8.4 necesitan un unnest() definido por el usuario. Podemos adaptar esta función antigua para devolver elementos con un índice:

CREATE FUNCTION unnest2(anyarray)
  RETURNS TABLE(v anyelement, i integer) AS
$BODY$
  SELECT $1[i], i
  FROM   generate_series(array_lower($1,1),
                         array_upper($1,1)) i;
$BODY$ LANGUAGE sql IMMUTABLE;

Answer 3

Si el orden del elemento no es importante, puedes

select 
  id, elem, row_number() over (partition by id) as nr
from (
  select
      id,
      unnest(string_to_array(elements, '','')) AS elem
  from myTable
) a

Answer 4

Tratar:

select v.*, row_number() over (partition by id order by elem) rn from
(select
    id,
    unnest(string_to_array(elements, '','')) AS elem
 from myTable) v

Answer 5

Use funciones de generación de subíndice .
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

Por ejemplo:

SELECT 
  id
  , elements[i] AS elem
  , i AS nr
FROM
  ( SELECT 
      id
      , elements
      , generate_subscripts(elements, 1) AS i
    FROM
      ( SELECT
          id
          , string_to_array(elements, '','') AS elements
        FROM
          myTable
      ) AS foo
  ) bar
;

Más simple:

SELECT
  id
  , unnest(elements) AS elem
  , generate_subscripts(elements, 1) AS nr
FROM
  ( SELECT
      id
      , string_to_array(elements, '','') AS elements
    FROM
      myTable
  ) AS foo
;