PostgreSQL でカンマ区切り値のネストを解除し、要素の順序を保持する方法

PostgreSQL での要素番号による展開

質問:

unnest() 関数を使用してカンマ区切り値を含む列を展開すると、文字列内の元の位置ではなく、要素自体のみが返されます。

ターゲット:

ソース文字列内の要素とそのシーケンス番号を取得します。

解決策:

PostgreSQL 14 以降

string_to_table() 関数と WITH ORDINALITY 句を使用します:

<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 句を unnest() 関数に追加します:

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

PostgreSQL 8.4 - 9.3

row_number() 関数の ORDER BY 句を省略します:

<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 8.1 - 8.4

要素とそのシリアル番号を抽出するカスタム関数 f_unnest_ord() を作成します:

<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">SELECT id, arr, (rec).*
FROM (SELECT *, f_unnest_ord(arr) AS rec FROM (VALUES (1, '{a,b,c}'::text[])...) t) sub;</code>

以上がPostgreSQL でカンマ区切り値のネストを解除し、要素の順序を保持する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。