本文为您介绍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;