1.21 jigowatts

Great Scott!

SQLServer 重複データを取り除く

概要

アプリケーションからExcel形式のデータを取り込んでデータベースに登録してほしい。

よくある仕様かと思います。これに加えて、取り込みデータに重複があった場合、より後のレコードを有効としたいケースについてのソリューションです。

アプリはこんな感じExcelファイルを取り込んで、ストアドプロシージャに渡します。

データ取り込み(イメージ)

取り込みデータが重複しています。
Name項目、Mail項目、Age項目があって、NameとMailでユニークであるとしたとき、以下の場合有効なAliceちゃんは17歳です。

DECLARE	@inputData as myType

INSERT INTO @inputData
VALUES
     (N'Alice',N'Alice@simple.com',16) --無効
    ,(N'Bob',N'Bob@simple.com',21)
    ,(N'Alice',N'Alice@simple.com',18) --無効
    ,(N'Stacy',N'Stacy@simple.com',23)
    ,(N'Alice',N'Alice@simple.com',17) --有効☆
	
SELECT * FROM @inputData

SELECT結果

ID Name Mail Age
1 Alice Alice@simple.com 16
2 Bob Bob@simple.com 21
3 Alice Alice@simple.com 18
4 Stacy Stacy@simple.com 23
5 Alice Alice@simple.com 17

ユーザー定義テーブル型を作成

定義にIDENTITYを含めておきます。こうしておけば、Excelを取り込んだ際に自動採番してくれます。

DROP TYPE [dbo].[myType]
GO

CREATE TYPE [dbo].[myType] AS TABLE(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Mail] [nvarchar](50) NOT NULL,
    [Age] [int] NOT NULL
)
GO

重複を取り除く

取り込みデータよりキー項目でグルーピングして、最大値より小さいID値を削除します。
もしも最初に出てきたレコードを有効にしたい場合は、逆に最小値より大きいID値を削除すればOKですね。

DELETE core
FROM
    @inputData core
WHERE
    core.ID < (SELECT		
                    Max(t.ID)
                FROM
                    @inputData t
                WHERE
                    t.Name = core.Name
                    AND t.Mail = core.Mail
                GROUP BY
                    t.Name,t.Mail
                )

SELECT * FROM @inputData

SELECT結果

ID Name Mail Age
2 Bob Bob@simple.com 21
4 Stacy Stacy@simple.com 23
5 Alice Alice@simple.com 17

取り込んだ順に連番が振られていれば、こいつを利用して有効なレコードのみに絞ることができます!

ユーザー定義テーブル型にIDENTITY列が定義されていなかった場合

私の場合がコレで、定義の変更も影響が大きいためやりたくなかった。
ROW_NUMBERも考えたんですが、OVER句のORDER BY句もデータ取り込み順のため使えない。
ので、前段としてIDENTITY列を追加しつつ一時テーブルを作成してみました。
こんなことができるんですね。

IF object_id('tempdb..#tmpData') IS NOT NULL
BEGIN
    DROP TABLE #tmpData;
END

SELECT
    IDENTITY(int) AS ID,
    Name,Mail,Age
    INTO #tmpData
FROM
    @inputData

SELECT * FROM #tmpData

この一時テーブルを元に上記の重複を取り除く処理を行うことでユーザー定義テーブル型にIDENTITY列が定義されていなくてもID値を使うことができます。



sh-yoshida.hatenablog.com
sh-yoshida.hatenablog.com
sh-yoshida.hatenablog.com