用户画像-用户购买类目表

--客户购买类目表
drop table is exists gdm.gdm_user_portrait_cat;
create external table gdm.gdm_user_portrait_cat(
user_id bigint,--客户id
first_cat bigint,--一级分类ID
first_cat_name string,--一级分类名称
second_cat bigint,--二级分类名称
second_cat_name string--二级分类名称
third_cat bigint,--三级分类名称
third_cat_name string,--三级分类名称
month1_cat_cnt bigint,--近30天购买类目次数
month1_cat_amt double,--近30天购买类目金额
month3_cat_cnt bigint,--近90天购买类目次数
month3_cat_amt double,--近90天购买类目金额
month6_cat_cnt bigint,--近180天购买类目次数
month6_cat_amt double,--近180天购买类目金额
total_cat_cnt bigint,--累计购买类目次数
total_cat_amt double,--累计购买类目金额
month1_cart_cat_cnt bigint,--近30天购物车类目次数
month3_cart_cat_cnt bigint,--近90天购物车类目次数
month6_cart_cat_cnt bigint,--近180天购物车类目次数
total_cart_cat_cnt bigint.--累计购物车类目次数
last_cat_time timestamp,--最后一次购买类目时间
last_cat_ago bigint,--最后一次购买类目距今天数
dw_date timestamp,)
partitioned by(dt string);

--用户画像-客户购买商店
drop table if exists temp.tmp_gdm_user_portrait_cat_01;--临时表1
create table temp.tmp_gdm_user_portrait_cat_01 as
select a.user_id,
        null first_cat,
        null first_cat_name,
        null second_cat,
        null second_cat_name,
        b.cat_id third_cat,
        b.cat_name third_cat_name,
        sum(case
            when a.dat_30=1 then
                b.goods_amount
            end) month1_cat_cnt,
        sum(case
            when a.dat_30=1 then
                coalesce(b.goods_price,0)*coalesce(b.goods_amount,0)
            end) month1_cat_amt,
        sum(case
            when a.dat_90=1 then
                b.goods_amount
            end) month3_cat_cnt,
        sum(case
            when a.dat_90=1 then
                coalesce(b.goods_price,0)*coalesce(b.goods_amount,0)
            end) month3_cat_amt,
        sum(case
            when a.dat_180=1 then
                b.goods_amount
            end) month6_cat_cnt,
        sum(case
            when a.dat_180=1 then
                coalesce(b.goods_price,0)*coalesce(b.goods_amount,0)
            end) month6_cat_amt,
        sum(b,goods_amount)total_cat_cnt,
        sum(coalesce(b.goods_price,0)*coalesce(b.goods_amount,0))total_cat_amt,
        max(a.order_date)last_cat_time,
        datediff(min(a.order_date),'2017-05-01')last_cat_ago,
        from_unixtime(unix_timestamp())dw_date
    from(select a.*,
                (case
                    when order_date >= date_sub('2017-05-01',29) and
                        order_date <= '2017-05-01' then
                        1
                    end) dat_30,
                (case
                    when order_date >= date_sub('2017-05-01',89) and
                        order_date <= '2017-05-01' then
                        1
                    end) dat_90,
                (case
                    when order_date >= date_sub('2017-05-01',179) and
                        order_date <= '2017-05-01' then
                        1
                    end) dat_180,
        from fdm.fdm_ord_order a where dt='2017-05-01'
        where dt='2017-05-01'
        and order_status=5) a 
    join (select * from fdm.fdm_ord_order_goods where dt='2017-05-01') b 
    on (a.order_id=b.order_id)
group by a.user_id,b.cat_id,b.cat_name;

--用户画像-购物车情况
drop table if exists temp.tmp_gdm_user_portrait_cat_02;--临时表2
create table temp.tmp_gdm_user_portrait_cat_02 as
select a.user_id,
        b.third_cat,
        sum(case
            when to_date(add_time)>=date_sub('2017-05-01',29) and
                to_date(add_time)<='2017-05-01' then
                1
            end) month1_cart_cat_cnt,
        sum(case
            when to_date(add_time)>=date_sub('2017-05-01',59) and
                to_date(add_time)<='2017-05-01' then
                1
            end) month3_cart_cat_cnt,
        sum(case
            when to_date(add_time)>=date_sub('2017-05-01',179) and
                to_date(add_time)<='2017-05-01' then
                1
            end) month6_cart_cat_cnt,
        count(1) total_cart_cat_cnt,
from(select * 
    from fdm.fdm_ord_cart--购物车表
    where dt-'2017-05-01' 
        and to_date(add_time)>=date_sub('2017-05-01',179)
        and to_date(add_time)<='2017-05-01') a 
left join(select goods_id,third_cat
            from fdm.fdm_prd_goods--商品表
            where dt='2017-05-01'
            group by goods_id,third_cat) b 
    on (a.goods_id=b.goods_id)
group by user_id,b.third_cat;

--用户画像--把类目购买和类目放入购物车的两张表union起来
drop table if exists temp.tmp_gdm_user_portrait_cat_99;
create table temp.tmp_gdm_user_portrait_cat_99 as
select a.user_id,
        first_cat,
        first_cat_name，
        second_cat,
        second_cat_name,
        a.third_cat,
        third_cat_name
    from(select user_id,third_cat
            from temp.tmp_gdm_user_portrait_cat_01
        union all
        select user_id.third_cat from temp.tmp_gdm_user_portrait_cat_02) a 
    left join dim.dim_category b --dim_category类目维表
        on (a.third_cat=b.third_cat)
    group by a.user_id,
            first_cat,
            first_cat_name,
            second_cat,
            second_cat_name,
            a.third_cat,
            third_cat_name;

--把数据插入到目标表
insert overwrite table gdm.gdm_user_portrait_cat partition(dt='2017-05-01')
select t.user_id,
        t.first_cat,
        t.first_cat_name,
        t.second_cat,
        t.second_cat_name,
        t.third_cat,
        t.third_cat_name,
        t1.month1_cat_cnt,
        t1.month1_cat_amt,
        t1.month3_cat_cnt,
        t1.month3_cat_amt,
        t1.month6_cat_cnt,
        t1.month6_cat_amt,
        t1.total_cat_cnt,
        t1.total_cat_amt,
        t2.month1_cart_cat_cnt,
        t2.month3_cart_cat_cnt,
        t2.month6_cart_cat_cnt,
        t2.total_cart_cat_cnt,
        t1.last_cat_time,
        t1.last_cat_ago,
        from_unixtime(inux_timestamp()) dw_date
    from temp.tmp_gdm_user_portait_cat_99 t 
    left join temp.tmp_gdm_user_portrait_cat_01 t1 
        on (t.user_id = t1.user_id and t.third_cat = t1.third_cat)
    left join temp.tmp_gdm_user_portrait_cat_02 t2 
        on (t.user_id = t2.user_id and t.third_cat = t2.third_cat);