sql-server - valores - sql remover duplicadas




Como posso remover linhas duplicadas? (20)

A partir do nível de aplicação (infelizmente). Concordo que a maneira correta de evitar a duplicação é no nível do banco de dados por meio do uso de um índice exclusivo, mas no SQL Server 2005, um índice pode ter apenas 900 bytes e meu campo varchar (2048) expira.

Eu não sei o quão bem isso seria, mas eu acho que você poderia escrever um gatilho para impor isso, mesmo que você não pudesse fazê-lo diretamente com um índice. Algo como:

-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism 
ON stories 
after INSERT, UPDATE 
AS 
    DECLARE @cnt AS INT 

    SELECT @cnt = Count(*) 
    FROM   stories 
           INNER JOIN inserted 
                   ON ( stories.story = inserted.story 
                        AND stories.story_id != inserted.story_id ) 

    IF @cnt > 0 
      BEGIN 
          RAISERROR('plagiarism detected',16,1) 

          ROLLBACK TRANSACTION 
      END 

Além disso, varchar (2048) soa suspeito para mim (algumas coisas na vida são 2048 bytes, mas é bastante incomum); deve realmente não ser varchar (max)?

Qual é a melhor maneira de remover linhas duplicadas de uma tabela bastante grande do SQL Server (ou seja, 300.000 + linhas)?

As linhas, obviamente, não serão duplicatas perfeitas devido à existência do campo de identidade RowID .

Minha mesa

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

  1. Criar nova tabela em branco com a mesma estrutura

  2. Executar consulta como esta

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) > 1
  3. Então execute esta consulta

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) = 1

A outra maneira é criar uma nova tabela com os mesmos campos e com o índice exclusivo . Em seguida, mova todos os dados da tabela antiga para a nova tabela . Automaticamente SQL SERVER ignorar (há também uma opção sobre o que fazer se houver um valor duplicado: ignore, interrompa ou sth) valores duplicados. Portanto, temos a mesma tabela sem linhas duplicadas. Se você não quiser o Índice Único, após os dados de transferência, você poderá soltá-lo .

Especialmente para tabelas maiores, você pode usar o DTS (pacote SSIS para importar / exportar dados) para transferir todos os dados rapidamente para sua nova tabela exclusivamente indexada. Para 7 milhões de linhas, leva apenas alguns minutos.


Ah com certeza. Use uma tabela temporária. Se você quer uma declaração única, não muito de alto desempenho, que "funciona", você pode usar:

DELETE FROM MyTable WHERE NOT RowID IN
    (SELECT 
        (SELECT TOP 1 RowID FROM MyTable mt2 
        WHERE mt2.Col1 = mt.Col1 
        AND mt2.Col2 = mt.Col2 
        AND mt2.Col3 = mt.Col3) 
    FROM MyTable mt)

Basicamente, para cada linha na tabela, a sub-seleção localiza o RowID superior de todas as linhas que são exatamente como a linha sob consideração. Então você acaba com uma lista de RowIDs que representam as linhas "originais" não duplicadas.


Ao usar a consulta abaixo, podemos excluir registros duplicados com base na coluna única ou em várias colunas. Abaixo, a consulta está sendo excluída com base em duas colunas. nome da tabela é: testing e nomes de coluna empno,empname

DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)

Aqui está outro bom artigo sobre como remover duplicatas .

Ele discute por que é difícil: " SQL é baseado em álgebra relacional, e duplicatas não podem ocorrer em álgebra relacional, porque duplicatas não são permitidas em um conjunto. "

A solução de tabela temporária e dois exemplos de mysql.

No futuro, você irá impedi-lo em um nível de banco de dados ou de uma perspectiva de aplicativo. Eu sugeriria o nível do banco de dados porque o seu banco de dados deveria ser responsável por manter a integridade referencial, os desenvolvedores só causariam problemas;)



Essa consulta mostrou um desempenho muito bom para mim:

DELETE tbl
FROM
    MyTable tbl
WHERE
    EXISTS (
        SELECT
            *
        FROM
            MyTable tbl2
        WHERE
            tbl2.SameValue = tbl.SameValue
        AND tbl.IdUniqueValue < tbl2.IdUniqueValue
    )

excluiu linhas de 1M em pouco mais de 30 segundos de uma tabela de 2M (50% de duplicatas)


Eu pensei em compartilhar minha solução, pois funciona em circunstâncias especiais. No meu caso, a tabela com valores duplicados não tinha uma chave estrangeira (porque os valores foram duplicados de outro banco de dados).

begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2

-- insert distinct values into temp
insert into #temp 
select distinct * 
from  tableName

-- delete from source
delete from tableName 

-- insert into source from temp
insert into tableName 
select * 
from #temp

rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!

PS: quando estou trabalhando em coisas assim, eu sempre uso uma transação, isso não apenas garante que tudo seja executado como um todo, mas também me permite testar sem arriscar nada. Mas é claro que você deve fazer um backup de qualquer maneira só para ter certeza ...


Eu preferiria CTE para excluir linhas duplicadas da tabela do sql server

É altamente recomendável seguir este artigo :: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

mantendo original

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

sem manter original

WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
 
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)

Eu quero visualizar as linhas que você está prestes a remover e manter o controle sobre quais das linhas duplicadas devem ser mantidas. Veja http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/

with MYCTE as (
  SELECT ROW_NUMBER() OVER (
    PARTITION BY DuplicateKey1
                ,DuplicateKey2 -- optional
    ORDER BY CreatedAt -- the first row among duplicates will be kept, other rows will be removed
  ) RN
  FROM MyTable
)
DELETE FROM MYCTE
WHERE RN > 1

Eu tinha uma tabela onde precisava preservar linhas não duplicadas. Não tenho certeza da velocidade ou eficiência.

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )


Outra maneira possível de fazer isso é

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

Estou usando ORDER BY (SELECT 0) acima, pois é arbitrário qual linha preservar no caso de empate.

Para preservar o mais recente na ordem RowID , por exemplo, você poderia usar ORDER BY RowID DESC

Planos de Execução

O plano de execução para isso é geralmente mais simples e mais eficiente do que na resposta aceita, já que não requer a associação automática.

Isso nem sempre é o caso no entanto. Um local onde a solução GROUP BY pode ser preferida são situações em que um agregado de hash seria escolhido em vez de um agregado de fluxo.

A solução ROW_NUMBER sempre dará praticamente o mesmo plano, enquanto a estratégia GROUP BY é mais flexível.

Fatores que podem favorecer a abordagem agregada de hash seriam

  • Nenhum índice útil nas colunas de particionamento
  • relativamente menos grupos com relativamente mais duplicatas em cada grupo

Em versões extremas desse segundo caso (se houver muito poucos grupos com muitas duplicatas em cada), é possível considerar simplesmente inserir as linhas para manter uma nova tabela, em seguida TRUNCAAR o original e copiá-los de volta para minimizar o registro em comparação com a exclusão uma proporção muito alta das linhas.


Usa isto

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
   As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1

Usando CTE. A ideia é unir uma ou mais colunas que formam um registro duplicado e depois remover o que você quiser:

;with cte as (
    select 
        min(PrimaryKey) as PrimaryKey
        UniqueColumn1,
        UniqueColumn2
    from dbo.DuplicatesTable 
    group by
        UniqueColumn1, UniqueColumn1
    having count(*) > 1
)
delete d
from dbo.DuplicatesTable d 
inner join cte on 
    d.PrimaryKey > cte.PrimaryKey and
    d.UniqueColumn1 = cte.UniqueColumn1 and 
    d.UniqueColumn2 = cte.UniqueColumn2;

CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)

INSERT INTO car(PersonId,CarId)
VALUES(1,2),(1,3),(1,2),(2,4)

--SELECT * FROM car

;WITH CTE as(
SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)

DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)

DELETE
FROM
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );

DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 1 

SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable






duplicates