使用last_value窗口函数时，HIVE中的语义异常错误 [英] Semantic exception error in HIVE while using last_value window function

问题描述

我有一个包含以下数据的表:

I have a table with the following data:

dt  device  id  count
2018-10-05  computer    7541185957382   6
2018-10-20  computer    7541185957382   3
2018-10-14  computer    7553187775734   6
2018-10-17  computer    7553187775734   10
2018-10-21  computer    7553187775734   2
2018-10-22  computer    7549187067178   5
2018-10-20  computer    7553187757256   3
2018-10-11  computer    7549187067178   10

我想获取每个id的最后一个和第一个dt.因此，我使用了如下窗口函数first_value和last_value:

I want to get the last and first dt for each id. Hence, I used the window functions first_value and last_value as follows:

select id,last_value(dt) over (partition by id order by dt) last_dt
from table
order by id
;

但是我收到此错误:

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: Primitve type DATE not supported in Value Boundary expression

我无法诊断出问题，希望对您有所帮助.

I am not able to diagnose the problem, and I would appreciate any help.

推荐答案

如果在查询中添加行之间子句，则查询将正常工作.

If you add rows between clause in your query, then your query will work fine.

hive> select id,last_value(dt) over (partition by id order by dt 
      rows between unbounded preceding and unbounded following) last_dt 
      from table order by id;

结果:

+----------------+-------------+--+
|       id       |   last_dt   |
+----------------+-------------+--+
| 7541185957382  | 2018-10-20  |
| 7541185957382  | 2018-10-20  |
| 7549187067178  | 2018-10-22  |
| 7549187067178  | 2018-10-22  |
| 7553187757256  | 2018-10-20  |
| 7553187775734  | 2018-10-21  |
| 7553187775734  | 2018-10-21  |
| 7553187775734  | 2018-10-21  |
+----------------+-------------+--+

有 Jira 关于原始类型支持，并已在 Hive.2.1.0

There is Jira regards to primitive type support and got fixed in Hive.2.1.0

更新:

对于不同的记录，您可以使用 ROW_NUMBER 窗口功能，并仅从结果集中过滤出first row.

For distinct records you can use ROW_NUMBER window function and filter out only the first row from the result set.

hive> select id,last_dt from 
          (select id,last_value(dt) over (partition by id order by dt 
              rows between unbounded preceding and unbounded following) last_dt,
              ROW_NUMBER() over (partition by id order by dt)rn 
              from so )t 
           where t.rn=1;

结果:

+----------------+-------------+--+
|       id       |     dt      |
+----------------+-------------+--+
| 7541185957382  | 2018-10-20  |
| 7553187757256  | 2018-10-20  |
| 7553187775734  | 2018-10-21  |
| 7549187067178  | 2018-10-22  |
+----------------+-------------+--+

这篇关于使用last_value窗口函数时，HIVE中的语义异常错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！