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:

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

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

id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...

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()):

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

Demo

PostgreSQL 9.4 atau lebih tinggi

Untuk fungsi yang mengembalikan koleksi, gunakan WITH ORDINALITY:

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

SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr)

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

SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr)

Untuk memudahkan, anda boleh menggunakan nama lajur lalai:

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

Boleh dipermudahkan lagi:

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

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

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

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 :

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

Untuk tatasusunan sebenar, versi yang lebih mudah boleh digunakan:

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

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:

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'

Fungsi yang diubah suai adalah seperti berikut:

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

Fungsi sambungan ini f_unnest_ord_idx mengembalikan lajur idx tambahan. Bandingkan:

id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...

Output

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

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!