SQL 带有元素编号的 PostgreSQL unnest()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8760419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL unnest() with element number
提问by BartekR
When I have a column with separated values, I can use the unnest()function:
当我有一个带有分隔值的列时，我可以使用该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
...
How can I include element numbers? I.e.:
如何包含元素编号？IE：
id | elem | nr
---+------+---
1 | ab | 1
1 | cd | 2
1 | efg | 3
1 | hi | 4
2 | jk | 1
...
I want the original positionof each element in the source string. I've tried with window functions (row_number(), rank()etc.) but I always get 1. Maybe because they are in the same row of the source table?
我想要源字符串中每个元素的原始位置。我试着窗口函数（row_number()，rank()等等），但我总是得到1。也许是因为它们在源表的同一行中？
I know it's a bad table design. It's not mine, I'm just trying to fix it.
我知道这是一个糟糕的桌子设计。这不是我的，我只是想修复它。
回答by Erwin Brandstetter
Postgres 9.4 or later
Postgres 9.4 或更高版本
Use WITH ORDINALITYfor set-returning functions:
使用WITH ORDINALITY了一组返回功能：
When a function in the
FROMclause is suffixed byWITH ORDINALITY, abigintcolumn is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such asUNNEST().
当
FROM子句中的函数以为后缀时WITH ORDINALITY，bigint将在输出中附加一列，该 列从 1 开始，并为函数输出的每一行增加 1。这在设置返回函数的情况下最有用，例如UNNEST().
In combination with the LATERALfeature in pg 9.3+, and according to this thread on pgsql-hackers, the above query can now be written as:
结合LATERALpg 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 TRUEpreserves all rows in the left table, even if the table expression to the right returns no rows. If that's of no concern you can use this otherwise equivalent, less verboseform with an implicit CROSS JOIN LATERAL:
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);
Or simpler if based off an actual array(arrbeing an array column):
或者更简单，如果基于实际数组（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;
ais automatically table andcolumn 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.
a是自动表和列别名。添加的序数列的默认名称是ordinality。但最好（更安全、更干净）添加显式列别名和表限定列。
Postgres 8.4 - 9.3
Postgres 8.4 - 9.3
With row_number() OVER (PARTITION BY id ORDER BY elem)you get numbers according to the sort order, not the ordinal number of the original ordinal positionin the string.
随着row_number() OVER (PARTITION BY id ORDER BY elem)您根据排序顺序，不是的序数得到的数字原顺序位置的的字符串中。
You could simply omit the ORDER BY:
你可以简单地省略ORDER BY：
SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t;
While this normally works and I have never seen it break in simple queries, PostgreSQL asserts nothing concerning the order of rows without ORDER BY. It happens to work due to an implementation detail.
虽然这通常有效并且我从未见过它在简单查询中中断，但 PostgreSQL 不会断言没有ORDER BY. 由于实现细节，它碰巧起作用。
To guarantee ordinal numbersof elements in the blank-separated string:
为了保证以空格分隔的字符串中元素的序号：
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;
Or simpler if based off an actual array:
或者更简单，如果基于实际数组：
SELECT id, arr[nr] AS elem, nr
FROM (SELECT *, generate_subscripts(arr, 1) AS nr FROM tbl) t;Related answer on dba.SE:
dba.SE 上的相关答案：
Postgres 8.1 - 8.4
Postgres 8.1 - 8.4
None of these features are available, yet: RETURNS TABLE, generate_subscripts(), unnest(), array_length(). But this works:
这些功能均不可用，但：RETURNS TABLE、generate_subscripts()、unnest()、array_length()。但这有效：
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';
Note in particular, that the array index can differ from ordinal positions of elements. Consider this demo with an extended function:
请特别注意，数组索引可能与元素的顺序位置不同。考虑这个带有扩展功能的演示：
CREATE FUNCTION f_unnest_ord_idx(anyarray, OUT val anyelement, OUT ordinality int, OUT idx int)
RETURNS SETOF record LANGUAGE sql IMMUTABLE AS
'SELECT [i], i - array_lower(,1) + 1, i
FROM generate_series(array_lower(,1), array_upper(,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:
相比：
回答by BartekR
Try:
尝试：
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
回答by YujiSoftware
Use Subscript Generating Functions.
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS
使用下标生成函数。
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS
For example:
例如：
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
;
More simply:
更简单：
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
;
回答by Florin Ghita
If the order of element is not important, you can
如果元素的顺序不重要，你可以
select
id, elem, row_number() over (partition by id) as nr
from (
select
id,
unnest(string_to_array(elements, ',')) AS elem
from myTable
) a
回答by Peter Krauss
unnest2()as exercise
unnest2()作为锻炼
Older versions before pg v8.4 need a user-defined unnest(). We can adapt this old function to return elements with an index:
pg v8.4 之前的旧版本需要用户定义的unnest(). 我们可以修改这个旧函数以返回带有索引的元素：
CREATE FUNCTION unnest2(anyarray)
RETURNS setof record AS
$BODY$
SELECT [i], i
FROM generate_series(array_lower(,1),
array_upper(,1)) i;
$BODY$ LANGUAGE sql IMMUTABLE;
