SQL-1,000万行以上のテーブルの日時列に基づくデータのパージ

debugcn 投稿 Dev

Vsagar

SQL Server 2012を使用していますが、約35列と1,000万行以上のテーブルがあります。ここで、日時スタンプやその他のさまざまなフィルターに基づいてデータのパージを実行したいと思います。

サンプルデータは以下の通りです

ID  DateTimeStamp               value1  value2   value3 .... Value 35
-------------------------------------------------------------------------
2   2016-07-26 15:12:41 0.00    126.20  328051.07
2   2016-07-26 15:14:41 0.00    126.20  328051.07
2   2016-07-26 15:18:17 0.00    126.14  328052.32
2   2016-07-26 15:23:17 0.00    126.75  328054.40
2   2016-07-26 15:24:34 0.00    126.75  328054.40
2   2016-07-26 15:25:18 0.00    126.75  328054.40
2   2016-07-26 15:28:15 0.00    126.95  328060.64
2   2016-07-26 15:29:15 0.00    126.95  328060.64
2   2016-07-26 15:30:15 0.00    126.95  328060.64
2   2016-07-26 15:34:15 0.00    126.95  328060.64

時間間隔に基づいてデータのパージを実行したいのですが、5分の時間間隔を選択した場合、期待される結果セットは次のようになります。

ID  DateTimeStamp               value1  value2   value3 .... Value 35
-----------------------------------------------------------------------
2   2016-07-26 15:12:41 0.00    126.20  328051.07
2   2016-07-26 15:18:17 0.00    126.14  328052.32
2   2016-07-26 15:23:17 0.00    126.75  328054.40
2   2016-07-26 15:28:15 0.00    126.95  328060.64
2   2016-07-26 15:34:15 0.00    126.95  328060.64

必要な日時スタンプが存在しない場合、最も近い値が存在するように発生する必要があります（前または次のいずれか最も近い値を考慮する必要があります）。

以下のロジック（擬似コード）で達成しましたが、非常に遅いです

While(1)
begin
    StartDate = Start date of data purging at first iteration latter on assign it to EndDate in all next iteration 

    EndDate = EndDate + Interval
    NextEndDate  = EndDate + Interval

    Set maxDateTime = Select top(1) * 
                      from <TableName> 
                      where dateTime between StartDate to End Date  
                      order by datetime asc

    Set minDateTime = Select top(1) * 
                      from <TableName> 
                      where dateTime between EndDate to End NextEndDate 
                      order by datetime desc

    Now compare difference and choose the one which is smaller.  
        Diff(maxDateTime, EndDateTime) & Diff (minDateTime, EndDateTime)
end

上記のロジックの効率的なアプローチを誰かが提案できますか

ダン・グズマン

以下は、各5分間隔内の最初の行を除くすべてを削除する例です。この方法では、間隔ごとにループを使用して同時実行性を向上させ、トランザクションログの入力を回避しますが、関係がない場合は、集計テーブル（またはCTE）を使用して間隔を計算する単一のセットベースの操作として実行できます。

DateTimeStampパフォーマンスを向上させるには、左端のキー列としてインデックス（理想的にはクラスター化）を用意することが重要です。

CREATE TABLE dbo.TableName(
      ID  int NOT NULL
    , DateTimeStamp datetime2(0) NOT NULL
    , value1 decimal(18,2) NOT NULL
    , value2 decimal(18,2) NOT NULL
    , value3 decimal(18,2) NOT NULL
)
GO

INSERT INTO dbo.TableName VALUES
     (2, '2016-07-26 15:12:41', 0.00, 126.20, 328051.07)
    ,(2, '2016-07-26 15:14:41', 0.00, 126.20, 328051.07)
    ,(2, '2016-07-26 15:18:17', 0.00, 126.14, 328052.32)
    ,(2, '2016-07-26 15:23:17', 0.00, 126.75, 328054.40)
    ,(2, '2016-07-26 15:24:34', 0.00, 126.75, 328054.40)
    ,(2, '2016-07-26 15:25:18', 0.00, 126.75, 328054.40)
    ,(2, '2016-07-26 15:28:15', 0.00, 126.95, 328060.64)
    ,(2, '2016-07-26 15:29:15', 0.00, 126.95, 328060.64)
    ,(2, '2016-07-26 15:30:15', 0.00, 126.95, 328060.64)
    ,(2, '2016-07-26 15:34:15', 0.00, 126.95, 328060.64);
GO
CREATE CLUSTERED INDEX cdx ON dbo.TableName(DateTimeStamp);
GO

SET NOCOUNT ON;
DECLARE
      @StartDateTimeStamp datetime2(0)
    , @LastDateTimeStamp datetime2(0)
    , @EndDateTimeStamp datetime2(0)
    , @IntervalSeconds int = 300;
SET @StartDateTimeStamp = (SELECT MIN(DateTimeStamp) FROM dbo.TableName);
SET @LastDateTimeStamp = (SELECT MAX(DateTimeStamp) FROM dbo.TableName);

WHILE @StartDateTimeStamp <= @LastDateTimeStamp
BEGIN
    SET @EndDateTimeStamp = DATEADD(second, @IntervalSeconds, @StartDateTimeStamp);

    WITH rows_to_delete AS (
        SELECT ROW_NUMBER() OVER(ORDER BY DateTimeStamp) AS row_num
        FROM dbo.TableName
        WHERE 
            DateTimeStamp >= @StartDateTimeStamp
            AND DateTimeStamp < @EndDateTimeStamp
        )
    DELETE rows_to_delete
    WHERE row_num > 1;

    SET @StartDateTimeStamp = DATEADD(second, @IntervalSeconds, @StartDateTimeStamp);
END;
GO

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]