SQL Server大表长短语模糊排序搜索实现方案咨询

一、TF-IDF + 余弦相似度的适用性分析 #

完全适配你的场景：

• 既考虑词频权重，能区分短语中重要单词和普通单词的影响；
• 通过n-gram（n元语法）改造可保留词序信息，解决单纯词袋模型忽略词序的问题；
• 预处理后计算效率远高于编辑距离，适合百万级以上的大表场景；
• 天然支持短语中增减单词的相似度判断，无需精确匹配子串或全词。

二、无CLR依赖的SQL Server实现步骤 #

Linux版SQL Server不支持unsafe权限的CLR，以下全程用T-SQL实现，分为预处理和查询两个核心阶段。

1. 预处理：构建n-gram词频与文档频率表 #

第一步：创建辅助表

-- 存储所有n-gram的全局文档频率（DF）
CREATE TABLE NGramStats (
    NGram NVARCHAR(100) NOT NULL, -- 根据n值调整长度，比如3元语法设为100足够覆盖多数短语片段
    DocumentFrequency INT NOT NULL DEFAULT 0,
    PRIMARY KEY (NGram)
);

-- 存储每行文本的n-gram词频（TF）
CREATE TABLE DocumentNGramTF (
    Id INT NOT NULL, -- 关联原表的主键Id
    NGram NVARCHAR(100) NOT NULL,
    TermFrequency INT NOT NULL DEFAULT 1,
    PRIMARY KEY (Id, NGram),
    FOREIGN KEY (Id) REFERENCES YourTableName(Id) -- 替换为你的表名
);

第二步：编写带词序的n-gram生成函数

因为词序重要，必须用带序号的拆分函数保证词序不丢失：

-- 带序号的单词拆分函数（严格保留原文本词序）
CREATE FUNCTION dbo.SplitWordsWithOrder(@Text NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS WordIndex,
        LTRIM(RTRIM(value)) AS Word
    FROM STRING_SPLIT(LOWER(@Text), ' ')
    WHERE value <> ''
);

-- 生成3元语法的函数（连续3个单词为一个片段，保留词序）
CREATE FUNCTION dbo.GenerateTrigrams(@Text NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN (
    SELECT CONCAT(w1.Word, ' ', w2.Word, ' ', w3.Word) AS Trigram
    FROM dbo.SplitWordsWithOrder(@Text) w1
    JOIN dbo.SplitWordsWithOrder(@Text) w2 ON w2.WordIndex = w1.WordIndex + 1
    JOIN dbo.SplitWordsWithOrder(@Text) w3 ON w3.WordIndex = w1.WordIndex + 2
);

提示：可根据短语长度调整n值，比如短短语用2元语法，长短语用3/4元语法，平衡词序保留和计算效率。

第三步：批量预处理原表数据

-- 清空预处理表（全量更新时用，增量更新可跳过）
TRUNCATE TABLE DocumentNGramTF;
TRUNCATE TABLE NGramStats;

-- 插入每行文本的3元语法并统计词频
INSERT INTO DocumentNGramTF(Id, NGram)
SELECT Id, Trigram
FROM YourTableName -- 替换为你的表名
CROSS APPLY dbo.GenerateTrigrams(Data)
GROUP BY Id, Trigram;

UPDATE dntf
SET TermFrequency = cnt
FROM DocumentNGramTF dntf
JOIN (
    SELECT Id, Trigram, COUNT(*) AS cnt
    FROM YourTableName
    CROSS APPLY dbo.GenerateTrigrams(Data)
    GROUP BY Id, Trigram
) t ON dntf.Id = t.Id AND dntf.NGram = t.NGram;

-- 计算每个n-gram的全局文档频率（DF）
INSERT INTO NGramStats(NGram, DocumentFrequency)
SELECT NGram, COUNT(DISTINCT Id) AS DF
FROM DocumentNGramTF
GROUP BY NGram;

2. 查询阶段：计算余弦相似度得分 #

输入搜索短语后，生成其n-gram并计算与每行文本的余弦相似度，按得分排序返回：

DECLARE @SearchPhrase NVARCHAR(MAX) = 'the quick brown ox jumps over a lazy dog';
DECLARE @TotalDocuments INT = (SELECT COUNT(*) FROM YourTableName); -- 替换为你的表名

-- 生成搜索短语的n-gram及其TF-IDF
WITH SearchTrigrams AS (
    SELECT Trigram, COUNT(*) AS TF
    FROM dbo.GenerateTrigrams(@SearchPhrase)
    GROUP BY Trigram
),
SearchTFIDF AS (
    SELECT 
        st.Trigram,
        TFIDF = st.TF * LOG10(@TotalDocuments / NULLIF(ns.DocumentFrequency, 0))
    FROM SearchTrigrams st
    LEFT JOIN NGramStats ns ON st.Trigram = ns.NGram
),
DocumentTFIDF AS (
    SELECT 
        dntf.Id,
        dntf.NGram,
        TFIDF = dntf.TermFrequency * LOG10(@TotalDocuments / NULLIF(ns.DocumentFrequency, 0))
    FROM DocumentNGramTF dntf
    JOIN NGramStats ns ON dntf.NGram = ns.NGram
),
DotProduct AS (
    SELECT 
        dt.Id,
        SUM(st.TFIDF * dt.TFIDF) AS DotProductValue
    FROM SearchTFIDF st
    JOIN DocumentTFIDF dt ON st.Trigram = dt.NGram
    GROUP BY dt.Id
),
Magnitudes AS (
    SELECT
        Id,
        SQRT(SUM(POWER(TFIDF, 2))) AS DocumentMagnitude
    FROM DocumentTFIDF
    GROUP BY Id
),
SearchMagnitude AS (
    SELECT SQRT(SUM(POWER(TFIDF, 2))) AS Value
    FROM SearchTFIDF
)
SELECT 
    yt.Id,
    yt.Data,
    -- 余弦相似度转为0-100的百分比得分
    SimilarityScore = ROUND((dp.DotProductValue / (m.DocumentMagnitude * sm.Value)) * 100, 2)
FROM YourTableName yt
LEFT JOIN DotProduct dp ON yt.Id = dp.Id
LEFT JOIN Magnitudes m ON yt.Id = m.Id
CROSS JOIN SearchMagnitude sm
WHERE dp.DotProductValue IS NOT NULL -- 仅返回有匹配n-gram的结果
ORDER BY SimilarityScore DESC;

3. 性能优化建议 #

• 增量更新预处理表：仅处理新增/修改的行，避免全量计算；
• 索引优化：给DocumentNGramTF(NGram, Id)和NGramStats(NGram)创建非聚集索引，加速查询时的JOIN；
• 过滤低频n-gram：预处理时排除仅出现1-2次的n-gram，减少噪音和计算量；
• 缓存搜索短语的TF-IDF：高频搜索短语可提前计算并存储，重复查询时直接复用。

内容的提问来源于stack exchange，提问作者Excel Kobayashi