数仓是为决策用的,它的架构逻辑十分严密。
数仓分区- ods层
- dwd与dim层
- 事务型事实表
- 周期型事实表
- 累计型快照事实表
- dws层
- dwt层
- ads层
- 用户行为路径分析
- 漏斗模型
- 留存用户
数仓是分层的。
ods层是直接从hdfs拿数据,不做任何修改,只是添加了时间分区。
添加时间分区后,方便后续的查询和管理。
那hdfs的原始数据从哪里来呢?如果是业务数据,那就是从mysql用sqoop导进来的,导入的时候就会使用压缩(比如LZO压缩)。mysql有什么表,hdfs就对应有什么表。如果是日志数据,那就从文件导入。
那为什么我们要再次将hdfs中的原始数据导到ods层呢?就是为了一次备份,原始数据,是需要备份的,因为你之后几层都要干嘛干嘛,不能把原始数据搞脏了。
dwd与dim层
dwd和dim是一起的。
数据建模有两种理论,一种是关系建模,一种是维度建模。
关系建模就是mysql等关系型数据库用得比较多的。
他有什么特点呢?就是数据会拆得比较散,解耦确实做的好,一致性也做的好,但是查询的时候就会有很多join。
维度建模有两种表,维度表和事实表。
维度表就是非常细节的表。比如有商品维度表,优惠券维度表,活动维度表,用户维度表,地区维度表,时间维度表,和mysql的表很像,但有时也需要从多个表join出数据来满足自己的字段。
事实表的字段是维度表的主键,然后事实表还有一类度量值。
我们注意几种事实表。
事务型事实表这种表以一个事务为一行数据。比如一个退单,就是一个事务,也是一行数据。它的数据只增不减,之前的数据不会改变。
因此,它的更新方式是增量更新。
比如一个评价事实表(评价一般是不会变的,只增不减):
DROp TABLE IF EXISTS dwd_comment_info; CREATE EXTERNAL TABLE dwd_comment_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `sku_id` STRING COMMENT '商品sku', `spu_id` STRING COMMENT '商品spu', `order_id` STRING COMMENT '订单ID', `appraise` STRING COMMENT '评价(好评、中评、差评、默认评价)', `create_time` STRING COMMENT '评价时间' ) COMMENT '评价事实表' PARTITIonED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_comment_info/' TBLPROPERTIES ("parquet.compression"="lzo");
那从mysql传到hdfs上的时候是怎么传的呢?
import_data(){ $sqoop import --connect jdbc:mysql://hadoop102:3306/gmall --username root --password root --target-dir /origin_data/gmall/db/comment_info/2020-06-14 --delete-target-dir --query " select id, user_id, sku_id, spu_id, order_id, appraise, create_time from comment_info where date_format(create_time,'%Y-%m-%d')='$do_date' and $CONDITIONS" --num-mappers 1 --fields-terminated-by 't' --compress --compression-codec lzop --null-string '\N' --null-non-string '\N' hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /origin_data/gmall/db/comment_info/2020-06-14
注意我们以create_time为单位进行导入的。所以ods层的每日分区得到的都是当日新增数据。那么dwd层得到的也是当日新增的数据。
周期型事实表这种表的业务特点就是忽略过程,只需要最终的结果。
比如加入购物车这个业务,你今天可能把两部苹果手机加入了购物车,但是明天又取消了,所以今天"将两部苹果手机加入购物车"的数据其实没有意义,我们关注的是当下购物车清零了。
因此它需要全量同步,每天都需要全量的数据,这样才能够知道最终的状态。
dwd层购物车事实表的建表语句:
CREATE EXTERNAL TABLE dwd_cart_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `sku_id` STRING COMMENT '商品ID', `source_type` STRING COMMENT '来源类型', `source_id` STRING COMMENT '来源编号', `cart_price` DECIMAL(16,2) COMMENT '加入购物车时的价格', `is_ordered` STRING COMMENT '是否已下单', `create_time` STRING COMMENT '创建时间', `operate_time` STRING COMMENT '修改时间', `order_time` STRING COMMENT '下单时间', `sku_num` BIGINT COMMENT '加购数量' ) COMMENT '加购事实表' PARTITIonED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_cart_info/' TBLPROPERTIES ("parquet.compression"="lzo");
这里的sku_num就是之前说的维度建模中事实表的度量值。
那从mysql是怎么同步到hdfs上的呢?
import_data(){ $sqoop import --connect jdbc:mysql://hadoop102:3306/gmall --username root --password root --target-dir /origin_data/gmall/db/cart_info/2020-06-14 --delete-target-dir --query " select id, user_id, sku_id, cart_price, sku_num, sku_name, create_time, operate_time, is_ordered, order_time, source_type, source_id from cart_info where 1=1 and $CONDITIONS" --num-mappers 1 --fields-terminated-by 't' --compress --compression-codec lzop --null-string '\N' --null-non-string '\N' hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /origin_data/gmall/db/cart_info/2020-06-14
它使用的是where 1=1,也就是每天都要全量的数据。当然到ods层,再到dwd层每天我们都是保留着全部的数据。
累计型快照事实表这种事实表的特点就是它的数据既可能增,又可能变。
比如支付事实表就是这种类型。它的支付状态会发生改变。新的一天会增加新的支付数据,同时之前未支付完成的数据可能支付完成了。
支付事实表的建表语句:
CREATE EXTERNAL TABLE dwd_payment_info ( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单编号', `user_id` STRING COMMENT '用户编号', `province_id` STRING COMMENT '地区ID', `trade_no` STRING COMMENT '交易编号', `out_trade_no` STRING COMMENT '对外交易编号', `payment_type` STRING COMMENT '支付类型', `payment_amount` DECIMAL(16,2) COMMENT '支付金额', `payment_status` STRING COMMENT '支付状态', `create_time` STRING COMMENT '创建时间',--调用第三方支付接口的时间 `callback_time` STRING COMMENT '完成时间'--支付完成时间,即支付成功回调时间 ) COMMENT '支付事实表表' PARTITIonED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_payment_info/' TBLPROPERTIES ("parquet.compression"="lzo");
我们使用回调时间(callback_time)作为是否完成支付的依据。
我们将未完成支付的数据放入9999-99-99分区,完成支付的数据放入当日完成的分区。
假设数仓于2020-06-14上线,那么数据的首日装载就会是:
insert overwrite table dwd_payment_info partition(dt) select pi.id, pi.order_id, pi.user_id, oi.province_id, pi.trade_no, pi.out_trade_no, pi.payment_type, pi.payment_amount, pi.payment_status, pi.create_time, pi.callback_time, nvl(date_format(pi.callback_time,'yyyy-MM-dd'),'9999-99-99') from ( select * from ods_payment_info where dt='2020-06-14' )pi left join ( select id,province_id from ods_order_info where dt='2020-06-14' )oi on pi.order_id=oi.id;
这里使用了letf join,因为我们需要从ods_order_info 中获取province_id。
此外,因为既要向6-14以及6-14之前的分区写,也要向9999-99-99分区写,所以需要使用动态分区。
至于每日数据状态,思路是一样的:
insert overwrite table dwd_payment_info partition(dt) select nvl(new.id,old.id), nvl(new.order_id,old.order_id), nvl(new.user_id,old.user_id), nvl(new.province_id,old.province_id), nvl(new.trade_no,old.trade_no), nvl(new.out_trade_no,old.out_trade_no), nvl(new.payment_type,old.payment_type), nvl(new.payment_amount,old.payment_amount), nvl(new.payment_status,old.payment_status), nvl(new.create_time,old.create_time), nvl(new.callback_time,old.callback_time), nvl(date_format(nvl(new.callback_time,old.callback_time),'yyyy-MM-dd'),'9999-99-99') from ( select id, order_id, user_id, province_id, trade_no, out_trade_no, payment_type, payment_amount, payment_status, create_time, callback_time from dwd_payment_info where dt = '9999-99-99' )old full outer join ( select pi.id, pi.out_trade_no, pi.order_id, pi.user_id, oi.province_id, pi.payment_type, pi.trade_no, pi.payment_amount, pi.payment_status, pi.create_time, pi.callback_time from ( select * from ods_payment_info where dt='2020-06-15' )pi left join ( select id,province_id from ods_order_info where dt='2020-06-15' )oi on pi.order_id=oi.id )new on old.id=new.id;
将dwd层9999-99-99分区的数据(未完成的支付数据)和ods层新的支付数据(以06-15作为例子)根据id使用全外联,此时有些数据会是用null进行填充,我们取出非null的数据(从全外联后的大表),然后依照回调时间动态分区,并覆盖原相同日期分区的数据(insert overwrite)。
dws层dwd的最后一个字母是detail,下一层(dws)就不用细节数据,我们要开始初步聚合了。
这一层会根据主题,或者说感兴趣的统计项进行统计。
比如一个用户主题:
CREATE EXTERNAL TABLE dws_user_action_daycount ( `user_id` STRING COMMENT '用户id', `login_count` BIGINT COMMENT '登录次数', `cart_count` BIGINT COMMENT '加入购物车次数', `favor_count` BIGINT COMMENT '收藏次数', `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_count` BIGINT COMMENT '支付次数', `payment_amount` DECIMAL(16,2) COMMENT '支付金额', `refund_order_count` BIGINT COMMENT '退单次数', `refund_order_num` BIGINT COMMENT '退单件数', `refund_order_amount` DECIMAL(16,2) COMMENT '退单金额', `refund_payment_count` BIGINT COMMENT '退款次数', `refund_payment_num` BIGINT COMMENT '退款件数', `refund_payment_amount` DECIMAL(16,2) COMMENT '退款金额', `coupon_get_count` BIGINT COMMENT '优惠券领取次数', `coupon_using_count` BIGINT COMMENT '优惠券使用(下单)次数', `coupon_used_count` BIGINT COMMENT '优惠券使用(支付)次数', `appraise_good_count` BIGINT COMMENT '好评数', `appraise_mid_count` BIGINT COMMENT '中评数', `appraise_bad_count` BIGINT COMMENT '差评数', `appraise_default_count` BIGINT COMMENT '默认评价数', `order_detail_stats` array> COMMENT '下单明细统计' ) COMMENT '每日用户行为' PARTITIonED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dws/dws_user_action_daycount/' TBLPROPERTIES ("parquet.compression"="lzo");
我们想了解每天用户的登录次数,加入购物车次数等等的。
其实,dws层应该是和维度表相对应的,用户主题的user_id就是一个用户维度主键,然后它的数据呢?它的数据就是各个事实表的度量值以及其他统计。
数据装载就是从各个dwd表中逐个获取然后join。
比如获取登录次数:
上一个页面ID是空说明他来到了首页,然后
user_id不能为空说明他是个用户而非访客。
这样的话:
select dt, user_id, count(*) login_count from dwd_page_log where user_id is not null and last_page_id is null group by dt,user_id
就可以统计出每天每个用户的登录次数了。
dwt层dws层是一天的数据聚合,dwt层是一段时间的数据聚合。
所以dwt层的表和dws层是一样的,只是字段更多,然后数据装载的sql会更复杂。
dwt层的用户主题建表:
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");
相较于dws层只统计一天的,dwt层统计着最近一天的,七天的,三十天的,最大最小的等等。
因为这些统计十分具有时效性,所以我们实际中应该只保留最近几个分区,而非保留所有的分区。
我们可以看一下首日数据装载(6-14,即数仓上线当天)的与用户登录相关的sql:
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) 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 from dws_user_action_daycount group by user_id) t2 on t1.id = t2.user_id
为什么我们要去dim_user_info维度表找用户的首日活跃日期呢?因为dws_user_action_daycount的登录信息来源于用户行为日志,所以在有埋点之前的用户行为就不会被记录下来,但dim_user_info中有全量的用户登录信息,所以去他那里拿。
此外还需要注意的一点是,每日数据装载(比如6-15),我们不需要再去统计七天的dws_user_action_daycount或者三十天的,我们可以借助已经聚合好的6-14的dwt_user_topic。
比如要算dwt_user_topic6-15的7天登录次数 =
6-14dwt_user_topic的7天登录次数
+
6-15dws_user_action_daycount的登录次数
-
7天前(6-8)dws_user_action_daycount的登录次数。
以6-15dwt_user_topic数据装载中的登陆相关为例:
select nvl(1d_ago.user_id, old.user_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), 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) 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 from dwt_user_topic where dt = date_add('2020-06-15', -1) ) old full outer join ( select user_id, login_count from dws_user_action_daycount where dt = '2020-06-15' ) 1d_ago on old.user_id = 1d_ago.user_id left join ( select user_id, login_count from dws_user_action_daycount where dt = date_add('2020-06-15', -7) ) 7d_ago on old.user_id = 7d_ago.user_id left join ( select user_id, login_count from dws_user_action_daycount where dt = date_add('2020-06-15', -30) ) 30d_ago on old.user_id = 30d_ago.user_id;ads层
ads层是面向报表的,所以这些统计对老板来说更有意义。
我们举几个例子。
用户行为路径分析它的表结构很简单:
CREATE EXTERNAL TABLE ads_page_path ( `dt` STRING COMMENT '统计日期', `recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `source` STRING COMMENT '跳转起始页面ID', `target` STRING COMMENT '跳转终到页面ID', `path_count` BIGINT COMMENT '跳转次数' ) COMMENT '页面浏览路径' ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' LOCATION '/warehouse/gmall/ads/ads_page_path/';
我们这里没有使用分区,也没有列式存储和LZO压缩,因为它们的数据不多(已经是最后的聚合了)。
我们的数据来源于dwd_page_log,也即页面日志信息
mid_id为设备id,他是唯一的,last_page_id是上一个页面ID,page_id为当前页面ID,ts为进入当前页面的时间戳,dt是分区。
我们最终的效果是一个Sankey图:
Sankey图要有来源(source),以及目的地(target)。但是source不能为null。
就我们的数据而言,source为null其实就是进入主页,所以我们需要做一个处理:
select mid_id, last_page_id, page_id, ts, if(last_page_id is null, ts, null) session_start_point from dwd_page_log where dt ='2020-06-14'
用个if判断就可以拿到进入主页的时间戳。
使用开窗函数last_value可以拿到某个设备进入主页开始活动的起始时间戳:
select mid_id, last_page_id, page_id, ts, concat(mid_id, '-', last_value(session_start_point, true) over (partition by mid_id order by ts)) session_id from ( select mid_id, last_page_id, page_id, ts, if(last_page_id is null, ts, null) session_start_point from dwd_page_log where dt ='2020-06-14' ) t1
将该起始时间戳和设备ID拼接可以标明某个设备进入主页的唯一性,结果是:
为了解决起始为null,我们需要使用lead开窗函数获取下一行;为了解决闭环的问题,比如从home跳到good_list,再从good_list跳到home,我们给行打上数字标签:
select mid_id, last_page_id, page_id source_temp, session_id, lead(page_id, 1, null) over (partition by session_id order by ts) target_temp, row_number() over (partition by session_id order by ts) rn from ( select mid_id, last_page_id, page_id, ts, concat(mid_id, '-', last_value(session_start_point, true) over (partition by mid_id order by ts)) session_id from ( select mid_id, last_page_id, page_id, ts, if(last_page_id is null, ts, null) session_start_point from dwd_page_log where dt ='2020-06-14' ) t1 ) t2
这样起始行为就不是null了。
我们把行为和行号拼接起来:
select concat('step-', rn, ':', source_temp) source, concat('step-', rn + 1, ':', target_temp) target from ( select mid_id, last_page_id, page_id source_temp, session_id, lead(page_id, 1, null) over (partition by session_id order by ts) target_temp, row_number() over (partition by session_id order by ts) rn from ( select mid_id, last_page_id, page_id, ts, concat(mid_id, '-', last_value(session_start_point, true) over (partition by mid_id order by ts)) session_id from ( select mid_id, last_page_id, page_id, ts, if(last_page_id is null, ts, null) session_start_point from dwd_page_log where dt ='2020-06-14' ) t1 ) t2 ) t3
这样就不会出现闭环了。
最终给source和target分组就能知道各个行为之间的次数了:
select source, target, count(*) path_count from ( select concat('step-', rn, ':', source_temp) source, concat('step-', rn + 1, ':', target_temp) target from ( select mid_id, last_page_id, page_id source_temp, session_id, lead(page_id, 1, null) over (partition by session_id order by ts) target_temp, row_number() over (partition by session_id order by ts) rn from ( select mid_id, last_page_id, page_id, ts, concat(mid_id, '-', last_value(session_start_point, true) over (partition by mid_id order by ts)) session_id from ( select mid_id, last_page_id, page_id, ts, if(last_page_id is null, ts, null) session_start_point from dwd_page_log where dt ='2020-06-14' ) t1 ) t2 ) t3 ) t4 group by source, target
这便完成了数据的筛选。
我们希望知道用户浏览首页,商品详情页,加入购物车,购买,支付的各个行为的数量之和。
CREATE EXTERNAL TABLE `ads_user_action` ( `dt` STRING COMMENT '统计日期', `recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `home_count` BIGINT COMMENT '浏览首页人数', `good_detail_count` BIGINT COMMENT '浏览商品详情页人数', `cart_count` BIGINT COMMENT '加入购物车人数', `order_count` BIGINT COMMENT '下单人数', `payment_count` BIGINT COMMENT '支付人数' ) COMMENT '漏斗分析' ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' LOCATION '/warehouse/gmall/ads/ads_user_action/';
浏览首页和浏览商品详情页的数据在dwd_page_log中:
我们可以对设备mid_id进行分组,将某个设备访问过的页面放在集合中,然后看这个集合是否包含home,是否包含good_detail,包含就记一个1,不包含就记0,这样一求和就能够知道访问主页的总数和访问商品详情页的总数了:
select recent_days, sum(if(array_contains(pages, 'home'), 1, 0)) home_count, sum(if(array_contains(pages, 'good_detail'), 1, 0)) good_detail_count from (select recent_days, mid_id, collect_set(page_id) pages from dwd_page_log lateral view explode(array(1, 7, 30)) tmp as recent_days where dt >= date_add('2020-06-14', -recent_days + 1) group by recent_days, mid_id) t3 group by recent_days
当然为了统计最近1,7,30天的,我们还是将数据炸成了三分。
至于加入购物车,下单和付款的数量,那是用户才能有的行为,我们已经在活跃用户统计表dwt_user_topic中做过一些聚合了:
所以最终选取的时候只要使用case when end就可以拿到最近1,7,30天的加购物,下单,付款了。
那么最终的sql就会是一个join的结果(假设统计日期为6-14):
select '2020-06-14', home_good_detail.recent_days home_count, good_detail_count, cart_count, order_count, payment_count from ( select recent_days, sum(if(array_contains(pages, 'home'), 1, 0)) home_count, sum(if(array_contains(pages, 'good_detail'), 1, 0)) good_detail_count from (select recent_days, mid_id, collect_set(page_id) pages from dwd_page_log lateral view explode(array(1, 7, 30)) tmp as recent_days where dt >= date_add('2020-06-14', -recent_days + 1) group by recent_days, mid_id) t3 group by recent_days ) home_good_detail join (select recent_days, sum(if(cart_count > 0, 1, 0)) cart_count, sum(if(order_count > 0, 1, 0)) order_count, sum(if(payment_count > 0, 1, 0)) payment_count from ( select recent_days, case when recent_days = 1 then cart_last_1d_count when recent_days = 7 then cart_last_7d_count when recent_days = 30 then cart_last_30d_count end cart_count, case when recent_days = 1 then order_last_1d_count when recent_days = 7 then order_last_7d_count when recent_days = 30 then order_last_30d_count end order_count, case when recent_days = 1 then payment_last_1d_count when recent_days = 7 then payment_last_7d_count when recent_days = 30 then payment_last_30d_count end payment_count from dwt_user_topic lateral view explode(array(1, 7, 30)) tmp as recent_days where dt = '2020-06-14' ) t1 group by recent_days) cart_order_payment on home_good_detail.recent_days = cart_order_payment.recent_days;留存用户
今天活跃的用户,有多少明天还活跃,就是用户留存。
我们以6-14这天作为最后活跃日期,以此作为终点来统计前7天,也即6-7,6-8,6-9,6-10,6-11,6-12,6-13号的新增用户数和留存数。
建表:
CREATE EXTERNAL TABLE ads_user_retention ( `dt` STRING COMMENT '统计日期', `create_date` STRING COMMENT '用户新增日期', `retention_day` BIGINT COMMENT '截至当前日期留存天数', `retention_count` BIGINT COMMENT '留存用户数量', `new_user_count` BIGINT COMMENT '新增用户数量', `retention_rate` DECIMAL(16,2) COMMENT '留存率' ) COMMENT '用户留存率' ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' LOCATION '/warehouse/gmall/ads/ads_user_retention/';
在dwt_user_topic这张活跃用户统计表中,假设我们关注的是6-7的新增用户数和留存数,那么它的新增用户数就是login_date_first为6-7的行数总和。
首次登陆日期就是新增用户,就是这么理解的。
留存用户数是相对于6-14这天的,也就是说,首次登录日期是6-7,最后登录日期(最后活跃日)是6-14,那么满足这两个条件的行数总和就是留存用户数。
留存率就是留存用户数比上新增用户数。
完整sql:
select '2020-06-14' dt, login_date_first create_date, datediff('2020-06-14', login_date_first) retention_day, sum(if(login_date_last = '2020-06-14', 1, 0)) retention_count, count(*) new_user_count, cast(sum(if(login_date_last = '2020-06-14', 1, 0)) / count(*) * 100 as decimal(16, 2)) retention_rate from dwt_user_topic where dt = '2020-06-14' and login_date_first >= date_add('2020-06-14', -7) and login_date_first < '2020-06-14' group by login_date_first;
ads层其他还有许多重要的统计,商品方面,优惠券,分地区的统计,开展的活动,复购率,等等的。