sql - 渐进搜索最长前缀

Question

我有一个包含电话号码前缀列的表格，例如:

prefix
------
9379
355422
35566
...

给定一个电话号码，我想从右边开始删除它的数字，直到它在前缀列中找到第一个匹配项。即:

937973418459
93797341845
9379734184
937973418
93797341
9379734
937973
93797
9379   <-- match found

请注意，我需要对电话号码列表执行此操作，因此批量操作很重要，而不是速度较慢的单个查询。我尝试使用 postgres 的全文搜索:

tsquery('937973418459|93797341845|9379734184|937973418|93797341|9379734|937973|93797|9379')

它可以工作，但在针对 10k 电话号码运行时速度很慢。有没有更有效的方法来解决这个问题？

Answer 1

要找到最长的前缀:

对于单个给定的数字:

SELECT *
FROM   prefix_tbl
WHERE  937973418459 LIKE prefix || '%'
ORDER  BY prefix DESC
LIMIT  1

对于给定数字的整个表格:

SELECT DISTINCT ON (t.nr )
       p.*
FROM   prefix_tbl p
JOIN   tel_nr t ON t.nr LIKE p.prefix || '%'
ORDER  BY t.nr, prefix DESC;

相关:

• ? LIKE (column || '%')

对于性能优化，请考虑 dba.SE 上这个密切相关的广泛答案:

• Algorithm for finding the longest prefix

还有这个:

• Optimize GROUP BY query to retrieve latest row per user