Postgresql
如何保留未嵌套數組中元素的原始順序?
給定字元串:
‘我認為 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;
這完成了我希望做的基本工作,但它不保留原始詞序。
相關問題:
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
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;