主页 / dba / 问题 / 43728
Accepted
WileCau
Asked: 2013-06-04 18:49:47 +0800 CST

如何在不包含冗余行的情况下合并数据集？

  • 772

我认为这一定是一件相当普遍的事情，但我找不到一个例子。它涉及合并涉及日期/时间（或更一般地说是任何顺序值）的数据，而不包括冗余记录。冗余是指可以被其他记录暗示的记录。

例如，如果昨天的价格是 1 美元，今天的价格是 1 美元，并且昨天和今天之间没有其他价格变化，那么可以从昨天的价格推断出今天的价格，而不需要存储两天的价格。

这是我要解决的方案。我们有一个物品的历史价格表。新价格会定期导入表中。导入文件可以包含任何时间的日期，我们不知道它们总是在历史表中的数据“之后”。

为避免数据膨胀，我只想在记录提供新信息时插入记录。因此，如果可以从旧记录中推断出新记录，那么我不想插入新记录，如果可以从新记录中推断出旧记录，那么我想删除旧记录并插入新记录。

一些具体的例子可能会有所帮助，这里有两个问题场景：

  1. 传入记录可以从现有记录中推断出来，因此传入记录是冗余的。例如

    旧记录：2013-04-23 1.00

    新记录：2013-04-24 1.00 <--这是现有记录暗示的，不要插入

  2. 现有记录可以从新记录中推断出来，因此现有记录是多余的。例如

    新纪录：2013-04-23 1.00

    旧记录：2013-04-24 1.00 <--这是新记录暗示的，删除它

  3. 这只是显示一个值何时不是冗余的示例。例如

    旧记录：2013-04-23 1.00

    旧记录：2013-04-24 1.20

    新记录：2013-04-25 1.00 <-- 不是多余的，价格自上次 1.00 起发生了变化

这里有一个更详细的例子http://sqlfiddle.com/#!3/2ef87/2

目前，我倾向于采用以下多步骤方法：

  1. 如果存在与较早日期具有相同价格的现有记录，则从收货中删除（上面的场景 1）。
  2. 从现有中删除具有相同价格且较早日期的传入记录（上面的场景 2）。
  3. 插入剩余的传入记录。

必须有更好的方法，也许使用 MERGE，但它正在努力解决如何做到这一点。

如何有效地“合并”现有记录和传入记录？谢谢

sql-server sql-server-2008

3 个回答

  1. Best Answer

    记录迟到的问题使重复删除更加复杂，但这并非不可能。使用视图（如您在其他问题中提出的那样）动态删除重复项是可行的，但针对该视图的查询可能会产生复杂和/或低效的查询计划。

    另一种设计是将重复的记录保存在单独的表中，以防需要它们来正确处理未来的迟到记录。这确实给数据导入过程增加了一点复杂性，但每一步都不是太难，结果是一个干净整洁的无重复历史表：

    表

    -- Original table
CREATE TABLE dbo.History
(
    Effective   datetime NOT NULL,      -- When the value became current
    Product     integer NOT NULL,       -- The product
    Kind        tinyint NOT NULL,       -- The price kind (RRP, special, etc)
    Price       smallmoney NOT NULL,    -- The new price

    CONSTRAINT [PK dbo.History Effective, Product, Kind]
        PRIMARY KEY CLUSTERED
            (Effective, Product, Kind)
);

-- Holding area for duplicates that may be needed in future
CREATE TABLE dbo.HistoryDuplicates
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,

    CONSTRAINT [PK dbo.HistoryDuplicates Product, Kind, Effective]
        PRIMARY KEY CLUSTERED
            (Product, Kind, Effective)
);

    初始数据

    CREATE TABLE #NewRows
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,
    [Action]    char(1) NOT NULL DEFAULT 'X',
);

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows (Product, Kind, Effective);

INSERT #NewRows
    (Effective, Product, Kind, Price)
VALUES
    ('2013-04-23T00:23:00', 1234, 1, 1.00),
    ('2013-04-24T00:24:00', 1234, 1, 1.00),
    ('2013-04-25T00:25:00', 1234, 1, 1.50),
    ('2013-04-25T00:25:00', 1234, 2, 2.00),
    ('2013-04-26T00:26:00', 1234, 1, 2.00),
    ('2013-04-27T00:27:00', 1234, 1, 2.00),
    ('2013-04-28T00:28:00', 1234, 1, 1.00);

    第一步是删除输入数据中的任何冗余，将删除的数据存储在新的保持表中：

    -- Remove redundancies in the input set
-- and save in the duplicates table
DELETE NR
OUTPUT
    DELETED.Effective,
    DELETED.Product,
    DELETED.Kind,
    DELETED.Price
INTO dbo.HistoryDuplicates
    (Effective, Product, Kind, Price)
FROM #NewRows AS NR
OUTER APPLY
(
    SELECT TOP (1)
        NR2.Price
    FROM #NewRows AS NR2
    WHERE
        NR2.Product = NR.Product
        AND NR2.Kind = NR.Kind
        AND NR2.Effective < NR.Effective
    ORDER BY
        NR2.Effective DESC
) AS X
WHERE
    EXISTS (SELECT X.Price INTERSECT SELECT NR.Price);

    去重执行计划

    分类输入行

    下一步是确定输入表中的每一行是否是冗余的（相对于历史表）。以下查询Action适当地设置输入集数据的列：

    -- Decide what to do with each row
UPDATE NewRows
SET [Action] = 
    CASE
        WHEN NOT EXISTS (SELECT ExistingRow.Price INTERSECT SELECT NewRows.Price)
            THEN 'I' -- Insert
        WHEN ExistingRow.Price = NewRows.Price
            THEN 'D' -- Duplicate
        ELSE 'X'
    END
FROM #NewRows AS NewRows
OUTER APPLY
(
    SELECT TOP (1)
        H.Price,
        H.Effective
    FROM dbo.History AS H
    WHERE
        H.Product = NewRows.Product
        AND H.Kind = NewRows.Kind
        AND H.Effective <= NewRows.Effective
    ORDER BY
        H.Effective DESC
) AS ExistingRow;

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows 
    ([Action], Product, Kind, Effective) 
WITH (DROP_EXISTING = ON);

    行动执行计划

    存储冗余行

    现在我们将标识为冗余的行存储到保留表中：

    -- Store duplicates
WITH Duplicates AS
(
    SELECT NR.*
    FROM #NewRows AS NR
    WHERE NR.[Action] = 'D'
)
MERGE dbo.HistoryDuplicates AS HD
USING Duplicates AS NR
    ON NR.Product = HD.Product
    AND NR.Kind = HD.Kind
    AND NR.Effective = HD.Effective
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Product, Kind, Effective, Price) 
    VALUES (Product, Kind, Effective, Price);

    冗余记录计划

    新的历史记录行

    非冗余行被添加到历史表中：

    -- Inserts
WITH Inserts AS
(
    SELECT NR.*
    FROM #NewRows AS NR
    WHERE NR.[Action] = 'I'
)
MERGE dbo.History AS H
USING Inserts AS NR
    ON NR.Product = H.Product
    AND NR.Kind = H.Kind
    AND NR.Effective = H.Effective
    AND NR.Price = H.Price
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Effective, Product, Kind, Price)
    VALUES (Effective, Product, Kind, Price);

    新行计划

    恢复冗余记录

    添加新记录可能会导致需要恢复冗余行。以下查询识别合格的冗余行并将它们移动到历史表：

    DELETE NextDuplicate
OUTPUT DELETED.Product,
       DELETED.Kind,
       DELETED.Price,
       DELETED.Effective
INTO dbo.History
    (Product, Kind, Price, Effective)
FROM #NewRows AS NR
CROSS APPLY
(
    SELECT TOP (1)
        H.Effective,
        H.Price
    FROM dbo.History AS H
    WHERE
        H.Product = NR.Product
        AND H.Kind = NR.Kind
        AND H.Effective > NR.Effective
    ORDER BY
        H.Effective ASC
) AS NextHistory
CROSS APPLY
(
    SELECT TOP (1)
        HD.Effective,
        HD.Product,
        HD.Kind,
        HD.Price
    FROM dbo.HistoryDuplicates AS HD
    WHERE
        HD.Product = NR.Product
        AND HD.Kind = NR.Kind
        AND HD.Effective > NR.Effective
        AND HD.Effective < NextHistory.Effective
    ORDER BY
        HD.Effective ASC
) AS NextDuplicate
WHERE
    NR.[Action] = 'I'
    AND NOT EXISTS (SELECT NextDuplicate.Price INTERSECT SELECT NR.Price);

    重新定位行计划

    结果

    历史表

    ╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-23 00:23:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    1 ║ 1.50  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    2 ║ 2.00  ║
║ 2013-04-26 00:26:00.000 ║    1234 ║    1 ║ 2.00  ║
║ 2013-04-28 00:28:00.000 ║    1234 ║    1 ║ 1.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

    历史重复表

    ╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-24 00:24:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-27 00:27:00.000 ║    1234 ║    1 ║ 2.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

    处理新数据

    前面的步骤很笼统。我们可以使用完全相同的代码处理一批新的行。下一个脚本加载包含两个样本行的输入表，其中一个是重复的，另一个是需要恢复先前冗余行的示例：

    -- If needed
DROP TABLE #NewRows;

CREATE TABLE #NewRows
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,
    [Action]    char(1) NOT NULL DEFAULT 'X',
);

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows (Product, Kind, Effective);

INSERT #NewRows
    (Effective, Product, Kind, Price)
VALUES
    ('2013-04-24 00:24:01.000', 1234, 1, 1.00), -- New duplicate
    ('2013-04-26 00:26:30.000', 1234, 1, 5.00); -- Complex new row

    运行一般脚本的其余部分会产生这个最终状态：

    历史：

    ╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-23 00:23:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    1 ║ 1.50  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    2 ║ 2.00  ║
║ 2013-04-26 00:26:00.000 ║    1234 ║    1 ║ 2.00  ║
║ 2013-04-26 00:26:30.000 ║    1234 ║    1 ║ 5.00  ║ <- New
║ 2013-04-27 00:27:00.000 ║    1234 ║    1 ║ 2.00  ║ <- Recovered
║ 2013-04-28 00:28:00.000 ║    1234 ║    1 ║ 1.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

    历史副本：

    ╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-24 00:24:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-24 00:24:01.000 ║    1234 ║    1 ║ 1.00  ║ <- New duplicate
╚═════════════════════════╩═════════╩══════╩═══════╝
    • 4

  2. （这更像是一个查询或“你确定你真的想这样做吗？”而不是一个答案，抱歉，评论太长了！）

    如果较早的数据可以稍后进入，您真的要删除任何内容吗？可能会出现一个新数据点，这意味着您需要保留已删除的内容，例如：

    2013-04-01 1.00
2013-04-04 2.00

    然后

    2013-04-02 2.00

    进来，你去重复

    2013-04-01 1.00
2013-04-02 2.00
2013-04-04 2.00

    至

    2013-04-01 1.00
2013-04-02 2.00

    但是之后

    2013-04-03 3.00

    到了，你想要

    2013-04-01 1.00
2013-04-02 2.00
2013-04-03 3.00
2013-04-04 2.00

    不是

    2013-04-01 1.00
2013-04-02 2.00
2013-04-03 3.00

    但是您已经删除了 4th 的值。

    由于过去的数据现在可以进来，当您只想考虑值变化而不是每次读数时，您可能需要保留所有内容并使用视图来筛选重复项。

    • 2

  3. 如果我理解正确：

    1. 如果传入记录的日期和时间早于同一组 PK（Site 和 PriceType）的现有记录，您希望保留传入的日期和时间（价格相同，State 和 PriceType 也相同）
    2. 如果传入记录的日期和时间晚于现有记录，则要丢弃传入记录（保留现有日期和时间）
    3. 如果有新的PK组合，将其添加到现有的

    这可以通过使用像这样的 MERGE 子句来完成：

    MERGE History h
USING incoming  i
ON h.product = i.product
   and h.kind = i.kind
   and h.price = i.price
   and h.effective >= i.effective
WHEN matched then
  update set h.effective = i.effective
when not matched by target then 
insert (Effective, Product, Kind, Price)
values (i.effective
       ,i.product
       ,i.kind
       ,i.price);

    在这个小提琴中检查它，看看这是否是你想要的结果。

    • 0

相关问题