数仓-ADS-各地区订单统计

lateral view

    -- lateral view首先为原始表的每行调用UDTF(explode,split)，UDTF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表
    -- 主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况

insert overwrite table ads_order_by_province
select * from ads_order_by_province
union
select
    '2021-06-14' dt,
    t1.recent_days,
    t1.province_id,
    t2.province_name,
    t2.area_code,
    t2.iso_code,
    t2.iso_3166_2,
    t1.order_count,
    t1.order_amount
from
(
    select
        recent_days,
        province_id,
        -- 使用case when，根据天数取出dwt_area_topic中对应字段
        case
            when recent_days=1 then order_last_1d_count
            when recent_days=7 then order_last_7d_count
            when recent_days=30 then order_last_30d_count
        end order_count,
        case
            when recent_days=1 then order_last_1d_final_amount
            when recent_days=7 then order_last_7d_final_amount
            when recent_days=30 then order_last_30d_final_amount
        end order_amount
    from dwt_area_topic
    lateral view explode(Array(1,7,30)) tmp as recent_days
    where dt='2021-06-14'
)t1
left join
(
    select
        id,
        province_name,
        area_code,
        iso_code,
        iso_3166_2
    from dim_base_province
)t2
on t1.province_id=t2.id;