SQL Server 2012 如何清理一组数据

Question

我有一张非常大的表格，其中包含价格历史记录。

CREATE TABLE [dbo].[SupplierPurchasePrice](
    [SupplierPurchasePriceId] [int] IDENTITY(1,1) PRIMARY KEY,
    [ExternalSupplierPurchasePriceId] [varchar](20) NULL,
    [ProductId] [int] NOT NULL,
    [SupplierId] [int] NOT NULL,
    [Price] [money] NOT NULL,
    [PreviousPrice] [money] NULL,
    [SupplierPurchasePriceDate] [date] NOT NULL,
    [Created] [datetime] NULL,
    [Modified] [datetime] NULL,
)

对于每个产品(Id)和供应商(Id)，我有数百个价格记录。现在需要删除大部分数据，但仍需要保留一些历史数据。对于每个产品(Id)和供应商(Id)，我想要保留14条记录，但不包括第一条和最后一条记录。我想要保留第一条和最后一条记录，然后在第一条和最后一条记录之间均匀地保留12条记录。这样我就能保留一些历史记录。

我无法直接通过存储过程来实现这一点，而不是通过我的c# ORM（这太慢了）。

- Erik

---

你能添加 SQL Fiddle 吗？ - Guido Leenders

虽然我喜欢这个问题，但我不确定这是否是一个特别健壮的存档数据方法。我认为更好的方法是在历史上固定时间点快照价格，这样您就可以可靠地比较供应商和产品。 - Gordon Linoff

Answer 1

下面是一种直接计数方法来解决这个问题：

select spp.*
from (select spp.*,
             sum(12.5 / (cnt - 1)) over (partition by SupplierId, ProductId
                                         order by SupplierPurchasePriceId
                                        ) as cum
      from (select spp.*,
                   row_number() over (partition by SupplierId, ProductId
                                      order by SupplierPurchasePriceId
                                     ) as seqnum,
                  count(*) over (partition by SupplierId, ProductId) as cnt,
            from SupplierPurchasePrice spp
           ) spp
     ) spp
where seqnum = 1 or seqnum = cnt or cnt <= 14 or
      (floor(cumgap) <> floor(cumgap - 12.5/(cnt - 1)));

挑战在于决定12个记录的位置。这会计算出记录之间平均“间隔”，如12.5 / (cnt-1)。这是一个常数，然后在记录中累积。它将从基本上0到最大记录的12.5。想法是抓住任何超过整数值的记录。因此，如果累积从2.1到2.3，则不选择该记录。如果它从2.9到3.1，则选择该记录。

数字12.5并不神奇。12和13之间的任何数字都可以。除了选择最旧和最近的值的问题。我选择12.5以确保这些不计入12个中。

您可以在SQL Fiddle 此处看到相同的逻辑。标志列显示将要选择的内容，totflag验证恰好选择了14个。

- Gordon Linoff

Answer 2

我会尝试类似这样的东西。

select
  to_keep.SupplierPurchasePriceId
from
   (select
      foo.SupplierPurchasePriceId,
      row_number() over (partition by ProductId, SupplierId, tile_num order by Created) as takeme
    from
      (select
         SupplierPurchasePriceId,
         ProductId,
         SupplierId,
         Created,
         ntile(13) over(partition by ProductId, SupplierId order by Created) as tile_num
       from
         SupplierPurchasePrice
      ) foo
   ) to_keep
where
  to_keep.takeme = 1

union

select distinct
  last_value(SupplierPurchasePriceId) over (partition by ProductId, SupplierId order by Created range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as SupplierPurchasePriceId
from
  SupplierPurchasePrice

这应该给出需要保留的行的主键。性能可能会有所不同。未经测试。

- GSerg

Answer 3

（注意 - 我错误地阅读了您的问题。这将保留第一个，最后一个和中间每14个记录，不一定总共14个记录。）

在备份数据库之后，首先进行干运行以检查是否会删除所需数据（假设记录的时间顺序由SupplierPurchasePriceDate设置 - 如果不是这种情况，请根据实际情况更改ORDER BY）。

WITH CTE AS
(
    SELECT 
        [SupplierId], 
        [ProductId],
        [SupplierPurchasePriceId], 
        [SupplierPurchasePriceDate],
        ROW_NUMBER()  OVER (Partition BY [SupplierId], [ProductId] 
            ORDER BY [SupplierPurchasePriceDate]) -1 AS Rnk
    FROM [dbo].[SupplierPurchasePrice]
)
SELECT cteRank.*
    FROM 
        CTE cteRank
        JOIN 
        (SELECT ProductId, SupplierId, MAX(Rnk) as MaxRnk
            FROM CTE cteMax
            GROUP BY ProductId, SupplierID) X
        ON cteRank.SupplierId = X.SupplierId AND cteRank.ProductId = X.ProductId
    WHERE cteRank.Rnk % 12 != 0 AND cteRank.Rnk !=  X.MaxRnk;

如果一切正常，而且由于您已经有了替代键，那么删除步骤就只需删除匹配的替代键即可。

DELETE FROM [dbo].[SupplierPurchasePrice]
WHERE [SupplierPurchasePriceId] IN (...)

它通过按日期对价格数据进行排名，然后删除不是模12的记录（根据您的要求更改）。-1是因为ROW_NUMBER()是基于1的。第一条记录被保留，因为它是第一个模匹配项。MaxRnk步骤也是为了保留每个Supplier，Product对的最新价格。

请注意，这将保留第一条记录，每隔12条记录保留一次，以及最后一条记录。因此，最新记录（Max）和倒数第二条记录之间可能会有不均匀的间隔（最后一个模为0）。但肯定足够接近了吧？ 在这里查看SqlFiddle

- StuartLC