商品主题
建表语句：
DROP TABLE IF EXISTS dwt_sku_topic;
CREATE EXTERNAL TABLE dwt_sku_topic
(
`sku_id` STRING COMMENT 'sku_id',
`order_last_1d_count` BIGINT COMMENT '最近1日被下单次数',
`order_last_1d_num` BIGINT COMMENT '最近1日被下单件数',
`order_activity_last_1d_count` BIGINT COMMENT '最近1日参与活动被下单次数',
`order_coupon_last_1d_count` BIGINT COMMENT '最近1日使用优惠券被下单次数',
`order_activity_reduce_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日优惠金额(活动)',
`order_coupon_reduce_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日优惠金额(优惠券)',
`order_last_1d_original_amount` DECIMAL(16,2) COMMENT '最近1日被下单原始金额',
`order_last_1d_final_amount` DECIMAL(16,2) COMMENT '最近1日被下单最终金额',
`order_last_7d_count` BIGINT COMMENT '最近7日被下单次数',
`order_last_7d_num` BIGINT COMMENT '最近7日被下单件数',
`order_activity_last_7d_count` BIGINT COMMENT '最近7日参与活动被下单次数',
`order_coupon_last_7d_count` BIGINT COMMENT '最近7日使用优惠券被下单次数',
`order_activity_reduce_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日优惠金额(活动)',
`order_coupon_reduce_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日优惠金额(优惠券)',
`order_last_7d_original_amount` DECIMAL(16,2) COMMENT '最近7日被下单原始金额',
`order_last_7d_final_amount` DECIMAL(16,2) COMMENT '最近7日被下单最终金额',
`order_last_30d_count` BIGINT COMMENT '最近30日被下单次数',
`order_last_30d_num` BIGINT COMMENT '最近30日被下单件数',
`order_activity_last_30d_count` BIGINT COMMENT '最近30日参与活动被下单次数',
`order_coupon_last_30d_count` BIGINT COMMENT '最近30日使用优惠券被下单次数',
`order_activity_reduce_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日优惠金额(活动)',
`order_coupon_reduce_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日优惠金额(优惠券)',
`order_last_30d_original_amount` DECIMAL(16,2) COMMENT '最近30日被下单原始金额',
`order_last_30d_final_amount` DECIMAL(16,2) COMMENT '最近30日被下单最终金额',
`order_count` BIGINT COMMENT '累积被下单次数',
`order_num` BIGINT COMMENT '累积被下单件数',
`order_activity_count` BIGINT COMMENT '累积参与活动被下单次数',
`order_coupon_count` BIGINT COMMENT '累积使用优惠券被下单次数',
`order_activity_reduce_amount` DECIMAL(16,2) COMMENT '累积优惠金额(活动)',
`order_coupon_reduce_amount` DECIMAL(16,2) COMMENT '累积优惠金额(优惠券)',
`order_original_amount` DECIMAL(16,2) COMMENT '累积被下单原始金额',
`order_final_amount` DECIMAL(16,2) COMMENT '累积被下单最终金额',
`payment_last_1d_count` BIGINT COMMENT '最近1日被支付次数',
`payment_last_1d_num` BIGINT COMMENT '最近1日被支付件数',
`payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日被支付金额',
`payment_last_7d_count` BIGINT COMMENT '最近7日被支付次数',
`payment_last_7d_num` BIGINT COMMENT '最近7日被支付件数',
`payment_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日被支付金额',
`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_order_last_1d_count` BIGINT COMMENT '最近1日退单次数',
`refund_order_last_1d_num` BIGINT COMMENT '最近1日退单件数',
`refund_order_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日退单金额',
`refund_order_last_7d_count` BIGINT COMMENT '最近7日退单次数',
`refund_order_last_7d_num` BIGINT COMMENT '最近7日退单件数',
`refund_order_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日退单金额',
`refund_order_last_30d_count` BIGINT COMMENT '最近30日退单次数',
`refund_order_last_30d_num` BIGINT COMMENT '最近30日退单件数',
`refund_order_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日退单金额',
`refund_order_count` BIGINT COMMENT '累积退单次数',
`refund_order_num` BIGINT COMMENT '累积退单件数',
`refund_order_amount` DECIMAL(16,2) COMMENT '累积退单金额',
`refund_payment_last_1d_count` BIGINT COMMENT '最近1日退款次数',
`refund_payment_last_1d_num` BIGINT COMMENT '最近1日退款件数',
`refund_payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日退款金额',
`refund_payment_last_7d_count` BIGINT COMMENT '最近7日退款次数',
`refund_payment_last_7d_num` BIGINT COMMENT '最近7日退款件数',
`refund_payment_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日退款金额',
`refund_payment_last_30d_count` BIGINT COMMENT '最近30日退款次数',
`refund_payment_last_30d_num` BIGINT COMMENT '最近30日退款件数',
`refund_payment_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日退款金额',
`refund_payment_count` BIGINT COMMENT '累积退款次数',
`refund_payment_num` BIGINT COMMENT '累积退款件数',
`refund_payment_amount` DECIMAL(16,2) COMMENT '累积退款金额',
`cart_last_1d_count` BIGINT COMMENT '最近1日被加入购物车次数',
`cart_last_7d_count` BIGINT COMMENT '最近7日被加入购物车次数',
`cart_last_30d_count` BIGINT COMMENT '最近30日被加入购物车次数',
`cart_count` BIGINT COMMENT '累积被加入购物车次数',
`favor_last_1d_count` BIGINT COMMENT '最近1日被收藏次数',
`favor_last_7d_count` BIGINT COMMENT '最近7日被收藏次数',
`favor_last_30d_count` BIGINT COMMENT '最近30日被收藏次数',
`favor_count` BIGINT COMMENT '累积被收藏次数',
`appraise_last_1d_good_count` BIGINT COMMENT '最近1日好评数',
`appraise_last_1d_mid_count` BIGINT COMMENT '最近1日中评数',
`appraise_last_1d_bad_count` BIGINT COMMENT '最近1日差评数',
`appraise_last_1d_default_count` BIGINT COMMENT '最近1日默认评价数',
`appraise_last_7d_good_count` BIGINT COMMENT '最近7日好评数',
`appraise_last_7d_mid_count` BIGINT COMMENT '最近7日中评数',
`appraise_last_7d_bad_count` BIGINT COMMENT '最近7日差评数',
`appraise_last_7d_default_count` BIGINT COMMENT '最近7日默认评价数',
`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 '商品主题宽表'
PARTITIONED BY (`dt` STRING)
STORED AS PARQUET
LOCATION '/warehouse/gmall/dwt/dwt_sku_topic/'
TBLPROPERTIES ("parquet.compression"="lzo");数据装载
每日装载：
insert overwrite table dwt_sku_topic partition(dt='2020-06-15')
select
nvl(1d_ago.sku_id,old.sku_id),
nvl(1d_ago.order_count,0),
nvl(1d_ago.order_num,0),
nvl(1d_ago.order_activity_count,0),
nvl(1d_ago.order_coupon_count,0),
nvl(1d_ago.order_activity_reduce_amount,0.0),
nvl(1d_ago.order_coupon_reduce_amount,0.0),
nvl(1d_ago.order_original_amount,0.0),
nvl(1d_ago.order_final_amount,0.0),
nvl(old.order_last_7d_count,0)+nvl(1d_ago.order_count,0)- nvl(7d_ago.order_count,0),
nvl(old.order_last_7d_num,0)+nvl(1d_ago.order_num,0)- nvl(7d_ago.order_num,0),
nvl(old.order_activity_last_7d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(7d_ago.order_activity_count,0),
nvl(old.order_coupon_last_7d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(7d_ago.order_coupon_count,0),
nvl(old.order_activity_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(7d_ago.order_activity_reduce_amount,0.0),
nvl(old.order_coupon_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(7d_ago.order_coupon_reduce_amount,0.0),
nvl(old.order_last_7d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(7d_ago.order_original_amount,0.0),
nvl(old.order_last_7d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(7d_ago.order_final_amount,0.0),
nvl(old.order_last_30d_count,0)+nvl(1d_ago.order_count,0)- nvl(30d_ago.order_count,0),
nvl(old.order_last_30d_num,0)+nvl(1d_ago.order_num,0)- nvl(30d_ago.order_num,0),
nvl(old.order_activity_last_30d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(30d_ago.order_activity_count,0),
nvl(old.order_coupon_last_30d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(30d_ago.order_coupon_count,0),
nvl(old.order_activity_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(30d_ago.order_activity_reduce_amount,0.0),
nvl(old.order_coupon_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(30d_ago.order_coupon_reduce_amount,0.0),
nvl(old.order_last_30d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(30d_ago.order_original_amount,0.0),
nvl(old.order_last_30d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(30d_ago.order_final_amount,0.0),
nvl(old.order_count,0)+nvl(1d_ago.order_count,0),
nvl(old.order_num,0)+nvl(1d_ago.order_num,0),
nvl(old.order_activity_count,0)+nvl(1d_ago.order_activity_count,0),
nvl(old.order_coupon_count,0)+nvl(1d_ago.order_coupon_count,0),
nvl(old.order_activity_reduce_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0),
nvl(old.order_coupon_reduce_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0),
nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0),
nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0),
nvl(1d_ago.payment_count,0),
nvl(1d_ago.payment_num,0),
nvl(1d_ago.payment_amount,0.0),
nvl(old.payment_last_7d_count,0)+nvl(1d_ago.payment_count,0)- nvl(7d_ago.payment_count,0),
nvl(old.payment_last_7d_num,0)+nvl(1d_ago.payment_num,0)- nvl(7d_ago.payment_num,0),
nvl(old.payment_last_7d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(7d_ago.payment_amount,0.0),
nvl(old.payment_last_30d_count,0)+nvl(1d_ago.payment_count,0)- nvl(30d_ago.payment_count,0),
nvl(old.payment_last_30d_num,0)+nvl(1d_ago.payment_num,0)- nvl(30d_ago.payment_num,0),
nvl(old.payment_last_30d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(30d_ago.payment_amount,0.0),
nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0),
nvl(old.payment_num,0)+nvl(1d_ago.payment_num,0),
nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0),
nvl(old.refund_order_last_1d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(1d_ago.refund_order_count,0),
nvl(old.refund_order_last_1d_num,0)+nvl(1d_ago.refund_order_num,0)- nvl(1d_ago.refund_order_num,0),
nvl(old.refund_order_last_1d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(1d_ago.refund_order_amount,0.0),
nvl(old.refund_order_last_7d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(7d_ago.refund_order_count,0),
nvl(old.refund_order_last_7d_num,0)+nvl(1d_ago.refund_order_num,0)- nvl(7d_ago.refund_order_num,0),
nvl(old.refund_order_last_7d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(7d_ago.refund_order_amount,0.0),
nvl(old.refund_order_last_30d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(30d_ago.refund_order_count,0),
nvl(old.refund_order_last_30d_num,0)+nvl(1d_ago.refund_order_num,0)- nvl(30d_ago.refund_order_num,0),
nvl(old.refund_order_last_30d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(30d_ago.refund_order_amount,0.0),
nvl(old.refund_order_count,0)+nvl(1d_ago.refund_order_count,0),
nvl(old.refund_order_num,0)+nvl(1d_ago.refund_order_num,0),
nvl(old.refund_order_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0),
nvl(1d_ago.refund_payment_count,0),
nvl(1d_ago.refund_payment_num,0),
nvl(1d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_last_7d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(7d_ago.refund_payment_count,0),
nvl(old.refund_payment_last_7d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(7d_ago.refund_payment_num,0),
nvl(old.refund_payment_last_7d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(7d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_last_30d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(30d_ago.refund_payment_count,0),
nvl(old.refund_payment_last_30d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(30d_ago.refund_payment_num,0),
nvl(old.refund_payment_last_30d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(30d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_count,0)+nvl(1d_ago.refund_payment_count,0),
nvl(old.refund_payment_num,0)+nvl(1d_ago.refund_payment_num,0),
nvl(old.refund_payment_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0),
nvl(1d_ago.cart_count,0),
nvl(old.cart_last_7d_count,0)+nvl(1d_ago.cart_count,0)- nvl(7d_ago.cart_count,0),
nvl(old.cart_last_30d_count,0)+nvl(1d_ago.cart_count,0)- nvl(30d_ago.cart_count,0),
nvl(old.cart_count,0)+nvl(1d_ago.cart_count,0),
nvl(1d_ago.favor_count,0),
nvl(old.favor_last_7d_count,0)+nvl(1d_ago.favor_count,0)- nvl(7d_ago.favor_count,0),
nvl(old.favor_last_30d_count,0)+nvl(1d_ago.favor_count,0)- nvl(30d_ago.favor_count,0),
nvl(old.favor_count,0)+nvl(1d_ago.favor_count,0),
nvl(1d_ago.appraise_good_count,0),
nvl(1d_ago.appraise_mid_count,0),
nvl(1d_ago.appraise_bad_count,0),
nvl(1d_ago.appraise_default_count,0),
nvl(old.appraise_last_7d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(7d_ago.appraise_good_count,0),
nvl(old.appraise_last_7d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)- nvl(7d_ago.appraise_mid_count,0),
nvl(old.appraise_last_7d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)- nvl(7d_ago.appraise_bad_count,0),
nvl(old.appraise_last_7d_default_count,0)+nvl(1d_ago.appraise_default_count,0)- nvl(7d_ago.appraise_default_count,0),
nvl(old.appraise_last_30d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(30d_ago.appraise_good_count,0),
nvl(old.appraise_last_30d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)- nvl(30d_ago.appraise_mid_count,0),
nvl(old.appraise_last_30d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)- nvl(30d_ago.appraise_bad_count,0),
nvl(old.appraise_last_30d_default_count,0)+nvl(1d_ago.appraise_default_count,0)- nvl(30d_ago.appraise_default_count,0),
nvl(old.appraise_good_count,0)+nvl(1d_ago.appraise_good_count,0),
nvl(old.appraise_mid_count,0)+nvl(1d_ago.appraise_mid_count,0),
nvl(old.appraise_bad_count,0)+nvl(1d_ago.appraise_bad_count,0),
nvl(old.appraise_default_count,0)+nvl(1d_ago.appraise_default_count,0)
from
(
select
sku_id,
order_last_1d_count,
order_last_1d_num,
order_activity_last_1d_count,
order_coupon_last_1d_count,
order_activity_reduce_last_1d_amount,
order_coupon_reduce_last_1d_amount,
order_last_1d_original_amount,
order_last_1d_final_amount,
order_last_7d_count,
order_last_7d_num,
order_activity_last_7d_count,
order_coupon_last_7d_count,
order_activity_reduce_last_7d_amount,
order_coupon_reduce_last_7d_amount,
order_last_7d_original_amount,
order_last_7d_final_amount,
order_last_30d_count,
order_last_30d_num,
order_activity_last_30d_count,
order_coupon_last_30d_count,
order_activity_reduce_last_30d_amount,
order_coupon_reduce_last_30d_amount,
order_last_30d_original_amount,
order_last_30d_final_amount,
order_count,
order_num,
order_activity_count,
order_coupon_count,
order_activity_reduce_amount,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_last_1d_count,
payment_last_1d_num,
payment_last_1d_amount,
payment_last_7d_count,
payment_last_7d_num,
payment_last_7d_amount,
payment_last_30d_count,
payment_last_30d_num,
payment_last_30d_amount,
payment_count,
payment_num,
payment_amount,
refund_order_last_1d_count,
refund_order_last_1d_num,
refund_order_last_1d_amount,
refund_order_last_7d_count,
refund_order_last_7d_num,
refund_order_last_7d_amount,
refund_order_last_30d_count,
refund_order_last_30d_num,
refund_order_last_30d_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_last_1d_count,
refund_payment_last_1d_num,
refund_payment_last_1d_amount,
refund_payment_last_7d_count,
refund_payment_last_7d_num,
refund_payment_last_7d_amount,
refund_payment_last_30d_count,
refund_payment_last_30d_num,
refund_payment_last_30d_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
cart_last_1d_count,
cart_last_7d_count,
cart_last_30d_count,
cart_count,
favor_last_1d_count,
favor_last_7d_count,
favor_last_30d_count,
favor_count,
appraise_last_1d_good_count,
appraise_last_1d_mid_count,
appraise_last_1d_bad_count,
appraise_last_1d_default_count,
appraise_last_7d_good_count,
appraise_last_7d_mid_count,
appraise_last_7d_bad_count,
appraise_last_7d_default_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
where dt=date_add('2020-06-15',-1)
)old
full outer join
(
select
sku_id,
order_count,
order_num,
order_activity_count,
order_coupon_count,
order_activity_reduce_amount,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_num,
payment_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
cart_count,
favor_count,
appraise_good_count,
appraise_mid_count,
appraise_bad_count,
appraise_default_count
from dws_sku_action_daycount
where dt='2020-06-15'
)1d_ago
on old.sku_id=1d_ago.sku_id
left join
(
select
sku_id,
order_count,
order_num,
order_activity_count,
order_coupon_count,
order_activity_reduce_amount,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_num,
payment_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
cart_count,
favor_count,
appraise_good_count,
appraise_mid_count,
appraise_bad_count,
appraise_default_count
from dws_sku_action_daycount
where dt=date_add('2020-06-15',-7)
)7d_ago
on old.sku_id=7d_ago.sku_id
left join
(
select
sku_id,
order_count,
order_num,
order_activity_count,
order_coupon_count,
order_activity_reduce_amount,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_num,
payment_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
cart_count,
favor_count,
appraise_good_count,
appraise_mid_count,
appraise_bad_count,
appraise_default_count
from dws_sku_action_daycount
where dt=date_add('2020-06-15',-30)
)30d_ago
on old.sku_id=30d_ago.sku_id;地区主题
建表语句：
DROP TABLE IF EXISTS dwt_area_topic;
CREATE EXTERNAL TABLE dwt_area_topic(
`province_id` STRING COMMENT '编号',
`visit_last_1d_count` BIGINT COMMENT '最近1日访客访问次数',
`login_last_1d_count` BIGINT COMMENT '最近1日用户访问次数',
`visit_last_7d_count` BIGINT COMMENT '最近7访客访问次数',
`login_last_7d_count` BIGINT COMMENT '最近7日用户访问次数',
`visit_last_30d_count` BIGINT COMMENT '最近30日访客访问次数',
`login_last_30d_count` BIGINT COMMENT '最近30日用户访问次数',
`visit_count` BIGINT COMMENT '累积访客访问次数',
`login_count` BIGINT COMMENT '累积用户访问次数',
`order_last_1d_count` BIGINT COMMENT '最近1天下单次数',
`order_last_1d_original_amount` DECIMAL(16,2) COMMENT '最近1天下单原始金额',
`order_last_1d_final_amount` DECIMAL(16,2) COMMENT '最近1天下单最终金额',
`order_last_7d_count` BIGINT COMMENT '最近7天下单次数',
`order_last_7d_original_amount` DECIMAL(16,2) COMMENT '最近7天下单原始金额',
`order_last_7d_final_amount` DECIMAL(16,2) COMMENT '最近7天下单最终金额',
`order_last_30d_count` BIGINT COMMENT '最近30天下单次数',
`order_last_30d_original_amount` DECIMAL(16,2) COMMENT '最近30天下单原始金额',
`order_last_30d_final_amount` DECIMAL(16,2) COMMENT '最近30天下单最终金额',
`order_count` BIGINT COMMENT '累积下单次数',
`order_original_amount` DECIMAL(16,2) COMMENT '累积下单原始金额',
`order_final_amount` DECIMAL(16,2) COMMENT '累积下单最终金额',
`payment_last_1d_count` BIGINT COMMENT '最近1天支付次数',
`payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1天支付金额',
`payment_last_7d_count` BIGINT COMMENT '最近7天支付次数',
`payment_last_7d_amount` DECIMAL(16,2) COMMENT '最近7天支付金额',
`payment_last_30d_count` BIGINT COMMENT '最近30天支付次数',
`payment_last_30d_amount` DECIMAL(16,2) COMMENT '最近30天支付金额',
`payment_count` BIGINT COMMENT '累积支付次数',
`payment_amount` DECIMAL(16,2) COMMENT '累积支付金额',
`refund_order_last_1d_count` BIGINT COMMENT '最近1天退单次数',
`refund_order_last_1d_amount` DECIMAL(16,2) COMMENT '最近1天退单金额',
`refund_order_last_7d_count` BIGINT COMMENT '最近7天退单次数',
`refund_order_last_7d_amount` DECIMAL(16,2) COMMENT '最近7天退单金额',
`refund_order_last_30d_count` BIGINT COMMENT '最近30天退单次数',
`refund_order_last_30d_amount` DECIMAL(16,2) COMMENT '最近30天退单金额',
`refund_order_count` BIGINT COMMENT '累积退单次数',
`refund_order_amount` DECIMAL(16,2) COMMENT '累积退单金额',
`refund_payment_last_1d_count` BIGINT COMMENT '最近1天退款次数',
`refund_payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1天退款金额',
`refund_payment_last_7d_count` BIGINT COMMENT '最近7天退款次数',
`refund_payment_last_7d_amount` DECIMAL(16,2) COMMENT '最近7天退款金额',
`refund_payment_last_30d_count` BIGINT COMMENT '最近30天退款次数',
`refund_payment_last_30d_amount` DECIMAL(16,2) COMMENT '最近30天退款金额',
`refund_payment_count` BIGINT COMMENT '累积退款次数',
`refund_payment_amount` DECIMAL(16,2) COMMENT '累积退款金额'
) COMMENT '地区主题宽表'
PARTITIONED BY (`dt` STRING)
STORED AS PARQUET
LOCATION '/warehouse/gmall/dwt/dwt_area_topic/'
TBLPROPERTIES ("parquet.compression"="lzo");活动主题
建表语句：
DROP TABLE IF EXISTS dwt_activity_topic;
CREATE EXTERNAL TABLE dwt_activity_topic(
`activity_rule_id` STRING COMMENT '活动规则ID',
`activity_id` STRING COMMENT '活动ID',
`order_last_1d_count` BIGINT COMMENT '最近1日参与某活动某规则下单次数',
`order_last_1d_reduce_amount` DECIMAL(16,2) COMMENT '最近1日参与某活动某规则下单优惠金额',
`order_last_1d_original_amount` DECIMAL(16,2) COMMENT '最近1日参与某活动某规则下单原始金额',
`order_last_1d_final_amount` DECIMAL(16,2) COMMENT '最近1日参与某活动某规则下单最终金额',
`order_count` BIGINT COMMENT '参与某活动某规则累积下单次数',
`order_reduce_amount` DECIMAL(16,2) COMMENT '参与某活动某规则累积下单优惠金额',
`order_original_amount` DECIMAL(16,2) COMMENT '参与某活动某规则累积下单原始金额',
`order_final_amount` DECIMAL(16,2) COMMENT '参与某活动某规则累积下单最终金额',
`payment_last_1d_count` BIGINT COMMENT '最近1日参与某活动某规则支付次数',
`payment_last_1d_reduce_amount` DECIMAL(16,2) COMMENT '最近1日参与某活动某规则支付优惠金额',
`payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日参与某活动某规则支付金额',
`payment_count` BIGINT COMMENT '参与某活动某规则累积支付次数',
`payment_reduce_amount` DECIMAL(16,2) COMMENT '参与某活动某规则累积支付优惠金额',
`payment_amount` DECIMAL(16,2) COMMENT '参与某活动某规则累积支付金额'
) COMMENT '活动主题宽表'
PARTITIONED BY (`dt` STRING)
STORED AS PARQUET
LOCATION '/warehouse/gmall/dwt/dwt_activity_topic/'
TBLPROPERTIES ("parquet.compression"="lzo");优惠券主题
建表语句：
DROP TABLE IF EXISTS dwt_coupon_topic;
CREATE EXTERNAL TABLE dwt_coupon_topic(
`coupon_id` STRING COMMENT '优惠券ID',
`get_last_1d_count` BIGINT COMMENT '最近1日领取次数',
`get_last_7d_count` BIGINT COMMENT '最近7日领取次数',
`get_last_30d_count` BIGINT COMMENT '最近30日领取次数',
`get_count` BIGINT COMMENT '累积领取次数',
`order_last_1d_count` BIGINT COMMENT '最近1日使用某券下单次数',
`order_last_1d_reduce_amount` DECIMAL(16,2) COMMENT '最近1日使用某券下单优惠金额',
`order_last_1d_original_amount` DECIMAL(16,2) COMMENT '最近1日使用某券下单原始金额',
`order_last_1d_final_amount` DECIMAL(16,2) COMMENT '最近1日使用某券下单最终金额',
`order_last_7d_count` BIGINT COMMENT '最近7日使用某券下单次数',
`order_last_7d_reduce_amount` DECIMAL(16,2) COMMENT '最近7日使用某券下单优惠金额',
`order_last_7d_original_amount` DECIMAL(16,2) COMMENT '最近7日使用某券下单原始金额',
`order_last_7d_final_amount` DECIMAL(16,2) COMMENT '最近7日使用某券下单最终金额',
`order_last_30d_count` BIGINT COMMENT '最近30日使用某券下单次数',
`order_last_30d_reduce_amount` DECIMAL(16,2) COMMENT '最近30日使用某券下单优惠金额',
`order_last_30d_original_amount` DECIMAL(16,2) COMMENT '最近30日使用某券下单原始金额',
`order_last_30d_final_amount` DECIMAL(16,2) COMMENT '最近30日使用某券下单最终金额',
`order_count` BIGINT COMMENT '累积使用(下单)次数',
`order_reduce_amount` DECIMAL(16,2) COMMENT '使用某券累积下单优惠金额',
`order_original_amount` DECIMAL(16,2) COMMENT '使用某券累积下单原始金额',
`order_final_amount` DECIMAL(16,2) COMMENT '使用某券累积下单最终金额',
`payment_last_1d_count` BIGINT COMMENT '最近1日使用某券支付次数',
`payment_last_1d_reduce_amount` DECIMAL(16,2) COMMENT '最近1日使用某券优惠金额',
`payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日使用某券支付金额',
`payment_last_7d_count` BIGINT COMMENT '最近7日使用某券支付次数',
`payment_last_7d_reduce_amount` DECIMAL(16,2) COMMENT '最近7日使用某券优惠金额',
`payment_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日使用某券支付金额',
`payment_last_30d_count` BIGINT COMMENT '最近30日使用某券支付次数',
`payment_last_30d_reduce_amount` DECIMAL(16,2) COMMENT '最近30日使用某券优惠金额',
`payment_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日使用某券支付金额',
`payment_count` BIGINT COMMENT '累积使用(支付)次数',
`payment_reduce_amount` DECIMAL(16,2) COMMENT '使用某券累积优惠金额',
`payment_amount` DECIMAL(16,2) COMMENT '使用某券累积支付金额',
`expire_last_1d_count` BIGINT COMMENT '最近1日过期次数',
`expire_last_7d_count` BIGINT COMMENT '最近7日过期次数',
`expire_last_30d_count` BIGINT COMMENT '最近30日过期次数',
`expire_count` BIGINT COMMENT '累积过期次数'
)comment '优惠券主题表'
PARTITIONED BY (`dt` STRING)
STORED AS PARQUET
LOCATION '/warehouse/gmall/dwt/dwt_coupon_topic/'
TBLPROPERTIES ("parquet.compression"="lzo");用户主题
建表语句：
DROP TABLE IF EXISTS dwt_user_topic;
CREATE EXTERNAL TABLE dwt_user_topic
(
`user_id` STRING COMMENT '用户id',
`login_date_first` STRING COMMENT '首次活跃日期',
`login_date_last` STRING COMMENT '末次活跃日期',
`login_date_1d_count` STRING COMMENT '最近1日登录次数',
`login_last_1d_day_count` BIGINT COMMENT '最近1日登录天数',
`login_last_7d_count` BIGINT COMMENT '最近7日登录次数',
`login_last_7d_day_count` BIGINT COMMENT '最近7日登录天数',
`login_last_30d_count` BIGINT COMMENT '最近30日登录次数',
`login_last_30d_day_count` BIGINT COMMENT '最近30日登录天数',
`login_count` BIGINT COMMENT '累积登录次数',
`login_day_count` BIGINT COMMENT '累积登录天数',
`order_date_first` STRING COMMENT '首次下单时间',
`order_date_last` STRING COMMENT '末次下单时间',
`order_last_1d_count` BIGINT COMMENT '最近1日下单次数',
`order_activity_last_1d_count` BIGINT COMMENT '最近1日订单参与活动次数',
`order_activity_reduce_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日订单减免金额(活动)',
`order_coupon_last_1d_count` BIGINT COMMENT '最近1日下单用券次数',
`order_coupon_reduce_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日订单减免金额(优惠券)',
`order_last_1d_original_amount` DECIMAL(16,2) COMMENT '最近1日原始下单金额',
`order_last_1d_final_amount` DECIMAL(16,2) COMMENT '最近1日最终下单金额',
`order_last_7d_count` BIGINT COMMENT '最近7日下单次数',
`order_activity_last_7d_count` BIGINT COMMENT '最近7日订单参与活动次数',
`order_activity_reduce_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日订单减免金额(活动)',
`order_coupon_last_7d_count` BIGINT COMMENT '最近7日下单用券次数',
`order_coupon_reduce_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日订单减免金额(优惠券)',
`order_last_7d_original_amount` DECIMAL(16,2) COMMENT '最近7日原始下单金额',
`order_last_7d_final_amount` DECIMAL(16,2) COMMENT '最近7日最终下单金额',
`order_last_30d_count` BIGINT COMMENT '最近30日下单次数',
`order_activity_last_30d_count` BIGINT COMMENT '最近30日订单参与活动次数',
`order_activity_reduce_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日订单减免金额(活动)',
`order_coupon_last_30d_count` BIGINT COMMENT '最近30日下单用券次数',
`order_coupon_reduce_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日订单减免金额(优惠券)',
`order_last_30d_original_amount` DECIMAL(16,2) COMMENT '最近30日原始下单金额',
`order_last_30d_final_amount` DECIMAL(16,2) COMMENT '最近30日最终下单金额',
`order_count` BIGINT COMMENT '累积下单次数',
`order_activity_count` BIGINT COMMENT '累积订单参与活动次数',
`order_activity_reduce_amount` DECIMAL(16,2) COMMENT '累积订单减免金额(活动)',
`order_coupon_count` BIGINT COMMENT '累积下单用券次数',
`order_coupon_reduce_amount` DECIMAL(16,2) COMMENT '累积订单减免金额(优惠券)',
`order_original_amount` DECIMAL(16,2) COMMENT '累积原始下单金额',
`order_final_amount` DECIMAL(16,2) COMMENT '累积最终下单金额',
`payment_date_first` STRING COMMENT '首次支付时间',
`payment_date_last` STRING COMMENT '末次支付时间',
`payment_last_1d_count` BIGINT COMMENT '最近1日支付次数',
`payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日支付金额',
`payment_last_7d_count` BIGINT COMMENT '最近7日支付次数',
`payment_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日支付金额',
`payment_last_30d_count` BIGINT COMMENT '最近30日支付次数',
`payment_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日支付金额',
`payment_count` BIGINT COMMENT '累积支付次数',
`payment_amount` DECIMAL(16,2) COMMENT '累积支付金额',
`refund_order_last_1d_count` BIGINT COMMENT '最近1日退单次数',
`refund_order_last_1d_num` BIGINT COMMENT '最近1日退单件数',
`refund_order_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日退单金额',
`refund_order_last_7d_count` BIGINT COMMENT '最近7日退单次数',
`refund_order_last_7d_num` BIGINT COMMENT '最近7日退单件数',
`refund_order_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日退单金额',
`refund_order_last_30d_count` BIGINT COMMENT '最近30日退单次数',
`refund_order_last_30d_num` BIGINT COMMENT '最近30日退单件数',
`refund_order_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日退单金额',
`refund_order_count` BIGINT COMMENT '累积退单次数',
`refund_order_num` BIGINT COMMENT '累积退单件数',
`refund_order_amount` DECIMAL(16,2) COMMENT '累积退单金额',
`refund_payment_last_1d_count` BIGINT COMMENT '最近1日退款次数',
`refund_payment_last_1d_num` BIGINT COMMENT '最近1日退款件数',
`refund_payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日退款金额',
`refund_payment_last_7d_count` BIGINT COMMENT '最近7日退款次数',
`refund_payment_last_7d_num` BIGINT COMMENT '最近7日退款件数',
`refund_payment_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日退款金额',
`refund_payment_last_30d_count` BIGINT COMMENT '最近30日退款次数',
`refund_payment_last_30d_num` BIGINT COMMENT '最近30日退款件数',
`refund_payment_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日退款金额',
`refund_payment_count` BIGINT COMMENT '累积退款次数',
`refund_payment_num` BIGINT COMMENT '累积退款件数',
`refund_payment_amount` DECIMAL(16,2) COMMENT '累积退款金额',
`cart_last_1d_count` BIGINT COMMENT '最近1日加入购物车次数',
`cart_last_7d_count` BIGINT COMMENT '最近7日加入购物车次数',
`cart_last_30d_count` BIGINT COMMENT '最近30日加入购物车次数',
`cart_count` BIGINT COMMENT '累积加入购物车次数',
`favor_last_1d_count` BIGINT COMMENT '最近1日收藏次数',
`favor_last_7d_count` BIGINT COMMENT '最近7日收藏次数',
`favor_last_30d_count` BIGINT COMMENT '最近30日收藏次数',
`favor_count` BIGINT COMMENT '累积收藏次数',
`coupon_last_1d_get_count` BIGINT COMMENT '最近1日领券次数',
`coupon_last_1d_using_count` BIGINT COMMENT '最近1日用券(下单)次数',
`coupon_last_1d_used_count` BIGINT COMMENT '最近1日用券(支付)次数',
`coupon_last_7d_get_count` BIGINT COMMENT '最近7日领券次数',
`coupon_last_7d_using_count` BIGINT COMMENT '最近7日用券(下单)次数',
`coupon_last_7d_used_count` BIGINT COMMENT '最近7日用券(支付)次数',
`coupon_last_30d_get_count` BIGINT COMMENT '最近30日领券次数',
`coupon_last_30d_using_count` BIGINT COMMENT '最近30日用券(下单)次数',
`coupon_last_30d_used_count` BIGINT COMMENT '最近30日用券(支付)次数',
`coupon_get_count` BIGINT COMMENT '累积领券次数',
`coupon_using_count` BIGINT COMMENT '累积用券(下单)次数',
`coupon_used_count` BIGINT COMMENT '累积用券(支付)次数',
`appraise_last_1d_good_count` BIGINT COMMENT '最近1日好评次数',
`appraise_last_1d_mid_count` BIGINT COMMENT '最近1日中评次数',
`appraise_last_1d_bad_count` BIGINT COMMENT '最近1日差评次数',
`appraise_last_1d_default_count` BIGINT COMMENT '最近1日默认评价次数',
`appraise_last_7d_good_count` BIGINT COMMENT '最近7日好评次数',
`appraise_last_7d_mid_count` BIGINT COMMENT '最近7日中评次数',
`appraise_last_7d_bad_count` BIGINT COMMENT '最近7日差评次数',
`appraise_last_7d_default_count` BIGINT COMMENT '最近7日默认评价次数',
`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 '会员主题宽表'
PARTITIONED BY (`dt` STRING)
STORED AS PARQUET
LOCATION '/warehouse/gmall/dwt/dwt_user_topic/'
TBLPROPERTIES ("parquet.compression"="lzo");访客主题
建表语句：
DROP TABLE IF EXISTS dwt_visitor_topic;
CREATE EXTERNAL TABLE dwt_visitor_topic
(
`mid_id` STRING COMMENT '设备id',
`brand` STRING COMMENT '手机品牌',
`model` STRING COMMENT '手机型号',
`channel` ARRAY<STRING> COMMENT '渠道',
`os` ARRAY<STRING> COMMENT '操作系统',
`area_code` ARRAY<STRING> COMMENT '地区ID',
`version_code` ARRAY<STRING> COMMENT '应用版本',
`visit_date_first` STRING COMMENT '首次访问时间',
`visit_date_last` STRING COMMENT '末次访问时间',
`visit_last_1d_count` BIGINT COMMENT '最近1日访问次数',
`visit_last_1d_day_count` BIGINT COMMENT '最近1日访问天数',
`visit_last_7d_count` BIGINT COMMENT '最近7日访问次数',
`visit_last_7d_day_count` BIGINT COMMENT '最近7日访问天数',
`visit_last_30d_count` BIGINT COMMENT '最近30日访问次数',
`visit_last_30d_day_count` BIGINT COMMENT '最近30日访问天数',
`visit_count` BIGINT COMMENT '累积访问次数',
`visit_day_count` BIGINT COMMENT '累积访问天数'
) COMMENT '设备主题宽表'
PARTITIONED BY (`dt` STRING)
STORED AS PARQUET
LOCATION '/warehouse/gmall/dwt/dwt_visitor_topic'
TBLPROPERTIES ("parquet.compression"="lzo");DWT层每日数据导入脚本
编写及执行脚本
（1）在/home/muzili/bin目录下创建脚本dws_to_dwt.sh
[muzili@hadoop102 bin]$ vim dws_to_dwt.sh
在脚本中填写如下内容：
#!/bin/bash
APP=gmall
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
do_date=$2
else
do_date=`date -d "-1 day" +%F`
fi
clear_date=`date -d "$do_date -2 day" +%F`
dwt_visitor_topic="
insert overwrite table ${APP}.dwt_visitor_topic partition(dt='$do_date')
select
nvl(1d_ago.mid_id,old.mid_id),
nvl(1d_ago.brand,old.brand),
nvl(1d_ago.model,old.model),
nvl(1d_ago.channel,old.channel),
nvl(1d_ago.os,old.os),
nvl(1d_ago.area_code,old.area_code),
nvl(1d_ago.version_code,old.version_code),
case when old.mid_id is null and 1d_ago.is_new=1 then '$do_date'
when old.mid_id is null and 1d_ago.is_new=0 then '2020-06-13'--无法获取准确的首次登录日期，给定一个数仓搭建日之前的日期
else old.visit_date_first end,
if(1d_ago.mid_id is not null,'$do_date',old.visit_date_last),
nvl(1d_ago.visit_count,0),
if(1d_ago.mid_id is null,0,1),
nvl(old.visit_last_7d_count,0)+nvl(1d_ago.visit_count,0)- nvl(7d_ago.visit_count,0),
nvl(old.visit_last_7d_day_count,0)+if(1d_ago.mid_id is null,0,1)- if(7d_ago.mid_id is null,0,1),
nvl(old.visit_last_30d_count,0)+nvl(1d_ago.visit_count,0)- nvl(30d_ago.visit_count,0),
nvl(old.visit_last_30d_day_count,0)+if(1d_ago.mid_id is null,0,1)- if(30d_ago.mid_id is null,0,1),
nvl(old.visit_count,0)+nvl(1d_ago.visit_count,0),
nvl(old.visit_day_count,0)+if(1d_ago.mid_id is null,0,1)
from
(
select
mid_id,
brand,
model,
channel,
os,
area_code,
version_code,
visit_date_first,
visit_date_last,
visit_last_1d_count,
visit_last_1d_day_count,
visit_last_7d_count,
visit_last_7d_day_count,
visit_last_30d_count,
visit_last_30d_day_count,
visit_count,
visit_day_count
from ${APP}.dwt_visitor_topic
where dt=date_add('$do_date',-1)
)old
full outer join
(
select
mid_id,
brand,
model,
is_new,
channel,
os,
area_code,
version_code,
visit_count
from ${APP}.dws_visitor_action_daycount
where dt='$do_date'
)1d_ago
on old.mid_id=1d_ago.mid_id
left join
(
select
mid_id,
brand,
model,
is_new,
channel,
os,
area_code,
version_code,
visit_count
from ${APP}.dws_visitor_action_daycount
where dt=date_add('$do_date',-7)
)7d_ago
on old.mid_id=7d_ago.mid_id
left join
(
select
mid_id,
brand,
model,
is_new,
channel,
os,
area_code,
version_code,
visit_count
from ${APP}.dws_visitor_action_daycount
where dt=date_add('$do_date',-30)
)30d_ago
on old.mid_id=30d_ago.mid_id;
alter table ${APP}.dwt_visitor_topic drop partition(dt='$clear_date');
"
dwt_user_topic="
insert overwrite table ${APP}.dwt_user_topic partition(dt='$do_date')
select
nvl(1d_ago.user_id,old.user_id),
nvl(old.login_date_first,'$do_date'),
if(1d_ago.user_id is not null,'$do_date',old.login_date_last),
nvl(1d_ago.login_count,0),
if(1d_ago.user_id is not null,1,0),
nvl(old.login_last_7d_count,0)+nvl(1d_ago.login_count,0)- nvl(7d_ago.login_count,0),
nvl(old.login_last_7d_day_count,0)+if(1d_ago.user_id is null,0,1)- if(7d_ago.user_id is null,0,1),
nvl(old.login_last_30d_count,0)+nvl(1d_ago.login_count,0)- nvl(30d_ago.login_count,0),
nvl(old.login_last_30d_day_count,0)+if(1d_ago.user_id is null,0,1)- if(30d_ago.user_id is null,0,1),
nvl(old.login_count,0)+nvl(1d_ago.login_count,0),
nvl(old.login_day_count,0)+if(1d_ago.user_id is not null,1,0),
if(old.order_date_first is null and 1d_ago.order_count>0, '$do_date', old.order_date_first),
if(1d_ago.order_count>0,'$do_date',old.order_date_last),
nvl(1d_ago.order_count,0),
nvl(1d_ago.order_activity_count,0),
nvl(1d_ago.order_activity_reduce_amount,0.0),
nvl(1d_ago.order_coupon_count,0),
nvl(1d_ago.order_coupon_reduce_amount,0.0),
nvl(1d_ago.order_original_amount,0.0),
nvl(1d_ago.order_final_amount,0.0),
nvl(old.order_last_7d_count,0)+nvl(1d_ago.order_count,0)- nvl(7d_ago.order_count,0),
nvl(old.order_activity_last_7d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(7d_ago.order_activity_count,0),
nvl(old.order_activity_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(7d_ago.order_activity_reduce_amount,0.0),
nvl(old.order_coupon_last_7d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(7d_ago.order_coupon_count,0),
nvl(old.order_coupon_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(7d_ago.order_coupon_reduce_amount,0.0),
nvl(old.order_last_7d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(7d_ago.order_original_amount,0.0),
nvl(old.order_last_7d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(7d_ago.order_final_amount,0.0),
nvl(old.order_last_30d_count,0)+nvl(1d_ago.order_count,0)- nvl(30d_ago.order_count,0),
nvl(old.order_activity_last_30d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(30d_ago.order_activity_count,0),
nvl(old.order_activity_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(30d_ago.order_activity_reduce_amount,0.0),
nvl(old.order_coupon_last_30d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(30d_ago.order_coupon_count,0),
nvl(old.order_coupon_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(30d_ago.order_coupon_reduce_amount,0.0),
nvl(old.order_last_30d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(30d_ago.order_original_amount,0.0),
nvl(old.order_last_30d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(30d_ago.order_final_amount,0.0),
nvl(old.order_count,0)+nvl(1d_ago.order_count,0),
nvl(old.order_activity_count,0)+nvl(1d_ago.order_activity_count,0),
nvl(old.order_activity_reduce_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0),
nvl(old.order_coupon_count,0)+nvl(1d_ago.order_coupon_count,0),
nvl(old.order_coupon_reduce_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0),
nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0),
nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0),
if(old.payment_date_first is null and 1d_ago.payment_count>0, '$do_date', old.payment_date_first),
if(1d_ago.payment_count>0,'$do_date',old.payment_date_last),
nvl(1d_ago.payment_count,0),
nvl(1d_ago.payment_amount,0.0),
nvl(old.payment_last_7d_count,0)+nvl(1d_ago.payment_count,0)-nvl(7d_ago.payment_count,0),
nvl(old.payment_last_7d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)-nvl(7d_ago.payment_amount,0.0),
nvl(old.payment_last_30d_count,0)+nvl(1d_ago.payment_count,0)-nvl(30d_ago.payment_count,0),
nvl(old.payment_last_30d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(30d_ago.payment_amount,0.0),
nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0),
nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0),
nvl(1d_ago.refund_order_count,0),
nvl(1d_ago.refund_order_num,0),
nvl(1d_ago.refund_order_amount,0.0),
nvl(old.refund_order_last_7d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(7d_ago.refund_order_count,0),
nvl(old.refund_order_last_7d_num,0)+nvl(1d_ago.refund_order_num, 0)- nvl(7d_ago.refund_order_num,0),
nvl(old.refund_order_last_7d_amount,0.0)+ nvl(1d_ago.refund_order_amount,0.0)- nvl(7d_ago.refund_order_amount,0.0),
nvl(old.refund_order_last_30d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(30d_ago.refund_order_count,0),
nvl(old.refund_order_last_30d_num,0)+nvl(1d_ago.refund_order_num, 0)- nvl(30d_ago.refund_order_num,0),
nvl(old.refund_order_last_30d_amount,0.0)+ nvl(1d_ago.refund_order_amount,0.0)- nvl(30d_ago.refund_order_amount,0.0),
nvl(old.refund_order_count,0)+nvl(1d_ago.refund_order_count,0),
nvl(old.refund_order_num,0)+nvl(1d_ago.refund_order_num,0),
nvl(old.refund_order_amount,0.0)+ nvl(1d_ago.refund_order_amount,0.0),
nvl(1d_ago.refund_payment_count,0),
nvl(1d_ago.refund_payment_num,0),
nvl(1d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_last_7d_count,0)+nvl(1d_ago.refund_payment_count,0)-nvl(7d_ago.refund_payment_count,0),
nvl(old.refund_payment_last_7d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(7d_ago.refund_payment_num,0),
nvl(old.refund_payment_last_7d_amount,0.0)+ nvl(1d_ago.refund_payment_amount,0.0)- nvl(7d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_last_30d_count,0)+nvl(1d_ago.refund_payment_count,0)-nvl(30d_ago.refund_payment_count,0),
nvl(old.refund_payment_last_30d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(30d_ago.refund_payment_num,0),
nvl(old.refund_payment_last_30d_amount,0.0)+ nvl(1d_ago.refund_payment_amount,0.0)- nvl(30d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_count,0)+nvl(1d_ago.refund_payment_count,0),
nvl(old.refund_payment_num,0)+nvl(1d_ago.refund_payment_num,0),
nvl(old.refund_payment_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0),
nvl(1d_ago.cart_count,0),
nvl(old.cart_last_7d_count,0)+nvl(1d_ago.cart_count,0)-nvl(7d_ago.cart_count,0),
nvl(old.cart_last_30d_count,0)+nvl(1d_ago.cart_count,0)-nvl(30d_ago.cart_count,0),
nvl(old.cart_count,0)+nvl(1d_ago.cart_count,0),
nvl(1d_ago.favor_count,0),
nvl(old.favor_last_7d_count,0)+nvl(1d_ago.favor_count,0)- nvl(7d_ago.favor_count,0),
nvl(old.favor_last_30d_count,0)+nvl(1d_ago.favor_count,0)- nvl(30d_ago.favor_count,0),
nvl(old.favor_count,0)+nvl(1d_ago.favor_count,0),
nvl(1d_ago.coupon_get_count,0),
nvl(1d_ago.coupon_using_count,0),
nvl(1d_ago.coupon_used_count,0),
nvl(old.coupon_last_7d_get_count,0)+nvl(1d_ago.coupon_get_count,0)- nvl(7d_ago.coupon_get_count,0),
nvl(old.coupon_last_7d_using_count,0)+nvl(1d_ago.coupon_using_count,0)- nvl(7d_ago.coupon_using_count,0),
nvl(old.coupon_last_7d_used_count,0)+ nvl(1d_ago.coupon_used_count,0)- nvl(7d_ago.coupon_used_count,0),
nvl(old.coupon_last_30d_get_count,0)+nvl(1d_ago.coupon_get_count,0)- nvl(30d_ago.coupon_get_count,0),
nvl(old.coupon_last_30d_using_count,0)+nvl(1d_ago.coupon_using_count,0)- nvl(30d_ago.coupon_using_count,0),
nvl(old.coupon_last_30d_used_count,0)+ nvl(1d_ago.coupon_used_count,0)- nvl(30d_ago.coupon_used_count,0),
nvl(old.coupon_get_count,0)+nvl(1d_ago.coupon_get_count,0),
nvl(old.coupon_using_count,0)+nvl(1d_ago.coupon_using_count,0),
nvl(old.coupon_used_count,0)+nvl(1d_ago.coupon_used_count,0),
nvl(1d_ago.appraise_good_count,0),
nvl(1d_ago.appraise_mid_count,0),
nvl(1d_ago.appraise_bad_count,0),
nvl(old.appraise_last_7d_default_count,0)+nvl(1d_ago.appraise_default_count,0)-nvl(7d_ago.appraise_default_count,0),
nvl(old.appraise_last_7d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(7d_ago.appraise_good_count,0),
nvl(old.appraise_last_7d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)-nvl(7d_ago.appraise_mid_count,0),
nvl(old.appraise_last_7d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)-nvl(7d_ago.appraise_bad_count,0),
nvl(old.appraise_last_7d_default_count,0)+nvl(1d_ago.appraise_default_count,0)-nvl(7d_ago.appraise_default_count,0),
nvl(old.appraise_last_30d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(30d_ago.appraise_good_count,0),
nvl(old.appraise_last_30d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)-nvl(30d_ago.appraise_mid_count,0),
nvl(old.appraise_last_30d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)-nvl(30d_ago.appraise_bad_count,0),
nvl(old.appraise_last_30d_default_count,0)+nvl(1d_ago.appraise_default_count,0)-nvl(30d_ago.appraise_default_count,0),
nvl(old.appraise_good_count,0)+nvl(1d_ago.appraise_good_count,0),
nvl(old.appraise_mid_count,0)+nvl(1d_ago.appraise_mid_count, 0),
nvl(old.appraise_bad_count,0)+nvl(1d_ago.appraise_bad_count,0),
nvl(old.appraise_default_count,0)+nvl(1d_ago.appraise_default_count,0)
from
(
select
user_id,
login_date_first,
login_date_last,
login_date_1d_count,
login_last_1d_day_count,
login_last_7d_count,
login_last_7d_day_count,
login_last_30d_count,
login_last_30d_day_count,
login_count,
login_day_count,
order_date_first,
order_date_last,
order_last_1d_count,
order_activity_last_1d_count,
order_activity_reduce_last_1d_amount,
order_coupon_last_1d_count,
order_coupon_reduce_last_1d_amount,
order_last_1d_original_amount,
order_last_1d_final_amount,
order_last_7d_count,
order_activity_last_7d_count,
order_activity_reduce_last_7d_amount,
order_coupon_last_7d_count,
order_coupon_reduce_last_7d_amount,
order_last_7d_original_amount,
order_last_7d_final_amount,
order_last_30d_count,
order_activity_last_30d_count,
order_activity_reduce_last_30d_amount,
order_coupon_last_30d_count,
order_coupon_reduce_last_30d_amount,
order_last_30d_original_amount,
order_last_30d_final_amount,
order_count,
order_activity_count,
order_activity_reduce_amount,
order_coupon_count,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_date_first,
payment_date_last,
payment_last_1d_count,
payment_last_1d_amount,
payment_last_7d_count,
payment_last_7d_amount,
payment_last_30d_count,
payment_last_30d_amount,
payment_count,
payment_amount,
refund_order_last_1d_count,
refund_order_last_1d_num,
refund_order_last_1d_amount,
refund_order_last_7d_count,
refund_order_last_7d_num,
refund_order_last_7d_amount,
refund_order_last_30d_count,
refund_order_last_30d_num,
refund_order_last_30d_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_last_1d_count,
refund_payment_last_1d_num,
refund_payment_last_1d_amount,
refund_payment_last_7d_count,
refund_payment_last_7d_num,
refund_payment_last_7d_amount,
refund_payment_last_30d_count,
refund_payment_last_30d_num,
refund_payment_last_30d_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
cart_last_1d_count,
cart_last_7d_count,
cart_last_30d_count,
cart_count,
favor_last_1d_count,
favor_last_7d_count,
favor_last_30d_count,
favor_count,
coupon_last_1d_get_count,
coupon_last_1d_using_count,
coupon_last_1d_used_count,
coupon_last_7d_get_count,
coupon_last_7d_using_count,
coupon_last_7d_used_count,
coupon_last_30d_get_count,
coupon_last_30d_using_count,
coupon_last_30d_used_count,
coupon_get_count,
coupon_using_count,
coupon_used_count,
appraise_last_1d_good_count,
appraise_last_1d_mid_count,
appraise_last_1d_bad_count,
appraise_last_1d_default_count,
appraise_last_7d_good_count,
appraise_last_7d_mid_count,
appraise_last_7d_bad_count,
appraise_last_7d_default_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 ${APP}.dwt_user_topic
where dt=date_add('$do_date',-1)
)old
full outer join
(
select
user_id,
login_count,
cart_count,
favor_count,
order_count,
order_activity_count,
order_activity_reduce_amount,
order_coupon_count,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
coupon_get_count,
coupon_using_count,
coupon_used_count,
appraise_good_count,
appraise_mid_count,
appraise_bad_count,
appraise_default_count
from ${APP}.dws_user_action_daycount
where dt='$do_date'
)1d_ago
on old.user_id=1d_ago.user_id
left join
(
select
user_id,
login_count,
cart_count,
favor_count,
order_count,
order_activity_count,
order_activity_reduce_amount,
order_coupon_count,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
coupon_get_count,
coupon_using_count,
coupon_used_count,
appraise_good_count,
appraise_mid_count,
appraise_bad_count,
appraise_default_count
from ${APP}.dws_user_action_daycount
where dt=date_add('$do_date',-7)
)7d_ago
on old.user_id=7d_ago.user_id
left join
(
select
user_id,
login_count,
cart_count,
favor_count,
order_count,
order_activity_count,
order_activity_reduce_amount,
order_coupon_count,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
coupon_get_count,
coupon_using_count,
coupon_used_count,
appraise_good_count,
appraise_mid_count,
appraise_bad_count,
appraise_default_count
from ${APP}.dws_user_action_daycount
where dt=date_add('$do_date',-30)
)30d_ago
on old.user_id=30d_ago.user_id;
alter table ${APP}.dwt_user_topic drop partition(dt='$clear_date');
"
dwt_sku_topic="
insert overwrite table ${APP}.dwt_sku_topic partition(dt='$do_date')
select
nvl(1d_ago.sku_id,old.sku_id),
nvl(1d_ago.order_count,0),
nvl(1d_ago.order_num,0),
nvl(1d_ago.order_activity_count,0),
nvl(1d_ago.order_coupon_count,0),
nvl(1d_ago.order_activity_reduce_amount,0.0),
nvl(1d_ago.order_coupon_reduce_amount,0.0),
nvl(1d_ago.order_original_amount,0.0),
nvl(1d_ago.order_final_amount,0.0),
nvl(old.order_last_7d_count,0)+nvl(1d_ago.order_count,0)- nvl(7d_ago.order_count,0),
nvl(old.order_last_7d_num,0)+nvl(1d_ago.order_num,0)- nvl(7d_ago.order_num,0),
nvl(old.order_activity_last_7d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(7d_ago.order_activity_count,0),
nvl(old.order_coupon_last_7d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(7d_ago.order_coupon_count,0),
nvl(old.order_activity_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(7d_ago.order_activity_reduce_amount,0.0),
nvl(old.order_coupon_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(7d_ago.order_coupon_reduce_amount,0.0),
nvl(old.order_last_7d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(7d_ago.order_original_amount,0.0),
nvl(old.order_last_7d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(7d_ago.order_final_amount,0.0),
nvl(old.order_last_30d_count,0)+nvl(1d_ago.order_count,0)- nvl(30d_ago.order_count,0),
nvl(old.order_last_30d_num,0)+nvl(1d_ago.order_num,0)- nvl(30d_ago.order_num,0),
nvl(old.order_activity_last_30d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(30d_ago.order_activity_count,0),
nvl(old.order_coupon_last_30d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(30d_ago.order_coupon_count,0),
nvl(old.order_activity_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(30d_ago.order_activity_reduce_amount,0.0),
nvl(old.order_coupon_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(30d_ago.order_coupon_reduce_amount,0.0),
nvl(old.order_last_30d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(30d_ago.order_original_amount,0.0),
nvl(old.order_last_30d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(30d_ago.order_final_amount,0.0),
nvl(old.order_count,0)+nvl(1d_ago.order_count,0),
nvl(old.order_num,0)+nvl(1d_ago.order_num,0),
nvl(old.order_activity_count,0)+nvl(1d_ago.order_activity_count,0),
nvl(old.order_coupon_count,0)+nvl(1d_ago.order_coupon_count,0),
nvl(old.order_activity_reduce_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0),
nvl(old.order_coupon_reduce_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0),
nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0),
nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0),
nvl(1d_ago.payment_count,0),
nvl(1d_ago.payment_num,0),
nvl(1d_ago.payment_amount,0.0),
nvl(old.payment_last_7d_count,0)+nvl(1d_ago.payment_count,0)- nvl(7d_ago.payment_count,0),
nvl(old.payment_last_7d_num,0)+nvl(1d_ago.payment_num,0)- nvl(7d_ago.payment_num,0),
nvl(old.payment_last_7d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(7d_ago.payment_amount,0.0),
nvl(old.payment_last_30d_count,0)+nvl(1d_ago.payment_count,0)- nvl(30d_ago.payment_count,0),
nvl(old.payment_last_30d_num,0)+nvl(1d_ago.payment_num,0)- nvl(30d_ago.payment_num,0),
nvl(old.payment_last_30d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(30d_ago.payment_amount,0.0),
nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0),
nvl(old.payment_num,0)+nvl(1d_ago.payment_num,0),
nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0),
nvl(old.refund_order_last_1d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(1d_ago.refund_order_count,0),
nvl(old.refund_order_last_1d_num,0)+nvl(1d_ago.refund_order_num,0)- nvl(1d_ago.refund_order_num,0),
nvl(old.refund_order_last_1d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(1d_ago.refund_order_amount,0.0),
nvl(old.refund_order_last_7d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(7d_ago.refund_order_count,0),
nvl(old.refund_order_last_7d_num,0)+nvl(1d_ago.refund_order_num,0)- nvl(7d_ago.refund_order_num,0),
nvl(old.refund_order_last_7d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(7d_ago.refund_order_amount,0.0),
nvl(old.refund_order_last_30d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(30d_ago.refund_order_count,0),
nvl(old.refund_order_last_30d_num,0)+nvl(1d_ago.refund_order_num,0)- nvl(30d_ago.refund_order_num,0),
nvl(old.refund_order_last_30d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(30d_ago.refund_order_amount,0.0),
nvl(old.refund_order_count,0)+nvl(1d_ago.refund_order_count,0),
nvl(old.refund_order_num,0)+nvl(1d_ago.refund_order_num,0),
nvl(old.refund_order_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0),
nvl(1d_ago.refund_payment_count,0),
nvl(1d_ago.refund_payment_num,0),
nvl(1d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_last_7d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(7d_ago.refund_payment_count,0),
nvl(old.refund_payment_last_7d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(7d_ago.refund_payment_num,0),
nvl(old.refund_payment_last_7d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(7d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_last_30d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(30d_ago.refund_payment_count,0),
nvl(old.refund_payment_last_30d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(30d_ago.refund_payment_num,0),
nvl(old.refund_payment_last_30d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(30d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_count,0)+nvl(1d_ago.refund_payment_count,0),
nvl(old.refund_payment_num,0)+nvl(1d_ago.refund_payment_num,0),
nvl(old.refund_payment_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0),
nvl(1d_ago.cart_count,0),
nvl(old.cart_last_7d_count,0)+nvl(1d_ago.cart_count,0)- nvl(7d_ago.cart_count,0),
nvl(old.cart_last_30d_count,0)+nvl(1d_ago.cart_count,0)- nvl(30d_ago.cart_count,0),
nvl(old.cart_count,0)+nvl(1d_ago.cart_count,0),
nvl(1d_ago.favor_count,0),
nvl(old.favor_last_7d_count,0)+nvl(1d_ago.favor_count,0)- nvl(7d_ago.favor_count,0),
nvl(old.favor_last_30d_count,0)+nvl(1d_ago.favor_count,0)- nvl(30d_ago.favor_count,0),
nvl(old.favor_count,0)+nvl(1d_ago.favor_count,0),
nvl(1d_ago.appraise_good_count,0),
nvl(1d_ago.appraise_mid_count,0),
nvl(1d_ago.appraise_bad_count,0),
nvl(1d_ago.appraise_default_count,0),
nvl(old.appraise_last_7d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(7d_ago.appraise_good_count,0),
nvl(old.appraise_last_7d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)- nvl(7d_ago.appraise_mid_count,0),
nvl(old.appraise_last_7d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)- nvl(7d_ago.appraise_bad_count,0),
nvl(old.appraise_last_7d_default_count,0)+nvl(1d_ago.appraise_default_count,0)- nvl(7d_ago.appraise_default_count,0),
nvl(old.appraise_last_30d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(30d_ago.appraise_good_count,0),
nvl(old.appraise_last_30d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)- nvl(30d_ago.appraise_mid_count,0),
nvl(old.appraise_last_30d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)- nvl(30d_ago.appraise_bad_count,0),
nvl(old.appraise_last_30d_default_count,0)+nvl(1d_ago.appraise_default_count,0)- nvl(30d_ago.appraise_default_count,0),
nvl(old.appraise_good_count,0)+nvl(1d_ago.appraise_good_count,0),
nvl(old.appraise_mid_count,0)+nvl(1d_ago.appraise_mid_count,0),
nvl(old.appraise_bad_count,0)+nvl(1d_ago.appraise_bad_count,0),
nvl(old.appraise_default_count,0)+nvl(1d_ago.appraise_default_count,0)
from
(
select
sku_id,
order_last_1d_count,
order_last_1d_num,
order_activity_last_1d_count,
order_coupon_last_1d_count,
order_activity_reduce_last_1d_amount,
order_coupon_reduce_last_1d_amount,
order_last_1d_original_amount,
order_last_1d_final_amount,
order_last_7d_count,
order_last_7d_num,
order_activity_last_7d_count,
order_coupon_last_7d_count,
order_activity_reduce_last_7d_amount,
order_coupon_reduce_last_7d_amount,
order_last_7d_original_amount,
order_last_7d_final_amount,
order_last_30d_count,
order_last_30d_num,
order_activity_last_30d_count,
order_coupon_last_30d_count,
order_activity_reduce_last_30d_amount,
order_coupon_reduce_last_30d_amount,
order_last_30d_original_amount,
order_last_30d_final_amount,
order_count,
order_num,
order_activity_count,
order_coupon_count,
order_activity_reduce_amount,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_last_1d_count,
payment_last_1d_num,
payment_last_1d_amount,
payment_last_7d_count,
payment_last_7d_num,
payment_last_7d_amount,
payment_last_30d_count,
payment_last_30d_num,
payment_last_30d_amount,
payment_count,
payment_num,
payment_amount,
refund_order_last_1d_count,
refund_order_last_1d_num,
refund_order_last_1d_amount,
refund_order_last_7d_count,
refund_order_last_7d_num,
refund_order_last_7d_amount,
refund_order_last_30d_count,
refund_order_last_30d_num,
refund_order_last_30d_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_last_1d_count,
refund_payment_last_1d_num,
refund_payment_last_1d_amount,
refund_payment_last_7d_count,
refund_payment_last_7d_num,
refund_payment_last_7d_amount,
refund_payment_last_30d_count,
refund_payment_last_30d_num,
refund_payment_last_30d_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
cart_last_1d_count,
cart_last_7d_count,
cart_last_30d_count,
cart_count,
favor_last_1d_count,
favor_last_7d_count,
favor_last_30d_count,
favor_count,
appraise_last_1d_good_count,
appraise_last_1d_mid_count,
appraise_last_1d_bad_count,
appraise_last_1d_default_count,
appraise_last_7d_good_count,
appraise_last_7d_mid_count,
appraise_last_7d_bad_count,
appraise_last_7d_default_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 ${APP}.dwt_sku_topic
where dt=date_add('$do_date',-1)
)old
full outer join
(
select
sku_id,
order_count,
order_num,
order_activity_count,
order_coupon_count,
order_activity_reduce_amount,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_num,
payment_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
cart_count,
favor_count,
appraise_good_count,
appraise_mid_count,
appraise_bad_count,
appraise_default_count
from ${APP}.dws_sku_action_daycount
where dt='$do_date'
)1d_ago
on old.sku_id=1d_ago.sku_id
left join
(
select
sku_id,
order_count,
order_num,
order_activity_count,
order_coupon_count,
order_activity_reduce_amount,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_num,
payment_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
cart_count,
favor_count,
appraise_good_count,
appraise_mid_count,
appraise_bad_count,
appraise_default_count
from ${APP}.dws_sku_action_daycount
where dt=date_add('$do_date',-7)
)7d_ago
on old.sku_id=7d_ago.sku_id
left join
(
select
sku_id,
order_count,
order_num,
order_activity_count,
order_coupon_count,
order_activity_reduce_amount,
order_coupon_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_num,
payment_amount,
refund_order_count,
refund_order_num,
refund_order_amount,
refund_payment_count,
refund_payment_num,
refund_payment_amount,
cart_count,
favor_count,
appraise_good_count,
appraise_mid_count,
appraise_bad_count,
appraise_default_count
from ${APP}.dws_sku_action_daycount
where dt=date_add('$do_date',-30)
)30d_ago
on old.sku_id=30d_ago.sku_id;
alter table ${APP}.dwt_sku_topic drop partition(dt='$clear_date');
"
dwt_activity_topic="
insert overwrite table ${APP}.dwt_activity_topic partition(dt='$do_date')
select
nvl(1d_ago.activity_rule_id,old.activity_rule_id),
nvl(1d_ago.activity_id,old.activity_id),
nvl(1d_ago.order_count,0),
nvl(1d_ago.order_reduce_amount,0.0),
nvl(1d_ago.order_original_amount,0.0),
nvl(1d_ago.order_final_amount,0.0),
nvl(old.order_count,0)+nvl(1d_ago.order_count,0),
nvl(old.order_reduce_amount,0.0)+nvl(1d_ago.order_reduce_amount,0.0),
nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0),
nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0),
nvl(1d_ago.payment_count,0),
nvl(1d_ago.payment_reduce_amount,0.0),
nvl(1d_ago.payment_amount,0.0),
nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0),
nvl(old.payment_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0),
nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0)
from
(
select
activity_rule_id,
activity_id,
order_count,
order_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_reduce_amount,
payment_amount
from ${APP}.dwt_activity_topic
where dt=date_add('$do_date',-1)
)old
full outer join
(
select
activity_rule_id,
activity_id,
order_count,
order_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_reduce_amount,
payment_amount
from ${APP}.dws_activity_info_daycount
where dt='$do_date'
)1d_ago
on old.activity_rule_id=1d_ago.activity_rule_id;
alter table ${APP}.dwt_activity_topic drop partition(dt='$clear_date');
"
dwt_coupon_topic="
insert overwrite table ${APP}.dwt_coupon_topic partition(dt='$do_date')
select
nvl(1d_ago.coupon_id,old.coupon_id),
nvl(1d_ago.get_count,0),
nvl(old.get_last_7d_count,0)+nvl(1d_ago.get_count,0)- nvl(7d_ago.get_count,0),
nvl(old.get_last_30d_count,0)+nvl(1d_ago.get_count,0)- nvl(30d_ago.get_count,0),
nvl(old.get_count,0)+nvl(1d_ago.get_count,0),
nvl(1d_ago.order_count,0),
nvl(1d_ago.order_reduce_amount,0.0),
nvl(1d_ago.order_original_amount,0.0),
nvl(1d_ago.order_final_amount,0.0),
nvl(old.order_last_7d_count,0)+nvl(1d_ago.order_count,0)- nvl(7d_ago.order_count,0),
nvl(old.order_last_7d_reduce_amount,0.0)+nvl(1d_ago.order_reduce_amount,0.0)- nvl(7d_ago.order_reduce_amount,0.0),
nvl(old.order_last_7d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(7d_ago.order_original_amount,0.0),
nvl(old.order_last_7d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(7d_ago.order_final_amount,0.0),
nvl(old.order_last_30d_count,0)+nvl(1d_ago.order_count,0)- nvl(30d_ago.order_count,0),
nvl(old.order_last_30d_reduce_amount,0.0)+nvl(1d_ago.order_reduce_amount,0.0)- nvl(30d_ago.order_reduce_amount,0.0),
nvl(old.order_last_30d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(30d_ago.order_original_amount,0.0),
nvl(old.order_last_30d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(30d_ago.order_final_amount,0.0),
nvl(old.order_count,0)+nvl(1d_ago.order_count,0),
nvl(old.order_reduce_amount,0.0)+nvl(1d_ago.order_reduce_amount,0.0),
nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0),
nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0),
nvl(old.payment_last_1d_count,0)+nvl(1d_ago.payment_count,0)- nvl(1d_ago.payment_count,0),
nvl(old.payment_last_1d_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0)- nvl(1d_ago.payment_reduce_amount,0.0),
nvl(old.payment_last_1d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(1d_ago.payment_amount,0.0),
nvl(old.payment_last_7d_count,0)+nvl(1d_ago.payment_count,0)- nvl(7d_ago.payment_count,0),
nvl(old.payment_last_7d_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0)- nvl(7d_ago.payment_reduce_amount,0.0),
nvl(old.payment_last_7d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(7d_ago.payment_amount,0.0),
nvl(old.payment_last_30d_count,0)+nvl(1d_ago.payment_count,0)- nvl(30d_ago.payment_count,0),
nvl(old.payment_last_30d_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0)- nvl(30d_ago.payment_reduce_amount,0.0),
nvl(old.payment_last_30d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(30d_ago.payment_amount,0.0),
nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0),
nvl(old.payment_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0),
nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0),
nvl(1d_ago.expire_count,0),
nvl(old.expire_last_7d_count,0)+nvl(1d_ago.expire_count,0)- nvl(7d_ago.expire_count,0),
nvl(old.expire_last_30d_count,0)+nvl(1d_ago.expire_count,0)- nvl(30d_ago.expire_count,0),
nvl(old.expire_count,0)+nvl(1d_ago.expire_count,0)
from
(
select
coupon_id,
get_last_1d_count,
get_last_7d_count,
get_last_30d_count,
get_count,
order_last_1d_count,
order_last_1d_reduce_amount,
order_last_1d_original_amount,
order_last_1d_final_amount,
order_last_7d_count,
order_last_7d_reduce_amount,
order_last_7d_original_amount,
order_last_7d_final_amount,
order_last_30d_count,
order_last_30d_reduce_amount,
order_last_30d_original_amount,
order_last_30d_final_amount,
order_count,
order_reduce_amount,
order_original_amount,
order_final_amount,
payment_last_1d_count,
payment_last_1d_reduce_amount,
payment_last_1d_amount,
payment_last_7d_count,
payment_last_7d_reduce_amount,
payment_last_7d_amount,
payment_last_30d_count,
payment_last_30d_reduce_amount,
payment_last_30d_amount,
payment_count,
payment_reduce_amount,
payment_amount,
expire_last_1d_count,
expire_last_7d_count,
expire_last_30d_count,
expire_count
from ${APP}.dwt_coupon_topic
where dt=date_add('$do_date',-1)
)old
full outer join
(
select
coupon_id,
get_count,
order_count,
order_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_reduce_amount,
payment_amount,
expire_count
from ${APP}.dws_coupon_info_daycount
where dt='$do_date'
)1d_ago
on old.coupon_id=1d_ago.coupon_id
left join
(
select
coupon_id,
get_count,
order_count,
order_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_reduce_amount,
payment_amount,
expire_count
from ${APP}.dws_coupon_info_daycount
where dt=date_add('$do_date',-7)
)7d_ago
on old.coupon_id=7d_ago.coupon_id
left join
(
select
coupon_id,
get_count,
order_count,
order_reduce_amount,
order_original_amount,
order_final_amount,
payment_count,
payment_reduce_amount,
payment_amount,
expire_count
from ${APP}.dws_coupon_info_daycount
where dt=date_add('$do_date',-30)
)30d_ago
on old.coupon_id=30d_ago.coupon_id;
alter table ${APP}.dwt_coupon_topic drop partition(dt='$clear_date');
"
dwt_area_topic="
insert overwrite table ${APP}.dwt_area_topic partition(dt='$do_date')
select
nvl(old.province_id, 1d_ago.province_id),
nvl(1d_ago.visit_count,0),
nvl(1d_ago.login_count,0),
nvl(old.visit_last_7d_count,0)+nvl(1d_ago.visit_count,0)- nvl(7d_ago.visit_count,0),
nvl(old.login_last_7d_count,0)+nvl(1d_ago.login_count,0)- nvl(7d_ago.login_count,0),
nvl(old.visit_last_30d_count,0)+nvl(1d_ago.visit_count,0)- nvl(30d_ago.visit_count,0),
nvl(old.login_last_30d_count,0)+nvl(1d_ago.login_count,0)- nvl(30d_ago.login_count,0),
nvl(old.visit_count,0)+nvl(1d_ago.visit_count,0),
nvl(old.login_count,0)+nvl(1d_ago.login_count,0),
nvl(1d_ago.order_count,0),
nvl(1d_ago.order_original_amount,0.0),
nvl(1d_ago.order_final_amount,0.0),
nvl(old.order_last_7d_count,0)+nvl(1d_ago.order_count,0)- nvl(7d_ago.order_count,0),
nvl(old.order_last_7d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(7d_ago.order_original_amount,0.0),
nvl(old.order_last_7d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(7d_ago.order_final_amount,0.0),
nvl(old.order_last_30d_count,0)+nvl(1d_ago.order_count,0)- nvl(30d_ago.order_count,0),
nvl(old.order_last_30d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(30d_ago.order_original_amount,0.0),
nvl(old.order_last_30d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(30d_ago.order_final_amount,0.0),
nvl(old.order_count,0)+nvl(1d_ago.order_count,0),
nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0),
nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0),
nvl(1d_ago.payment_count,0),
nvl(1d_ago.payment_amount,0.0),
nvl(old.payment_last_7d_count,0)+nvl(1d_ago.payment_count,0)- nvl(7d_ago.payment_count,0),
nvl(old.payment_last_7d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(7d_ago.payment_amount,0.0),
nvl(old.payment_last_30d_count,0)+nvl(1d_ago.payment_count,0)- nvl(30d_ago.payment_count,0),
nvl(old.payment_last_30d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(30d_ago.payment_amount,0.0),
nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0),
nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0),
nvl(1d_ago.refund_order_count,0),
nvl(1d_ago.refund_order_amount,0.0),
nvl(old.refund_order_last_7d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(7d_ago.refund_order_count,0),
nvl(old.refund_order_last_7d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(7d_ago.refund_order_amount,0.0),
nvl(old.refund_order_last_30d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(30d_ago.refund_order_count,0),
nvl(old.refund_order_last_30d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(30d_ago.refund_order_amount,0.0),
nvl(old.refund_order_count,0)+nvl(1d_ago.refund_order_count,0),
nvl(old.refund_order_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0),
nvl(1d_ago.refund_payment_count,0),
nvl(1d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_last_7d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(7d_ago.refund_payment_count,0),
nvl(old.refund_payment_last_7d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(7d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_last_30d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(30d_ago.refund_payment_count,0),
nvl(old.refund_payment_last_30d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(30d_ago.refund_payment_amount,0.0),
nvl(old.refund_payment_count,0)+nvl(1d_ago.refund_payment_count,0),
nvl(old.refund_payment_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)
from
(
select
province_id,
visit_last_1d_count,
login_last_1d_count,
visit_last_7d_count,
login_last_7d_count,
visit_last_30d_count,
login_last_30d_count,
visit_count,
login_count,
order_last_1d_count,
order_last_1d_original_amount,
order_last_1d_final_amount,
order_last_7d_count,
order_last_7d_original_amount,
order_last_7d_final_amount,
order_last_30d_count,
order_last_30d_original_amount,
order_last_30d_final_amount,
order_count,
order_original_amount,
order_final_amount,
payment_last_1d_count,
payment_last_1d_amount,
payment_last_7d_count,
payment_last_7d_amount,
payment_last_30d_count,
payment_last_30d_amount,
payment_count,
payment_amount,
refund_order_last_1d_count,
refund_order_last_1d_amount,
refund_order_last_7d_count,
refund_order_last_7d_amount,
refund_order_last_30d_count,
refund_order_last_30d_amount,
refund_order_count,
refund_order_amount,
refund_payment_last_1d_count,
refund_payment_last_1d_amount,
refund_payment_last_7d_count,
refund_payment_last_7d_amount,
refund_payment_last_30d_count,
refund_payment_last_30d_amount,
refund_payment_count,
refund_payment_amount
from ${APP}.dwt_area_topic
where dt=date_add('$do_date',-1)
)old
full outer join
(
select
province_id,
visit_count,
login_count,
order_count,
order_original_amount,
order_final_amount,
payment_count,
payment_amount,
refund_order_count,
refund_order_amount,
refund_payment_count,
refund_payment_amount
from ${APP}.dws_area_stats_daycount
where dt='$do_date'
)1d_ago
on old.province_id=1d_ago.province_id
left join
(
select
province_id,
visit_count,
login_count,
order_count,
order_original_amount,
order_final_amount,
payment_count,
payment_amount,
refund_order_count,
refund_order_amount,
refund_payment_count,
refund_payment_amount
from ${APP}.dws_area_stats_daycount
where dt=date_add('$do_date',-7)
)7d_ago
on old.province_id= 7d_ago.province_id
left join
(
select
province_id,
visit_count,
login_count,
order_count,
order_original_amount,
order_final_amount,
payment_count,
payment_amount,
refund_order_count,
refund_order_amount,
refund_payment_count,
refund_payment_amount
from ${APP}.dws_area_stats_daycount
where dt=date_add('$do_date',-30)
)30d_ago
on old.province_id= 30d_ago.province_id;
alter table ${APP}.dwt_area_topic drop partition(dt='$clear_date');
"
case $1 in
"dwt_visitor_topic" )
hive -e "$dwt_visitor_topic"
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_visitor_topic/dt=$clear_date
;;
"dwt_user_topic" )
hive -e "$dwt_user_topic"
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_user_topic/dt=$clear_date
;;
"dwt_sku_topic" )
hive -e "$dwt_sku_topic"
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_sku_topic/dt=$clear_date
;;
"dwt_activity_topic" )
hive -e "$dwt_activity_topic"
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_activity_topic/dt=$clear_date
;;
"dwt_coupon_topic" )
hive -e "$dwt_coupon_topic"
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_coupon_topic/dt=$clear_date
;;
"dwt_area_topic" )
hive -e "$dwt_area_topic"
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_area_topic/dt=$clear_date
;;
"all" )
hive -e "$dwt_visitor_topic$dwt_user_topic$dwt_sku_topic$dwt_activity_topic$dwt_coupon_topic$dwt_area_topic"
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_visitor_topic/dt=$clear_date
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_user_topic/dt=$clear_date
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_sku_topic/dt=$clear_date
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_activity_topic/dt=$clear_date
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_coupon_topic/dt=$clear_date
hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_area_topic/dt=$clear_date
;;
esac（2）增加脚本执行权限
[muzili@hadoop102 bin]$ chmod 777 dws_to_dwt.sh
（3）执行脚本
[muzili@hadoop102 bin]$ dws_to_dwt.sh 2021-09-16
（4）查看导入数据
1141
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
