तत्व संख्या के साथ PostgreSQL unnest()




arrays window-functions (4)

अभ्यास के रूप में unnest2()

पीजी v8.4 से पहले पुराने संस्करणों को उपयोगकर्ता द्वारा परिभाषित unnest() । हम इंडेक्स के साथ तत्वों को वापस करने के लिए इस पुराने फ़ंक्शन को अनुकूलित कर सकते हैं:

CREATE FUNCTION unnest2(anyarray)
  RETURNS TABLE(v anyelement, i integer) AS
$BODY$
  SELECT $1[i], i
  FROM   generate_series(array_lower($1,1),
                         array_upper($1,1)) i;
$BODY$ LANGUAGE sql IMMUTABLE;

जब मेरे पास अलग-अलग मानों वाला कॉलम होता है, तो मैं 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() , rank() इत्यादि के साथ प्रयास किया है) लेकिन मुझे हमेशा 1 मिलता है। शायद क्योंकि वे स्रोत तालिका की एक ही पंक्ति में हैं?

मुझे पता है कि यह एक खराब टेबल डिजाइन है। यह मेरा नहीं है, मैं बस इसे ठीक करने की कोशिश कर रहा हूं।


पोस्टग्रेस 9.4 या बाद में

सेट-रिटर्निंग फ़ंक्शंस के लिए WITH ORDINALITY के WITH ORDINALITY उपयोग करें:

जब FROM खंड में कोई फ़ंक्शन WITH ORDINALITY , तो एक बड़ा कॉलम आउटपुट में जोड़ा जाता है जो 1 से शुरू होता है और फ़ंक्शन के आउटपुट की प्रत्येक पंक्ति के लिए 1 से बढ़ता है। यह UNNEST() जैसे सेट रिटर्निंग फ़ंक्शंस के मामले में सबसे उपयोगी है।

पीजी 9.3+ में लेटरियल फीचर के साथ संयोजन में , और इस थ्रेड के अनुसार pgsql-hackers पर , उपर्युक्त क्वेरी अब इस प्रकार लिखा जा सकता है:

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 बाएं टेबल में सभी पंक्तियों को सुरक्षित रखता है, भले ही दाईं ओर तालिका अभिव्यक्ति कोई पंक्ति नहीं लौटाती हो। यदि यह कोई चिंता का विषय नहीं है तो आप इस अन्यथा समकक्ष, कम वर्बोज़ फॉर्म का उपयोग एक अंतर्निहित CROSS JOIN LATERAL साथ कर सकते हैं:

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;

a स्वचालित रूप से टेबल और कॉलम उपनाम है। जोड़ा ordinality कॉलम का डिफ़ॉल्ट नाम ordinality । लेकिन स्पष्ट कॉलम उपनाम और तालिका-योग्य कॉलम जोड़ने के लिए यह बेहतर (सुरक्षित, क्लीनर) है।

पोस्टग्रेर्स 8.4 - 9.3

row_number() OVER (PARTITION BY id ORDER BY elem) आपको क्रमबद्ध क्रम के अनुसार संख्याएं मिलती हैं, स्ट्रिंग में मूल क्रमिक स्थिति की क्रमिक संख्या नहीं।

आप बस ORDER BY कर सकते हैं:

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

हालांकि यह सामान्य रूप से काम करता है और मैंने इसे कभी भी सरल प्रश्नों में तोड़ नहीं देखा है, PostgreSQL बिना ORDER BY के पंक्तियों के क्रम से संबंधित कुछ भी नहीं कहता है। यह एक कार्यान्वयन विस्तार के कारण काम करने के लिए होता है।

रिक्त-पृथक स्ट्रिंग में तत्वों की क्रमिक संख्या की गारंटी के लिए:

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;

या वास्तविक सरणी के आधार पर सरल:

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

Dba.SE पर संबंधित उत्तर:

पोस्टग्रेर्स 8.1 - 8.4

इनमें से कोई भी सुविधा उपलब्ध नहीं है, फिर भी: RETURNS TABLE unnest() , generate_subscripts() unnest() , generate_subscripts() , unnest() , unnest()
लेकिन यह काम करता है:

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

विशेष रूप से ध्यान दें, कि सरणी अनुक्रमणिका तत्वों के क्रमिक पदों से भिन्न हो सकती है। एक विस्तारित समारोह के साथ इस डेमो पर विचार करें:

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

SELECT id, arr, (rec).*
FROM  (
   SELECT *, f_unnest_ord_idx(arr) AS rec
   FROM  (VALUES (1, '{a,b,c}'::text[])  --  short for: '[1:3]={a,b,c}'
               , (2, '[5:7]={a,b,c}')
               , (3, '[-9:-7]={a,b,c}')
      ) t(id, arr)
   ) sub;

 id |       arr       | val | ordinality | idx
----+-----------------+-----+------------+-----
  1 | {a,b,c}         | a   |          1 |   1
  1 | {a,b,c}         | b   |          2 |   2
  1 | {a,b,c}         | c   |          3 |   3
  2 | [5:7]={a,b,c}   | a   |          1 |   5
  2 | [5:7]={a,b,c}   | b   |          2 |   6
  2 | [5:7]={a,b,c}   | c   |          3 |   7
  3 | [-9:-7]={a,b,c} | a   |          1 |  -9
  3 | [-9:-7]={a,b,c} | b   |          2 |  -8
  3 | [-9:-7]={a,b,c} | c   |          3 |  -7

की तुलना करें:


यदि तत्व का क्रम महत्वपूर्ण नहीं है, तो आप कर सकते हैं

select 
  id, elem, row_number() over (partition by id) as nr
from (
  select
      id,
      unnest(string_to_array(elements, ',')) AS elem
  from myTable
) a

सब्सक्रिप्शन जेनरेटिंग फ़ंक्शंस का उपयोग करें।
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

उदाहरण के लिए:

SELECT 
  id
  , elements[i] AS elem
  , i AS nr
FROM
  ( SELECT 
      id
      , elements
      , generate_subscripts(elements, 1) AS i
    FROM
      ( SELECT
          id
          , string_to_array(elements, ',') AS elements
        FROM
          myTable
      ) AS foo
  ) bar
;

अधिक केवल:

SELECT
  id
  , unnest(elements) AS elem
  , generate_subscripts(elements, 1) AS nr
FROM
  ( SELECT
      id
      , string_to_array(elements, ',') AS elements
    FROM
      myTable
  ) AS foo
;




set-returning-functions