regex - 我可以使用正则表达式来匹配一组值吗？

Question

我想检查集合中的每个元素是否包含在列的行的任何部分中。例如，

集合 A = {5013, aab, 402dha)

B 列

adaf**5013**dad344  23**aab**yyyy  zzz**402dha**vuuuda  .....  ...

1. 我不能使用“A 中 B 列的位置”，因为有前导字符和尾随字符；
2. 我不能使用 TRIM、SUBSTRING，因为我要搜索的元素可以在列行的任何部分；
3. 当 A 只有 3 个元素时，我可以在 where 子句中写 3 个正则表达式，但当 A 有大约 1000 个元素时，我不能这样做。

我的问题是我是否有办法做到这一点？或者Excel中是否有类似于'VLookup'的功能？

我会很感激任何想法!

Answer 1

这是一个非常糟糕的架构；最好的结果将通过更改它来实现，因此您将多个值存储在:

• 普通关系子表；
• Arrays ;或
• hstore

所有这些都允许您使用相当简单和明智的 SQL 表达式来确定您想要什么，并且是可索引的(通过子表的常规 b-tree 索引，以及通过数组和 hstore 的 GiST 或 GIN 索引)以获得更好的性能在大 table 上。

按原样当然是可能的，但性能会很糟糕。一种方法是使用 regexp_split_to_array 将列转换为数组，然后使用 array operators测试重叠。

参见 this SQLFiddle demo ，它使用了扩展的测试集，因为您的测试集还不足以证明问题。

我已经显示了“任何集合出现在列中”(%%) 和“所有集合出现在列中”(@>)，因为它不是从你想要的问题中明确。

---

设置:

CREATE TABLE test(gah text);

INSERT INTO test(gah) VALUES
('adaf**5013**dad344'),
('23**aab**yyyy'),
('zzz**402dha**vuuuda'),
('no**matches**here**lalala'),
('5013**aab**402dha'),
('402dha**aab**somethingelse**5013'),
('402dha**aab**5013');

演示:

regress=> SELECT gah FROM test 
          WHERE regexp_split_to_array(gah, '\*\*') && ARRAY['5013', 'aab', '402dha'];
               gah                
----------------------------------
 adaf**5013**dad344
 23**aab**yyyy
 zzz**402dha**vuuuda
 5013**aab**402dha
 402dha**aab**somethingelse**5013
 402dha**aab**5013
(6 rows)

regress=> SELECT gah FROM test 
          WHERE regexp_split_to_array(gah, '\*\*') @> ARRAY['5013', 'aab', '402dha'];
               gah                
----------------------------------
 5013**aab**402dha
 402dha**aab**somethingelse**5013
 402dha**aab**5013
(3 rows)

令人惊奇的是，您实际上可以通过利用 PostgreSQL 对表达式索引的支持来创建一个有利于此查询的索引。当然，仅仅因为你能做到并不意味着它是个好主意:

regress=> CREATE INDEX test_glah_resplit_gin ON test 
          USING GIN(( regexp_split_to_array(gah, '\*\*') ));
CREATE INDEX
regress=> -- Only for testing purposes, don't use in production:
regress=> SET enable_seqscan = off;
SET
regress=> explain SELECT gah FROM test WHERE regexp_split_to_array(gah, '\*\*') @> ARRAY['5013', 'aab', '402dha'];
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=16.00..20.02 rows=1 width=32)
   Recheck Cond: (regexp_split_to_array(gah, '\*\*'::text) @> '{5013,aab,402dha}'::text[])
   ->  Bitmap Index Scan on test_glah_resplit_gin  (cost=0.00..16.00 rows=1 width=0)
         Index Cond: (regexp_split_to_array(gah, '\*\*'::text) @> '{5013,aab,402dha}'::text[])
(4 rows)

regress=> explain SELECT gah FROM test WHERE regexp_split_to_array(gah, '\*\*') && ARRAY['5013', 'aab', '402dha'];
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=16.00..20.02 rows=1 width=32)
   Recheck Cond: (regexp_split_to_array(gah, '\*\*'::text) && '{5013,aab,402dha}'::text[])
   ->  Bitmap Index Scan on test_glah_resplit_gin  (cost=0.00..16.00 rows=1 width=0)
         Index Cond: (regexp_split_to_array(gah, '\*\*'::text) && '{5013,aab,402dha}'::text[])
(4 rows)

GIN 索引的更新成本很高，因此如果您使用这种方法，您将在 insert/update 上付出显着的性能代价。普通数组也是如此；使用 regexp_split_to_table 即时创建它们只会让事情变得更糟。参见 GIN tips和 the intro to GIN indexes .

例如，使用 INSERT INTO test(gah) SELECT 'aaaaabbbbb'||(x::text) FROM generate_series(1,1000000) x; 向我的测试表中插入一百万行； GIN 索引到位 22 秒，删除后 1.6 秒。不过，由于值的统一性，这可能是一个特别糟糕的情况。