本文为您介绍Top-N修改的可兼容性和不可兼容性详情。

可兼容的修改

是否输出rank number的值,不影响兼容性,该修改属于完全兼容修改。
-- 原始SQL。
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

-- 输出rank number字段:rk,该修改属于完全兼容修改。
select a, b, c, rk from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

不兼容的修改

  • 新增、删除、修改partition by key或者partition by key涉及字段的计算逻辑发生变化,该修改属于不兼容修改。
    -- 原始SQL。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by c) as rk
       from MyTable)
    where rk < 3;
    
    -- 新增partition by字段:d,该修改属于不兼容修改。
    select a, b, c from (
      select *,
       row_number() over (partition by a, d order by c) as rk
       from MyTable)
    where rk < 3;
    
    -- 删除partition by字段:a,该修改属于不兼容修改。
    select a, b, c from (
      select *,
       row_number() over (order by c) as rk
       from MyTable)
    where rk < 3;
    
    -- 修改partition by字段:a -> a + 1,该修改属于不兼容修改。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by c) as rk
       from (select a + 1 as a, b, c from MyTable))
    where rk < 3;
  • 修改order by相关属性(排序字段和方向),该修改属于不兼容修改。
    -- 原始SQL。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by c) as rk
       from MyTable)
    where rk < 3;
    
    -- 修改order key:c -> b,当前修改属于不兼容修改。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by b) as rk
       from MyTable)
    where rk < 3;
    
    -- 修改order key:c -> substring(c, 1, 5),当前修改属于不兼容修改。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by c) as rk
       from (select a, b, substring(c, 1, 5) as c from MyTable))
    where rk < 3;
    
    -- 修改order:asc -> desc,当前修改属于不兼容修改。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by c desc) as rk
       from MyTable)
    where rk < 3;
  • 修改rank range的值(Top-N中N的值),则该修改属于不兼容修改。
    -- 原始SQL。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by c) as rk
       from MyTable)
    where rk < 3;
    
    -- 修改rank range:3 -> 5,当前修改属于不兼容修改。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by c) as rk
       from MyTable)
    where rk < 5;
  • 修改了输入字段,则该修改属于不兼容修改。
    -- 原始SQL。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by c) as rk
       from MyTable)
    where rk < 3;
    
    -- 新增输入字段:d,当前修改属于不兼容修改。
    select a, b, c, d from (
      select *,
       row_number() over (partition by a order by c) as rk
       from MyTable)
    where rk < 3;
    
    -- 删除输入字段:b,当前修改属于不兼容修改。
    select a, c from (
      select *,
       row_number() over (partition by a order by c) as rk
       from MyTable)
    where rk < 3;
    
    -- 修改输入字段:b -> b + 1,当前修改属于不兼容修改。
    select a, b, c from (
      select *,
       row_number() over (partition by a order by c) as rk
       from (select a, b + 1 as b, c from MyTable))
    where rk < 3;