sql - 使用窗口函数更新列 mith 多个值

Question

我有下表，其中包含大约。100,000 行，每个 id 都需要更新。

组织表 A

id    start_dt    end_dt      cust_start_dt    cust_end_dt
 1    8/1/2008    9/12/2019   8/7/2008          9/12/2019
 1    9/17/2019   9/20/2019   9/17/2019         12/31/9999
 1    9/21/2019   12/31/9999  9/17/2019         12/31/9999

表 A 应如下所示...

id    start_dt    end_dt      cust_start_dt    cust_end_dt
 1    8/1/2008    9/16/2019   9/17/2019         12/31/9999
 1    9/17/2019   9/20/2019   9/17/2019         12/31/9999
 1    9/21/2019   12/31/9999  9/17/2019         12/31/9999

sql

select
id
start_dt,
case
when lead(start_dt) over (partition by id order by start_dt) - end_dt > 1 then lead(start_dt) over (partition by id order by start_dt) - 1
else end_dt
end end_dt, 
first_value(start_dt) over (partition by id order by start_dt) as cust_start_dt,
first_value(end_dt) over (partition by id order by start_dt desc) as cust_end_dt

from a

那么我该如何进行上述查询并更新表A？

标签： sqloraclesql-update

Answer 1

使用merge声明

Merge into a a
Using
(select id
        start_dt,
        case 
          when lead(start_dt) over (partition by id order by start_dt) - end_dt > 1 
          then lead(start_dt) over (partition by id order by start_dt) - 1
          else end_dt
        end end_dt, 
        first_value(start_dt) over (partition by id order by start_dt) as cust_start_dt,
        first_value(end_dt) over (partition by id order by start_dt desc) as cust_end_dt
   from a) b
On (a.id = b.id
    And a.start_dt = b.start_dt)
When matched then
Update set
  A.end_dt = b.end_dt,
  A.cust_start_dt = b.cust_start_dt,
  A.cust_end_dt = b.cust_end_dt

干杯！！