hive 表达式不在按键分组中
我在 HIVE 中创建了一个表。 它有以下列：
id bigint, rank bigint, date string
我想获得每月的平均（排名）。我可以使用这个命令。有用。
select a.lens_id, avg(a.rank)
from tableA a
group by a.lens_id, year(a.date_saved), month(a.date_saved);
但是，我还想获取日期信息。我使用这个命令：
select a.lens_id, avg(a.rank), a.date_saved
from lensrank_archive a
group by a.lens_id, year(a.date_saved), month(a.date_saved);
它抱怨：Expression Not In Group By Key
I create a table in HIVE.
It has the following columns:
id bigint, rank bigint, date string
I want to get avg(rank) per month. I can use this command. It works.
select a.lens_id, avg(a.rank)
from tableA a
group by a.lens_id, year(a.date_saved), month(a.date_saved);
However, I also want to get date information. I use this command:
select a.lens_id, avg(a.rank), a.date_saved
from lensrank_archive a
group by a.lens_id, year(a.date_saved), month(a.date_saved);
It complains: Expression Not In Group By Key
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
完整的错误消息应采用以下格式：
Expression Not In Group By Key [value]。[value]将告诉您Group By中需要包含什么表达式。只要看一下这两个查询，我就会说您需要将
a.date_saved显式添加到Group By中。The full error message should be in the format
Expression Not In Group By Key [value].The
[value]will tell you what expression needs to be in theGroup By.Just looking at the two queries, I'd say that you need to add
a.date_savedexplicitly to theGroup By.解决方法是将附加字段放入collect_set 中并返回该集合的第一个元素。例如
A walk around is to put the additional field in a collect_set and return the first element of the set. For example
这是因为您的分组依据下有多个“date_saved”记录。您可以将这些“date_saved”记录转换为数组并输出它们。
This is because there is more than one ‘date_saved’ record under your group by. You can turn these ‘date_saved’ records into arrays and output them.