具有元素编号的 PostgreSQL unnest()

小开

试试:

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

小开

如果元素的顺序不重要，则可以

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

小开

最佳答案

Postgres 9.4 or later

对集合返回函数使用强 > WITH ORDINALITY强:

如果 FROM子句中的函数以 WITH ORDINALITY为后缀，则 bigint列被追加到从1开始的输出对于函数输出的每一行增加1 对于诸如 unnest()这样的集合返回函数非常有用。

In combination with the LATERAL pg9.3 + 特征, and according to this 在 pgsql 上的线程-黑客, the above query can now be written as:

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

Or even, with minimal syntax:

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

a is automatically table 还有 column alias. The default name of the added ordinality column is ordinality. But it's better (safer, cleaner) to add explicit column aliases and table-qualify columns.

Postgres 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的情况下不会断言任何关于行顺序的内容。由于实现细节的原因，它正好可以工作。

对于空格分隔的绳子中元素的 < strong > 保证序数 :

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 的相关回答:

如何保持未嵌套数组中元素的原始顺序？

Postgres 8.1-8.4

None of these features are available, yet: RETURNS TABLE, generate_subscripts(), unnest(), array_length(). But this works:

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

Compare:

规范化数组下标，使它们以1开始

小开

使用 Subscript Generating Functions。
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
;

小开

`unnest2()` as exercise

Older versions before pg v8.4 need a user-defined unnest(). We can adapt this old function to return elements with an 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;

小开

我认为这是相关的，使用一个相关的子查询将任意的排名/序数值分配给最终的集合。这是一个更实际的应用使用 PG 数组处理去枢轴数据集(工程 w/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 )
;

脱枢结果集:

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

具有元素编号的 PostgreSQL unnest()

Postgres 9.4 or later

Postgres 8.4-9.3

Postgres 8.1-8.4

unnest2() as exercise

`unnest2()` as exercise