在Postgres中查找字符串按任意随机顺序排列的行

我目前正在开发一个Postgres SQL查询，该查询将检查以任意随机顺序匹配空格分隔字符串（3个单词）的行。

例如，我想查找一个匹配"lorem ipsum dolor"的行，它应该返回行id 0。

+----+-------------------+
| id | sentence          |
| 0  | lorem dolor ipsum |
| 1  | lorem ipsum       |
| 2  | ipsum dolor       |
| 3  | ipsum dolor       |
+----+-------------------+

因此它必须满足以下条件：

在这种情况下是相同的3个单词
按任意随机顺序：
1. Lorem ipsum dolor
2. dolor Lorem ipsum
3. ipsum dolor Lorem
4. Lorem dolor ipsum

如果我是正确的，这将导致3*3*3=27种可能的格式。但这可以想象，当使用更多的单词时，这是相当密集的。我如何在不破坏服务器的情况下实现这一点，或者寻找正确的方向。

Clodoaldo Neto描述的方法非常适合对单词进行排序。如果性能对您来说至关重要，您甚至可以为此创建索引以提高查找速度。首先创建自定义函数sortwords：

CREATE OR REPLACE FUNCTION sortwords (words text) RETURNS text AS 
   $$ SELECT string_agg(lower(s), ' ' order by s) 
      FROM regexp_split_to_table($1, 's+') s(s) $$ 
      LANGUAGE sql IMMUTABLE;

关键字IMMUTABLE指定，函数结果仅取决于其参数，因此该函数适用于创建索引。

然后，创建索引：

CREATE INDEX mytable_sortwords ON mytable (sortwords(sentence));

并执行选择，如：

SELECT * FROM mytable WHERE sortwords(sentence) = sortwords('some words');

这样做的优点是，单词的排序（可能非常昂贵）每行只执行一次（无论是在创建索引时还是在行插入时）。

with t(s) as (values
    ('lorem dolor ipsum'),
    ('lorem ipsum'),
    ('ipsum dolor'),
    ('ipsum dolor')
)
select *
from t
where 
    (
        select string_agg(lower(s), ' ' order by s)
        from regexp_split_to_table(s, 's+') s(s)
    )
    =
    (
        select string_agg(lower(s), ' ' order by s)
        from regexp_split_to_table('lorem ipsum dolor', 's+') s(s)
    )
;
         s         
-------------------
 lorem dolor ipsum

http://www.postgresql.org/docs/current/static/functions-aggregate.htmlhttp://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-字符串-其他

实现这一点的一种方法是打破搜索文本，然后添加如下过滤器：

select * from test 
 where sentence like '%lorem%'
   and sentence like '%ipsum%'
   and sentence like '%dolor%';

这将得到包含这三个单词的所有句子，按任何顺序排列。请在此处查看它的工作情况：http://sqlfiddle.com/#！15/28ac1/3

编辑

为了在任何情况下获得结果，您必须向字段sentence添加lower函数，如下所示：

select * from test 
 where lower(sentence) like '%lorem%'
   and lower(sentence) like '%ipsum%'
   and lower(sentence) like '%dolor%';

请在此处查看：http://sqlfiddle.com/#！15/6dfb9/1

编辑2

正如OP在评论中所说，他只需要只包含三个搜索词的注册表，我会采用这种方法：

select * from test 
 where position('lorem' in lower(sentence))>0
   and position('ipsum' in lower(sentence))>0
   and position('dolor' in lower(sentence))>0
   and array_length(regexp_split_to_array(sentence, E'\s+')::text[],1) =
       array_length(regexp_split_to_array('lorem ipsum dolor', E'\s+')::text[],1)

请在此处查看它的工作情况：http://sqlfiddle.com/#！15/a5404/5

相关内容

最新更新

热门标签：