时间戳

首页 > 解决方案 > 删除或更改 ETL 2 中的记录

sql - 删除或更改 ETL 2 中的记录

问题描述

我想跟进我之前 POST 中的一个问题：删除或更改 ETL 中的记录

那里提到的问题已通过以下方式解决：

 ; with todelete as (
      select *, 
           count(*) over (partition by label) as cnt, 
           lag(cost) over (partition by label order by time ASC) as lastcost
           ROW_NUMBER() over (partition by label order by time ASC) as r_number
      from Table1
     )
delete from todelete 
    where cnt > 1 and r_number between 1 and (cnt/2)*2 and  cost=ISNULL(lastcost,cost)

，然而，在测试过程中，当这些运动发生在桌子上时，我遇到了一个问题（它们无法阻止）。：

label   cost   time
x2       29    14/5/2020 01:00:00
x3       20    14/5/2020 01:02:00
x2       29    15/5/2020 03:12:02

对于相同的“标签”，我有两条相同的行（时间戳除外）具有相同的“成本”。而只是上述解决方案将删除这两条记录。我只需要删除旧的。

谢谢你的建议

更新：

我的目标是。

我有记录表：

label   cost   time
x2       29    14/5/2020 01:00:00
x3       20    14/5/2020 01:02:00
x2       30    15/5/2020 03:12:02

现在我有删除功能：

; with todelete as (
          select *, 
               count(*) over (partition by label) as cnt, 
               lag(cost) over (partition by label order by time ASC) as lastcost
               ROW_NUMBER() over (partition by label order by time ASC) as r_number
          from Table1
         )
    delete from todelete 
        where cnt > 1 and r_number between 1 and (cnt/2)*2 and  cost=ISNULL(lastcost,cost)

我得到我的他们想要一张桌子：

label   cost   time
x3       20    14/5/2020 01:02:00
x2       30    15/5/2020 03:12:02

但是当原始表如下所示时会出现问题：

label   cost   time
x2       29    14/5/2020 01:00:00
x3       20    14/5/2020 01:02:00
x2       29    15/5/2020 03:12:02

现在删除功能（上面提到）

我要一张桌子：

 label     cost   time
    x3       20    14/5/2020 01:02:00

使用上述删除功能，标签“X2”的两条记录都将被删除，但我只想删除较旧的记录。

标签： sqlsql-serversql-deleterow-number

解决方案

没人什么？

我试试这个：我无法解决它。在这里，您会看到它会为我删除两条记录（我只想要旧的）： https ://rextester.com/TLLQ93275

在这种情况下，它可以正常工作，但如果“x2”具有相同的价格（例如，29），它也会删除两个条目。 https://rextester.com/RHB70490

更新：

我终于设法解决了这个问题。我添加了另一个排名函数并对其进行了适当的调整。

; with todelete as (
      select *, 
           count(*) over (partition by label) as cnt, 
     lag(cost) over (partition by label order by time ASC) as lastcost
     ,ROW_NUMBER() over (partition by label order by time ASC) as r_number
      ,ROW_NUMBER() over (partition by label order by time DESC) as r_number2
    ,RANK() over (partition by cost order by time asc) as TEST
    ,Row_NUMBER() over (partition by label order by TIME DESC) as TEST2
       from Table1
     )
DELETE from todelete 
    where (cnt > 1 and r_number between 1 and (cnt/2)*2 and cost=ISNULL(lastcost,cost) AND TEST2 !=1)  OR (cnt>1 AND TEST2<>1 AND r_number2 != 1)

为了说明，这里：https ://rextester.com/DONME54328

推荐阅读