PostgreSQL의 unnest() 함수를 사용할 때 요소 번호를 얻는 방법은 무엇입니까?

PostgreSQL unnest() 함수 및 요소 번호

질문

구분된 값이 포함된 열을 발견하면 unnest() 함수를 사용하여 이러한 값을 추출할 수 있습니다.

<code class="language-sql">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
...</code>

그러나 다음 형식으로 요소 번호도 포함할 수 있습니다.

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

궁극적인 목표는 row_number() 또는 rank()와 같은 창 함수를 사용하지 않고 소스 문자열에서 각 요소의 원래 위치를 얻는 것입니다. 이러한 함수는 항상 1을 반환합니다. 아마도 모든 요소가 소스의 동일한 행에 있기 때문일 것입니다. 테이블.

솔루션

PostgreSQL 14 이상

쉼표로 구분된 문자열의 경우 string_to_table() 대신 unnest(string_to_array())을 사용하세요.

<code class="language-sql">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</code>

데모

PostgreSQL 9.4 이상

컬렉션을 반환하는 함수의 경우 WITH ORDINALITY:

를 사용하세요.

<code class="language-sql">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</code>

LEFT JOIN ... ON true 오른쪽 테이블 표현식이 행을 반환하는지 여부에 관계없이 왼쪽 테이블의 모든 행이 유지되도록 합니다.

또는 LEFT JOIN ... ON true이 모든 행을 유지하므로 보다 간결한 버전의 쿼리를 사용할 수 있습니다.

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr)</code>

실제 배열(arr은 배열 열임)의 경우 더 간결한 형식을 사용할 수 있습니다.

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr)</code>

단순화를 위해 기본 열 이름을 사용할 수 있습니다.

<code class="language-sql">SELECT id, a, ordinality
FROM   tbl, unnest(arr) WITH ORDINALITY a</code>

더 단순화할 수 있습니다:

<code class="language-sql">SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a</code>

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

a은 테이블 별칭과 열 별칭(첫 번째 열)으로 모두 사용되며, 추가되는 서수 열의 기본 이름은 ordinality입니다.

PostgreSQL 8.4 - 9.3

(서수 위치가 아닌) 정렬 순서에 따라 숫자를 얻으려면 row_number() OVER (PARTITION BY id ORDER BY elem)을 사용하세요.

<code class="language-sql">SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM  (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t</code>

이 방법은 일반적으로 작동하고 간단한 쿼리에서는 오류가 관찰되지 않았지만 PostgreSQL은 ORDER BY 없이 행 순서를 보장하지 않습니다. 현재 동작은 구현 세부 사항의 결과입니다.

문자열 에 있는 요소의 공백으로 구분된 일련 번호를 확인하세요.

<code class="language-sql">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</code>

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

<code class="language-sql">SELECT id, arr[nr] AS elem, nr
FROM  (SELECT *, generate_subscripts(arr, 1) AS nr FROM tbl) t</code>

PostgreSQL 8.1 - 8.4

PostgreSQL 버전 8.1~8.4에는 RETURNS TABLE, generate_subscripts(), unnest(), array_length() 등 일부 기능이 없기 때문에 f_unnest_ord이라는 사용자 지정 SQL 함수를 사용할 수 있습니다.

<code class="language-sql">CREATE FUNCTION f_unnest_ord(anyarray, OUT val anyelement, OUT ordinality integer)
  RETURNS SETOF record
  LANGUAGE sql IMMUTABLE AS
'SELECT [i], i - array_lower(,1) + 1
 FROM   generate_series(array_lower(,1), array_upper(,1)) i'</code>

수정된 기능은 다음과 같습니다.

<code class="language-sql">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
...</code>

이 확장 함수 f_unnest_ord_idx는 추가 idx 열을 반환합니다. 비교:

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

출력

<code class="language-sql">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</code>

위 내용은 PostgreSQL의 unnest() 함수를 사용할 때 요소 번호를 얻는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!