PostgreSQLのunnest()関数を使用するときに要素番号を取得するにはどうすればよいですか?

PostgreSQL unnest() 関数と要素番号

質問

区切り文字で区切られた値を含む列が見つかった場合、unnest() 関数はこれらの値を抽出する方法を提供します。

<code class="language-sql">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
...</code>

ただし、次の形式で要素番号も含めることができます:

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

最終的な目標は、row_number() や rank() のようなウィンドウ関数を使用せずに、ソース文字列内の各要素の元の位置を取得することです。これらの関数は常に 1 を返します。これは、おそらくすべての要素がソースの同じ行にあるためです。テーブル。

解決策

PostgreSQL 14 以降

カンマ区切りの文字列の場合は、string_to_table() の代わりに unnest(string_to_array()) を使用します。

<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 を使用します:

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

LEFT JOIN ... ON true 右側のテーブル式が行を返すかどうかに関係なく、左側のテーブルのすべての行が保持されるようにします。

または、LEFT JOIN ... ON true はすべての行を保持するため、より簡潔なバージョンのクエリを使用できます。

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr)</code>

実際の配列 (arr は配列の列) の場合、より簡潔な形式を使用できます:

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr)</code>

わかりやすくするために、デフォルトの列名を使用できます:

<code class="language-sql">SELECT id, a, ordinality
FROM   tbl, unnest(arr) WITH ORDINALITY a</code>

さらに簡略化できます:

<code class="language-sql">SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a</code>

この最終形式は、tbl のすべての列を返します。もちろん、列の別名とテーブル修飾された列を明示的に指定すると、明確さが向上します。

a はテーブルのエイリアスと列のエイリアス (最初の列) の両方として使用され、追加される順序列のデフォルト名は ordinality です。

PostgreSQL 8.4 - 9.3

(順序位置ではなく) ソート順序に基づいて数値を取得するには、row_number() OVER (PARTITION BY id ORDER BY elem) を使用します:

<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 は ORDER BY を使用しない行の順序を保証しません。現在の動作は実装の詳細の結果です。

へは、文字列内の要素のスペース区切りのシリアル番号を確認します:

<code class="language-sql">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</code>

実際の配列の場合、より単純なバージョンを使用できます:

<code class="language-sql">SELECT id, arr[nr] AS elem, nr
FROM  (SELECT *, generate_subscripts(arr, 1) AS nr FROM tbl) t</code>

PostgreSQL 8.1 - 8.4

PostgreSQL バージョン 8.1 ～ 8.4 には、RETURNS TABLE、generate_subscripts()、unnest()、array_length() などの一部の機能がないため、f_unnest_ord という名前のカスタム SQL 関数を使用できます。

<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">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
...</code>

この拡張関数 f_unnest_ord_idx は追加の idx 列を返します。比較:

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

出力

<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のunnest()関数を使用するときに要素番号を取得するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。