如何在行之間找到最常見的字元串

在 SQL 2017 中，我有一個“食物”表，如下所示：

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

儘管“成分”是單個欄位中的 CSV，但我將它們移動到相關表中沒有問題：

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

等等，或者只是選擇它們CROSS APPLY string_split

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

現在這是我想要嘗試的：

對於任何給定的行例如：我想返回具有最常見成分(where id = 1)的前 n 行（除了） 。id = 1``id = 1

因此，最常見成分的前 2 個id = 1應該是：

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)
     

結果應該返回帶有id = 2和的行，id = 4因為兩者都具有與行相同的 3 個成分id = 1

這是我在 StackExchange 上的第一個問題，希望很清楚。我可以把它寫成一個 C# 或 Python 批處理程序來創建一個“最接近的成分”表，但我希望我可以在 SQL 中做到這一點。

謝謝 ！

請將成分儲存在表格中，而不是列表中。所以讓我們ItemIngredient用兩列來稱呼它ItemId，Ingredient然後就這樣吧。主鍵將是(ItemId, Ingredient). 因為搜尋 byIngredient可能會發生很多，讓我們放置一個非唯一索引Ingredient（這是下面最有效地工作所必需的）。

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
   

您可以在某個時候嘗試使用RANKvs來查看什麼可以為您提供最佳結果。DENSE_RANK如果您需要有關的詳細資訊，Item您可以加入外部查詢。

引用自：https://dba.stackexchange.com/questions/271606