删除或更改ETL中的记录 [英] Delete or change records in ETL

问题描述

我有一张桌子，在上面建立了ETL服务.货物记录(到达/离开)进入表格.我已经做完了，我的桌子将被删除.当商品标识符第二次到达数据库时，两条记录都将被删除.

I have a table over which I have built an ETL service. Goods records (arrival / departure) go to the table. I have done that my table will be erased. When the item identifier arrives in the database for the second time, both records are deleted.

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

现在ETL服务删除记录(每30秒一次):

Now ETL service remove records (every 30s):

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

我使用以下功能将其删除:

I delete it using the function:

with todelete as (
      select *, count(*) over (partition by label) as cnt, ROW_NUMBER() over (partition by label order by time DESC) as r_number
      from Table1
     )
delete from todelete
    where cnt >= 2 

另一个问题困扰.而谈到餐桌，这仅意味着价格的变化.

And another problem gets in the way. And when it comes to the table, which only means a change in price.

变种1:

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

现在，删除功能"和

我的目标:

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

我不知道如何在删除函数中同时处理这两个问题.

I don't know how to treat both of these things in a delete function.

推荐答案

要满足这两个要求，您应该检查成本变化，如下所示:

To meet both your requirements you should check for change in cost like below

 ; 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)

这篇关于删除或更改ETL中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！