sql - 带元素编号的 PostgreSQL unnest()

Question

当我有一个包含分隔值的列时，我可以使用 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
...

如何包含元素编号？即:

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

我想要源字符串中每个元素的原始位置。我试过使用窗口函数(row_number()、rank() 等)，但我总是得到 1。可能是因为它们在源表的同一行？

我知道这是一个糟糕的表格设计。这不是我的，我只是想修复它。

Answer 1

Postgres 9.4 或更高版本

使用WITH ORDINALITY对于设置返回函数:

When a function in the FROM clause is suffixed by WITH ORDINALITY, abigint column is appended to the output which starts from 1 andincrements by 1 for each row of the function's output. This is mostuseful in the case of set returning functions such as unnest().

结合LATERAL feature in pg 9.3+ ，并根据这个 thread on pgsql-hackers ，上面的查询现在可以写成:

SELECT t.id, a.elem, a.nrFROM   tbl AS tLEFT   JOIN LATERAL unnest(string_to_array(t.elements, ','))                    WITH ORDINALITY AS a(elem, nr) ON true;

LEFT JOIN ... ON true preserves 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 verbose form with an implicit 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);

甚至，使用最少的语法:

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

a 自动成为表和 列的别名。添加的序数列的默认名称是 ordinality。但最好(更安全、更干净)添加显式列别名和表限定列。

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 的行的顺序没有任何断言。由于实现细节，它恰好可以工作。

要保证序数以空格分隔的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;

或者如果基于实际数组则更简单:

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

Related answer on dba.SE:

• How to preserve the original order of elements in an unnested array?

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

比较:

• Normalize array subscripts so they start with 1