鉴于字符串：
‘I think that Postgresql is nifty’
我想对该字符串中的单个单词进行操作.从本质上讲,我有一个单独的,我可以得到单词的详细信息,并希望在这本字典中加入该字符串的unnested数组.
到目前为止我有：
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 element number
@H_
502_17@
Postgres 9.4或更高版本中的ORDINALITY
新功能简化了这类问题.上面的查询现在可以简单地是：
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;
