字典表上的递归关系 [英] Recursive Relationship on Dictionary Table

问题描述

我正在研究一个穷人，但是对我们来说可以，在Firebird中仅使用PSQL进行全文搜索.我将着重解决我的问题，以尽可能简化:

I'm working on a poor, but ok for us, full-text search using only PSQL in Firebird. I'll try to simplify as much as possible by focusing on my problem:

总结一下，这是一个字典表:

Summing up, this a dictionary table:

SELECT * FROM FTS_KEYWORDS

 ID | KEYWORD
----+-----------
  1 | 'FORD'
  1 | 'MUSTANG'
  1 | '2010'
  2 | 'FORD'
  2 | 'FUSION'
  2 | 'TURBO'
  2 | '2010'
  3 | 'FORD'
  3 | 'RANGER'
  3 | 'TURBO'
  3 | '2010'
  3 | 'BLACK'

也有一个FTS_TOKENIZE()过程来从整个字符串中获取单词

There is too a FTS_TOKENIZE() procedure to get the words from the whole strings

案例1:使用1个关键字的用户搜索

SELECT TOKENS FROM FTS_TOKENIZE('FORD')

 TOKENS
-------------
  'FORD'

这将是获得正确结果所需的SQL:

This would then be the SQL required to get the correct results:

:TOKEN_1 = 'FORD'

SELECT DISTINCT ID
FROM FTS_KEYWORDS
WHERE (KEYWORD STARTING :TOKEN_1)

 ID 
-----
  1
  2 
  3 

---

案例2:用户搜索了3个关键字

SELECT TOKENS FROM FTS_TOKENIZE('FORD 2010 BLACK')

 TOKENS
-------------
 'FORD'
 '2010'
 'BLACK'

因此，SQL检索正确的值:

So, SQL to retrieve the correct values:

:TOKEN_1 = 'FORD'
:TOKEN_2 = '2010'
:TOKEN_3 = 'BLACK'

SELECT DISTINCT K1.ID
FROM FTS_KEYWORDS K1
WHERE (K1.KEYWORD STARTING :TOKEN_1)
  AND (K1.ID IN (SELECT DISTINCT K2.ID
                 FROM FTS_KEYWORDS K2
                 WHERE (K2.KEYWORD STARTING :TOKEN_2)))
                   AND (K2.ID IN (SELECT DISTINCT K3.ID
                                  FROM FTS_KEYWORDS K3
                                  WHERE (K3.KEYWORD STARTING :TOKEN_3)))

 ID 
-----
  3 

ID 3是唯一具有所有与搜索匹配的关键字的ID.

ID 3 is the only ID that has all the keywords matching the search.

用于检索值的SQL是由令牌数量用户查询搜索嵌套的递归.

The SQL to retrieve values is a recursive nested by the tokens amount user query search.

当前，在过程FTS_SEARCH()中，我构建了一个SQL字符串并以EXECUTE STATEMENT的方式使用了它，但是我认为这不是理想的选择.

Currently, in a procedure FTS_SEARCH(), I build a SQL string and use then in an EXECUTE STATEMENT way, but I do not think this is ideal.

我认为可以使用递归公用表表达式("WITH ... AS ... SELECT")，但我无法执行此操作，因为基于当前可用的示例，它需要具有Parent_ID的表，并且不接受输入参数，不是我的情况.

I think this can be done with recursive Common Table Expressions ("WITH ... AS ... SELECT"), but I was not able to do it, because, based on the current examples available, it requires a table with Parent_ID and does not accept input parameters, which is not my case.

我的问题是:是否可以使用CTE或其他SQL技巧以递归方式进行此搜索?

My question is: Is there a way to do this search in a recursive way using CTE or other SQL trick?

推荐答案

您可以通过构建前缀列表来实现. 作为前缀，我使用了ASCII_CHAR(5)

You can do this by building prefixed list. As prefix i have used ASCII_CHAR(5)

SELECT 
  K.ID, COUNT(*) 
FROM FTS_KEYWORDS K
WHERE
  (SELECT ASCII_CHAR(5) || LIST(T.TOKEN, ASCII_CHAR(5)) || ASCII_CHAR(5) FROM FTS_TOKENIZE('FORD 2010 BLACK') T)
  LIKE '%' || ASCII_CHAR(5) || K.KEYWORD || ASCII_CHAR(5) || '%'
GROUP BY K.ID
HAVING COUNT(*)=(SELECT COUNT(*) FROM FTS_TOKENIZE('FORD 2010 BLACK') TX)

这应该更快(获取次数更少)，但是您必须在您的环境中对其进行测试.

this should be faster (lower fetches), but you must test this in your environment.

您也可以通过完全删除FTS_TOKENIZE来加快此速度，而您只需执行

You can speed this up also by removing FTS_TOKENIZE at all and instead of 'FORD 2010 BLACK' you simply do

SELECT 
  K.ID, COUNT(*) 
FROM FTS_KEYWORDS K
WHERE
  ASCII_CHAR(5) || 'FORD' || ASCII_CHAR(5) || '2010' || ASCII_CHAR(5) || 'BLACK' || ASCII_CHAR(5) 
  LIKE '%' || ASCII_CHAR(5) || K.KEYWORD || ASCII_CHAR(5) || '%'
GROUP BY K.ID
HAVING COUNT(*)=3

但是我不知道您的真实情况，特别是如何构建此字符串以传递给FTS_TOKENIZE

but i do not know your real case especially how this string is build to pass to FTS_TOKENIZE

UPDATE1 不是您问题的答案，但是您可以通过以下方式优化当前查询:

UPDATE1 Not the answer to your question but you can optimize your current query by:

SELECT
    DISTINCT K1.ID
FROM
    FTS_KEYWORDS K1
    INNER JOIN FTS_KEYWORDS K2 ON K2.ID = K1.ID AND K2.KEYWORD STARTING 'FORD'
    INNER JOIN FTS_KEYWORDS K3 ON K3.ID = K2.ID AND K3.KEYWORD STARTING '2010'
WHERE
    K1.KEYWORD STARTING 'BLACK' 

这篇关于字典表上的递归关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！