How to find the most common strings between rows

Question

In SQL 2017, I have a 'food' table as follows:

ID    item    Ingredients
----  -----   -----------
1     item1   flour,sugar,cocoa,butter
2     item2   flour,sugar,butter,water
3     item3   sugar,cocoa,water
4     item4   sugar,cocoa,butter
5     item5   flour,water

Although the "Ingredients" are a CSV in a single field, I have no problem moving them to a related table:

ID    Ingredient
---   ----------
 1    flour
 1    sugar
 1    cocoa
 1    butter
 2    flour

etc or just select them with a CROSS APPLY string_split

select ID, item, ingredient
from food
CROSS APPLY string_split(food.ingredients,',') as ing

Now here is what I am trying to attempt:

For any given row ex:(where id = 1) I want to return the top n rows (other than id = 1) that have the most common ingredients with id = 1.

So the top 2 with the most common ingredients with id = 1 should be:

ID    item    Ingredients
----  -----   -----------
2     item2   flour,sugar,butter,water    ( 3 in common)
3     item3   sugar,cocoa,water           ( 2 in common)
4     item4   sugar,cocoa,butter          ( 3 in common)
5     item5   flour,water                 ( 1 in common)
      

The result should return rows with id = 2 and id = 4 as both have 3 ingredients in common with row id = 1

This is my first question on StackExchange, and I hope it is clear. I can write this as a C# or Python batch program to create a table of 'closest ingredients', but I'm hoping I can do this in SQL.

Thanks !

Answer 1

Please store the ingredients in a table not a list. So let's call that ItemIngredient with two columns ItemId and Ingredient and leave it at that. The primary key will be (ItemId, Ingredient). Because searches by Ingredient will probably happen a lot, let's put a non-unique index on Ingredient (which is necessary for below to work the most efficiently).

SELECT
  ItemId
 ,MatchItemId
 ,IngredientMatchCount
 ,MatchRank
FROM
  (
    SELECT
      Base.ItemId
     ,Match.ItemId AS MatchItemId
     ,COUNT(*) AS IngredientMatchCount
     ,RANK() OVER (PARTITION BY Base.ItemId ORDER BY COUNT(*) DESC, Match.ItemId) AS MatchRank
    FROM
      ItemIngredient Base
    INNER JOIN
      ItemIngredient Match
        ON Match.Ingredient = Base.Ingredient 
             AND Match.ItemId <> Base.ItemId
    -- Put your WHERE clause here if you want to only fine matches for certain Ids
    GROUP BY
      Base.ItemId
     ,Match.ItemId
  ) matched
WHERE
  MatchRank <= 1 --Or whatever
ORDER BY
  ItemId
    

You can experiment with RANK vs DENSE_RANK at some point to see what provides the best results for you. If you need details about the Item you can join back in the outer query.