目录
六.使用explode()和group by配合使用进行分组
一:获取数据并且插入到表中
use namespace hehe;
// 名称
set mapred.job.name=v_haha_heheyoutubehehe_ads_people_kehuduan_experience_data_{DATE};
-- 以下选填
set hive.map.aggr=true; //开启Map 端聚合参数设置
// 有数据倾斜的时候进行负载均衡(默认是false)
set hive.groupby.skewindata=true;
insert overwrite table udw_ns.default.temp_table
partition(event_day='{DATE}',dim_type='heheyoutubehehe_ads_people_kehuduan_experience_data')
select
concat_ws(
'\t',
cast('{DATE}' as string),
cast(coalesce(os, '-') as string),
cast(coalesce(soft_version, '-') as string),
cast(coalesce(is_app_new, '-') as string),
cast(coalesce(new_cnt, 0) AS string),
cast(coalesce(is_open_cnt, 0) AS string),
cast(coalesce(no_open_cnt, 0) AS string),
cast(coalesce(disp_pv, 0) AS string),
cast(coalesce(disp_uv, 0) AS string),
cast(coalesce(agree_clk_uv, 0) AS string),
cast(coalesce(disagree_clk_uv, 0) AS string),
cast(coalesce(second_disp_pv, 0) AS string),
cast(coalesce(second_disp_uv, 0) AS string),
cast(coalesce(second_agree_clk_uv, 0) AS string),
cast(coalesce(second_think_clk_uv, 0) AS string),
cast(coalesce(third_disp_pv, 0) AS string),
cast(coalesce(third_disp_uv, 0) AS string),
cast(coalesce(third_check_agreement_clk_uv, 0) AS string),
cast(coalesce(third_exit_clk_uv, 0) AS string),
) as dim_value
from(
select
C.os as os,
C.soft_version as soft_version,
C.is_app_new as is_app_new,
count(case when k = 'display' and v = 'privacy_pop' then b.cuid end) as disp_pv, -- 弹窗首次引导展现pv(验证)
count(distinct case when k = 'display' and v = 'privacy_pop' then b.cuid end) as disp_uv, -- 弹窗首次引导展现uv(验证)
count(distinct case when k = 'agree_clk' and v = 'privacy_pop' then b.cuid end) as agree_clk_uv, -- 弹窗首次引导「同意并继续」点击uv
count(distinct case when k = 'disagree_clk' and v = 'privacy_pop' then b.cuid end) as disagree_clk_uv, -- 弹窗首次引导「不同意」点击uv
count(case when k = 'display' and v = 'privacy_pop_again' then b.cuid end) as second_disp_pv, -- 弹窗二次挽留展现pv
count(distinct case when k = 'display' and v = 'privacy_pop_again' then b.cuid end) as second_disp_uv, -- 弹窗二次挽留展现uv
count(distinct case when k = 'agree_clk' and v = 'privacy_pop_again' then b.cuid end) a