删除或更改ETL 2中的记录

Question

我想跟进我之前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"；将被删除，但我只想删除旧的。

Answer 1

什么都没有？

我试试这个：我解决不了。在这里，你可以看到它将为我删除这两个记录(我只想要旧的一个(：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