维度爆炸背景下uv计算在Feed业务的高效实践

-- 表名：feed_dws_kpi_dau_1d-- 字段名及注释：appid##产品线，pay_type##付费类型，r_type##资源类型，tab_type##频道类型，page_type##页面类型，cuid##用户标识
select    appid_all,  -- 产品线    pay_type_all, -- 付费类型    r_type_all,  -- 资源类型    tab_type_all,  -- 频道类型    page_type_all,  -- 页面类型    count(distinct cuid) as feed_daufrom(    select        cuid,        appid,        pay_type,        r_type,        tab_type,        page_type    from feed_dws_kpi_dau_1d    group by 1,2,3,4,5,6) tablateral view explode(array(appid, 'all')) B as appid_all lateral view explode(array(pay_type, 'all')) B as pay_type_all lateral view explode(array(r_type, 'all')) B as r_type_all lateral view explode(array(tab_type, 'all')) B as tab_type_alllateral view explode(array(page_type, 'all')) B as page_type_allgroup by 1,2,3,4,5

-- 基于明细数据产出维度结果数据，并进行编码with dim_res as (    select        distinct        appid_all,  -- 产品线        pay_type_all, -- 付费类型        r_type_all,  -- 资源类型        tab_type_all,  -- 频道类型        page_type_all,  -- 页面类型        dim_key,        DENSE_RANK() OVER(ORDER BY appid_all,pay_type_all,r_type_all,tab_type_all,page_type_all) AS dim_id     from(        select            appid,            pay_type,            r_type,            tab_type,            page_type,            concat_ws(                '#',                coalesce(appid,'unknow'),                coalesce(pay_type,'unknow'),                coalesce(r_type,'unknow'),                coalesce(tab_type,'unknow'),                coalesce(page_type,'unknow')            ) as dim_key        from feed_dws_kpi_dau_1d        group by 1,2,3,4,5,6    ) t0    lateral view explode(array(appid, 'all')) B as appid_all     lateral view explode(array(pay_type, 'all')) B as pay_type_all     lateral view explode(array(r_type, 'all')) B as r_type_all     lateral view explode(array(tab_type, 'all')) B as tab_type_all    lateral view explode(array(page_type, 'all')) B as page_type_all),
-- 生成cuid聚合数据+对应的维度编码组合cuid_dim as(    select /*+ MAPJOIN(t1) */        cuid,        array_distinct(split(concat_ws(',',collect_set(concat_ws(',',dim_id_arry))),',')) as click_dim_id_arry    from(        select            cuid,            concat_ws(                '#',                coalesce(appid,'unknow'),                coalesce(pay_type,'unknow'),                coalesce(r_type,'unknow'),                coalesce(tab_type,'unknow'),                coalesce(page_type,'unknow')            ) as dim_key        from feed_dws_kpi_dau_1d        group by 1,2    ) t0    join (        -- 生成每个维度原始值对应的编码数组，减少shuffle过程的数据量        select            dim_key,            collect_set(dim_id) as dim_id_arry        from dim_res        group by dim_key    ) t1 on t0.dim_key = t1.dim_key    group by cuid)
-- 将维度编码回写为原始日志select /*+ MAPJOIN(t1) */    appid_all,  -- 产品线    pay_type_all, -- 付费类型    r_type_all,  -- 资源类型    tab_type_all,  -- 频道类型    page_type_all,  -- 页面类型    feed_daufrom(    select        -- 基于维度编码进行计数        dim_id,        sum(feed_dau) as feed_dau    from(        -- 将维度数组转为字符串直接求和        select            concat_ws(',',click_dim_id_arry) as dim_id_str,            count(1) as feed_dau        from cuid_dim        group by 1    ) tab    lateral view explode(split(dim_id_str,',')) B as dim_id    group by dim_id) t0join (    select         distinct        appid_all,  -- 产品线        pay_type_all, -- 付费类型        r_type_all,  -- 资源类型        tab_type_all,  -- 频道类型        page_type_all,  -- 页面类型        dim_id     from dim_res) t1 on t0.dim_id = t1.dim_idorder by 1,2,3,4,5,6