Блог

Я веду домашнее хозяйство с дублирующимися данными. У меня есть разные таблицы, такие как Recipes , Ingredients , и RecipeIngredients .

В Ingredients таблице пользователи ранее добавили несколько ингредиентов с одинаковым именем / названием, например, «курица» появится много элементов вместо одного. Я хочу удалить дубликаты, но при этом сохранить ссылку на рецепт.

Я пытаюсь использовать SQL MERGE , но он удаляет неправильные данные, и я ослепил себя этим. Что я делаю не так / возможно, это просто быстрое решение??

Когда я запускаю приведенный ниже код, я получаю это табличное отношение:

 Chicken Recipe
Chicken

Burger Recipe
Salt, Pepper, Patty
 

Но чего я действительно хочу, так это:

 Chicken Recipe
Chicken, Salt

Burger Recipe
Salt, Pepper, Patty
 

Оператор MERGE удаляет «Соль» из RecipeIngredient вместо удаления дубликата. Что я делаю не так?

 -- create table structure
CREATE TABLE #Recipes (
    Id int,
    Title nvarchar(50)
)

CREATE TABLE #Ingredients (
    Id int,
    Title nvarchar(50)
)

CREATE TABLE #RecipeIngredients (
    Id int,
    Recipe_id int,
    Ingredient_id int
)

-- load data
INSERT INTO #Recipes (Id,Title) VALUES (1,'Chicken Recipe');
INSERT INTO #Recipes (Id,Title) VALUES (2,'Burger Recipe');

INSERT INTO #Ingredients (Id,Title) VALUES (1,'Chicken'); 
INSERT INTO #Ingredients (Id,Title) VALUES (2,'Chicken'); -- duplicate ingredient
INSERT INTO #Ingredients (Id,Title) VALUES (3,'Salt'); 
INSERT INTO #Ingredients (Id,Title) VALUES (4,'Pepper');
INSERT INTO #Ingredients (Id,Title) VALUES (5,'Patty');

INSERT INTO #RecipeIngredients (Id,Recipe_id,Ingredient_id) VALUES (1,1,2); -- chicken has chicken
INSERT INTO #RecipeIngredients (Id,Recipe_id,Ingredient_id) VALUES (2,1,3); -- chicken has salt

INSERT INTO #RecipeIngredients (Id,Recipe_id,Ingredient_id) VALUES (3,2,3); -- burger has salt
INSERT INTO #RecipeIngredients (Id,Recipe_id,Ingredient_id) VALUES (4,2,4); -- burger has pepper
INSERT INTO #RecipeIngredients (Id,Recipe_id,Ingredient_id) VALUES (5,2,5); -- burger has patty


-- try to clean up
MERGE #RecipeIngredients 
USING
(
    SELECT MAX(id) as MyId
    FROM #Ingredients
    GROUP BY Title
) NewIngredients ON #RecipeIngredients.Id = NewIngredients.MyId
WHEN MATCHED THEN
    UPDATE SET #RecipeIngredients.Ingredient_id = NewIngredients.MyId
WHEN NOT MATCHED BY SOURCE THEN DELETE;
GO

-- delete duplicate ingredients, i.e., that no longer has a value in table #RecipeIngredients
DELETE FROM #Ingredients WHERE Id NOT IN (SELECT Ingredient_Id FROM #RecipeIngredients)
  
 -- clean up
DROP TABLE #Recipes
DROP TABLE #Ingredients
DROP TABLE #RecipeIngredients
 

Сначала вы должны обновить все повторяющиеся идентификаторы до единого идентификатора, а затем выполнить очистку.

Я изменил определение выигрышного идентификатора с MAX на MIN на, поскольку он останется и будет иметь то же значение, если между ними была вставка (надеюсь, вы увеличиваете идентификаторы как идентификатор). Или вы можете использовать SNAPSHOT уровень изоляции для обеспечения максимального значения (или SERIALIZABLE для прекращения создания новых дубликатов во время этой транзакции). Кроме того, при очистке #Ingredients таблицы не следует использовать in фильтр, потому что по замыслу можно иметь неиспользуемые ингредиенты, и пользователи не хотят терять свои данные. Итак, я удалил дубликаты таким же образом с MIN(id) помощью .

Это MERGE оператор updated, который устанавливает Ingredient_ID единственное значение

 MERGE #RecipeIngredients as t
USING
(
    SELECT id, min(id) over(partition by title) as MaxId
    FROM #Ingredients
) as NewIngredients
  ON t.Ingredient_Id = NewIngredients.Id
WHEN MATCHED THEN
  UPDATE SET t.Ingredient_id = NewIngredients.MaxId;
 

Затем я удаляю дубликаты из #RecipeIngredients :

 /*Cleanup duplicates from RecipeIngredients*/
delete t from (
  select
    row_number() over(
      partition by
        Recipe_id,
        Ingredient_id

      order by
        id asc
    ) as rn
  from #RecipeIngredients
) as t
where rn > 1
 

И, наконец, очистка дедуплицированных записей в #Ingredients таблице:

 delete t from (
  select
    id,
    min(id) over(partition by title) as minid
  from #Ingredients
) as t
where id <> minid
 

И весь код в db<>fiddle здесь

UPD Я добавил более надежный способ очистки:

• сначала удалите дубликаты из #Ingredients таблицы
• захват удаленных записей
• затем обновите Ingredient_Id для этого удаленные идентификаторы в #RecipeIngredients таблице, удалив дубликаты в ней (которые могут быть созданы после объединения, я не знаю, так ли это) с MERGE помощью statement .

Вот новый код и db<>fiddle для него. Также я добавил другой дублированный ингредиент в #Ingredients таблицу и другой ингредиент с другим Ingredient_Id в #RecipeIngredients таблице (чтобы показать MERGE часть удаления).

 /*Declare the table for unified ingredients*/
declare @deletedIngredients table (
  id int,
  unifiedId int
);

/*Cleanup of duplicate ingredients and
  catch the deleted records with the corresponding unified Id
*/
with i_del as (
  /*Leave only the first (by ID) record with the same name*/
  select id, min(id) over(partition by title) as unifiedId
  from #Ingredients
)
delete from i
  /*Catch the deleted records and corresponding unified Ids*/
  output
    deleted.id,
    i_del.unifiedId

  into @deletedIngredients

from #Ingredients as i
  join i_del
    on i.id = i_del.id
/*Remove only duplicates where Id is not equal to the master record Id*/
where i.id <> i_del.unifiedId
;

/*Then do an update of IDs on the RecipeIngredients
  and delete the duplicates from it (that can be created during the unification of Ingredients_Id)
*/
merge into #RecipeIngredients as t
using (
  select
    ri.id,
    i.unifiedid,
    /*Number the rows per Receipe_Id and new Ingredient_Id*/
    row_number() over(
      partition by
        ri.Recipe_Id,
        i.unifiedId
      
      order by ri.id asc
    ) as rn
  from #RecipeIngredients as ri
    join @deletedIngredients as i
      on ri.Ingredient_Id = i.id
) as s
  on t.id = s.id
/*The first record should have the new unified id*/
when matched and s.rn = 1 then
  update set ingredient_id = s.unifiedId
/*And unintentionally created duplicate should be removed*/
when matched and s.rn > 1 then delete
;

commit;