mysql - 查找具有最大匹配属性的项目

Question

这是我的表结构 - 表名“propAssign”

(indexed)        (composite index for attributeName and attributeValue)
 productId     attributeName     attributeValue 
    1              Height             3
    1              Weight             1
    1              Class              X1
    1              Category           C1
    2              Height             2
    2              Weight             2 
    2              Class              X2
    2              Category           C1
    3              Height             3
    3              Weight             1
    3              Class              X1
    3              Category           C1
    4              Height             4
    4              Weight             5
    4              Class              X2
    4              Category           C3

我想要做的是，获取 productId 列表，按最大匹配属性值对排序。在实际表中，我使用属性名称和值的数字 ID，我在这里使用文本以便于表示。

因此，如果我想查找 productId=1 的匹配产品，我希望它查找具有最大匹配度的产品(例如 Height=3、Weight=1、Class=X1 和 Category=C1)。可能没有任何 100% 匹配(所有 4 个匹配)，但如果有，它们应该排在第一位，接下来是 productId，它具有任何 3 个属性匹配，然后是任何 2 个，依此类推。

如果需要，我可以添加更多索引，如果不需要的话更好，因为有数百万行。准确地说是 MariaDB v10。

期望的结果 - 如果我尝试为 productId=1 找到匹配的产品，它应该以相同的顺序返回以下内容。

productId
-----------
3
2

原因 - 3 具有与 1 匹配的所有属性，2 有一些匹配，4 没有匹配。

Answer 1

您可以使用条件聚合首先检索匹配次数最多的 productId。

select productId, 
    count(case when attributeName = 'Height' and attributeValue='3' then 1 end)
    + count(case when attributeName = 'Weight' and attributeValue='1' then 1 end)
    + count(case when attributeName = 'Category' and attributeValue='C1' then 1 end) as rank
from mytable
group by productId
order by rank desc

上面的查询返回所有行，即使有 0 个匹配项。如果您只想返回具有 1 个或多个匹配项的行，请使用下面的查询，它应该能够利用您的复合索引:

select productId, count(*) as rank
from mytable
where (attributeName = 'Height' and attributeValue = '3')
or (attributeName = 'Weight' and attributeValue = '1')
or (attributeName = 'Category' and attributeValue = 'C1')
group by productId
order by rank desc