蜂房表达式不是按键在组中

I create a table in HIVE. It has the following columns:

我在HIVE中创建了一个表格。它有以下几栏:

id bigint, rank bigint, date string

I want to get avg(rank) per month. I can use this command. It works.

我想获得avg(等级)每月。我可以使用这个命令。它的工作原理。

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

它报错:表达式不在组中按键

3 个解决方案

#1

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 the Group By.

完整的错误消息应该在格式表达式中，而不是按键[值]分组。[value]将告诉您组中需要使用什么表达式。

Just looking at the two queries, I'd say that you need to add a.date_saved explicitly to the Group By.

看看这两个查询，我想说你需要添加a。date_显式地保存给组By。

#2

A walk around is to put the additional field in a collect_set and return the first element of the set. For example

在collect_set中放置附加字段并返回集合的第一个元素

select a.lens_id, avg(a.rank), collect_set(a.date_saved)[0]
from lensrank_archive a
group by a.lens_id, year(a.date_saved), month(a.date_saved);

#3

-1

I was also facing the same problem. If you are trying to execute the query using beeline then write your query in lowercase. Like this :

我也面临着同样的问题。如果您试图使用beeline执行查询，那么请用小写的形式编写查询。是这样的:

select column_name(s)
from table_name
where condition
group by column_name(s).

Writing it in lowercase worked for me try it I think it will work.

用小写字母写对我来说很有用我想可以。

#1