postgresql unnest () 함수 및 요소 번호

질문

분리 된 값이 포함 된 열이 발생하면 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
...

그러나 다음과 같이 형식으로 요소 번호를 포함시킬 수도 있습니다.

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

궁극적 인 목표는 row_number () rank ()

와 같은 창 함수를 사용하지 않고 소스 문자열에서 각 요소의 원래 위치를 얻는 것입니다. 이러한 함수는 항상 1을 반환하기 때문에 모든 요소는 소스 테이블의 동일한 행에 있기 때문일 수 있습니다.

해결책

postgresql 14 이상

쉼표로 구분 된 문자열의 경우 string_to_table () 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

t.id, a.elem, a.nr을 선택하십시오 tbl t에서 왼쪽 결합 옆 String_to_table (t.elements, ',')은 진정한 의 (Elem, nr)로서의 조례를 가지고 있습니다.

데모

PostgreSQL 9.4 이상

세트를 반환하는 함수의 경우

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

t.id, a.elem, a.nr을 선택하십시오 tbl에서 t로서 왼쪽에 가입 한 측면 불신 (string_to_array (t.elements, ','))는 진짜

에 (Elem, nr)의 조례를 가지고 있습니다.

왼쪽 결합 ... true 오른쪽 테이블 표현식이 행을 반환하는지 여부에 관계없이 왼쪽 테이블의 모든 행이 보존되어 있는지 확인하십시오.

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

t.id, a.elem, a.nr을 선택하십시오 tbl t에서, unnest (string_to_array (t.elements, ','))는 조례 a (elem, nr)

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

t.id, a.elem, a.nr을 선택하십시오 조례 A (Elem, NR)

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

id, a, ordinality를 선택하십시오 TBL에서, 안식 a

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

select * from tbl, tbl, unnest (arr) a

이 최종 양식은 tbl 의 모든 열을 반환합니다. 물론, 열 별칭 및 테이블 자격이있는 열을 명시 적으로 지정하면 명확성이 향상 될 수 있습니다.

A

는 테이블 별칭과 열 별칭 (첫 번째 열의 경우)으로 사용되며 첨부 된 시퀀스 열의 기본 이름은

ordinality

입니다. postgresql 8.4-9.3

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

select *, row_number () over (id in id) nr로 from (select id, regexp_split_to_table (elements, ',')은 tbl의 elem으로) t

이것은 일반적으로 유효하며 간단한 쿼리에서는 실패가 관찰되지 않지만 PostgreSQL은

주문없이 행의 순서를 보장하지 않습니다. 현재 동작은 구현 세부 사항의 결과입니다. 공간이 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

실제 배열

의 경우 더 간단한 버전을 사용할 수 있습니다.

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

postgresql 8.1-8.4

Postgresql 버전 8.1 ~ 8.4 반환 테이블 , generate_subscripts () f_unnest_ord : 함수 생성 f_unnest_ord Setof 레코드를 반환합니다 언어 SQL 불변 '$ 1 [i], i -array_lower ($ 1,1) 1을 선택하십시오 Generate_Series (Array_Lower ($ 1,1), Array_Upper ($ 1,1)) I '

에서

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'

함수 생성 f_unnest_ord_idx (anyArray, out anylement, Out Out ordinality int, out idx int) Setof 레코드를 반환합니다 언어 SQL 불변 '$ 1 [i], i -array_lower ($ 1,1) 1, i를 선택하십시오 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'

idx

열을 반환합니다. 비교하다: id, arr, (rec)를 선택하십시오.* 에서 ( 선택 *, f_unnest_ord_idx (ARR)를 Rec 에서 ( 값 (1, '{a, b, c}':: text []) - '[1 : 3] = {a, b, c}' , (2, '[5 : 7] = {a, b, c}') , (3, '[-9 : -7] = {a, b, c}') ) t (id, arr) ) sub

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 | 발 | 의식 | 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