Finding elements with maximum matching attributes
Here is my table structure - table name "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
What I want to do is get a list of productId sorted by maximum attribute-value pair. In a real table, I am using a numeric ID of the attribute name and value, I used the text here for presentation convenience.
So, if I want to find matching products with productId = 1, I want it to search for the product that has the maximum match (for example, Height = 3, Weight = 1, class = X1, and Category = C1). There may not be a 100% match (all 4 matches), but if there are, they must come first, followed by productId, which has any 3 attributes, and then any 2, etc.
I could add additional indexes if needed, better if I don't need it as there are millions of rows. This is MariaDB v10 to be precise.
Desired result. If I try to find a suitable product for productId = 1, it should return in the same order.
productId
-----------
3
2
Reason - 3 has all attributes matching 1, 2 has multiple matches, and 4 has no match.
source to share
You can use conditional aggregation to retrieve the productId with the most matches in the first place.
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
The above query will return all rows even with a match of 0. If you only want to return rows with 1 or more matches, then use the below query which should be able to use your composite index:
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
source to share