dwt_sku_topic

商品主题宽表

类似累积事实表

主题宽表基本这个套路

drop table if exists dwt_sku_topic; 
create external table dwt_sku_topic 
( 
    sku_id string comment 'sku_id', 
    spu_id string comment 'spu_id', 
    order_last_30d_count bigint comment '最近 30 日被下单次数', 
    order_last_30d_num bigint comment '最近 30 日被下单件数', 
    order_last_30d_amount decimal(16,2) comment '最近 30 日被下单金额', 
    order_count bigint comment '累积被下单次数', 
    order_num bigint comment '累积被下单件数', 
    order_amount decimal(16,2) comment '累积被下单金额', 
    payment_last_30d_count bigint comment '最近 30 日被支付次数', 
    payment_last_30d_num bigint comment '最近 30 日被支付件数', 
    payment_last_30d_amount decimal(16,2) comment '最近 30 日被支付金额', 
    payment_count bigint comment '累积被支付次数', 
    payment_num bigint comment '累积被支付件数', 
    payment_amount decimal(16,2) comment '累积被支付金额', 
    refund_last_30d_count bigint comment '最近三十日退款次数', 
    refund_last_30d_num bigint comment '最近三十日退款件数',
    refund_last_30d_amount decimal(10,2) comment '最近三十日退款金额', 
    refund_count bigint comment '累积退款次数', 
    refund_num bigint comment '累积退款件数', 
    refund_amount decimal(10,2) comment '累积退款金额', 
    cart_last_30d_count bigint comment '最近 30 日被加入购物车次数', 
    cart_last_30d_num bigint comment '最近 30 日被加入购物车件数', 
    cart_count bigint comment '累积被加入购物车次数', 
    cart_num bigint comment '累积被加入购物车件数', 
    favor_last_30d_count bigint comment '最近 30 日被收藏次数', 
    favor_count bigint comment '累积被收藏次数', 
    appraise_last_30d_good_count bigint comment '最近 30 日好评数', 
    appraise_last_30d_mid_count bigint comment '最近 30 日中评数', 
    appraise_last_30d_bad_count bigint comment '最近 30 日差评数', 
    appraise_last_30d_default_count bigint comment '最近 30 日默认评价数', 
    appraise_good_count bigint comment '累积好评数', 
    appraise_mid_count bigint comment '累积中评数', 
    appraise_bad_count bigint comment '累积差评数', 
    appraise_default_count bigint comment '累积默认评价数' 
)COMMENT '商品主题宽表' 
stored as parquet location '/ecdw/dwt/dwt_sku_topic/' 
tblproperties ("parquet.compression"="lzo");



-- 插入
insert overwrite table dwt_sku_topic 
select 
    nvl(new.sku_id,old.sku_id), 
    sku_info.spu_id, 
    nvl(new.order_count30,0), 
    nvl(new.order_num30,0), 
    nvl(new.order_amount30,0), 
    nvl(old.order_count,0) + nvl(new.order_count,0), 
    nvl(old.order_num,0) + nvl(new.order_num,0), 
    nvl(old.order_amount,0) + nvl(new.order_amount,0), 
    nvl(new.payment_count30,0), 
    nvl(new.payment_num30,0), 
    nvl(new.payment_amount30,0), 
    nvl(old.payment_count,0) + nvl(new.payment_count,0), 
    nvl(old.payment_num,0) + nvl(new.payment_count,0), 
    nvl(old.payment_amount,0) + nvl(new.payment_count,0), 
    nvl(new.refund_count30,0), 
    nvl(new.refund_num30,0), 
    nvl(new.refund_amount30,0), 
    nvl(old.refund_count,0) + nvl(new.refund_count,0), 
    nvl(old.refund_num,0) + nvl(new.refund_num,0), 
    nvl(old.refund_amount,0) + nvl(new.refund_amount,0), 
    nvl(new.cart_count30,0), 
    nvl(new.cart_num30,0), 
    nvl(old.cart_count,0) + nvl(new.cart_count,0), 
    nvl(old.cart_num,0) + nvl(new.cart_num,0), 
    nvl(new.favor_count30,0), 
    nvl(old.favor_count,0) + nvl(new.favor_count,0), 
    nvl(new.appraise_good_count30,0), 
    nvl(new.appraise_mid_count30,0), 
    nvl(new.appraise_bad_count30,0), 
    nvl(new.appraise_default_count30,0) , 
    nvl(old.appraise_good_count,0) + nvl(new.appraise_good_count,0), 
    nvl(old.appraise_mid_count,0) + nvl(new.appraise_mid_count,0), 
    nvl(old.appraise_bad_count,0) + nvl(new.appraise_bad_count,0), 
    nvl(old.appraise_default_count,0) + nvl(new.appraise_default_count,0)
from 
( 
    select 
        sku_id, 
        spu_id,
        order_last_30d_count, 
        order_last_30d_num, 
        order_last_30d_amount, 
        order_count, 
        order_num, 
        order_amount, 
        payment_last_30d_count, 
        payment_last_30d_num, 
        payment_last_30d_amount, 
        payment_count, payment_num, 
        payment_amount, 
        refund_last_30d_count, 
        refund_last_30d_num, 
        refund_last_30d_amount, 
        refund_count, refund_num, 
        refund_amount, 
        cart_last_30d_count, 
        cart_last_30d_num, 
        cart_count, cart_num, 
        favor_last_30d_count, 
        favor_count, 
        appraise_last_30d_good_count, 
        appraise_last_30d_mid_count, 
        appraise_last_30d_bad_count, 
        appraise_last_30d_default_count, 
        appraise_good_count, 
        appraise_mid_count, 
        appraise_bad_count, 
        appraise_default_count 
    from dwt_sku_topic 
)old 

full outer join 
( 
    select 
        sku_id, 
        sum(if(dt='2020-03-10', order_count,0 )) order_count, -- 取3-10号的数据,用于累积天数计算
        sum(if(dt='2020-03-10',order_num ,0 )) order_num, 
        sum(if(dt='2020-03-10',order_amount,0 )) order_amount, 
        sum(if(dt='2020-03-10',payment_count,0 )) payment_count, 
        sum(if(dt='2020-03-10',payment_num,0 )) payment_num, 
        sum(if(dt='2020-03-10',payment_amount,0 )) payment_amount, 
        sum(if(dt='2020-03-10',refund_count,0 )) refund_count, 
        sum(if(dt='2020-03-10',refund_num,0 )) refund_num, 
        sum(if(dt='2020-03-10',refund_amount,0 )) refund_amount, 
        sum(if(dt='2020-03-10',cart_count,0 )) cart_count, 
        sum(if(dt='2020-03-10',cart_num,0 )) cart_num, 
        sum(if(dt='2020-03-10',favor_count,0 )) favor_count, 
        sum(if(dt='2020-03-10',appraise_good_count,0 )) appraise_good_count, 
        sum(if(dt='2020-03-10',appraise_mid_count,0 ) ) appraise_mid_count , 
        sum(if(dt='2020-03-10',appraise_bad_count,0 )) appraise_bad_count, 
        sum(if(dt='2020-03-10',appraise_default_count,0 )) appraise_default_count, 
        sum(order_count) order_count30,   -- 用于最近30天的计算
        sum(order_num) order_num30, 
        sum(order_amount) order_amount30, 
        sum(payment_count) payment_count30,
        sum(payment_num) payment_num30, 
        sum(payment_amount) payment_amount30, 
        sum(refund_count) refund_count30, 
        sum(refund_num) refund_num30, 
        sum(refund_amount) refund_amount30, 
        sum(cart_count) cart_count30, 
        sum(cart_num) cart_num30, 
        sum(favor_count) favor_count30, 
        sum(appraise_good_count) appraise_good_count30, 
        sum(appraise_mid_count) appraise_mid_count30, 
        sum(appraise_bad_count) appraise_bad_count30, 
        sum(appraise_default_count) appraise_default_count30 
    from dws_sku_action_daycount 
    where dt >= date_add ('2020-03-10', -30) 
    group by sku_id 
)new 
on new.sku_id = old.sku_id

--获取spu_id
left join 
(
    select 
        * 
    from 
    dwd_dim_sku_info 
    where dt='2020-03-10'
) sku_info 
on nvl(new.sku_id,old.sku_id)= sku_info.id;

 

上一篇:【小波变换】基于matlab GUI界面DWT与SVD算法的数字水印 【含Matlab源码 253期】


下一篇:hive分区表快速复制