sql - Postgresql:动态正则表达式模式

Question

我有如下所示的事件数据:

 id | instance_id | value
 1  | 1           | a
 2  | 1           | ap
 3  | 1           | app
 4  | 1           | appl
 5  | 2           | b
 6  | 2           | bo
 7  | 1           | apple
 8  | 2           | boa
 9  | 2           | boat
10  | 2           | boa
11  | 1           | appl
12  | 1           | apply

基本上，每一行都是用户键入一个新字母。他们还可以删除字母。

我想创建一个看起来像这样的数据集，我们称它为data

 id | instance_id | value
 7  | 1           | apple
 9  | 2           | boat
12  | 1           | apply

我的目标是在每个实例中提取所有完整的单词，同时考虑删除 - 因此仅获取最长的单词或最近输入的单词是不够的。

为此，我打算像这样进行正则表达式操作:

select * from data
where not exists (select * from data d2 where d2.value ~ (d.value || '.'))

实际上，我正在尝试构建一个动态正则表达式，它添加的匹配字符比现有字符多一个，并且特定于它所匹配的行。

上面的代码似乎不起作用。在 Python 中，我可以在使用之前“编译”一个正则表达式模式。 PostgreSQL 中动态构建模式的等效项是什么？

Answer 1

尝试简单的 LIKE 运算符而不是正则表达式模式:

SELECT * FROM data d1
WHERE NOT EXISTS (
  SELECT * FROM data d2
  WHERE d2.value LIKE d1.value ||'_%'
)

演示:https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=cd064c92565639576ff456dbe0cd5f39

在 value 列上创建索引，这应该会稍微加快查询速度。