[SQL] 요소 번호의 PostgreSQL unnest ()

요소 번호의 PostgreSQL unnest ()

I 분리 값 열이있는 경우, I가 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 () 등의 순위 ())을 시도했지만 그들은 소스 테이블의 같은 행에 있기 때문에 난 항상 어쩌면 1.거야?

나는 그것이 나쁜 테이블 디자인 알고있다. 난 그냥 그것을 해결하기 위해 노력하고있어 내 것이 아니합니다.

해결법

1. ==============================

   1.집합을 반환하는 기능을 함께 사용의 순서와 :

   집합을 반환하는 기능을 함께 사용의 순서와 :

   9.3+ PG의 횡 기능을 조합하고 pgSQL의-해커에이 스레드에있어서, 상기 쿼리가 지금과 같이 쓸 수있다 :

   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;

   왼쪽은 모든 행 왼쪽 테이블의 TRUE 보존 ON ... 가입하는 경우에도 올바른 반환하지 행에 대한 테이블 식입니다. 그 더 관심 있다면 당신은 측면 가입 암시 십자가 형태로 자세한 이하, 그렇지 않으면 해당 이것을 사용할 수 있습니다 :

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

   또는 간단 (배열 열 인 편곡) 실제 배열을 기반으로하는 경우 :

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

   심지어 최소한의 구문 :

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

   A는 자동으로 테이블과 컬럼 별명입니다. 추가 된 순서와 컬럼의 기본 이름에 순서입니다. 그러나 명시 적으로 열 별칭과 테이블 자격 열을 추가하는 것이 좋습니다 (안전, 청소기)입니다.

   와 ROW_NUMBER () OVER (ELEM BY 아이디 ORDER BY PARTITION) 당신은 정렬 순서가 아닌 문자열의 원래 순서 위치의 서수에 따라 번호를 얻을.

   당신은 단순히 순서에 의해 생략 할 수있다 :

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

   이 정상적으로 작동하고 나는 그것이 간단한 쿼리에서 휴식 본 적이 있지만, PostgreSQL는 ORDER BY없이 행의 순서에 관하여 아무것도 주장하지 않습니다. 이 때문에 구현 세부 사항 작업에 발생합니다.

   빈 구분 된 문자열에서 요소의 순서 번호를 보장하려면 :

   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;

   dba.SE에 대답 관련 :

   이러한 기능 중 어느 것도 아직 사용할 수 없습니다 : RETURNS 표, generate_subscripts (), unnest (), array_length (). 하지만이 작동합니다 :

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

   비교:

2. ==============================

   2.시험:

   시험:

   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
   

3. ==============================

   3.사용 첨자 생성 기능. http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

   사용 첨자 생성 기능. http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

   예를 들면 :

   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
   ;
   

   더 간단하게 :

   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
   ;
   

4. ==============================

   4.요소의 순서가 중요하지 않은 경우, 당신은 할 수

   요소의 순서가 중요하지 않은 경우, 당신은 할 수

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

5. ==============================

   5.페이지의 V8.4 전에 이전 버전의 사용자 정의 unnest을 ()가 필요합니다. 우리는 인덱스로 요소를 반환하는이 오래된 기능을 적용 할 수 있습니다 :

   페이지의 V8.4 전에 이전 버전의 사용자 정의 unnest을 ()가 필요합니다. 우리는 인덱스로 요소를 반환하는이 오래된 기능을 적용 할 수 있습니다 :

   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;
   

from https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number by cc-by-sa and MIT license