sql - 使用评分在 SQL 中查找最佳匹配

Question

假设我有一个数据表，例如:

ID | Col1 | Col2 | Col3
 1    a      b      23
 2    a      c      14
 3    f      g      11

假设我有一个 POSSIBLE_MATCHES 表，例如:

MatchID  | Col1 | Col2 | Col3
101         a      a      11
102         a      b      11
103         a      b      14
104         a      c      23
105         f      a      1

假设我有一个权重表(如果您想为了讨论和简单起见假设所有权重均为 1 - 我可以稍后临时改进我的解决方案以合并权重):

Col | Weight
Col1    1
Col2    1.5
Col3    2

因此，对于每个可能的匹配，我们都会计算每个匹配列的SCORE。

    Score = Col1 Weight * (CASE WHEN DATA.COL1 = POSSIBLE_MATCHES.Col1 THEN 1 ELSE 0) + 
            Col2 Weight * (CASE WHEN DATA.COL2 = POSSIBLE_MATCHES.Col2 THEN 1 ELSE 0) +
            Col3 Weight * (CASE WHEN DATA.COL3 = POSSIBLE_MATCHES.Col3 THEN 1 ELSE 0)

例如第一行的最佳匹配:Col1 = a，Col2 = b，Col3 = 23:

MatchID  | Col1 | Col2 | Col3 | Score
101         a      a      11     1*1 + 1.5*0 + 2*0 = 1
102         a      b      11     1*1 + 1.5*1 + 2*0 = 2.5
103         a      b      14     1*1 + 1.5*1 + 2*0 = 2.5
104         a      c      23     1*1 + 1.5*0 + 2*1 = 3
105         f      a      1      1*0 + 1.5*0 + 2*0 = 0

因此，在本例中，ID:1 的最佳匹配是 MatchID:104。如果分数相同则取最低的MatchID。

如果你想尝试一下，这里有一个 sql fiddle : http://sqlfiddle.com/#!6/9df45/1

对于数据中的每个 ID，我如何在可能的匹配中找到最佳匹配？

Answer 1

在此解决方案中，我们进行完全连接以获取所有可能性并评估所有可能性的分数。然后，我们用 ROW_NUMBER 从最好到最低为它们分配一个数字。最后，我们排除所有那些不是最好的“WHERE Rank = 1”

SELECT * 
FROM 
(SELECT data.ID,
        possible_matches.MatchID,
        Score =  (CASE WHEN data.Col1 = possible_matches.Col1 THEN 1 ELSE 0 END) * 1 +
                (CASE WHEN data.Col2 = possible_matches.Col2 THEN 1 ELSE 0 END) * 1.5 +
                (CASE WHEN data.Col3 = possible_matches.Col3 THEN 1 ELSE 0 END) * 2,
        [Rank] = ROW_NUMBER() OVER(PARTITION BY data.ID ORDER BY (CASE WHEN data.Col1 = possible_matches.Col1 THEN 1 ELSE 0 END) * 1 +
                                                            (CASE WHEN data.Col2 = possible_matches.Col2 THEN 1 ELSE 0 END) * 1.5 +
                                                            (CASE WHEN data.Col3 = possible_matches.Col3 THEN 1 ELSE 0 END) * 2 DESC)
from data, possible_matches) AS AllScore
WHERE AllScore.[Rank] = 1