PostgreSQL unnest() avec numéro d'élément

Question

Lorsque j'ai une colonne avec des valeurs séparées, je peux utiliser la fonction unnest() fonction :

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

Comment puis-je inclure des numéros d'éléments ? Par exemple :

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

Je veux que le position initiale de chaque élément de la chaîne source. J'ai essayé avec des fonctions de fenêtre ( row_number() , rank() etc.) mais j'obtiens toujours 1 . Peut-être parce qu'ils se trouvent sur la même ligne du tableau source ?

Je sais qu'il s'agit d'une mauvaise conception de la table. Ce n'est pas le mien, j'essaie juste de le réparer.

Answer 1

Essayez :

select v.*, row_number() over (partition by id order by elem) rn from
(select
    id,
    unnest(string_to_array(elements, ',')) AS elem
 from myTable) v

Answer 2

Utilisation Fonctions génératrices d'indices .
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

Par exemple :

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
;

Plus simplement :

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
;

Answer 3

Si l'ordre des éléments n'est pas important, vous pouvez

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

Answer 4

Je pense que cela est lié à l'utilisation d'une sous-requête corrélée pour attribuer des valeurs arbitraires classées / ordinales à l'ensemble final. Il s'agit plutôt d'une utilisation pratique de la gestion des tableaux de PG pour dépivoter un ensemble de données (fonctionne avec PG 9.4).

WITH _students AS ( /** CTE **/
                  SELECT * FROM
                    (   SELECT 'jane'::TEXT ,'doe'::TEXT , 1::INT 
                         UNION
                        SELECT 'john'::TEXT ,'doe'::TEXT , 2::INT 
                         UNION
                        SELECT 'jerry'::TEXT ,'roe'::TEXT , 3::INT 
                         UNION
                        SELECT 'jodi'::TEXT ,'roe'::TEXT , 4::INT 
                    ) s ( fn, ln, id )
) /** end WITH **/   
SELECT s.id
 , ax.fanm
 , ax.anm
 , ax.val
 , ax.num
FROM _students s
,UNNEST /** MULTI-UNNEST() BLOCK **/
    (
        ( SELECT ARRAY[ fn, ln ]::text[] AS anm 
                  /** CORRELATED SUBQUERY **/
                 FROM _students s2 WHERE s2.id = s.id 
         )   

        ,( SELECT ARRAY[ 'first name', 'last name' ]::text[] AS fanm )  

        ,( SELECT ARRAY[ '9','8','7'] AS val) 

        ,( SELECT ARRAY[ 1,2,3,4,5   ] AS num) 

   ) ax (  anm, fanm, val, num )
;   

L'ENSEMBLE DES RÉSULTATS DÉPIVOTÉS :

+--+----------+-----+----+---+
|id|fanm      |anm  |val |num|
+--+----------+-----+----+---+
|2 |first name|john |9   |1  |
|2 |last name |doe  |8   |2  |
|2 |NULL      |NULL |7   |3  |
|2 |NULL      |NULL |NULL|4  |
|2 |NULL      |NULL |NULL|5  |
|1 |first name|jane |9   |1  |
|1 |last name |doe  |8   |2  |
|1 |NULL      |NULL |7   |3  |
|1 |NULL      |NULL |NULL|4  |
|1 |NULL      |NULL |NULL|5  |
|4 |first name|jodi |9   |1  |
|4 |last name |roe  |8   |2  |
|4 |NULL      |NULL |7   |3  |
|4 |NULL      |NULL |NULL|4  |
|4 |NULL      |NULL |NULL|5  |
|3 |first name|jerry|9   |1  |
|3 |last name |roe  |8   |2  |
|3 |NULL      |NULL |7   |3  |
|3 |NULL      |NULL |NULL|4  |
|3 |NULL      |NULL |NULL|5  |
+--+----------+-----+----+---+

Answer 5

unnest2() comme exercice

Les versions antérieures à pg v8.4 ont besoin d'un fichier de type unnest() . Nous pouvons adapter cette ancienne fonction pour qu'elle renvoie des éléments avec un index :

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