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

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

ingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;margin: 1.5em 8px;letter-spacing: 0.1em;color: rgb(63, 63, 63);">

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

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

ingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;margin: 1.5em 8px;letter-spacing: 0.1em;color: rgb(63, 63, 63);">注意：由于每行数据包含丰富的文本信息，我们在文中将其称为“文档”。

ingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;font-size: 1.2em;font-weight: bold;display: table;margin: 4em auto 2em;padding-right: 0.2em;padding-left: 0.2em;background: rgb(15, 76, 129);color: rgb(255, 255, 255);">全文搜索的工作原理

ingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;margin: 1.5em 8px;letter-spacing: 0.1em;color: rgb(63, 63, 63);">全文搜索会扫描整个文本以查找特定单词的完全匹配，这与利用向量嵌入识别语义相似性的语义搜索不同。

ingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;margin: 1.5em 8px;letter-spacing: 0.1em;color: rgb(63, 63, 63);">DuckDB 的FTS 扩展^[1]提供了字符串搜索功能，它通过创建倒排索引实现。

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

ingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;margin: 1.5em 8px;letter-spacing: 0.1em;color: rgb(63, 63, 63);">图 1：全文搜索创建倒排索引并利用其进行搜索的示意图。彩色框表示关键词。

ingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;font-size: 1.1em;font-weight: bold;margin-top: 2em;margin-right: 8px;margin-bottom: 0.75em;padding-left: 8px;border-left: 3px solid rgb(15, 76, 129);color: rgb(63, 63, 63);">演示：电影数据集

ingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;margin: 1.5em 8px;letter-spacing: 0.1em;color: rgb(63, 63, 63);">在本文中，我们将使用与嵌入搜索 [文章](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。

首先，加载数据集：

createtablemoviesas
selecttitle,overview
FROM's3://us-prd-motherduck-open-datasets/movies/parquet/movies.parquet';

describemovies;

column_name	column_type	null	key	default	extra
title	VARCHAR	YES	NULL	NULL	NULL
overview	VARCHAR	YES	NULL	NULL	NULL

DuckDB 中的全文搜索 (FTS)

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

pragmacreate_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'。

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

pragmacreate_fts_index(movies,title,title,overview)

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

selectdatabase,schema,name,column_namesFROM(showalltables)

database	schema	name	column_names	column_types
memory	fts_main_movies	dict	[termid, term, df]	[BIGINT, VARCHAR, BIGINT]
memory	fts_main_movies	docs	[docid, name, len]	[BIGINT, VARCHAR, BIGINT]
memory	fts_main_movies	fields	[fieldid, field]	[BIGINT, VARCHAR]
memory	fts_main_movies	stats	[num_docs, avgdl]	[BIGINT, DOUBLE]
memory	fts_main_movies	stopwords	[sw]	[VARCHAR]
memory	fts_main_movies	terms	[docid, fieldid, termid]	[BIGINT, BIGINT, BIGINT]
memory	main	movies	[title, overview]	[VARCHAR, VARCHAR]

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

schema	name	column_names	description（描述）
fts_main_movies	dict	[termid, term, df]	存储所有术语（文档中的关键词）到termid的映射
fts_main_movies	docs	[docid, name, len]	创建一个内部docid的映射，将存储在这里的input_id作为名称，用于FTS索引以及文档长度
fts_main_movies	fields	[fieldid, field]	将fieldid映射到输入值中给出的被索引的表列
fts_main_movies	stats	[num_docs, avgdl]	用于计算相似度分数的统计数据
fts_main_movies	stopwords	[sw]	列出了这个索引的所有停用词，对应于创建索引时选择的选项
fts_main_movies	terms	[docid, fieldid, termid]	将文档docid映射到字段fieldid（列）到术语termid
main	movies	[title, overview]	被索引用于全文搜索的表格

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

使用查询进行文本搜索

执行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列：

withftsas(
select*,fts_main_movies.match_bm25(
title,
'adventureacrossthegalaxyfortheultimatepowerstruggle',
fields:='overview'
)asscore
frommovies
)
selecttitle,overview,score
fromfts
wherescoreisnotnull
orderbyscoredesc
limit5;

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

title	overview	score
Mighty Morphin Pow…	Power up with six incredible teens who out-maneuver and defeat evil eve…	5.73340016
Threads of Destiny	94 years after The Battle of Yavin, the New Republic has been resurrect…	5.70256148
Stargate: The Ark …	SG-1 searches for an ancient weapon which could help them defeat the Or…	5.65603264
The Final Master	Determined to pass down his art, the Final Master of Wing Chun is caugh…	5.54863581
Star Trek	The fate of the galaxy rests in the hands of bitter rivals. One, James …	5.14211669

混合搜索

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

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

column_name	column_type	null	key	default	extra
title	VARCHAR	YES	NULL	NULL	NULL
overview	VARCHAR	YES	NULL	NULL	NULL
title_embeddings	DOUBLE[]	YES	NULL	NULL	NULL
overview_embeddings	DOUBLE[]	YES	NULL	NULL	NULL

结果排序指标

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

倒数排名融合 (RRF)

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

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

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

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

wihtftsas(
select
title,
overview,
fts_main_movies.match_bm25(
title,
'anadventureacrossthegalaxyfortheultimatepowerstruggle',
fields:='overview'
)asscore
frommovies
),
embdas(
select
title,
overview,
array_cosine_similarity(overview_embeddings,cast([0.343..,...]asfloat[1536]))asscore
frommovies
),
normalized_scoresas(
select
fts.title,
fts.overview,
fts.scoreasraw_fts_score,
embd.scoreasraw_embd_score,
(fts.score/(selectmax(score)fromfts))asnorm_fts_score,
((embd.score+1)/(selectmax(score)+1fromembd))asnorm_embd_score
from
fts
innerjoin
embd
onfts.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)ASscore_cc
from
normalized_scores
orderby
score_ccdesc
limit5;

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

title	overview	norm_fts_score	norm_embd_score	score_cc
Threads of Destiny	94 years after The Battle of Yavin, the New Republic has been resurrec…	0.99462122	1.0	0.99892424
Stargate: The Ark of Truth	SG-1 searches for an ancient weapon which could help them defeat the O…	0.98650582	0.97546876	0.97767617
Star Trek	The fate of the galaxy rests in the hands of bitter rivals. One, James…	0.89687036	0.98548452	0.96776169
Mighty Morphin Power Rangers: The Movie	Power up with six incredible teens who out-maneuver and defeat evil ev…	1.0	0.94973698	0.95978958
Ratchet & Clank	Ratchet and Clank tells the story of two unlikely heroes as they strug…	0.83376640	0.95988163	0.93465858

与仅使用 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