转载自 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