行:维度对象的累计汇总行为
列:维度ID+维度模型中与该维度相关的事实表的度量的累积汇总。
分区:截止当日的全量的历史累计
dwt的字段是dws字段的n倍。dws仅计算用户当天的汇总数据,dwt包含 首次、末次、最近n天、开始到至今等指标。最近n天的指标每天都有,具有时效性。
用户主题
DROP TABLE IF EXISTS dwt_user_topic;
CREATE EXTERNAL TABLE IF NOT EXISTS 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");
首日装载
首日活跃日期可能存在不准的情况,比如之前没有埋点系统,用户活跃过,但是没有业务操作行为,然后今天来了后,他的首次日期不应该是今天,而是他的注册日期。末日登录日期设置数仓上线前一天
--首日装载
insert overwrite table dwt_user_topic partition(dt='2020-06-14')
select
id,
login_date_first,--以用户的创建日期作为首次登录日期
nvl(login_date_last,date_add('2020-06-14',-1)),--若有历史登录记录,则根据历史记录获取末次登录日期,否则统一指定一个日期(数仓上线前一天)
nvl(login_last_1d_count,0),
nvl(login_last_1d_day_count,0),
nvl(login_last_7d_count,0),
nvl(login_last_7d_day_count,0),
nvl(login_last_30d_count,0),
nvl(login_last_30d_day_count,0),
nvl(login_count,0),
nvl(login_day_count,0),
order_date_first,
order_date_last,
nvl(order_last_1d_count,0),
nvl(order_activity_last_1d_count,0),
nvl(order_activity_reduce_last_1d_amount,0),
nvl(order_coupon_last_1d_count,0),
nvl(order_coupon_reduce_last_1d_amount,0),
nvl(order_last_1d_original_amount,0),
nvl(order_last_1d_final_amount,0),
nvl(order_last_7d_count,0),
nvl(order_activity_last_7d_count,0),
nvl(order_activity_reduce_last_7d_amount,0),
nvl(order_coupon_last_7d_count,0),
nvl(order_coupon_reduce_last_7d_amount,0),
nvl(order_last_7d_original_amount,0),
nvl(order_last_7d_final_amount,0),
nvl(order_last_30d_count,0),
nvl(order_activity_last_30d_count,0),
nvl(order_activity_reduce_last_30d_amount,0),
nvl(order_coupon_last_30d_count,0),
nvl(order_coupon_reduce_last_30d_amount,0),
nvl(order_last_30d_original_amount,0),
nvl(order_last_30d_final_amount,0),
nvl(order_count,0),
nvl(order_activity_count,0),
nvl(order_activity_reduce_amount,0),
nvl(order_coupon_count,0),
nvl(order_coupon_reduce_amount,0),
nvl(order_original_amount,0),
nvl(order_final_amount,0),
payment_date_first,
payment_date_last,
nvl(payment_last_1d_count,0),
nvl(payment_last_1d_amount,0),
nvl(payment_last_7d_count,0),
nvl(payment_last_7d_amount,0),
nvl(payment_last_30d_count,0),
nvl(payment_last_30d_amount,0),
nvl(payment_count,0),
nvl(payment_amount,0),
nvl(refund_order_last_1d_count,0),
nvl(refund_order_last_1d_num,0),
nvl(refund_order_last_1d_amount,0),
nvl(refund_order_last_7d_count,0),
nvl(refund_order_last_7d_num,0),
nvl(refund_order_last_7d_amount,0),
nvl(refund_order_last_30d_count,0),
nvl(refund_order_last_30d_num,0),
nvl(refund_order_last_30d_amount,0),
nvl(refund_order_count,0),
nvl(refund_order_num,0),
nvl(refund_order_amount,0),
nvl(refund_payment_last_1d_count,0),
nvl(refund_payment_last_1d_num,0),
nvl(refund_payment_last_1d_amount,0),
nvl(refund_payment_last_7d_count,0),
nvl(refund_payment_last_7d_num,0),
nvl(refund_payment_last_7d_amount,0),
nvl(refund_payment_last_30d_count,0),
nvl(refund_payment_last_30d_num,0),
nvl(refund_payment_last_30d_amount,0),
nvl(refund_payment_count,0),
nvl(refund_payment_num,0),
nvl(refund_payment_amount,0),
nvl(cart_last_1d_count,0),
nvl(cart_last_7d_count,0),
nvl(cart_last_30d_count,0),
nvl(cart_count,0),
nvl(favor_last_1d_count,0),
nvl(favor_last_7d_count,0),
nvl(favor_last_30d_count,0),
nvl(favor_count,0),
nvl(coupon_last_1d_get_count,0),
nvl(coupon_last_1d_using_count,0),
nvl(coupon_last_1d_used_count,0),
nvl(coupon_last_7d_get_count,0),
nvl(coupon_last_7d_using_count,0),
nvl(coupon_last_7d_used_count,0),
nvl(coupon_last_30d_get_count,0),
nvl(coupon_last_30d_using_count,0),
nvl(coupon_last_30d_used_count,0),
nvl(coupon_get_count,0),
nvl(coupon_using_count,0),
nvl(coupon_used_count,0),
nvl(appraise_last_1d_good_count,0),
nvl(appraise_last_1d_mid_count,0),
nvl(appraise_last_1d_bad_count,0),
nvl(appraise_last_1d_default_count,0),
nvl(appraise_last_7d_good_count,0),
nvl(appraise_last_7d_mid_count,0),
nvl(appraise_last_7d_bad_count,0),
nvl(appraise_last_7d_default_count,0),
nvl(appraise_last_30d_good_count,0),
nvl(appraise_last_30d_mid_count,0),
nvl(appraise_last_30d_bad_count,0),
nvl(appraise_last_30d_default_count,0),
nvl(appraise_good_count,0),
nvl(appraise_mid_count,0),
nvl(appraise_bad_count,0),
nvl(appraise_default_count,0)
from
(
select
id,
date_format(create_time,'yyyy-MM-dd') login_date_first
from dim_user_info--用户维度拉链表
where dt='9999-99-99'--最新的数据
)t1
left join
(
select
user_id user_id,
-- 登录指标
max(dt) login_date_last,
sum(if(dt='2020-06-14',login_count,0)) login_last_1d_count,
sum(if(dt='2020-06-14' and login_count>0,1,0)) login_last_1d_day_count,
sum(if(dt>=date_add('2020-06-14',-6),login_count,0)) login_last_7d_count,
sum(if(dt>=date_add('2020-06-14',-6) and login_count>0,1,0)) login_last_7d_day_count,
sum(if(dt>=date_add('2020-06-14',-29),login_count,0)) login_last_30d_count,
sum(if(dt>=date_add('2020-06-14',-29) and login_count>0,1,0)) login_last_30d_day_count,
sum(login_count) login_count,
sum(if(login_count>0,1,0)) login_day_count,
-- 订单指标 --
--首次下单时间
min(if(order_count>0,dt,null)) order_date_first,
--末次下单时间
max(if(order_count>0,dt,null)) order_date_last,
--最近1日下单次数
sum(if(dt='2020-06-14',order_count,0)) order_last_1d_count,
--最近1日订单参与活动次数
sum(if(dt='2020-06-14',order_activity_count,0)) order_activity_last_1d_count,
--最近1日订单减免金额-活动
sum(if(dt='2020-06-14',order_activity_reduce_amount,0)) order_activity_reduce_last_1d_amount,
--最近1日下单用券次数
sum(if(dt='2020-06-14',order_coupon_count,0)) order_coupon_last_1d_count,
--最近1日订单减免金额-优惠券
sum(if(dt='2020-06-14',order_coupon_reduce_amount,0)) order_coupon_reduce_last_1d_amount,
--最近1日原始下单金额
sum(if(dt='2020-06-14',order_original_amount,0)) order_last_1d_original_amount,
--最近1日最终下单金额
sum(if(dt='2020-06-14',order_final_amount,0)) order_last_1d_final_amount,
--最近7日下单次数
sum(if(dt>=date_add('2020-06-14',-6),order_count,0)) order_last_7d_count,
--最近7日订单参与活动次数
sum(if(dt>=date_add('2020-06-14',-6),order_activity_count,0)) order_activity_last_7d_count,
--最近7日订单减免金额-活动
sum(if(dt>=date_add('2020-06-14',-6),order_activity_reduce_amount,0)) order_activity_reduce_last_7d_amount,
--最近7日下单用券次数
sum(if(dt>=date_add('2020-06-14',-6),order_coupon_count,0)) order_coupon_last_7d_count,
--最近7日订单减免金额-优惠券
sum(if(dt>=date_add('2020-06-14',-6),order_coupon_reduce_amount,0)) order_coupon_reduce_last_7d_amount,)
--最近7日原始下单金额
sum(if(dt>=date_add('2020-06-14',-6),order_original_amount,0)) order_last_7d_original_amount,
--最近7日最终下单金额
sum(if(dt>=date_add('2020-06-14',-6),order_final_amount,0)) order_last_7d_final_amount,
--最近30日下单次数
sum(if(dt>=date_add('2020-06-14',-29),order_count,0)) order_last_30d_count,
--最近30日订单参与活动次数
sum(if(dt>=date_add('2020-06-14',-29),order_activity_count,0)) order_activity_last_30d_count,
--最近30日订单减免金额活动
sum(if(dt>=date_add('2020-06-14',-29),order_activity_reduce_amount,0)) order_activity_reduce_last_30d_amount,
--最近30日下单用券次数
sum(if(dt>=date_add('2020-06-14',-29),order_coupon_count,0)) order_coupon_last_30d_count,
--最近30日订单减免金额优惠券
sum(if(dt>=date_add('2020-06-14',-29),order_coupon_reduce_amount,0)) order_coupon_reduce_last_30d_amount,
--最近30日原始下单金额
sum(if(dt>=date_add('2020-06-14',-29),order_original_amount,0)) order_last_30d_original_amount,
--最近30日最终下单金额
sum(if(dt>=date_add('2020-06-14',-29),order_final_amount,0)) order_last_30d_final_amount,
--累积下单次数
sum(order_count) order_count,
--累积订单参与活动次数
sum(order_activity_count) order_activity_count,
--累积订单减免金额活动
sum(order_activity_reduce_amount) order_activity_reduce_amount,
--累积下单用券次数
sum(order_coupon_count) order_coupon_count,
--累积订单减免金额优惠券
sum(order_coupon_reduce_amount) order_coupon_reduce_amount,
--累积原始下单金额
sum(order_original_amount) order_original_amount,
--累积最终下单金额
sum(order_final_amount) order_final_amount,
-- 支付指标
min(if(payment_count>0,dt,null)) payment_date_first,
max(if(payment_count>0,dt,null)) payment_date_last,
sum(if(dt='2020-06-14',payment_count,0)) payment_last_1d_count,
sum(if(dt='2020-06-14',payment_amount,0)) payment_last_1d_amount,
sum(if(dt>=date_add('2020-06-14',-6),payment_count,0)) payment_last_7d_count,
sum(if(dt>=date_add('2020-06-14',-6),payment_amount,0)) payment_last_7d_amount,
sum(if(dt>=date_add('2020-06-14',-29),payment_count,0)) payment_last_30d_count,
sum(if(dt>=date_add('2020-06-14',-29),payment_amount,0)) payment_last_30d_amount,
sum(payment_count) payment_count,
sum(payment_amount) payment_amount,
-- 退单指标
sum(if(dt='2020-06-14',refund_order_count,0)) refund_order_last_1d_count,
sum(if(dt='2020-06-14',refund_order_num,0)) refund_order_last_1d_num,
sum(if(dt='2020-06-14',refund_order_amount,0)) refund_order_last_1d_amount,
sum(if(dt>=date_add('2020-06-14',-6),refund_order_count,0)) refund_order_last_7d_count,
sum(if(dt>=date_add('2020-06-14',-6),refund_order_num,0)) refund_order_last_7d_num,
sum(if(dt>=date_add('2020-06-14',-6),refund_order_amount,0)) refund_order_last_7d_amount,
sum(if(dt>=date_add('2020-06-14',-29),refund_order_count,0)) refund_order_last_30d_count,
sum(if(dt>=date_add('2020-06-14',-29),refund_order_num,0)) refund_order_last_30d_num,
sum(if(dt>=date_add('2020-06-14',-29),refund_order_amount,0)) refund_order_last_30d_amount,
sum(refund_order_count) refund_order_count,
sum(refund_order_num) refund_order_num,
sum(refund_order_amount) refund_order_amount,
-- 退款指标
sum(if(dt='2020-06-14',refund_payment_count,0)) refund_payment_last_1d_count,
sum(if(dt='2020-06-14',refund_payment_num,0)) refund_payment_last_1d_num,
sum(if(dt='2020-06-14',refund_payment_amount,0)) refund_payment_last_1d_amount,
sum(if(dt>=date_add('2020-06-14',-6),refund_payment_count,0)) refund_payment_last_7d_count,
sum(if(dt>=date_add('2020-06-14',-6),refund_payment_num,0)) refund_payment_last_7d_num,
sum(if(dt>=date_add('2020-06-14',-6),refund_payment_amount,0)) refund_payment_last_7d_amount,
sum(if(dt>=date_add('2020-06-14',-29),refund_payment_count,0)) refund_payment_last_30d_count,
sum(if(dt>=date_add('2020-06-14',-29),refund_payment_num,0)) refund_payment_last_30d_num,
sum(if(dt>=date_add('2020-06-14',-29),refund_payment_amount,0)) refund_payment_last_30d_amount,
sum(refund_payment_count) refund_payment_count,
sum(refund_payment_num) refund_payment_num,
sum(refund_payment_amount) refund_payment_amount,
-- 加购指标
sum(if(dt='2020-06-14',cart_count,0)) cart_last_1d_count,
sum(if(dt>=date_add('2020-06-14',-6),cart_count,0)) cart_last_7d_count,
sum(if(dt>=date_add('2020-06-14',-29),cart_count,0)) cart_last_30d_count,
sum(cart_count) cart_count,
-- 收藏指标
sum(if(dt='2020-06-14',favor_count,0)) favor_last_1d_count,
sum(if(dt>=date_add('2020-06-14',-6),favor_count,0)) favor_last_7d_count,
sum(if(dt>=date_add('2020-06-14',-29),favor_count,0)) favor_last_30d_count,
sum(favor_count) favor_count,
-- 优惠券指标
sum(if(dt='2020-06-14',coupon_get_count,0)) coupon_last_1d_get_count,
sum(if(dt='2020-06-14',coupon_using_count,0)) coupon_last_1d_using_count,
sum(if(dt='2020-06-14',coupon_used_count,0)) coupon_last_1d_used_count,
sum(if(dt>=date_add('2020-06-14',-6),coupon_get_count,0)) coupon_last_7d_get_count,
sum(if(dt>=date_add('2020-06-14',-6),coupon_using_count,0)) coupon_last_7d_using_count,
sum(if(dt>=date_add('2020-06-14',-6),coupon_used_count,0)) coupon_last_7d_used_count,
sum(if(dt>=date_add('2020-06-14',-29),coupon_get_count,0)) coupon_last_30d_get_count,
sum(if(dt>=date_add('2020-06-14',-29),coupon_using_count,0)) coupon_last_30d_using_count,
sum(if(dt>=date_add('2020-06-14',-29),coupon_used_count,0)) coupon_last_30d_used_count,
sum(coupon_get_count) coupon_get_count,
sum(coupon_using_count) coupon_using_count,
sum(coupon_used_count) coupon_used_count,
-- 评价指标
sum(if(dt='2020-06-14',appraise_good_count,0)) appraise_last_1d_good_count,
sum(if(dt='2020-06-14',appraise_mid_count,0)) appraise_last_1d_mid_count,
sum(if(dt='2020-06-14',appraise_bad_count,0)) appraise_last_1d_bad_count,
sum(if(dt='2020-06-14',appraise_default_count,0)) appraise_last_1d_default_count,
sum(if(dt>=date_add('2020-06-14',-6),appraise_good_count,0)) appraise_last_7d_good_count,
sum(if(dt>=date_add('2020-06-14',-6),appraise_mid_count,0)) appraise_last_7d_mid_count,
sum(if(dt>=date_add('2020-06-14',-6),appraise_bad_count,0)) appraise_last_7d_bad_count,
sum(if(dt>=date_add('2020-06-14',-6),appraise_default_count,0)) appraise_last_7d_default_count,
sum(if(dt>=date_add('2020-06-14',-29),appraise_good_count,0)) appraise_last_30d_good_count,
sum(if(dt>=date_add('2020-06-14',-29),appraise_mid_count,0)) appraise_last_30d_mid_count,
sum(if(dt>=date_add('2020-06-14',-29),appraise_bad_count,0)) appraise_last_30d_bad_count,
sum(if(dt>=date_add('2020-06-14',-29),appraise_default_count,0)) appraise_last_30d_default_count,
sum(appraise_good_count) appraise_good_count,
sum(appraise_mid_count) appraise_mid_count,
sum(appraise_bad_count) appraise_bad_count,
sum(appraise_default_count) appraise_default_count
from dws_user_action_daycount--用户天粒度汇总
group by user_id
)t2
on t1.id=t2.user_id;
最近n天 sum(if)
首次、末次 min、max、min(if) 、max(if)
累积 sum()
每日装载
T-2的dwt 和T-1的dws full join ,生成T-1的dwt。因为dwt的数据具有时效性,保留最近两天分区即可。
--代码没有注释,如何根据ddl的注视快速在代码中加注释?
--先把ddl的注释拷贝出来,然后再拷贝代码。要保证注释的行数和代码的行数是一致的。
insert overwrite table dwt_user_topic partition(dt='2020-06-15')
select
nvl(1d_ago.user_id,old.user_id), --用户id
nvl(old.login_date_first,'2020-06-15'), --首次活跃日期
if(1d_ago.user_id is not null,'2020-06-15',old.login_date_last), --末次活跃日期
nvl(1d_ago.login_count,0), --最近1日登录次数
if(1d_ago.user_id is not null,1,0), --最近1日登录天数
nvl(old.login_last_7d_count,0)+nvl(1d_ago.login_count,0)- nvl(7d_ago.login_count,0), --最近7日登录次数
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), --最近7日登录天数
nvl(old.login_last_30d_count,0)+nvl(1d_ago.login_count,0)- nvl(30d_ago.login_count,0), --最近30日登录次数
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), --最近30日登录天数
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, '2020-06-15', old.order