postgresql unnest () وظيفة ورقم العنصر

سؤال

عند مواجهة عمود يحتوي على قيم منفصلة ، توفر وظيفة 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 () أو رتبة () ) ، لأن هذه الوظائف تُرجع دائمًا 1 ، والتي ربما تكون جميع العناصر في نفس الصف في جدول المصدر.

حل

postgresql 14 أو الأحدث

للحصول على سلاسل مفصولة ، استخدم string_to_table () بدلاً من 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 أو لاحقًا

للوظائف التي ترجع مجموعات ، استخدم مع الأوامر :

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

اليسار الانضمام ... على True تأكد من الحفاظ على جميع الصفوف في الجدول الأيسر بغض النظر عما إذا كان التعبير الجدول الأيمن يعيد أي صفوف.

أو ، بما أن غادر الانضمام ... على True يحتفظ بجميع الصفوف ، يمكن استخدام نسخة استعلام أنظف:

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 * FROM tbl, unnest(arr) WITH ORDINALITY a

هذا النموذج النهائي يرجع جميع أعمدة tbl . بطبيعة الحال ، يمكن تحديد الاسم المستعار بشكل صريح للعمود والأعمدة المؤهلة للجدول إلى تحسين الوضوح.

يتم استخدام

A ككلم مستعار للوحد والمستعار للعمود (للعمود الأول) ، والاسم الافتراضي لعمود التسلسل المرفق هو المرسوم .

postgresql 8.4 - 9.3

استخدم row_number () Over (partition by order by elem) احصل على الرقم وفقًا لترتيب الفرز (وليس موضع الرقم):

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

في حين أن هذا صحيح بشكل عام ولا يتم ملاحظته في استفسارات بسيطة ، فإن postgreSQL لا يضمن ترتيب الصفوف دون الترتيب بواسطة . السلوك الحالي هو نتيجة تفاصيل التنفيذ.

للتأكد من أن مساحة تفصل بين رقم تسلسل العنصر في سلسلة حدد المعرف ، arr [nr] كـ elem ، nr من ( SELECT *، cender_subscripts (arr ، 1) as nr من (SELECT ID ، string_to_array (عناصر ، '') كـ arr from tbl) t ) sub

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

حدد المعرف ، arr [nr] كـ elem ، nr من (SELECT *، cender_subscripts (arr ، 1) as nr from tbl) t

postgresql 8.1 - 8.4

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

،

f_unnest_ord

: إنشاء دالة f_unnest_ord (anyarray ، out val anelement ، out ordinality integer) إرجاع سجل setof اللغة SQL غير قابلة "حدد $ 1 [i] ، i - array_lower (1،1 دولار) 1 من cenerte_series (array_lower (1،1 دولار) ، array_upper (1،1 دولار)) i ' الوظيفة المعدلة هي كما يلي: إنشاء دالة f_unnest_ord_idx (anyarray ، out val anelement ، out ordinality int ، out idx int) إرجاع سجل setof اللغة SQL غير قابلة "حدد $ 1 [i] ، i - array_lower (1،1 دولار) 1 ، أنا من cenerte_series (array_lower (1،1 دولار) ، array_upper (1،1 دولار)) i '

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'

idx

. يقارن:

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'

الإخراج ]]

id | arr | فال | مرتبة | IDX ---- ----------------- ----- ------------ ----- 1 | {a ، b ، c} | أ | 1 | 1 1 | {a ، b ، c} | ب | 2 | 2 1 | {a ، b ، c} | ج | 3 | 3 2 | [5: 7] = {a ، b ، c} | أ | 1 | 5 2 | [5: 7] = {a ، b ، c} | ب | 2 | 6 2 | [5: 7] = {a ، b ، c} | ج | 3 | 7 3 | [-9: -7] = {a ، b ، c} | أ | 1 | -9 3 | [-9: -7] = {a ، b ، c} | ب | 2 | -8 3 | [-9: -7] = {a ، b ، c} | ج | 3 | -7