【转】Hive ,row_number,dense_rank,percent_rank不要带参数 ；rank要带参数

转载自 https://blog.csdn.net/u010002184/article/details/109499535
感谢作者。

正确如下：

row_number() OVER( partition by session_id order by  request_time) as row_num -- 不能带参数
rank(request_time) OVER( partition by session_id order by  request_time) as row_num -- 要带参数
dense_rank() OVER( partition by session_id order by  request_time) as row_num -- 不能带参数
percent_rank() OVER( partition by session_id order by  request_time) as row_num -- 不能带参数

错误如下：

row_number(request_time) OVER( partition by session_id order by  request_time) as row_num
rank() OVER( partition by session_id order by  request_time) as row_num
dense_rank(request_time) OVER( partition by session_id order by  request_time) as row_num
percent_rank(request_time) OVER( partition by session_id order by  request_time) as row_num

 

Hive2.0.0版本

select
request_time,session_id,c3,c4,
Row_Number() OVER( partition by request_time,session_id,c3,c4
                order by  request_time,session_id,c3,c4) as row_num
from 
table_test1 where dt='2099-99-99' 
 --正确
 
 
select
request_time,session_id,c3,c4,
Row_Number(request_time,session_id,c3,c4) OVER( partition by request_time,session_id,c3,c4
                order by  request_time,session_id,c3,c4) as row_num
from 
table_test1 where dt='2099-99-99' 
--  FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
-- Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: No argument is expected.
 
 
 
 select
request_time,session_id,c3,c4,
Row_Number(request_time) OVER( partition by request_time,session_id,c3,c4
                order by  request_time,session_id,c3,c4) as row_num
from 
table_test1 where dt='2099-99-99' 
--  FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
-- Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: No argument is expected.
 
 
 
 select
request_time,session_id,c3,c4,
Row_Number(request_time) OVER( partition by request_time,session_id,c3,c4
                order by  request_time) as row_num
from 
table_test1 where dt='2099-99-99' 
 --  FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
-- Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: No argument is expected.
 
 
===========================
 
 select
request_time,session_id,c3,c4,
rank(request_time) OVER( partition by request_time,session_id,c3,c4
                order by  request_time) as row_num
from 
table_test1 where dt='2099-99-99' 
 --正确
 
 
 
 select
session_id,request_time,c3,c4,
rank(request_time) OVER( partition by session_id order by  request_time) as row_num
from 
table_test1 where dt='2020-03-16'
and session_id in ('158430718024490880175|1','15837004010552038104667|17');
-- 正确
 
 
 select
session_id,request_time,c3,c4,
rank() OVER( partition by session_id order by  request_time) as row_num
from 
table_test1 where dt='2020-03-16'
and session_id in ('158430718024490880175|1','15837004010552038104667|17');
-- FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
-- Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: One or more arguments are expected.
 
 
===========================
 
select
request_time,session_id,c3,c4,
dense_rank(request_time) OVER( partition by request_time,session_id,c3,c4
                order by  request_time) as row_num
from 
table_test1 where dt='2099-99-99' 
--  FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
-- Underlying error: Ranking Functions can take no arguments
 
 
 
 select
session_id,request_time,c3,c4,
dense_rank(request_time) OVER( partition by session_id order by  request_time) as row_num
from 
table_test1 where dt='2020-03-16'
and session_id in ('158430718024490880175|1','15837004010552038104667|17');
-- FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
-- Underlying error: Ranking Functions can take no arguments
 
 
 select
session_id,request_time,c3,c4,
dense_rank() OVER( partition by session_id order by  request_time) as row_num
from 
table_test1 where dt='2020-03-16'
and session_id in ('158430718024490880175|1','15837004010552038104667|17');
 -- 正确 
 
===========================
 
select
request_time,session_id,c3,c4,
percent_rank(request_time) OVER( partition by request_time,session_id,c3,c4
                order by  request_time) as row_num
from 
table_test1 where dt='2099-99-99' 
 --  FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
-- Underlying error: Ranking Functions can take no arguments
 
 
 
 select
session_id,request_time,c3,c4,
percent_rank(request_time) OVER( partition by session_id order by  request_time) as row_num
from 
table_test1 where dt='2020-03-16'
and session_id in ('158430718024490880175|1','15837004010552038104667|17');
-- FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
-- Underlying error: Ranking Functions can take no arguments
 
 
 
 select
session_id,request_time,c3,c4,
percent_rank() OVER( partition by session_id order by  request_time) as row_num
from 
table_test1 where dt='2020-03-16'
and session_id in ('158430718024490880175|1','15837004010552038104667|17');
-- 正确 

Hive 2.0.0