sql - 获取相关文章的高级方法(语义)

Question

我面临着编写更智能/高级的“相关内容”算法的挑战，并且不知道从哪里开始，所以我决定提出一个问题，是否有人会指出我正确的方向。

我们的数据库包含很多文章，到目前为止，我们使用关键字/标签查询了相关文章，但发现通常我们不会获得非常相关的结果，因为大多数关键字过于笼统（例如政府，税收等）。

一个很大的想法是，我们将以某种方式查询整个内容，并尝试匹配与当前显示的主题最相关的内容。但是同时，算法还应该“知道”匹配的内容是否具有负面含义。

例如，让我们看一下3条虚构的文章：


一篇文章说，如果您通过互联网预订机票，如何可以便宜机票
一篇文章说机票价格由于...下降。
一篇文章说，有300多人在飞机失事中丧生


在这种情况下，所有三篇文章（全部内容）都与飞行和飞机有关，但第三篇文章具有负面含义。因此，前两个应相互关联，但第三个不应以任何方式与前两个相关。

所以我的问题是-如何在拥有超过一百万篇文章的数据库中以编程方式完成类似的工作？我知道这不能仅通过SQL查询来完成-您将以某种方式需要字典或其他内容，但是我不知道从哪里开始探索该主题。所以，请问有人能指出我正确的方向吗？

Answer 1

TL，DR
在Wiki上查看有关TF*IDF的信息，然后查看有关Cosine similarity的信息。

长答案（带有示例）

什么是TF * IDF

TF * IDF代表术语频率*反文档频率。
这是为大型群组中的文档创建良好标签的方法之一。
其背后的思想是摘录单个文档中用什么词来描述同一文档。
为此，它使用两种不同的统计数据，第一种是术语频率。

术语频率是一个单词在单个文档或句子中的重要性的指示。
例如句子

SQL Post. Asking about semantic in SQL with generic document example, SQL generic

获取单个单词，并消除混乱的单词（噪声单词）

Word     | Count | Frequency
----------------------------
SQL      |   3   |  0.231
generic  |   2   |  0.154
Post     |   1   |  0.077
Asking   |   1   |  0.077
semantic |   1   |  0.077
document |   1   |  0.077
example  |   1   |  0.077

术语频率可以通过多种方式进行计算，表中有一个简单的数字，原始计数和一个归一化的数字（即情感中的频率），第二个是将TF * IDF归一化为0到0的更好选择。 1。
该示例说明了单词“ SQL”如何表征示例句子，因此它是标记的理想选择。

反向文档频率是单词在所有文档语料库中重要性的指标：出现在每个文档中的单词对搜索没有帮助，出现在很少文档中的单词会带来更多信息。
反向文档频率计算为

IDF = ln(Document count / Document with the word)

例如，我们使用三个句子作为我们的语料库，加上我们之前的一个

SQL Post. Asking about semantic in SQL with generic document example, SQL generic
C# Post. This is a C# answer with an example
SQL Post. Asking a good SQL question with an example
Math Post. This is a Math answer with an example of equation

如果我们计算上一个句子的最佳指示符的IDF，则总共有四个文档，其中有四个出现“ example”的文档

IDF = ln(4/4) -> ln(1) -> 0

每个句子中都包含“示例”一词，因此对于我们的文档而言，这不是一个很好的搜索项目。
我们使用“问题”或“答案”一词

IDF = ln(4/1) -> ln(4) -> 1.386 for "question"
IDF = ln(4/2) -> ln(2) -> 0.693 for "answer"

它们都是在我们的文档中搜索的更好选择。

然后结合起来，我们就可以得出一个单词对文档的重要性如何以及在文档语料库中的良好选择的单一指标。
使用IDF更新上表

Word     | Frequency|  IDF  | TF*IDF
-------------------------------------
SQL      |  0.231   | 0.693 | 0.160
generic  |  0.154   | 1.386 | 0.213
Post     |  0.077   | 0     | 0
Asking   |  0.077   | 0.693 | 0.053
semantic |  0.077   | 1.386 | 0.107
document |  0.077   | 1.386 | 0.107
example  |  0.077   | 0     | 0

使用TF * IDF，我们可以看到，即使“ SQL”是句子中最突出的词，如果我们考虑整个文档列表，它也会被“泛型”击败，因为“ SQL”分两行显示。

TF * IDF可以为整个句子/文档库中有意义的每个句子提供相关单词的列表。

计算每个文档的Word / TF * IDF列表是起点，要检查两个或多个文档的相似性，我们可以使用余弦相似性

什么是余弦相似度

余弦相似度可以视为度量：一种计算两点之间距离的方法。在我们的案例中，重点就是我们的句子。
为了测量距离，我们需要点的坐标，对于句子，坐标是它们的单词TF * IDF的列表。

余弦相似度的公式为

sim = (A*B)/(||A||*||B||)

其中A和B是句子坐标。
从公式的向量形式展开它变成

sim = Sum(A[word] * B[word])/(Sqrt(Sum(A[word]^2)) * Sqrt(Sum(B[word]^2)))

要么

sim = cross_product/(norm(A) * norm(B))

哪里

cross_product = Sum(A[word] * B[word])
norm(X) = Sqrt(Sum(X[word]^2))

例如，我们可以使用之前的第一个和第三个句子，第三个的单词向量是

Word     | Frequency|  IDF  | TF*IDF
-------------------------------------
SQL      |  0.2     | 0.693 | 0.139
Asking   |  0.1     | 0.693 | 0.069
good     |  0.1     | 1.386 | 0.139
question |  0.1     | 1.386 | 0.139

对于叉积，我们只能对两个矢量中都出现的单词进行数学运算，对于其他单词，我们将其他值设为0。

cross_product = 0.160*0.053 (SQL) + 0.023*0.069 (Asking) = 0,02587
norm(1) = sqrt(0.160^2 + 0.213^2 + 0.053^2 + 0.107^2 + 0.107^2) = 0.31090
norm(3) = sqrt(0.139^2 + 0.069^2 + 0.139^2 + 0.139^2) = 0.24992
sim = cross_product/(norm(1) * norm(3)) = 0.333

由于TD * IDF严格为正，因此余弦相似度值将严格为正。

由于对使用的TD * IDF进行了归一化，因此余弦相似度将具有边界[0; 1]。0表示无共享信息，1表示文档完全相同（如果不是针对干扰词的话）。

一个SQLServer示例

SQLServer具有全文搜索功能，可以用来完成此工作，其基础是函数 sys.dm_fts_parser，该函数给定一个字符串，返回带有单个单词的表，一个值表示一个单词是噪音和其他信息。
要计算TD * IDF，最重要的是将文档分解为单词，每个可以做到这一点的DB都可以胜任，我的选择仅仅是基于我自己的专业知识。

注意sys.dm_fts_parser只能由具有sysadmin角色的用户执行。

我们首先创建一个包含测试数据的临时表

SELECT 1 AS Id, N'SQL Post. Asking about semantic in SQL with generic document'
              + N' example, SQL generic' AS txt 
INTO #testTable
UNION ALL SELECT 2, N'C# Post. This is a C# answer with an example' 
UNION ALL SELECT 3, N'SQL Post. Asking a good SQL question with an example' 
UNION ALL SELECT 4, N'Math Post. This is a Math answer with an example of'
                  + N' equation'

然后我们继续用句子词TD * IDF创建临时表

With TF AS (
SELECT DISTINCT id, display_term, special_term
     , CAST(COUNT(display_term) 
            OVER (PARTITION BY id, display_term) AS DECIMAL(10, 8))
     / COUNT(occurrence) OVER (PARTITION BY id) TF
FROM   #testTable
       CROSS APPLY sys.dm_fts_parser('"'+REPLACE(txt,'"','""')+'"', 1033, 0,0)
WHERE  TXT IS NOT NULL
  AND  display_term NOT LIKE 'nn%'
  AND  special_term <> 'End Of Sentence'
), IDF AS (
SELECT display_term word
     , sentences = COUNT(DISTINCT tt.ID)
     , sentence_with_word 
     = COUNT(DISTINCT CASE WHEN tt.txt LIKE '%' + tf.display_term + '%' 
                           THEN tt.id 
                           ELSE NULL 
                      END)
   , IDF = LOG(CAST(COUNT(DISTINCT tt.ID) AS DECIMAL (10, 8)) 
             / COUNT(DISTINCT CASE WHEN tt.txt LIKE '%' + tf.display_term + '%' 
                                   THEN tt.id 
                                   ELSE NULL 
                              END))
FROM   #testTable tt
       CROSS JOIN TF
WHERE  TF.special_term = 'Exact Match'
group by display_term
)
SELECT tf.Id sentence, word
     , TD = TF.TF, IDF.IDF
     , TD_IDF = TF.TF * IDF.IDF
INTO   #sentence_word_TD_IDF
FROM   TF
       INNER JOIN IDF ON tf.display_term = IDF.word

在每个句子中都有每个单词的TD * IDF，我们可以继续进行句子1和3之间的余弦相似度

WITH S1 AS (
SELECT word, TD_IDF 
FROM   #sentence_word_TD_IDF 
WHERE  sentence = 1
), S2 AS (
SELECT word, TD_IDF 
FROM   #sentence_word_TD_IDF 
WHERE  sentence = 3
), cat AS (
SELECT word = COALESCE(S1.word, S2.word)
     , word_S1_TD_IDF = COALESCE(S1.TD_IDF, 0)
     , word_S2_TD_IDF = COALESCE(S2.TD_IDF, 0)
FROM   S1
       FULL JOIN S2 ON S1.word = S2.word
)
SELECT cross_product = SUM(word_S1_TD_IDF * word_S2_TD_IDF)
     , norm_1 = SQRT(SUM(word_S1_TD_IDF * word_S1_TD_IDF))
     , norm_2 = SQRT(SUM(word_S2_TD_IDF * word_S2_TD_IDF))
     , co_sim = SUM(word_S1_TD_IDF * word_S2_TD_IDF)
              / (SQRT(SUM(word_S1_TD_IDF * word_S1_TD_IDF)) 
               * SQRT(SUM(word_S2_TD_IDF * word_S2_TD_IDF)))
FROM   cat

查询具有一些局部步骤，以便于检查，例如，在 CTE IDF中，存在“句子”和“ sentence_with_word”列，而不仅仅是IDF。

这些查询可以在没有全文索引的表上运行。
有了全文索引，就有可能获得词干，使用词的词根而不是句子中的屈折形式。

为了更快地响应查询，最好为每个文档的每个单词创建一个带有TF * IDF值的向量表，并为每个文档与其他文档建立余弦相似度的多对多联接表，以便只需计算新文档或搜索查询的值。

下一步：极化

所有这些还不够，OP要求一个想法来将具有不同“情感”的相似句子区别开来，那就是，即使相似度很高，好消息和坏消息也永远不应该相关。
为此，我们必须欺骗余弦相似度，其中的杠杆在定义中

由于TD * IDF严格为正，因此余弦相似度值将严格为正。

可以对搜索错误关键字的文章进行分类，如果找到了某些东西，则整个文档的TD * IDF将变为负数。
好消息和坏消息之间的余弦相似度将通过TD * IDF（正值（对于好消息）和负值（对于坏消息））来计算，这意味着如果存在共同词，叉积将为负，并且负叉积变为负相似性，因为 SQRT(TD_IDF ^ 2)将为正，无论TD_IDF的性质如何。
对于具有相同“情感”的两个新闻的余弦相似性，我们将使用具有相同符号的TD * IDF，这将使正叉积和正相似性相结合。

通过此更改，余弦相似度的边界将变为] -1; 1]


-1永远不会达到，因为至少一个单词需要不同才能承载不同的情感
负数表示具有相反“情感”的相似文档
0表示文档之间没有相关性
正数表示具有相同“情感”的相似文档
1表示相同的文件（如果不包含干扰词）（并且必须使用相同的编号）