在hive2.3.3中使用嵌套选择时出现错误消息:"当前上下文中不支持TOK_ALLCOLREF"

Question

当我使用像这样的嵌套选择单元时

Select 
a.month,
a.day,
sum(a.pv)/count(a.*) 
from 
(Select month,day, remote_addr,count(1) as pv 
from ods_weblog_detail group by remote_addr,month,day) as a; 

它返回错误消息："当前上下文中不支持TOK_ALLCOLREF"。

但是当我分别选择a.month、a.day和sum(a.pv(/count(a.*(时，如下所示：

Select 
sum(a.pv)/count(a.*) 
from 
(Select month,day, remote_addr,count(*) as pv from ods_weblog_detail group by remote_addr,month,day) as a;

或者这个：

Select 
a.month,a.day 
from 
(Select month,day, remote_addr,count(*) as pv 
from 
ods_weblog_detail 
group by remote_addr,month,day) as a;

这两种说法都给了我正确的答案那么为什么我不能在一个语句中同时选择这三个(a.month、a.day和sum(a.pv(/count(a.*((呢？非常感谢！！！

Answer 1

查询缺少group by。

Select 
a.month,
a.day,
sum(a.pv)/count(a.*) 
from (select month,day, remote_addr,count(1) as pv 
from ods_weblog_detail 
group by remote_addr,month,day
) a
group by a.month,a.day

查询也可以简化如下。

select month,day,remote_addr
,sum(count(*)) over(partition by month,day,remote_addr)/count(*) over(partition by month,day) as res
from ods_weblog_detail