postgresql – 如何保持unnested数组中元素的原始顺序？

新功能简化了这类问题.上面的查询现在可以简单地是：

SELECT *
FROM   regexp_split_to_table('I think postgres is nifty',' ') WITH ORDINALITY x(word,rn);

或者,应用于表格：

SELECT *
FROM   tbl t,regexp_split_to_table(t.my_column,rn);

细节：

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

关于隐式LATERAL连接：

> What is the difference between LATERAL and a subquery in PostgreSQL?

Postgres 9.3或更早 – 更一般的解释

对于单个字符串

您可以应用窗口函数row_number()来记住元素的顺序.但是,使用通常的row_number()OVER(ORDER BY col),您可以根据排序顺序获取数字,而不是字符串中的原始位置.

您可以尝试简单地省略ORDER BY以“按原样”获取位置：

SELECT *,row_number() OVER () AS rn
FROM  (
   SELECT regexp_split_to_table('I think postgres is nifty',' ') AS word
   ) x;

regexp_split_to_table()的性能随长字符串而降低. unnest(string_to_array(…))更好地扩展：

SELECT *,row_number() OVER () AS rn
FROM  (
   SELECT unnest(string_to_array('I think postgres is nifty',' ')) AS word
   ) x;

然而,虽然这通常有效,并且我从未在简单查询中看到过它,但PostgreSQL没有关于没有显式ORDER BY的行的顺序.

要保证原始字符串中元素的序数,请使用generate_subscript()(通过@deszo评论改进)：

SELECT arr[rn] AS word,rn
FROM   (
   SELECT *,generate_subscripts(arr,1) AS rn
   FROM  (
      SELECT string_to_array('I think postgres is nifty',' ') AS arr
      ) x
   ) y;

对于一个字符串表

将PARTITION BY id添加到OVER子句中……

演示表：

CREATE TEMP TABLE strings(string text);
INSERT INTO strings VALUES
  ('I think postgres is nifty'),('And it keeps getting better');

我使用ctid作为主键的ad-hoc替代品.如果您有一个(或任何唯一列),请改用它.

SELECT *,row_number() OVER (PARTITION BY ctid) AS rn
FROM  (
   SELECT ctid,unnest(string_to_array(string,' ')) AS word
   FROM   strings
   ) x;

这没有任何明确的ID：

SELECT arr[rn] AS word,rn
FROM  (
   SELECT *,generate_subscripts(arr,1) AS rn
   FROM  (
      SELECT string_to_array(string,' ') AS arr
      FROM   strings
      ) x
   ) y;

SQL Fiddle.

回答问题

SELECT z.arr,z.rn,z.word,d.meaning   --,partofspeech -- ?
FROM  (
   SELECT *,arr[rn] AS word
   FROM  (
      SELECT *,1) AS rn
      FROM  (
         SELECT string_to_array(string,' ') AS arr
         FROM   strings
         ) x
      ) y
   ) z
JOIN   dictionary d ON d.wordname = z.word
ORDER  BY z.arr,z.rn;