Hive中的窗口函数:lead()等函数与窗口函数over()一起使用时的问题

总体上Hive中的窗口函数用法和MySQL8.0中窗口函数的用法相似。这篇文章不再详细介绍Hive中的窗口函数用法，具体可以参考：MySQL中的窗口函数

首先，看下面一段SQL代码：

select feature,feature_name,bins,all_rate,ind_rate,member_rate, week,date_range,
             lead(all_rate,1)over(partition by feature,bins order by week asc range between 10 preceding and 10 following) as last_all_rate,
             lead(ind_rate)over(partition by feature,bins order by week asc range between 10 preceding and 10 following) as last_ind_rate,
             lead(member_rate)over(partition by feature,bins order by week asc range between 10 preceding and 10 following) as last_member_rate
from xy_jc.mashang_c_bins_rate_weekly

在hive上执行上述代码会提示如下错误：

Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.

将上述代码中的window子句中的range换成row，也会提示上述错误。(如果将window子句改成between unbounded preceding and unbounded following 则是可以正常执行的)。在网上找了很久的资料，都没有找到解决方法（似乎其他人都不是在窗口的函数的问题上提示这个错误的)。所以就只能换其他写法了(个人感觉应该是lead()不能跟window子句用)。最后的改写代码如下：

select feature,feature_name,bins,all_rate,ind_rate,member_rate, week,date_range,
             first_value(all_rate)over(partition by feature,bins order by week asc range between 1 preceding and current row) as last_all_rate,
             first_value(ind_rate)over(partition by feature,bins order by week asc range between 1 preceding and current row) as last_ind_rate,
             first_value(member_rate)over(partition by feature,bins order by week asc range between 1 preceding and current row) as last_member_rate
from xy_jc.mashang_c_bins_rate_weekly