postgreSql unnest () fonction et numéro d'élément

question

Lorsqu'une colonne contenant des valeurs séparées est rencontrée, la fonction Unnest () fournit un moyen d'extraire ces valeurs:

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

Cependant, vous voudrez peut-être également inclure les numéros d'élément, dans le format comme suit:

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

Le but ultime est d'obtenir la position d'origine de chaque élément dans la chaîne source sans utiliser de fonctions de fenêtre telles que row_number () ou rank () ), car ces fonctions renvoient toujours 1, ce qui est probablement parce que tous les éléments sont dans la même ligne dans le tableau source.

Solution

PostgreSQL 14 ou ultérieure

Pour les chaînes séparées des virgules, utilisez string_to_table () au lieu de 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

démo

postgresql 9.4 ou ultérieure

Pour les fonctions qui renvoient les ensembles, utilisez avec l'ordinalité :

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

gauche join ... sur true Assurez-vous que toutes les lignes de la table de gauche sont conservées, que l'expression de la table droite renvoie des lignes.

ou, puisque gauche join ... sur true conserve toutes les lignes, une version de requête plus propre peut être utilisée:

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

pour les tableaux réels ( arr est une colonne de tableau), un formulaire plus simple peut être utilisé:

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

Pour la simplicité, le nom de colonne par défaut peut être utilisé:

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

peut également être encore simplifié:

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

Ce formulaire final renvoie toutes les colonnes de tbl . Bien sûr, la spécification explicite d'alias de colonnes et les colonnes qualifiées de table peuvent améliorer la clarté.

a est utilisé à la fois comme alias de table et un alias de colonne (pour la première colonne), et le nom par défaut de la colonne de séquence jointe est ordinalité .

PostgreSQL 8.4 - 9.3

Utilisez row_number () sur (partition by id ordre par elem) Obtenez le numéro en fonction de l'ordre de tri (pas la position du nombre):

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

Bien que cela soit généralement valide et qu'aucune défaillance ne soit observée dans des requêtes simples, PostgreSQL ne garantit pas l'ordre des lignes sans ordre par . Le comportement actuel est le résultat des détails de mise en œuvre.

pour s'assurer que l'espace sépare le numéro de séquence de l'élément dans la chaîne : : ]

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

pour le tableau réel

, une version plus simple peut être utilisée:

sélectionner id, arr [nr] comme elem, nr From (select *, generate_subscripts (arr, 1) comme nr de tbl) t

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

Because PostgreSQL versions 8.1 to 8.4 lack certain features, such as

RETURNS TABLE

, generate_subscripts(), unnest() and array_length()], you can use a custom SQL function called f_unnest_ord :

Créer une fonction f_unnest_ord (anyArray, out val anyelement, out Ordinality Integer) Renvoie l'enregistrement Langue sql immuable comme «Sélectionnez 1 $ [i], i - array_lower (1,1 $) 1 De Generate_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'

Créer une fonction f_unnest_ord_idx (anyArray, out Val anyyElement, out Ordinality int, out idx int) Renvoie l'enregistrement Langue sql immuable comme «Sélectionnez 1 $ [i], i - array_lower (1,1 $) 1, I De 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'

f_unnest_ord_idx

renvoie la colonne jointe idx . Comparer:

select id, arr, (rec). * DEPUIS ( SELECT *, f_unnest_ord_idx (arr) comme rec DEPUIS ( VALEURS (1, '{a, b, c}' :: text []) - abréviation pour: '[1: 3] = {a, b, c}' , (2, '[5: 7] = {a, b, c}') , (3, '[-9: -7] = {a, b, c}') ) t (id, arr) ) sub

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

Sortir

]

id | Arr | Val | Ordinalité | 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