hive的用as重定义的字段进行group的问题 - Tobycc
49
post-template-default,single,single-post,postid-49,single-format-standard,ajax_fade,page_not_loaded,,qode-theme-ver-13.5,qode-theme-bridge,disabled_footer_bottom,wpb-js-composer js-comp-ver-5.4.5,vc_responsive

hive的用as重定义的字段进行group的问题

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前的整体拷过来就可以

标签:
, , , ,