04月02 hive的用as重定义的字段进行group的问题
hql中的group by不能直接用as重定义的别名进行分组
但是可以将as前整体的计算式作为groub by的一个字段
即可以用字段的计算结果进行分组,但不能直接用别名
例:
select
channel_id,
client_ip,
count(distinct client_cuid) as cuid_num,
(cast(from_unixtime(cast(server_timestamp as int),’HH’) as int) * 60 + cast(from_unixtime(cast(server_timestamp as int),’mm’) as int) ) as minute
from insight_mc_appsearch_union
where event_day = ‘20140309’
and activate_flag = ‘0’
and action_type = ‘0’
and access_server_flag = ‘appsearch’
and event_product = ‘appsearch’
and app_name = ‘appsearch’
group by channel_id,client_ip,minute
上面写法会报错
select
channel_id,
client_ip,
count(distinct client_cuid) as cuid_num,
(cast(from_unixtime(cast(server_timestamp as int),’HH’) as int) * 60 + cast(from_unixtime(cast(server_timestamp as int),’mm’) as int) ) as minute
from insight_mc_appsearch_union
where event_day = ‘20140309’
and activate_flag = ‘0’
and action_type = ‘0’
and access_server_flag = ‘appsearch’
and event_product = ‘appsearch’
and app_name = ‘appsearch’
group by channel_id,client_ip,(cast(from_unixtime(cast(server_timestamp as int),’HH’) as int) * 60 + cast(from_unixtime(cast(server_timestamp as int),’mm’) as int) )
把as前的整体拷过来就可以