Bagaimana untuk Mendapatkan Nombor Elemen Apabila Menggunakan Fungsi unnest() PostgreSQL?

PostgreSQL unnest() Fungsi dan nombor elemen

Soalan

Apabila anda menemui lajur yang mengandungi nilai terhad, fungsi unnest() menyediakan cara untuk mengekstrak nilai ini:

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

Walau bagaimanapun, anda mungkin ingin memasukkan nombor elemen juga, dalam format berikut:

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

Matlamat utama adalah untuk mendapatkan kedudukan asal setiap elemen dalam rentetan sumber tanpa menggunakan fungsi tetingkap seperti row_number() atau rank() kerana fungsi ini sentiasa mengembalikan 1, mungkin kerana semua elemen berada dalam baris sumber yang sama meja.

Penyelesaian

PostgreSQL 14 atau lebih tinggi

Untuk rentetan dipisahkan koma, gunakan string_to_table() bukannya 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>

Demo

PostgreSQL 9.4 atau lebih tinggi

Untuk fungsi yang mengembalikan koleksi, gunakan 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 Memastikan semua baris dari jadual kiri dikekalkan tanpa mengira sama ada ungkapan jadual kanan mengembalikan sebarang baris.

Sebagai alternatif, kerana LEFT JOIN ... ON true mengekalkan semua baris, versi pertanyaan yang lebih ringkas boleh digunakan:

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

Untuk tatasusunan sebenar (arr ialah lajur tatasusunan), bentuk yang lebih ringkas boleh digunakan:

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

Untuk memudahkan, anda boleh menggunakan nama lajur lalai:

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

Boleh dipermudahkan lagi:

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

Borang akhir ini mengembalikan semua lajur tbl. Sudah tentu, spesifikasi eksplisit bagi alias lajur dan lajur layak jadual boleh meningkatkan kejelasan.

a digunakan sebagai alias jadual dan alias lajur (untuk lajur pertama), dan nama lalai lajur ordinal yang dilampirkan ialah ordinality.

PostgreSQL 8.4 - 9.3

Gunakan row_number() OVER (PARTITION BY id ORDER BY elem) untuk mendapatkan nombor berdasarkan susunan isihan (bukan kedudukan ordinal):

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

Walaupun ini biasanya berfungsi, dan tiada kegagalan diperhatikan dalam pertanyaan mudah, PostgreSQL tidak menjamin susunan baris tanpa ORDER BY. Tingkah laku semasa adalah hasil butiran pelaksanaan.

Untuk memastikan dipisahkan ruang nombor siri elemen dalam rentetan :

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

Untuk tatasusunan sebenar, versi yang lebih mudah boleh digunakan:

<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

Memandangkan PostgreSQL versi 8.1 hingga 8.4 tiada beberapa ciri, seperti RETURNS TABLE, generate_subscripts(), unnest() dan array_length(), fungsi SQL tersuai bernama f_unnest_ord boleh digunakan:

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

Fungsi yang diubah suai adalah seperti berikut:

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

Fungsi sambungan ini f_unnest_ord_idx mengembalikan lajur idx tambahan. Bandingkan:

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

Output

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

Atas ialah kandungan terperinci Bagaimana untuk Mendapatkan Nombor Elemen Apabila Menggunakan Fungsi unnest() PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!