sql-server - 重複削除 - アクセス 重複 片方 削除




重複行を削除する方法 (20)

かなり大きな SQL Server テーブルから重複する行(つまり300,000行以上)を削除するための最善の方法は何ですか?

RowID IDフィールドが存在するため、行はもちろん完全に重複することはありません。

MyTable

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

アプリケーションレベルから(残念ながら)。 重複を防ぐための適切な方法は一意のインデックスを使用することによるデータベースレベルであることに同意しますが、SQL Server 2005では、インデックスは900バイトしか許されず、私のvarchar(2048)フィールドは削除されます。

それがどれほどうまく機能するかはわかりませんが、インデックスを使用して直接実行できない場合でも、これを強制するトリガーを作成できると思います。 何かのようなもの:

-- 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 

また、varchar(2048)は私にとってはぎこちなく聞こえます(人生の中には2048バイトですが、あまり一般的ではありません)。 それは本当にvarchar(max)ではないのですか?


  1. 同じ構造を持つ新しい空のテーブルを作成します

  2. このようにクエリを実行する

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) > 1
  3. その後、このクエリを実行します

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

NULLがないと仮定すると、一意の列を GROUP BY 、保持する行として MIN (or MAX) RowIdを SELECT します。 次に、行IDを持たないものをすべて削除します。

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

整数ではなくGUIDがある場合は、置き換えることができます。

MIN(RowId)

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

このクエリは私にとって非常に良いパフォーマンスを示しました:

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

2Mの表から30秒以内に1M行を削除しました(50%重複)。


これは 重複 取り除くこと に関するもう一つの良い記事です。

SQLはリレーショナル代数に基づいており、重複は集合内では許可されていないため、リレーショナル代数には使用できません。

一時テーブルソリューション、および2つのmysqlの例。

将来的には、データベースレベルで、またはアプリケーションの観点からそれを防ぐつもりです。 あなたのデータベースは参照整合性の維持に責任があるべきだから、私はデータベースレベルを提案するでしょう、開発者はただ問題を起こすでしょう;)



これを行うもう一つの可能​​な方法は

; 

--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;

上記の ORDER BY (SELECT 0) を使用しているのは、同順位の場合にどの行を保持するかが任意だからです。

たとえば、 RowID 順で最新のものを保持するには、 ORDER BY RowID DESC 使用できます。

実行計画

これに対する実行計画は、自己結合を必要としないため、受け入れられた回答よりも単純で効率的です。

しかし、これは必ずしもそうとは限りません。 GROUP BY ソリューションが好まれる場所の1つは、 ハッシュアグリゲート がストリームアグリゲートよりも優先して選択される状況です。

ROW_NUMBER ソリューションは常にほぼ同じ計画を提供しますが、 GROUP BY 戦略はより柔軟です。

ハッシュ集約アプローチを支持するかもしれない要因は

  • パーティション化列に有用な索引がない
  • 各グループ内で比較的重複するグループが比較的少ない

この2番目のケースの極端な例では(それぞれ重複するグループが非常に少ない場合)、新しいテーブルに保存するために行を挿入してからオリジナルを TRUNCATE し、削除するよりロギングを最小にするためにそれらをコピーすることも考えられます。行の非常に高い割合。


はい。 一時テーブルを使用してください。 「うまく」機能するような、パフォーマンスが良くない単一のステートメントが必要な場合は、次のようにします。

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)

基本的に、表の各行について、副選択は考慮中の行とまったく同じすべての行の先頭のRowIDを見つけます。 そのため、「元の」重複していない行を表すRowIDのリストが表示されます。


マイクロソフトサポートサイトに 重複 削除すること に関する良い記事があります。 それはかなり保守的です - 彼らはあなたに別々のステップですべてをやらせてもらいます - しかしそれは大きなテーブルに対してはうまくいくはずです。

私はこれまで自己結合を使用してきましたが、おそらくHAVING節で整形することができます。

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField

以下のクエリを使用することで、単一列または複数列に基づいて重複レコードを削除できます。 以下のクエリは2つの列に基づいて削除されています。 テーブル名は次のとおりです empno,empname とカラム名 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)

次のクエリは、重複行を削除するのに役立ちます。 この例の表は ID 列として ID を持ち、重複データを持つ列は Column1Column2 、および Column3 です。

DELETE FROM TableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM   TableName
                  GROUP  BY Column1,
                            Column2,
                            Column3
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL) 

次のスクリプトは、1つのクエリでの GROUP BYHAVINGORDER BY 使用方法を示し、重複した列とその数を含む結果を返します。

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC 

正確で重複した行を削除するための迅速かつダーティ(小さなテーブル用):

select  distinct * into t2 from t1;
delete from t1;
insert into t1 select *  from t2;
drop table t2;

私はこのアプローチについても言及したいと思いますし、すべてのSQLサーバーで機能します。重複は1〜2つしかなく、IDと重複数はわかっていることがよくあります。 この場合:

SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0

私は自分の解決策を共有することにしたのですが、それは特別な状況の下でうまくいくからです。 私の場合、値が重複しているテーブルに外部キーがありませんでした(値が別のデータベースから重複していたため)。

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!!

シモンズ:このようなことに取り組むとき、私はいつもトランザクションを使います、これはすべてが全体として実行されることを確実にするだけでなく、何も危険を冒さずにテストすることを可能にします。 しかし、もちろん念のためにとにかくバックアップを取るべきです...


重複しない行を保存する必要があるテーブルがありました。 速度や効率がよくわかりません。

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

http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server 貼り付けられたリンクでさらに別の簡単な解決策を見つけることができ http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server 。 これは簡単に理解でき、ほとんどの同様の問題に効果的であるようです。 これはSQL Server用ですが、使用される概念は許容できる以上のものです。

リンクされたページの関連部分は次のとおりです。

次のデータを検討してください。

EMPLOYEE_ID ATTENDANCE_DATE
A001    2011-01-01
A001    2011-01-01
A002    2011-01-01
A002    2011-01-01
A002    2011-01-01
A003    2011-01-01

では、どうやってそれらの重複データを削除することができるのでしょうか

まず、次のコードを使用してそのテーブルにidentity列を挿入します。

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

以下のコードを使用して解決してください。

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

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