本文重点介绍了如何在 DuckDB 中以纯SQL的方式

之前的文章已经介绍过了混合搜索，本文重点介绍了如何在 DuckDB 中以纯SQL的方式，结合全文搜索和嵌入搜索来构建混合搜索系统，以实现更灵活、准确和高效的文本数据检索。

本篇是 DuckDB 搜索系列的第三部分。在之前的文章中，我们深入探讨了基于向量嵌入的文本搜索以及如何构建可搜索的知识库。向量嵌入搜索侧重于语义理解和相似度匹配，但在法律、合规和医疗领域，精确的关键词匹配至关重要，例如查找特定的法律条文或医学术语。全文搜索等词法搜索方法在这种情况下非常有效。当查询表达模糊、文档来自不同领域或包含语义不同但存在重叠关键词时，混合搜索方法能够结合词法匹配和语义理解的优势，兼顾搜索范围和结果的准确性。

本文将介绍全文搜索，以及如何将其与嵌入搜索结合构建混合搜索系统。对于混合搜索结果的排序，我们将介绍两种常用的指标融合方法：倒数排名融合和凸组合，并解释其计算公式和 SQL 实现。

注意： 由于每行数据包含丰富的文本信息，我们在文中将其称为“文档”。

全文搜索的工作原理

全文搜索会扫描整个文本以查找特定单词的完全匹配，这与利用向量嵌入识别语义相似性的语义搜索不同。

DuckDB 的 FTS 扩展^[1] 提供了字符串搜索功能，它通过创建倒排索引实现。

倒排索引建立了关键词和包含该关键词的文档 ID 之间的映射关系，从而加快搜索速度。首先，系统识别查询字符串中的关键词，然后利用倒排索引定位包含这些关键词的文档。找到相关文档后，下一步是对其进行排序，因为用户通常只关心排名靠前的结果。FTS 扩展使用 Okapi BM25^[2] 评分函数对文档进行评分。该函数考虑了关键词在文档中出现的次数、文档长度、文档集合的平均长度以及包含该关键词的文档数量。这里^[3] 是该公式的详细解释。需要注意的是，该评分函数没有考虑关键词在文档中的位置关系。计算出每个文档的分数后，系统会根据分数对文档进行排序，并将得分最高的前 N 个文档作为最相关的结果返回给用户。

图 1：全文搜索创建倒排索引并利用其进行搜索的示意图。彩色框表示关键词。

演示：电影数据集

在本文中，我们将使用与嵌入搜索 [文章](https://mp.weixin.qq.com/s?__biz=MzU1NTg2ODQ5Nw==&mid=2247489373&idx=1&sn=49a53954caf16de23113c9f2a327c264&chksm=fbcc9f63ccbb1675d7aed56cdf594f0c4664094e661e5d409b3b5b956a019211c9c37df150ec&token=195762896&lang=zh_CN#rd 相同的 Kaggle 电影数据集 ^[4]。

您可以通过以下两种方式加载数据集：

1. 1. 使用 MotherDuck 公共数据集。所有 MotherDuck 用户都可以访问 sample_data 数据库^[5]，其中包含电影数据集，位于 sample_data.kaggle.movies 下。您还可以利用 MotherDuck 云平台的强大功能。

2. 2. 从我们存储开放数据集的 AWS S3 公共存储桶中读取数据（请参阅下面的代码）。该文件大小约为 500 MB。

首先，加载数据集：

create table movies as 
select title, overview
FROM 's3://us-prd-motherduck-open-datasets/movies/parquet/movies.parquet';

describe movies;

DuckDB 中的全文搜索 (FTS)

FTS 在 DuckDB 中以扩展^[6] 的形式提供，并在调用以下 pragma 语句时自动加载^[7]。该扩展添加了两个 PRAGMA 语句，分别用于创建和删除 FTS 索引。您可以使用以下语句创建索引：

pragma create_fts_index(
    input_table,
    input_id,
* input_values,
    stemmer ='porter',
    stopwords ='english',
    ignore ='(\\.|[^a-z])+',
    strip_accents =1,
    lower =1,
    overwrite =0
)

该语句为指定的表 input_table 创建索引，并使用 input_id 列将关键词映射到文档。input_id 通常是文档的唯一标识符。您可以指定要为哪些列创建索引。此外，您还可以使用一些可选参数来自定义索引创建过程。默认情况下，系统会将所有字符转换为小写并忽略转义序列。您可以使用可选参数修改默认行为。例如，您可以使用 ignore 参数指定要忽略的字符模式，该参数默认为 '(\\.|[^a-z])+'，表示忽略所有转义字符和非小写字母字符。您还可以使用 strip_accents = 1 参数去除文本中的重音符号（例如将 á 转换为 a），该参数默认值为 1。lower = 1 参数将所有字符转换为小写，overwrite = 0 参数覆盖现有索引。stemmer 和 stopwords 参数将在下文详细介绍。规范化操作按以下顺序执行：

strip_accents -> lowercase -> ignore_regex -> stemmer -> stopwords

词干提取

词干提取是指去除单词词尾的过程，例如将 running 转换为 run，将 cats 转换为 cat。这可以简化不同形式的关键词搜索。DuckDB 提供了多种词干提取算法，默认算法为 stemmer = porter。您也可以通过将 stemmer 参数设置为 none 来禁用词干提取功能。

停用词

停用词是指在语言中经常出现的词，例如英语中的 “a”、“is”、“the”、“are” 等。这些词通常对搜索结果的贡献很小，因此在基于关键词的搜索系统中经常被过滤掉。FTS 扩展默认使用英语停用词，即 stopwords = 'english'。

接下来，我们为电影数据集的标题和概述列创建索引，使用默认参数设置，并使用标题列作为文档标识符，因为标题是每部电影的唯一标识符。

pragma create_fts_index(movies, title, title, overview)

执行该语句后，系统会在与 input_table 相同的数据库中创建一些新的表，这些表位于名为 fts_main_<input_table> 的 schema 下。这些表存储了全文搜索的倒排索引。

select database, schema, name, column_names FROM (show all tables)

以下是每个新创建的表的说明：

注意： 您可以查询和检查这些表，并在其他查询中使用它们。

使用查询进行文本搜索

执行 PRAGMA create_fts_index 语句后，系统会创建一个检索宏并将其与索引关联。该宏的定义如下：

match_bm25(
    input_id,
    query_string,
    fields := NULL,
    k := 1.2,
    b := 0.75,
    conjunctive := 0
)

该宏使用 Okapi BM25 算法计算搜索得分，其中 input_id 是文档标识符的列名，query_string 是输入的搜索字符串，fields 是一个字符串，包含要搜索的列名，多个列名之间用逗号分隔，如果为 NULL 则表示搜索所有列。k 和 b 参数用于调整 BM25 评分，conjunctive = 1 表示仅返回包含所有关键词的文档。

接下来，我们使用该宏搜索电影数据集，查询字符串为：

adventure across the galaxy for the ultimate power struggle

并将搜索范围限制在 overview 列：

with fts as(
select*, fts_main_movies.match_bm25(
        title,
'adventure across the galaxy for the ultimate power struggle',
         fields :='overview'
)as score
from movies     
)
select title, overview, score
from fts
where score isnotnull
orderby score desc
limit 5;

以下是排名靠前的 5 部电影：

混合搜索

现在我们已经了解了全文搜索和嵌入搜索^[8]，如何将它们结合起来构建混合搜索系统呢？

首先，我们需要为电影数据集生成标题和概述的嵌入向量，并创建 FTS 索引。最终的电影数据表如下所示：

结果排序指标

搜索算法的核心是对搜索结果进行排序。对于混合搜索，我们需要一种能够融合两种搜索模式得分的方法。两种常用的指标融合方法是：(i) 凸组合和 (ii) 倒数排名融合。

倒数排名融合 (RRF)

RRF 将文档排名的倒数加起来，其中排名是指文档按照得分降序排序后的序号。为了调整低排名文档的权重，可以在排名上加一个常数 k。RRF 的计算公式如下：

凸组合（加权归一化得分）

据报道，凸组合在经过校准后比 RRF 表现更好。它是一个线性函数，将加权后的归一化得分加起来。权重参数 ⍺ 需要使用标注数据集进行校准，如果没有标注数据集，可以使用 0.8 作为默认值，该值适用于大多数领域^[9] 。凸组合的计算公式如下：

在本文中，我们将使用凸组合方法，因为它性能更好，而且参数 ⍺ 可以根据实际情况进行调整。

wiht fts as (
select
        title,
        overview,
        fts_main_movies.match_bm25(
            title,
'an adventure across the galaxy for the ultimate power struggle',
            fields :='overview'
)as score
from movies
),
embd as(
select
        title,
        overview,
        array_cosine_similarity(overview_embeddings,cast([0.343..,...]asfloat[1536]))as score
from movies
),
normalized_scores as(
select
        fts.title,
        fts.overview,
        fts.score as raw_fts_score,
        embd.score as raw_embd_score,
(fts.score /(selectmax(score)from fts))as norm_fts_score,
((embd.score +1)/(selectmax(score)+1from embd))as norm_embd_score
from
        fts
innerjoin
        embd 
on fts.title = embd.title
)
select
    title,
    raw_fts_score,
    raw_embd_score,
    norm_fts_score,
    norm_embd_score,
-- (alpha * norm_embd_score + (1-alpha) * norm_fts_score)
(0.8*norm_embd_score +0.2*norm_fts_score)AS score_cc
from
    normalized_scores
orderby
    score_cc desc
limit 5;

以下是排名靠前的 5 部电影：

与仅使用 FTS 的结果相比，混合搜索结果的排名发生了变化，排名第一的电影也不同。此外，混合搜索结果中还出现了一部在仅使用 FTS 时没有进入前 5 名的电影。

总结

随着越来越多的非结构化文本数据被存储到分析数据库中，高效的文本搜索功能变得越来越重要。DuckDB 提供了强大的全文搜索和嵌入搜索功能，可以直接在 SQL 中使用，无需依赖其他工具。此外，DuckDB 还支持使用公用表表达式 (CTE) 计算融合排名指标，方便用户构建混合搜索系统。

引用链接

[1] FTS 扩展: https://duckdb.org/docs/extensions/full_text_search.html
[2] Okapi BM25: https://en.wikipedia.org/wiki/Okapi_BM25
[3] 这里: https://en.wikipedia.org/wiki/Okapi_BM25
[4] Kaggle 电影数据集 : https://www.kaggle.com/datasets/rounakbanik/the-movies-dataset?resource=download
[5] sample_data 数据库: https://motherduck.com/docs/getting-started/sample-data-queries/attach-sample-database
[6] 扩展: https://duckdb.org/docs/extensions/full_text_search.html
[7] 自动加载: https://duckdb.org/docs/extensions/overview.html#autoloading-extensions
[8] 嵌入搜索: https://motherduck.com/blog/search-using-duckdb-part-1/
[9] 大多数领域: https://dl.acm.org/doi/pdf/10.1145/3596512