Hive ,row_number,dense_rank,percent_rank不要带参数 ;rank要带参数

正确如下:

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

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页