postgresql unnest（） function and element number

質問

分離値を含む列が発生した場合、 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以降

comma分離された文字列の場合は、

unnest（string_to_array（））：の代わりに string_to_table（）を使用します。

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以降

セットを返す関数については、

で

で

を使用します：

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

右のテーブルの式が行を返すかどうかに関係なく、左のテーブルのすべての行が保存されていることを確認してください。

または、 left join ... true

はすべての行を保持するため、クリーナークエリバージョンを使用できます。

Select T.Id、A.Elem、A.Nr Tbl Tから、Unnest（String_to_array（T.Elements、 '、'））with ordinality a（elem、nr）

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 TBL Tから、普通（T.ARR）と普段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 TBLから、普通性を持つUnnest（arr）a

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

select * from tbl、unnest（arr）with ordinality a

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

のすべての列を返します。もちろん、列のエイリアスとテーブル資格のある列を明示的に指定すると、明確さが向上する可能性があります。

a

は、テーブルエイリアスと列エイリアス（最初の列の）の両方として使用され、添付のシーケンス列のデフォルト名は

ardinality です。 postgresql 8.4-9.3

use

row_number（）over（elemによるid順序でパーティション）

並べ替え順序に従って番号を取得します（番号の位置ではありません）：

select *、row_number（）over（id by id by id）as nr from（select id、regexp_split_to_table（elements、 '、'）tblのelemとして）t

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

順序なしに行の順序を保証するものではありません。現在の動作は、実装の詳細の結果です。

空間が

シーケンス番号を

文字列：：]の要素のシーケンス番号を確実に分離することを確認するために Select id、arr [nr] as Elem、nr から （ select *、generate_subscripts（arr、1）as nr from（select id、string_to_array（elements、 ''）as asr from tbl）t t t ）sub

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

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

、

、

generate_subscripts（）

、 unnest（）、 array_length（&& f_unnest_ord ： Create function f_unnest_ord（anyArray、out val Anyelement、Out Ordinality Integer） 記録のセットを返します 言語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'

この拡張機能

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）。* から （ select *、f_unnest_ord_idx（arr）as 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 | val |普通性| 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