요소 번호가 있는 PostgreSQL unnest()

itgroup 2023. 5. 8. 22:06

요소 번호가 있는 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아마도 그들이 소스 테이블의 같은 열에 있기 때문일까요?

테이블 디자인이 안 좋은 건 알아요.제 것이 아니라 고치려는 거예요.

Postgres 9.4 이상

설정 반환 기능에 사용:

의 함수가 다음과 같은 경우FROM절 뒤에 다음이 붙습니다.WITH ORDINALITY,abigint열은 함수 출력의 각 행에 대해 1에서 시작하여 1씩 증가하는 출력에 추가됩니다.이는 다음과 같은 집합 반환 함수의 경우에 가장 유용합니다.unnest().

페이지 9.3+의 기능과 함께, 그리고 pgsql-hackers의 이 스레드에 따르면, 위의 쿼리는 이제 다음과 같이 작성될 수 있습니다.

t.id , a.elem, a.nr 을 선택합니다.tblAST에서LEFT JOIN 수평 언네스트(string_to_array(t.elements, ',))정상성을 a(엘렘, nr)로 참으로 설정;

LEFT JOIN ... ON true오른쪽에 있는 테이블 식이 행을 반환하지 않더라도 왼쪽 테이블의 모든 행을 보존합니다.문제가 없다면 이와 동등하고 덜 장황한 형식을 암시적으로 사용할 수 있습니다.CROSS JOIN LATERAL:

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

또는 실제 어레이를 기반으로 하는 경우 더 단순합니다.arr배열 열):

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는 자동으로 테이블 및 열 별칭입니다.추가된 일반성 열의 기본 이름은 다음과 같습니다.ordinality그러나 명시적 열 별칭과 테이블 수식 열을 추가하는 것이 더 좋습니다(안전하고 깨끗함).

포스트그레스 8.4 - 9.3

와 함께row_number() OVER (PARTITION BY id ORDER BY elem)문자열에서 원래 순서 위치의 순서 번호가 아닌 정렬 순서에 따라 숫자를 얻습니다.

간단히 생략할 수 있습니다.ORDER BY:

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, ar[nr] ASEM, nrFROM (SELECT *, generate_subscripts(arr, 1) ASr FROM tbl) t;

dba에 대한 관련 답변입니다.SE:

중첩되지 않은 배열에서 요소의 원래 순서를 보존하는 방법은 무엇입니까?

포스트그레스 8.1 - 8.4

아직 사용할 수 있는 기능은 없습니다. , , , . 그러나 이 기능은 작동합니다.

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

비교:

어레이 구독을 1로 시작하도록 정규화

시도:

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

첨자 생성 함수를 사용합니다.
http://www.postgresql.org/docs/current/static/functions-srf.html#://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
;

요소의 순서가 중요하지 않으면 다음과 같이 할 수 있습니다.

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

상관된 하위 쿼리를 사용하여 임의의 순위/순서 값을 최종 집합에 할당하는 것과 관련이 있다고 생각합니다.PG 어레이 처리를 사용하여 데이터 세트를 De-Pivot(PG 9.4와 함께 작동)하는 것이 실용적인 용도입니다.

WITH _students AS ( /** CTE **/
                  SELECT * FROM
                    (   SELECT 'jane'::TEXT ,'doe'::TEXT , 1::INT 
                         UNION
                        SELECT 'john'::TEXT ,'doe'::TEXT , 2::INT 
                         UNION
                        SELECT 'jerry'::TEXT ,'roe'::TEXT , 3::INT 
                         UNION
                        SELECT 'jodi'::TEXT ,'roe'::TEXT , 4::INT 
                    ) s ( fn, ln, id )
) /** end WITH **/   
SELECT s.id
 , ax.fanm
 , ax.anm
 , ax.val
 , ax.num
FROM _students s
,UNNEST /** MULTI-UNNEST() BLOCK **/
    (
        ( SELECT ARRAY[ fn, ln ]::text[] AS anm 
                  /** CORRELATED SUBQUERY **/
                 FROM _students s2 WHERE s2.id = s.id 
         )   
   
        ,( SELECT ARRAY[ 'first name', 'last name' ]::text[] AS fanm )  
     
        ,( SELECT ARRAY[ '9','8','7'] AS val) 
   
        ,( SELECT ARRAY[ 1,2,3,4,5   ] AS num) 
        
   ) ax (  anm, fanm, val, num )
;

분산된 결과 집합:

+--+----------+-----+----+---+
|id|fanm      |anm  |val |num|
+--+----------+-----+----+---+
|2 |first name|john |9   |1  |
|2 |last name |doe  |8   |2  |
|2 |NULL      |NULL |7   |3  |
|2 |NULL      |NULL |NULL|4  |
|2 |NULL      |NULL |NULL|5  |
|1 |first name|jane |9   |1  |
|1 |last name |doe  |8   |2  |
|1 |NULL      |NULL |7   |3  |
|1 |NULL      |NULL |NULL|4  |
|1 |NULL      |NULL |NULL|5  |
|4 |first name|jodi |9   |1  |
|4 |last name |roe  |8   |2  |
|4 |NULL      |NULL |7   |3  |
|4 |NULL      |NULL |NULL|4  |
|4 |NULL      |NULL |NULL|5  |
|3 |first name|jerry|9   |1  |
|3 |last name |roe  |8   |2  |
|3 |NULL      |NULL |7   |3  |
|3 |NULL      |NULL |NULL|4  |
|3 |NULL      |NULL |NULL|5  |
+--+----------+-----+----+---+

`unnest2()` 삼아

v8에는 사용자 v8.4 버전이 합니다.unnest()는 이 하는 데 적용할 수 오 래 함 사 수 있 반 습 니 다 이 할 환 를 요

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

언급URL : https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number

'IT' 카테고리의 다른 글

사용자가 제공한 입력을 SQL 문에 추가하려면 어떻게 해야 합니까? (0)	2023.05.08
WPF 탐색을 위한 Window vs Page vs UserControl? (0)	2023.05.08
메이븐 프로젝트를 이클립스로 가져오는 중 (0)	2023.05.08
디렉토리에 있는 모든 코드 행을 재귀적으로 계산하려면 어떻게 해야 합니까? (0)	2023.05.08
T-SQL: 새 Excel 파일로 내보내기 (0)	2023.05.08

현재글요소 번호가 있는 PostgreSQL unnest()

각종 프로그래밍 정보를 다루는 블로그입니다.

git, Ajax, C, sql-server, WordPress, oracle, Java, MySQL, Python, MariaDB, php, JavaScript, AngularJS, MongoDB, JSON, Excel, jQuery, reactjs, spring-boot, powershell,

Today :
Yesterday :

일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

itgroup