主页 / dba / 问题 / 27279
Accepted
swasheck
Asked: 2012-10-20 12:00:28 +0800 CST

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

  • 772

给定字符串：

'我认为 PostgreSQL 很漂亮'

我想对该字符串中的单个单词进行操作。本质上，我有一个单独的，我可以从中获取单词详细信息，并希望在该字典中加入该字符串的未嵌套数组。

到目前为止，我有：

select word, meaning, partofspeech
from unnest(string_to_array('I think that PostgreSQL is nifty',' ')) as word
from table t
join dictionary d
on t.word = d.wordname;

这完成了我希望做的基本工作，但它不保留原始词序。

相关问题：
带有元素编号的 PostgreSQL unnest()

postgresql sorting

1 个回答

  1. Best Answer

    WITH ORDINALITY在 Postgres 9.4 或更高版本中

    查询现在可以简单地是：

    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.col, ' ') WITH ORDINALITY x(word, rn);

    细节：

    关于隐式LATERAL连接：

    db<>fiddle here
    旧的 sqlfiddle

    Postgres 9.3 或更早版本 - 以及更一般的解释

    对于单个字符串

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

    您可以简单地省略ORDER BY以获得“原样”的位置：

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

    regexp_split_to_table()长字符串会降低 性能。unnest(string_to_array(...))规模更好：

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

    然而，虽然这通常有效，而且我从未见过它在简单查询中中断，但 Postgres 对没有显式ORDER BY.

    为保证原始字符串中元素的序数，请使用generate_subscript()（由@deszo 评论改进）：

    SELECT arr[rn] AS word, rn
FROM   (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM   string_to_array('I think Postgres is nifty', ' ') AS x(arr)
   ) 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的临时替代品。如果您有一个（或任何唯一列），请改用它。

    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;

    回答问题

    SELECT z.arr, z.rn, z.word, d.meaning   -- , partofspeech -- ?
FROM  (
   SELECT *, arr[rn] AS word
   FROM  (
      SELECT *, generate_subscripts(arr, 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;
    • 29

相关问题