Deduplication

可兼容的修改

-- 原始SQL。
select a, b, c from (
  select *,
   row_number() over (partition by a order by proctime asc) as rk
   from MyTable)
where rk = 1;

-- 新增输入字段：d，该修改属于完全兼容修改。
select a, b, c, d from (
  select *,
   row_number() over (partition by a order by proctime asc) as rk
   from MyTable)
where rk = 1;

-- 删除输入字段：b，该修改属于完全兼容修改。
select a, c from (
  select *,
   row_number() over (partition by a order by proctime asc) as rk
   from MyTable)
where rk = 1;

-- 修改输入字段：c -> substring(c, 1, 5)，该修改属于完全兼容修改。
select a, b, c from (
  select *,
   row_number() over (partition by a order by proctime asc) as rk
   from (select a, b, substring(c, 1, 5) as c from MyTable))
where rk = 1;

不兼容的修改

• 新增、删除、修改partition by key或者partition by key涉及字段的计算逻辑发生变化，该修改属于不兼容修改。

  -- 原始SQL。
  select a, b, c from (
    select *,
      row_number() over (partition by a order by proctime) as rk
     from MyTable)
  where rk = 1;
  
  -- 新增partition by字段：d，该修改属于不兼容修改。
  select a, b, c from (
    select *,
     row_number() over (partition by a, d order by proctime) as rk
     from MyTable)
  where rk = 1;
  
  -- 删除partition by字段：a，该修改属于不兼容修改。
  select a, b, c from (
    select *,
     row_number() over (order by proctime) as rk
     from MyTable)
  where rk = 1;
  
  -- 修改partition by字段：a -> a + 1，该修改属于不兼容修改。
  select a, b, c from (
    select *,
     row_number() over (partition by a order by proctime) as rk
     from (select a + 1 as a, b, c from MyTable))
  where rk = 1;

• 修改order by相关属性（排序字段和方向），该修改属于不兼容修改。

  -- 原始SQL。
  select a, b, c from (
    select *,
     row_number() over (partition by a order by proctime asc) as rk
     from MyTable)
  where rk = 1;
  
  -- 修改order key：proctime -> ts，当前修改属于不兼容修改。
  select a, b, c from (
    select *,
     row_number() over (partition by a order by ts asc) as rk
     from MyTable)
  where rk = 1;
  
  -- 修改order：asc -> desc，当前修改属于不兼容修改。
  select a, b, c from (
    select *,
     row_number() over (partition by a order by proctime desc) as rk
     from MyTable)
  where rk = 1;

• 当基于rowtime做order by或基于proctime进行order by且按降序排序时（order by proctime desc） 时，修改了输入字段，则该修改属于不兼容修改。

  -- 原始SQL。
  select a, b, c from (
    select *,
     row_number() over (partition by a order by ts) as rk
     from MyTable)
  where rk = 1;
  
  -- 新增输入字段：d，当前修改属于不兼容修改。
  select a, b, c, d from (
    select *,
     row_number() over (partition by a order by ts) as rk
     from MyTable)
  where rk = 1;
  
  -- 删除输入字段：c，当前修改属于不兼容修改。
  select a, b from (
    select *,
     row_number() over (partition by a order by ts) as rk
     from MyTable)
  where rk = 1;
  
  -- 输入字段：c -> substring(c, 1, 5)，当前修改属于不兼容修改。
  select a, b, c from (
    select *,
     row_number() over (partition by a order by ts) as rk
   from (select a, b, substring(c, 1, 5) as c from MyTable))
  where rk = 1;