Hive中CTE内Row_Number窗口函数重复调用结果不一致问题

问题场景 #

你编写的Hive SQL代码如下：

with data(
    select 1 a,1 b
union select 1,2
union select 1,3
union select 1,4
...
union select 1,26
),
data_with_row_num (
select a,b,row_number() over(partition by 1) as rn from data
)
select * from data_with_row_num
union all 
select * from data_with_row_num

期望两次引用data_with_row_num时，相同的rn能对应相同的b值，但实际结果中同一rn对应的b值却不相同。

原因分析 #

1. Hive CTE的执行特性：Hive中的CTE属于视图型CTE，而非物化CTE。这意味着每次引用CTE时，都会重新执行CTE定义中的完整查询逻辑，而非预先将结果存储为临时表。所以两次调用data_with_row_num时，相当于两次独立执行了select a,b,row_number()... from data这个查询。
2. row_number()无排序的不确定性：你的row_number() over(partition by 1)中没有指定order by子句。窗口函数row_number()在没有明确排序规则时，数据库无法保证返回行的顺序——Hive会根据底层数据的存储位置、执行计划调度等因素返回行，两次执行的行顺序可能完全不同，导致rn的分配结果不一致。

解决办法 #

方法1：给row_number()添加明确的排序规则 #

在over()子句中加入order by，固定行的排序逻辑，这样每次计算row_number()时，rn和b的对应关系就会稳定：

with data(
    select 1 a,1 b
union select 1,2
union select 1,3
union select 1,4
...
union select 1,26
),
data_with_row_num (
select a,b,row_number() over(partition by 1 order by b) as rn from data
)
select * from data_with_row_num
union all 
select * from data_with_row_num

方法2：将CTE结果物化为临时表 #

如果需要预先固定rn的结果，可以先将data_with_row_num的查询结果存入临时表，之后两次引用这个临时表即可：

with data(
    select 1 a,1 b
union select 1,2
union select 1,3
union select 1,4
...
union select 1,26
)
CREATE TEMPORARY TABLE data_with_row_num AS
select a,b,row_number() over(partition by 1) as rn from data;

select * from data_with_row_num
union all 
select * from data_with_row_num;

内容的提问来源于stack exchange，提问作者Eric