Comment obtenir le numéro d'élément lors de l'utilisation de la fonction unnest() de PostgreSQL ?

PostgreSQL unnest() Fonctions et numéros d'éléments

Question

Lorsque vous rencontrez une colonne contenant des valeurs délimitées, la fonction unnest() fournit un moyen d'extraire ces valeurs :

<code class="language-sql">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
...</code>

Cependant, vous souhaiterez peut-être également inclure le numéro de l'élément, dans le format suivant :

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

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 comme row_number() ou rank() car ces fonctions renvoient toujours 1, probablement parce que tous les éléments sont dans la même ligne de la source tableau.

Solution

PostgreSQL 14 ou supérieur

Pour les chaînes séparées par des virgules, utilisez string_to_table() au lieu de unnest(string_to_array()) :

<code class="language-sql">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</code>

Démo

PostgreSQL 9.4 ou supérieur

Pour les fonctions qui renvoient une collection, utilisez WITH ORDINALITY :

<code class="language-sql">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</code>

LEFT JOIN ... ON true Garantit que toutes les lignes du tableau de gauche sont conservées, que l'expression du tableau de droite renvoie ou non des lignes.

Alternativement, puisque LEFT JOIN ... ON true conserve toutes les lignes, une version plus concise de la requête peut être utilisée :

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr)</code>

Pour les tableaux réels (arr sont des colonnes de tableau), une forme plus concise peut être utilisée :

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr)</code>

Pour plus de simplicité, vous pouvez utiliser les noms de colonnes par défaut :

<code class="language-sql">SELECT id, a, ordinality
FROM   tbl, unnest(arr) WITH ORDINALITY a</code>

Peut être encore simplifié :

<code class="language-sql">SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a</code>

Ce formulaire final renvoie toutes les colonnes de tbl. Bien entendu, la spécification explicite des alias de colonnes et des colonnes qualifiées de table peut améliorer la clarté.

a est utilisé à la fois comme alias de table et comme alias de colonne (pour la première colonne), et le nom par défaut de la colonne ordinale ajoutée est ordinality.

PostgreSQL 8.4 - 9.3

Utilisez row_number() OVER (PARTITION BY id ORDER BY elem) pour obtenir des nombres en fonction de l'ordre de tri (plutôt que de la position ordinale) :

<code class="language-sql">SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM  (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t</code>

Bien que cela fonctionne généralement et qu'aucun échec n'ait été observé dans les requêtes simples, PostgreSQL ne garantit pas l'ordre des lignes sans ORDER BY. Le comportement actuel est le résultat des détails de mise en œuvre.

Pour s'assurer que le numéro de série séparé par des espaces des éléments de la chaîne  :

<code class="language-sql">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</code>

Pour les tableaux réels, une version plus simple peut être utilisée :

<code class="language-sql">SELECT id, arr[nr] AS elem, nr
FROM  (SELECT *, generate_subscripts(arr, 1) AS nr FROM tbl) t</code>

PostgreSQL 8.1 - 8.4

Étant donné que certaines fonctionnalités de PostgreSQL 8.1 à 8.4 manquent, telles que RETURNS TABLE, generate_subscripts(), unnest() et array_length(), une fonction SQL personnalisée nommée f_unnest_ord peut être utilisée :

<code class="language-sql">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'</code>

La fonction modifiée est la suivante :

<code class="language-sql">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
...</code>

Cette fonction d'extension f_unnest_ord_idx renvoie des idx colonnes supplémentaires. Comparez :

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

Sortie

<code class="language-sql">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</code>

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!