分離された値を持つ列がある場合は、次の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。ソース テーブルの同じ行にあるためでしょうか?
テーブルのデザインが悪いのはわかっています。私のものではないので、修正しようとしているだけです。
ベストアンサー1
Postgres 14 以降
使用string_to_table()unnest(string_to_array())カンマ区切りの文字列の代わりに:
t.id、a.elem、を選択します。a.nr
tbl tから
左結合横文字列をテーブルに変換する(t.elements, ',')
順序性をもってAS a(要素、番号) ON 真;
関連している：
実際の配列のネスト解除は Postgres 9.4 以降変更されていません。
Postgres 9.4 以降
使用WITH ORDINALITY集合を返す関数の場合:
節内の関数の
FROM末尾に が付いている場合WITH ORDINALITY、bigint関数の出力の各行ごとに 1 ずつ増加する列が出力に追加されます。これは、 などのセットを返す関数の場合に最も便利ですunnest()。
と組み合わせてLATERAL9.3ページ以降に掲載、これによるとpgsql-hackers のスレッド上記のクエリは次のように記述できます。
t.id、a.elem、を選択します。a.nr
tbl から t へ
左結合 LATERAL unnest(string_to_array(t.elements, ','))
順序性をもってAS a(要素、番号) ON 真;
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;
あるいはもっと短く:
SELECT id, a.* FROM tbl, unnest(arr) WITH ORDINALITY a;
または最小限の構文:
SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a;
tblもちろん、最後のものは のすべての列を返します。
a自動的にテーブルそして列の別名（最初の列）。追加された順序列のデフォルト名は ですordinality。ただし、明示的な列の別名とテーブル修飾列を追加する方が明確です。
この方法では、配列要素の元の順序が保持されます。マニュアルunnest():
配列を行のセットに拡張します。配列の要素は格納順に読み取られます。
ポストグレ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, arr[nr] AS elem, nr FROM (SELECT *, generate_subscripts(アール, 1) AS nr FROM tbl) t;
dba.SE の関連回答:
ポストグレ8.1 - 8.4
以下の機能はまだ利用できません:RETURNS TABLE、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
比較する：