hive - 使用 last_value 窗口函数时 HIVE 中的语义异常错误
问题描述
我有一个包含以下数据的表:
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
我想dt
为每个获得最后一个和第一个id
。因此,我使用窗口函数 first_value 和 last_value 如下:
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
我无法诊断问题,如果有任何帮助,我将不胜感激。
解决方案
如果您在查询中的子句之间添加行,那么您的查询将正常工作。
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中得到修复
更新:
对于不同的记录,您可以使用ROW_NUMBERfirst row
窗口函数并仅从结果集中过滤掉。
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 |
+----------------+-------------+--+
推荐阅读
- swift - UIImage in UIImageView that is in UIScrollView is automatically zoomed in - how to initially view whole UIImage?
- reactjs - react-hooks/exhaustive-deps 警告
- javascript - How to push objects with different JSON structure from an http request?
- python - 使用 for loop tkinter 生成唯一按钮
- javascript - 屏幕显示响应式定位精灵与移相器 3
- linux - cat文件中的-是什么意思-
- c# - 如何使用 PrincipalContext 删除 OU
- node.js - 如何管理 NodeJs TCP 客户端
- angular - “Firebase”类型上不存在属性“firestore”
- python - Pass Lock object to class that inherits multiprocessing.Process