数仓是为决策用的，它的架构逻辑十分严密。
ods层
数仓是分层的。
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<struct<sku_id:string,sku_num:bigint,order_count:bigint,activity_reduce_amount:decimal(16,2),coupon_reduce_amount:decimal(16,2),original_amount:decimal(16,2),final_amount:decimal(16,2)>> 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层其他还有许多重要的统计，商品方面，优惠券，分地区的统计，开展的活动，复购率，等等的。
2250
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
