Postgresql

如何保留未嵌套數組中元素的原始順序?

  • October 11, 2021

給定字元串:

‘我認為 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()

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 idOVER子句…

展示表:

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;

引用自:https://dba.stackexchange.com/questions/27279