sql - 需要处理大型数据集的小子集的方法

Question

我正面临一个概念问题，我很难克服。我希望 SO 人员可以在正确的方向上插入我克服它。

我正在使用非常相似且非常大的源数据进行一些 ETL 工作。我正在将它加载到一个用于复制的表中，我只需要此目标表中的最基本信息。

我的源表看起来像这样:

我需要我的目标表来反射(reflect)它:

如您所见，我没有在源表中重复的 InTransit 状态重复它。我想弄清楚如何实现的步骤是

1. 获取自上次查询运行以来输入的任何新的不同行。 (简单)
2. 对于每个 TrackingId，我需要检查每个新状态是否已经是目标中的最新状态，如果是则忽略，否则继续并插入它。这意味着我也必须从最早的新状态开始，然后从那里开始。 (我不知道 *(!#in clue I'll do this)
3. 每 15 分钟执行一次此操作，以便状态保持最新，因此第 2 步必须高效。

我的源表很容易包含 100k+ 行，但需要每 15 分钟运行一次，这要求我确保它的性能非常好，因此我真正想要避免使用游标。

目前我能看到的唯一方法是使用 CLR 存储过程，但我认为可能有更好的方法，因此我希望你们能在正确的方向上插入我。

我确定我可能遗漏了一些您可能需要的信息，所以请告诉我您可能需要的信息，我很乐意提供。

提前致谢!

编辑:好吧，我的问题不够明确。我的源表将包含多个跟踪 ID。它可能多达 100k+ 行，其中包含多个 TrackingId 和每个 trackingId 的多个状态。我必须如上所述为每个单独的跟踪 ID 更新目标表，但我的源将是 trackingId 的混合物。

Answer 1

这是一个没有自连接的解决方案:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (ORDER BY statusDate) AS rn,
                ROW_NUMBER() OVER (PARTITION BY status ORDER BY statusDate) AS rns
        FROM    tracking
        WHERE   tackingId = @id
        ),
        qs AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY rn - rns ORDER BY statusDate) AS rnn
        FROM    q
        )
SELECT  *
FROM    qs
WHERE   rnn = 1
ORDER BY
        statusDate

这是一个要检查的脚本:

DECLARE @tracking TABLE
        (
        id INT NOT NULL PRIMARY KEY,
        trackingId INT NOT NULL,
        status INT,
        statusDate DATETIME
        )

INSERT
INTO    @tracking
SELECT  1, 1, 1, DATEADD(d, 1, '2010-01-01')
UNION ALL
SELECT  2, 1, 2, DATEADD(d, 2, '2010-01-01')
UNION ALL
SELECT  3, 1, 2, DATEADD(d, 3, '2010-01-01')
UNION ALL
SELECT  4, 1, 2, DATEADD(d, 4, '2010-01-01')
UNION ALL
SELECT  5, 1, 3, DATEADD(d, 5, '2010-01-01')
UNION ALL
SELECT  6, 1, 3, DATEADD(d, 6, '2010-01-01')
UNION ALL
SELECT  7, 1, 4, DATEADD(d, 7, '2010-01-01')
UNION ALL
SELECT  8, 1, 2, DATEADD(d, 8, '2010-01-01')
UNION ALL
SELECT  9, 1, 2, DATEADD(d, 9, '2010-01-01')
UNION ALL
SELECT  10, 1, 1, DATEADD(d, 10, '2010-01-01')
;
WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (ORDER BY statusDate) AS rn,
                ROW_NUMBER() OVER (PARTITION BY status ORDER BY statusDate) AS rns
        FROM    @tracking
        ),
        qs AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY rn - rns ORDER BY statusDate) AS rnn
        FROM    q
        )
SELECT  *
FROM    qs
WHERE   rnn = 1
ORDER BY
        statusDate