PostGresql UNNEST () função e número do elemento

pergunta

...

myTable id | elementos --- ------------- 1 | AB, CD, EFG, HI 2 | JK, LM, não, pq 3 | RSTUV, WXYZ Selecione ID, desnecess (string_to_array (elementos, ',')) como elem de mytable id | Elem ---- ------ 1 | ab 1 | cd 1 | efg 1 | oi 2 | JK ...

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

id | elem | nr --- ----------- 1 | ab | 1 1 | CD | 2 1 | efg | 3 1 | oi | 4 2 | jk | 1 ...

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

rank ()

), porque essas funções sempre retornam 1, o que provavelmente ocorre porque todos os elementos estão na mesma linha na tabela de origem. solução PostGresql 14 ou mais tarde

para seqüências de seqüências de seleções de vírgula, use

string_to_table ()

em vez de

unsest (string_to_array ())

: selecione T.id, A.Elem, A.NR De tbl t Juntar à esquerda lateral string_to_table (t.elements, ',') com a ordinalidade como um (elem, nr) em true

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

PostGresql 9.4 ou mais tarde Para funções que retornam conjuntos, use

com ordenalidade

:

selecione T.id, A.Elem, A.NR De tbl como t Juntar à esquerda junto lateral desnecess (string_to_array (t.elements, ',', ')) com a ordinalidade como um (elem, nr) em true

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

ou, desde que junção ... no true mantém todas as linhas, uma versão mais limpa pode ser usada:

selecione T.id, A.Elem, A.NR De tbl t, desnecess (string_to_array (t.Elements, ',')) com a ordinalidade a (elem, nr)

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

selecione T.id, A.Elem, A.NR De tbl t, desnecess (t.arr) com a ordinalidade a (elem, nr)

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

selecione ID, a, ordinalalidade De tbl, desnecess (arr) com a ordinalidade a

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

tbl

. Obviamente, especificar explicitamente o alias da coluna e as colunas qualificadas pela tabela podem melhorar a clareza.

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

ordenalidade

. PostGresql 8.4 - 9.3

use row_number () sobre (partição por ordem de identificação por elem) obtenha o número de acordo com a ordem de classificação (não a posição do número):

selecione *, row_number () sobre (partição por id) como nr De (selecione ID, regexp_split_to_table (elementos, ',') como elem de tbl) t

Embora isso geralmente seja válido e nenhuma falha seja observada em consultas simples, o PostgreSQL não garante a ordem das linhas sem ordem por . O comportamento atual é o resultado dos detalhes da implementação.

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

string

: : ]

selecione ID, arr [nr] como elem, nr DE ( Selecione *, Gereate_subscripts (arr, 1) como nr De (selecione ID, String_to_array (elementos, '') como arr de tbl) t ) sub Para a matriz real , uma versão mais simples pode ser usada:

selecione ID, arr [nr] como elem, nr De (selecione *, generate_subscripts (arr, 1) como nr de tbl) t

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

porque as versões PostGRESQL 8.1 a 8.4 não têm certos recursos, como retorna tabela ,

generate_subscripts ()

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

f_unnest_ord

:

Crie função f_unnest_ord (AnyArray, fora Val Anyelement, outinalidade outentalidade) Retorna setOf Record Idioma sql imutável como 'Selecione $ 1 [i], i - Array_lower ($ 1,1) 1 De Generate_series (Array_lower ($ 1,1), Array_upper ($ 1,1)) I ' A função modificada é a seguinte: Crie função f_unnest_ord_idx (anyarray, out val anyelement, out ordinality int, out idx int) Retorna setOf Record Idioma sql imutável como 'Selecione $ 1 [i], i - Array_lower ($ 1,1) 1, i i De Generate_series (Array_lower ($ 1,1), Array_upper ($ 1,1)) I '

Esta função de extensão

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'

colun. Comparar:

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'

Saída] id | arr | val | ordinalidade | 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