数据仓库——下篇

文章目录

六、DWS层

6.1 业务术语

6.1.1 用户

用户以设备为判断标准,在移动统计中,每个独立设备认为是一个独立用户,Android系统根据IMEI号,IOS系统根据OpenUDID来标识一个独立用户,每部手机一个用户。

6.1.2 新增用户

首次联网使用应用的用户,如果一个用户首次打开某APP,那么这个用户定义为新增用户,卸载再安装的设备,不会被算作一次新增,新增用户包括日新增用户、周新增用户、月新增用户。

6.1.3 活跃用户

打开应用的用户即为活跃用户。不考虑用户的使用情况,每天一个设备打开多次会被计为一个活跃用户。

6.1.4 周(月)活跃用户

某个自然周(月)内启动过应用的用户,该周(月)内的多次启动只记一个活跃用户

6.1.5 月活跃率

月活跃用户与截止到该月累积的用户总和之间的比例

6.1.6 沉默用户

用户仅在安装当天(次日)启动一次,后续时间再无启动行为,该指标可以反映新增用户质量和用户APP的匹配程度。

6.1.7 版本分布

不同版本的周内各天新增的用户数,活跃用户数和启动次数。利于判断APP个版本之间的优劣好用户行为习惯 。

6.1.8 本周回流用户

上周未启动过应用,本周启动了应用的用户。

6.1.9 连续n周活跃用户

连续n周,每周至少启动一次

6.1.10 忠诚用户

连续活跃5周以上的用户。

6.1.11 连续活跃用户

连续2周及以上活跃的用户

6.1.12 近期流失用户

连续n(2<= n <= 4)周没有启动应用的用户。(第n+1没有启动过)

6.1.13 留存用户应用的

某段时间内新增的用户,经过一段时间后,任然使用应用的被认为是留存用户:这部分用户占当时新增用户的比例即是留存率。

6.1.14 用户新鲜度

每天启动应用的新老用户比例,即新增用户数占活跃用户数的比例。

6.1.15 单次使用时长

每次启动使用的时间长度

6.1.16 日使用时长

累计一天内的使用时间长度

6.1.17 启动次数计算标准

IOS平台应用退到后台就算一次独立的启动:Android平台我们规定,两次启动之间的间隔小于30秒,被计算一次启动。用户在使用过程中,若因收发短信或接电话等退出应用30秒又再次返回应用中,那这两次行为应该是延续而非独立,所以可以算作一次使用行为,即一次启动。业内大多使用30秒这个标准,但用户还是可以自定义此时间间隔。

6.2 系统函数

6.2.1 collet_set函数

聚合函数

6.2.2 nvl 函数

基本语法:

​ NVL(表达式1,表达式2)

​ 如果表达式1为空值,NVL返回值为表达式2的值,否则返回表达式1的值。该函数的目的是把一个空值(null)转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一一个类型

6.2.3 日期处理函数

1)date_format函数(根据格式整理日期)

select date_format('2020-10-10','yyy-MM');

2)date_add函数(加减日期)

select date_add('2020-10-10',-1)

select date_add('2020-10-10',1)

3)next_day函数

​ a)取当前天的下一个周一

select next_day('2020-10-10','MO');

说明:星期一到星期日的英文(Monday,Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday)

​ b)取当前周的周一

select date_add (next_day('2020-10-10','MO'),-7)

4)last_day函数(求当月最后一天日期)

select lase_day('2020-10-10');

DWS层 特点

1.紧密贴合需求,需求要什么字段,就提供什么字段

2.将需求进行分类,按照主题分类,划分设备,用户,商品,活动,地区五大类

3.表都是宽表(既有dwd事实表的字段,还有dwd层维度表的字段)都是分区(每天一个分区)表!

6.3 用户行为数据

6.3.1 每日设备行为 —— dws_uv_detail_daycount

每天设备每天的行为
'从启动日志表中'(每个用户的每台设备启动一次的记录)
	取设备id,手机品牌,手机型号,
	活跃次数(按每个设备id分组,每组的数量,就是该设备的活跃次数)
'从页面日志表中'
	取页面访问统计{
		目标id
		目标数量(按每个设备的设备id分组,每组的该设备的舒朗那就是目标数量)
	}
均按mid_id分组
6.3.1.1 创建表
drop table if exists dws_uv_detail_daycount;
create external table dws_uv_detail_daycount
(
    --从dwd_start_log取
    `mid_id` string COMMENT '设备id', 
    `brand` string COMMENT '手机品牌', 
    `model` string COMMENT '手机型号', 
    `login_count` bigint COMMENT '活跃次数',
    --从dwd_page_log取
    -- collect_set ,collect_list : 将N列多行转 N列1行(array)
    -- named_struct 生成struct
    `page_stats` array<struct<page_id:string,page_count:bigint>> COMMENT '页面访问统计'
) COMMENT '每日设备行为表'
partitioned by(dt string)
stored as parquet
location '/warehouse/gmall/dws/dws_uv_detail_daycount'
tblproperties ("parquet.compression"="lzo");
6.3.3.2 导入数据
insert overwrite table dws_uv_detail_daycount partition(dt='2020-10-10')
SELECT
    t1.mid_id, brand, model, login_count, page_stats
from
(SELECT 
    mid_id,
    concat_ws('|',collect_set(brand))  brand,
    concat_ws('|',collect_set(model))  model,
    count(*) login_count
-- dwd_start_log粒度   1个用户启动一次的信息记录
from dwd_start_log 
where dt='2020-10-10'
GROUP by mid_id ) t1
join
(SELECT 
    mid_id,
    COLLECT_list(named_struct('page_id',page_id,'page_count',page_count)) page_stats
from
(SELECT 
    mid_id, page_id,count(*) page_count
-- dwd_start_log粒度   1个用户访问一个页面一次的信息记录
from dwd_page_log 
where dt='2020-10-10'
GROUP by mid_id, page_id) tmp
GROUP by mid_id) t2
on t1.mid_id=t2.mid_id

6.4 业务数据

6.4.1 每日会员行为——dws_user_action_daycount

每个会员每天
'从启动日志表取:'
	--【启动日志表粒度:每个用户每登录一次记录一天】
		用户id,登录次数(每个用户一个组,每组的数量就是登录次数)

'从行动日志表取:'
	--【每个用户,用什么设备,登录哪些页面,停留多久,看了哪件商品,商品类型,是否将商品加入购物车或者添加收藏,等一系列动作信息】
		加入购物车次数(按用户分组,取某天加入购物车这一动作的次数)

'从订单事实表取:'
	--【每个用户下一笔单的下单时间就是本表的创建时间,以及订单的支付,取消,完成,退款,退款完成时间,订单寄往哪个省份,订单参与了哪些活动,原价金额,优惠金额,订单金额,运费金额】
		下单次数(按用户分组后,每组的数量)
		下单金额(就是原价金额)

'从支付事实表中取:'
	--【一行,就是一个用户的一笔订单,每个用户的每笔订单,什么时候,支付了多少钱,该笔订单是发往哪个省份的】
		支付次数(按用户分组后,每组的数量)
		支付金额(按用户分组后,每个用户每笔订单支付的数量之和)
	
'从订单明细事实表中取:'
	--【一行,就是用户一笔订单的一件商品,每个用户,每笔订单的商品数量,名称,该笔订单的创建时间,订单发往何处,以及订单来源类型,编号,该笔订单的每件商品的原始价格分摊,够买价格分摊,运费分摊,优惠券分摊】
	下单明细【会员的一个订单的其中一件商品详细信息】
			(包含该笔订单的
					商品id,
					商品数量,[就是该订单的商品数量]
					下单次数,[
                                按每个用户,每种商品分组,
                          	    每组的行数就是下单的次数,
                                因为该表粒度是一笔订单一件商品,
                                每种商品有多少行就是该商品的下单次数
                         		]
					订单金额 :
							粒度为每个用户每笔订单
							[
                                即该笔订单该商品的原始价格分摊求和,
                     		   求和的原因是:
                                每个用户,每笔订单有好几个商品,
                                所以每个用户每笔订单有好几行,
                                这些商品(这几行)的原始价格分摊求和,
                                就是该用户该笔订单的订单金额
                               ]
6.4.1.1 创建表
drop table if exists dws_user_action_daycount;
create external table dws_user_action_daycount
(   
    --dwd_start_log
    user_id string comment '用户 id',
    login_count bigint comment '登录次数',
    --无法求出,如需要求此数据,需要加入购物车记录表
    --dwd_action_log
    cart_count bigint comment '加入购物车次数',
    --dwd_fact_order_info
    order_count bigint comment '下单次数',
    order_amount    decimal(16,2)  comment '下单金额',
     --dwd_fact_payment_info
    payment_count   bigint      comment '支付次数',
    payment_amount  decimal(16,2) comment '支付金额',
    --dwd_fact_order_detail
    order_detail_stats 
    array<struct<sku_id:string,
    sku_num:bigint,
    order_count(某个用户,某种商品的数量):bigint,
    order_amount(原始价格分摊,即是订单中某类商品的总价):decimal(20,2)>> comment '下单明细统计'
) COMMENT '每日会员行为'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dws/dws_user_action_daycount/'
tblproperties ("parquet.compression"="lzo");
6.4.1.2 导入数据
with 
t1 as
(SELECT 
    user_id,count(*) login_count
from dwd_start_log 
where dt='2020-10-10' and user_id is not null
GROUP by user_id),
t2 as
(
SELECT 
    user_id,count(*) cart_count
from dwd_action_log 
where dt='2020-10-10' and action_id='cart_add'
GROUP by user_id),
t3 as
(SELECT 
    user_id,count(*) order_count,sum(original_total_amount) order_amount
from dwd_fact_order_info 
where dt='2020-10-10'
GROUP by user_id),
t4 as
(select 
    user_id,count(*) payment_count,SUM(payment_amount)  payment_amount
from dwd_fact_payment_info 
where dt='2020-10-10'
GROUP by user_id ),
t5 as
(SELECT 
    user_id ,COLLECT_list(sku_id_struct) order_detail_stats 
from
(SELECT 
    user_id,
    named_struct('sku_id',sku_id,'sku_num',sum(sku_num),'order_count',count(*),
    'order_amount',cast(sum(original_amount_d) as decimal(20,2))) sku_id_struct
--粒度:  一笔订单的一件商品  是一行
from dwd_fact_order_detail 
where dt='2020-10-10'
GROUP by user_id,sku_id ) t
GROUP by user_id)
insert overwrite table dws_user_action_daycount partition(dt='2020-10-10')
select 
    t1.user_id,
    nvl(login_count,0),
    nvl(cart_count,0),
    nvl(order_count,0),
    nvl(order_amount,0.00),
    nvl(payment_count,0),
    nvl(payment_amount,0.00) ,
    order_detail_stats
from t1 
left join t2 on t1.user_id=t2.user_id
left join t3 on t1.user_id=t3.user_id
left join t4 on t1.user_id=t4.user_id
left join t5 on t1.user_id=t5.user_id

6.4.2 每日商品行为——dws_sku_action_daycount

每件商品每天
'从订单明细事实表取:'一个用户一笔订单的一件商品一行
		--【
    	--从订单明细事实表中取下单的商品id,
    	--商品按商品id分组,每组的数量就是每件商品被下单的次数
    	--商品的数量求和就是被下单件数数
    	--商品的原始价格分摊就是每件商品的被下单金额
    	--】
			被下单的次数,件数,金额
'从支付事实表取:'(存在跨天支付)
  		--【
   		--跨天支付
    	--先取支付事实表的订单id和
    	--订单明细事实表的订单id关联
    	--按商品id分组,每组的数量就是被支付的次数
    	--取订单明细事实表的订单id,商品id,商品数量,最终价格
    	--商品数量求和就是被支付的件数
    	--商品的最终价格求和就是被支付金额
   	 	--】
			被支付的次数,件数,金额
'从退款事实表取:'
	 --【从退款事实表取出每件商品每天退款的次数,件数,被退款金额】
			被退款次数,件数,金额
'从动作日志表取:'
	加入购物车次数
	收藏次数
'从评价事实表取:'
	好拼数,中评数,差评数,默认评论数
	
各表按商品id拼接,分组
每组的数量就是次数
6.4.2.1 创建表
drop table if exists dws_sku_action_daycount;
create external table dws_sku_action_daycount 
(   
    --dwd_fact_order_detail 
    sku_id string comment 'sku_id',
    order_count bigint comment '被下单次数',
    order_num bigint comment '被下单件数',
    order_amount decimal(16,2) comment '被下单金额',
    
    --dwd_fact_payment_info 存在跨天支付,取dwd_fact_order_detail当天和前一天,进行Join
    payment_count bigint  comment '被支付次数',
    payment_num bigint comment '被支付件数',
    payment_amount decimal(16,2) comment '被支付金额',
    
    
    -- dwd_fact_refound_info
    refund_count bigint  comment '被退款次数',
    refund_num bigint comment '被退款件数',
    refund_amount  decimal(16,2) comment '被退款金额',
    
    --dwd_action_log
    cart_count bigint comment '被加入购物车次数',
    favor_count bigint comment '被收藏次数',
    
    --dwd_fact_comment_info
    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/dws/dws_sku_action_daycount/'
tblproperties ("parquet.compression"="lzo");
6.4.2.2 导入数据(方式一)
with
t1 as
(SELECT 
    sku_id,
    sum(if(appraise='1201',1,0)) appraise_good_count,
    sum(if(appraise='1202',1,0)) appraise_mid_count,
    sum(if(appraise='1203',1,0)) appraise_bad_count,
    sum(if(appraise='1204',1,0)) appraise_default_count
from dwd_fact_comment_info 
where dt='2020-10-10'
GROUP by sku_id ),
t2 as 
(SELECT 
    item sku_id,
    sum(if(action_id='cart_add',1,0)) cart_count,
    sum(if(action_id='favor_add',1,0)) favor_count
from dwd_action_log 
where dt='2020-10-10' and item_type='sku_id' 
GROUP by item),
t3 as
(SELECT 
    sku_id ,
    count(*) refund_count,
    sum(refund_num ) refund_num,
    sum(refund_amount) refund_amount
--粒度: 一笔订单的一种商品是一条
from dwd_fact_order_refund_info 
where dt='2020-10-10'
GROUP by sku_id ),
t4 as
(--2020-10-10所有被支付的商品的信息
SELECT
    sku_id,
    count(*) payment_count,
    sum(sku_num) payment_num,
    sum(final_amount_d) payment_amount
from
(SELECT 
    order_id 
from dwd_fact_payment_info 
where dt='2020-10-10') p
join 
(select
    order_id,sku_id,sku_num,final_amount_d
from dwd_fact_order_detail 
where dt='2020-10-10' or dt=date_sub('2020-10-10',1)
) o
on p.order_id=o.order_id
GROUP by sku_id),
t5 as
(SELECT 
    sku_id,
    count(*) order_count,
    sum(sku_num ) order_num,
    sum(original_amount_d) order_amount
from dwd_fact_order_detail 
where dt='2020-10-10'
GROUP by sku_id )
select
   nvl(nvl(nvl(nvl(t1.sku_id,t2.sku_id),t3.sku_id),t4.sku_id),t5.sku_id) id,
   nvl(order_count,0), 
   nvl(order_num,0), 
   nvl(order_amount,0.00),
   nvl(payment_count,0),
   nvl(payment_num,0),
   nvl(payment_amount,0.00), 
    nvl(refund_count,0),
    nvl(refund_num,0),
    nvl(refund_amount,0.00),
    nvl(cart_count,0),
    nvl(favor_count,0),
    nvl(appraise_good_count,0),
    nvl(appraise_mid_count,0),
    nvl(appraise_bad_count,0),
    nvl(appraise_default_count,0)
from t1
full join t2  on t1.sku_id=t2.sku_id
full join t3  on t1.sku_id=t3.sku_id or t2.sku_id=t3.sku_id
full join t4  on t1.sku_id=t4.sku_id or t2.sku_id=t4.sku_id or t3.sku_id=t4.sku_id
full join t5  on t1.sku_id=t5.sku_id or t2.sku_id=t5.sku_id or t3.sku_id=t5.sku_id or t4.sku_id=t5.sku_id
6.4.2.3 导入数据(方式二)

将join 替换为 xxx union all xxx group by xxx

insert overwrite table dws_sku_action_daycount partition(dt='2020-10-10')
SELECT 
    sku_id ,
    sum(order_count ),
    sum(order_num ),
    sum(order_amount ),
    sum(payment_count ),
    sum(payment_num ),
    sum(payment_amount ),
    sum(refund_count ),
    sum(refund_num ),
    sum(refund_amount ),
    sum(cart_count ),
    sum(favor_count ),
    sum(appraise_good_count ),
    sum(appraise_mid_count ),
    sum(appraise_bad_count ),
    sum(appraise_default_count )
from
(SELECT 
    sku_id,0 order_count, 0 order_num, 0 order_amount, 
    0 payment_count,0 payment_num,0 payment_amount,
   0 refund_count, 0 refund_num, 0 refund_amount, 
    0 cart_count,0 favor_count, 
    sum(if(appraise='1201',1,0)) appraise_good_count,
    sum(if(appraise='1202',1,0)) appraise_mid_count,
    sum(if(appraise='1203',1,0)) appraise_bad_count,
    sum(if(appraise='1204',1,0)) appraise_default_count
from dwd_fact_comment_info 
where dt='2020-10-10'
GROUP by sku_id 
union all
SELECT 
    item sku_id,
    0 order_count, 0 order_num, 0 order_amount, 
    0 payment_count,0 payment_num,0 payment_amount,
   0 refund_count, 0 refund_num, 0 refund_amount,
    sum(if(action_id='cart_add',1,0)) cart_count,
    sum(if(action_id='favor_add',1,0)) favor_count,
   0 appraise_good_count,0 appraise_mid_count, 
   0 appraise_bad_count,0 appraise_default_count
from dwd_action_log 
where dt='2020-10-10' and item_type='sku_id' 
GROUP by item
union all
SELECT 
    sku_id ,
    0 order_count, 0 order_num, 0 order_amount, 
    0 payment_count,0 payment_num,0 payment_amount,
    count(*) refund_count,
    sum(refund_num ) refund_num,
    sum(refund_amount) refund_amount,
     0 cart_count,0 favor_count, 
    0 appraise_good_count,0 appraise_mid_count, 
   0 appraise_bad_count,0 appraise_default_count
--粒度: 一笔订单的一种商品是一条
from dwd_fact_order_refund_info 
where dt='2020-10-10'
GROUP by sku_id 
union all
--2020-10-10所有被支付的商品的信息
SELECT
    sku_id,
    0 order_count, 0 order_num, 0 order_amount,
    count(*) payment_count,
    sum(sku_num) payment_num,
    sum(final_amount_d) payment_amount,
     0 refund_count, 0 refund_num, 0 refund_amount, 
    0 cart_count,0 favor_count, 
    0 appraise_good_count,0 appraise_mid_count, 
   0 appraise_bad_count,0 appraise_default_count
from
(SELECT 
    order_id 
from dwd_fact_payment_info 
where dt='2020-10-10') p
join 
(select
    order_id,sku_id,sku_num,final_amount_d
from dwd_fact_order_detail 
where dt='2020-10-10' or dt=date_sub('2020-10-10',1)
) o
on p.order_id=o.order_id
GROUP by sku_id
union all
SELECT 
    sku_id,
    count(*) order_count,
    sum(sku_num ) order_num,
    sum(original_amount_d) order_amount,
     0 payment_count,0 payment_num,0 payment_amount,
     0 refund_count, 0 refund_num, 0 refund_amount, 
    0 cart_count,0 favor_count, 
    0 appraise_good_count,0 appraise_mid_count, 
   0 appraise_bad_count,0 appraise_default_count
from dwd_fact_order_detail 
where dt='2020-10-10'
GROUP by sku_id) tmp
GROUP by sku_id 

6.4.3 每日活动统计——dws_activity_info_daycount

从活动信息表中获取每个活动,统计每个活动每天的曝光信息,订单信息,以及支付信息
'从活动信息表取:'
	--【一行是一个活动的名称,类型,开始时间,结合时间,以及该表创建时间】
		取所有字段
'从曝光日志表取:'
	--【曝光的页面的商品信息,登录该页面的设备id,会员id,所曝光的商品的id】
		取曝光次数(按曝光对象id分组,魅每组的数量就是曝光次数)
'订单事实表:'
	--【一行,就是用户一笔订单的一件商品,每个用户,每笔订单的商品数量,名称,该笔订单的创建时间,订单发往何处,以及订单来源类型,编号,该笔订单的每件商品的原始价格分摊,够买价格分摊,运费分摊,优惠券分摊,以及该订单参与的活动id】
	取下单次数(按活动分组,取每组订单数量就是该活动的下单数量)
	下单金额(分组后每行的订单金额求和)
'支付事实表:'
	--【一行,就是一个用户的一笔订单,每个用户的每笔订单,在什么时候支付了多少钱,该笔订单是发往哪个省份的】
		支付次数(按活动分组后,每组的数量)
		支付金额(按活动分组后,每个活动每笔订单支付的数量之和)
6.4.3.1 创建表
drop table if exists dws_activity_info_daycount;
create external table dws_activity_info_daycount(
    -- 从dwd_dim_activity_info取
    -- 活动得在有效期内  end_time >= 今天
    `id` string COMMENT '编号',
    `activity_name` string  COMMENT '活动名称',
    `activity_type` string  COMMENT '活动类型',
    `start_time` string  COMMENT '开始时间',
    `end_time` string  COMMENT '结束时间',
    `create_time` string  COMMENT '创建时间',
    --dwd_display_log
    `display_count` bigint COMMENT '曝光次数',
    --dwd_fact_order_info
    `order_count` bigint COMMENT '下单次数',
    `order_amount` decimal(20,2) COMMENT '下单金额',
    --dwd_fact_payment_info 存在跨天支付 取前一天和今天的订单
    `payment_count` bigint COMMENT '支付次数',
    `payment_amount` decimal(20,2) COMMENT '支付金额'
) COMMENT '每日活动统计'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dws/dws_activity_info_daycount/'
tblproperties ("parquet.compression"="lzo");

6.4.3.2 导数据入
with 
t1 as
(SELECT 
    activity_id,
    count(*) payment_count,
    sum(payment_amount) payment_amount
from
-- 求昨天和今天下单的参与活动的,在今天进行支付的订单有哪些
(SELECT 
    order_id,payment_amount 
from dwd_fact_payment_info 
where dt='2020-10-10') p
join 
(select
    DISTINCT id,activity_id
from dwd_fact_order_info 
where (dt='2020-10-10' or dt=date_sub('2020-10-10',1)) 
and activity_id  is not NULL 
) o
on p.order_id=o.id
GROUP  by activity_id),
t2 as
(SELECT 
    activity_id,
    count(*) order_count,
    sum(original_total_amount ) order_amount
from dwd_fact_order_info 
where dt='2020-10-10' and  activity_id is not null
GROUP  by activity_id ),
t3 as
(SELECT 
    item activity_id,
    count(*) display_count
FROM  dwd_display_log 
where dt='2020-10-10' and item_type ='activity_id'
GROUP by item), 
t4 as
(SELECT 
    *
from dwd_dim_activity_info 
where dt='2020-10-10')
insert overwrite table dws_activity_info_daycount partition(dt='2020-10-10')
SELECT 
    t4.id, activity_name, activity_type,
    start_time, end_time, create_time, 
    nvl(display_count,0),
    nvl(order_count,0),
    nvl(order_amount,0.0),
    nvl(payment_count,0),
    nvl(payment_amount,0.0)
from t4 
left join t3 on t4.id=t3.activity_id
left join t2 on t4.id=t2.activity_id
left join t1 on t4.id=t1.activity_id

6.4.4 每日地区统计——dws_area_stats_daycount

每个地区每天产生的订单数
'从地区维度表中:'
	取编号,省份名称,地区编码,iso编码,地区id,地区名称
'从启动日志表中:'
	取活跃设备数(就是设备id)
'从订单事实表中:'
	--【一行,就是用户一笔订单的一件商品,每个用户,每笔订单的商品数量,名称,该笔订单的创建时间,订单发往何处,以及订单来源类型,编号,该笔订单的每件商品的原始价格分摊,够买价格分摊,运费分摊,优惠券分摊,以及该订单参与的活动id】
		取下单次数(按活动分组,取每组订单数量就是该活动的下单数量)
		下单金额(分组后每行的订单金额求和)
'支付事实表:'
	--【一行,就是一个用户的一笔订单,每个用户的每笔订单,在什么时候支付了多少钱,该笔订单是发往哪个省份的】
		支付次数(按省份分组后,每组的数量)
		支付金额(按省份分组后,每个用户每笔订单支付的数量之和)
	取支付次数,支付金额
6.4.4.1 创建表
drop table if exists dws_area_stats_daycount;
create external table dws_area_stats_daycount(
    --dwd_dim_base_province
    `id` bigint COMMENT '编号',
    `province_name` string COMMENT '省份名称',
    `area_code` string COMMENT '地区编码',
    `iso_code` string COMMENT 'iso编码',
    `region_id` string COMMENT '地区ID',
    `region_name` string COMMENT '地区名称',
    --dwd_start_log
    `login_count` string COMMENT '活跃设备数',
    --从dwd_fact_order_info
    `order_count` bigint COMMENT '下单次数',
    `order_amount` decimal(20,2) COMMENT '下单金额',
    ----dwd_fact_payment_info 存在跨天支付 取前一天和今天的订单
    `payment_count` bigint COMMENT '支付次数',
    `payment_amount` decimal(20,2) COMMENT '支付金额'
) COMMENT '每日地区统计表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dws/dws_area_stats_daycount/'
tblproperties ("parquet.compression"="lzo");

6.4.4.2 导入数据
insert overwrite table dws_area_stats_daycount partition(dt='2020-10-10')
SELECT 
    id, province_name, t1.area_code, iso_code,
    region_id, region_name, 
    nvl(login_count,0), 
    nvl(order_count,0),
    nvl(order_amount,0.0),
    nvl(payment_count,0), 
    nvl(payment_amount,0.0)
from dwd_dim_base_province t1
left join
(SELECT 
    province_id,
    count(*) order_count,
    sum(original_total_amount ) order_amount
from dwd_fact_order_info 
where dt='2020-10-10'
GROUP by province_id ) t2
on t1.id = t2.province_id
left join
(SELECT 
     province_id,count(*) payment_count,
     sum(payment_amount ) payment_amount
from dwd_fact_payment_info 
where dt='2020-10-10'
GROUP  by province_id ) t3
on t1.id= t3.province_id
left join
(select
    area_code,count(*) login_count
from
(select
    area_code ,mid_id
from dwd_start_log 
where dt='2020-10-10' 
GROUP by area_code ,mid_id ) tmp
GROUP by area_code) t4
on t1.area_code=t4.area_code

6.4.5 dwd_to_ods_db.sh——总的脚本


七、DWT层

7.1 简介

1.DWT层也是数据服务层!
和DWS层的区别在于,DWT是一张累积型宽表(从多张事实表和维度表中取数据)!
2.不是分区表,是一张全量表
3.全量表在更新时决定了方式:old(dwt表)  full   join  new (dws今天新导入的数据),新旧交替,覆盖原表!

4.DWT层是从DWS层取数据!   DWS层将同一主题数据按天聚合,DWT层是将同一主题数据累积聚合!

7.2 设备主题宽表 —— dwt_uv_topic

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BVRMxzGN-1604590441912)(https://i.loli.net/2020/10/24/ZaePtkODpiqrjSz.png)]

UV: user views : 用户浏览数量

PV: page views : 页面浏览数量

7.2.1 创建表

drop table if exists dwt_uv_topic;
create external table dwt_uv_topic
(
    `mid_id` string comment '设备id',
    `brand` string comment '手机品牌',
    `model` string comment '手机型号',
    -- 第一次活跃的时间
    `login_date_first` string  comment '首次活跃时间',
    --最后一次活跃的时间
    `login_date_last` string  comment '末次活跃时间',
    `login_day_count` bigint comment '当日活跃次数',
    -- dwt累积的活跃天数+ if(当天活跃,1,0)
    `login_count` bigint comment '累积活跃天数'
) COMMENT '设备主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_uv_topic'
tblproperties ("parquet.compression"="lzo");

old full join new on old.id = new.id

今天活跃的老用户: where old.id is not null and new.id is not null

新用户: where old.id id null

今天未活跃的老用户: where new.id id null

老用户:where old.id id not null

自定义函数导入hive的auxlib文件夹后要进入hive客户端
hive>use gmall;
hive>creat function 函数名 as '自定义函数的全类名'

7.2.2 导入数据

INSERT overwrite table dwt_uv_topic 
SELECT 
    nvl(old.mid_id,new.mid_id ) mid_id,
    if(old.brand is null ,new.brand,IF(new.brand is null,old.brand,compactstr(old.brand ,new.brand)) ) brand,
    if(old.model is null ,new.model,IF(new.model is null,old.model,compactstr(old.model ,new.model)) ) model,
  --首次活跃时间
  --老的设备是空的,就是使用新设备的使用时间为首次活跃时间,否则老的设备首次活跃时间不变
  	if(old.mid_id is null,new.dt,old.login_date_first ),
  --末次活跃时间
  --新的设备是空的,就是使用老的设备最后一次使用时间为,末次活跃时间,否则就是本次时间为该型设备末次活跃时间
    if(new.mid_id is null,old.login_date_last,new.dt ),
    --新设备的累积活跃天数为空的话,记0
    nvl(new.login_count,0),
    --累积活跃天数为老设备的活跃天数加上新设备的活跃天数(如果新设备不为空,则加上)
    nvl(old.login_count,0)+if(new.mid_id is null,0,1)   
from
dwt_uv_topic old
full join 
(select
    mid_id,brand ,model, login_count ,dt
from dws_uv_detail_daycount 
where dt='2020-10-10') new
on old.mid_id = new.mid_id

7.3 会员主题宽表——dwt_user_topic

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rVT1U1UN-1604590441916)(https://i.loli.net/2020/10/24/oHn24aXf83BuqQL.png)]

'old表从会员主题宽表取:'
	用户id:
		如果有新会员,记录新会员id,没有就还是原来的id   --nvl(old.user_id ,new.user_id ) user_id,
    首次登录时间:
    	如果有原来的登录时间为空就用新的时间,不空就还是用原来的 --nvl(old.login_date_first,new.dt) login_date_first,
    末次登录时间:
    	如果新的时间为空,就是原来的末次活跃时间,如果本次活跃了就用本次的时间  --nvl(new.dt,old.login_date_last) login_date_last,
	-- old为null,说明此人一次单也没下,此时判断今天下单的数量是否为0,
    首次下单时间:nvl(old.login_date_first ,IF (new.order_count = 0,null,new.dt)) order_date_first,
    末次下单时间:IF (new.order_count > 0,new.dt,old.order_date_last) order_date_last,
    首次支付时间: nvl(old.payment_date_first ,IF (new.payment_count = 0,null,new.dt)) payment_date_first,
    末次支付时间:IF (new.payment_count > 0,new.dt,old.payment_date_last) payment_date_last,
    --old + new
    累积登录天数:
    	如果累积登录天数为空,则记0,如果有新设备登录,则累积的登录天数加1 --nvl(old.login_count,0 )+ if(new.login_count is null,0,1) login_count,
    累积下单次数:
   	 	如果累积下单次数为空,则记0,如果有新下的单,则累积下单次数加上新下单的数量 --nvl(old.order_count,0) + nvl(new.order_count,0) order_count,
    累积下单金额:
    	如果累积下单金额为空,则记0,如果有新下的单,则累积下单金额加上新下的单的金额 --nvl(old.order_amount,0.0) + nvl(new.order_amount,0.0) order_amount,
    累积支付次数:
    	如果支付次数为空,则记0,如果有新的支付订单,则原来的支付次数加新的支付次数 --nvl(old.payment_count,0) + nvl(new.payment_count,0) payment_count,
    累积支付金额:
    	如果支付金额为空,则记0,如果有新下的单,则原来的累积支付金额加上新的订单的支付金额 --nvl(old.payment_amount,0.0) + nvl(new.payment_amount,0.0) payment_amount

--new表从每日会员行为表取最新的
	会员id,登录次数,下单次数,支付次数,下单金额,支付金额,时间dt
	
	两表做full join操作
	
	最近30天的数据从每日会员行为表中找
	where dt BETWEEN date_sub('2020-10-10',29) and '2020-10-10'  

7.3.1 创建表

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 '末次登录时间',
     order_date_first string  comment '首次下单时间',
    order_date_last string  comment '末次下单时间',
     payment_date_first string  comment '首次支付时间',
    payment_date_last string  comment '末次支付时间',
    
    login_count bigint comment '累积登录天数',
    order_count bigint comment '累积下单次数',
    order_amount decimal(16,2) comment '累积下单金额',
     payment_count decimal(16,2) comment '累积支付次数',
    payment_amount decimal(16,2) comment '累积支付金额',
    
    login_last_30d_count bigint comment '最近30日登录天数',
    order_last_30d_count bigint comment '最近30日下单次数',
    order_last_30d_amount bigint comment '最近30日下单金额',
    payment_last_30d_count decimal(16,2) comment '最近30日支付次数',
    payment_last_30d_amount decimal(16,2) comment '最近30日支付金额'
)COMMENT '会员主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_user_topic/'
tblproperties ("parquet.compression"="lzo");

7.3.2 导入数据

insert overwrite table dwt_user_topic 
SELECT
t1.user_id, login_date_first, login_date_last, 
login_count, login_last_30d_count, order_date_first, 
order_date_last, order_count, order_amount, order_last_30d_count,
order_last_30d_amount, payment_date_first, payment_date_last,
payment_count, payment_amount, payment_last_30d_count, 
payment_last_30d_amount
from
(SELECT 
    nvl(old.user_id ,new.user_id ) user_id,
    nvl(old.login_date_first,new.dt) login_date_first,
    nvl(new.dt,old.login_date_last) login_date_last,
    -- old为null,说明此人一次单也没下,此时判断今天下单的数量是否为0,
    nvl(old.login_date_first ,IF (new.order_count = 0,null,new.dt)) order_date_first,
    IF (new.order_count > 0,new.dt,old.order_date_last) order_date_last,
     nvl(old.payment_date_first ,IF (new.payment_count = 0,null,new.dt)) payment_date_first,
    IF (new.payment_count > 0,new.dt,old.payment_date_last) payment_date_last,
    --old + new
    nvl(old.login_count,0 )+ if(new.login_count is null,0,1) login_count,
    nvl(old.order_count,0) + nvl(new.order_count,0) order_count,
    nvl(old.order_amount,0.0) + nvl(new.order_amount,0.0) order_amount,
    nvl(old.payment_count,0) + nvl(new.payment_count,0) payment_count,
    nvl(old.payment_amount,0.0) + nvl(new.payment_amount,0.0) payment_amount
from dwt_user_topic old
full join
(select
--login_count 不可能为0,从启动日志查询
--order_count ,payment_count 有可能为0,如果为0代表今天并没有下单或支付
    user_id,login_count ,order_count ,payment_count 
    ,order_amount,payment_amount,dt
from dws_user_action_daycount 
where dt='2020-10-10') new
on old.user_id =new.user_id) t1
left join
(SELECT 
    user_id,
    count(*) login_last_30d_count,
    sum(order_count ) order_last_30d_count,
    sum(order_amount ) order_last_30d_amount,
    sum(payment_count ) payment_last_30d_count,
    sum(payment_amount ) payment_last_30d_amount
from 
dws_user_action_daycount 
where dt BETWEEN date_sub('2020-10-10',29) and '2020-10-10'  --【累积30天的数据】
GROUP by user_id ) t2
on t1.user_id = t2.user_id

7.4 商品主题宽表——dwt_sku_topic

7.4.1 创建表

drop table if exists dwt_sku_topic;
create external table dwt_sku_topic
(
    sku_id string comment 'sku_id',
    spu_id string comment 'spu_id',
    order_last_30d_count bigint comment '最近30日被下单次数',
    order_last_30d_num bigint comment '最近30日被下单件数',
    order_last_30d_amount decimal(16,2)  comment '最近30日被下单金额',
    payment_last_30d_count   bigint  comment '最近30日被支付次数',
    payment_last_30d_num bigint comment '最近30日被支付件数',
    payment_last_30d_amount  decimal(16,2) comment '最近30日被支付金额',
      refund_last_30d_count bigint comment '最近三十日退款次数',
    refund_last_30d_num bigint comment '最近三十日退款件数',
    refund_last_30d_amount decimal(16,2) comment '最近三十日退款金额',
    cart_last_30d_count bigint comment '最近30日被加入购物车次数',
    favor_last_30d_count bigint comment '最近30日被收藏次数',
     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日默认评价数',
    
    order_count bigint comment '累积被下单次数',
    order_num bigint comment '累积被下单件数',
    order_amount decimal(16,2) comment '累积被下单金额', 
    payment_count   bigint  comment '累积被支付次数',
    payment_num bigint comment '累积被支付件数',
    payment_amount  decimal(16,2) comment '累积被支付金额',
    refund_count bigint comment '累积退款次数',
    refund_num bigint comment '累积退款件数',
    refund_amount decimal(16,2) comment '累积退款金额',
    cart_count bigint comment '累积被加入购物车次数',
    favor_count bigint comment '累积被收藏次数',
    appraise_good_count bigint comment '累积好评数',
    appraise_mid_count bigint comment '累积中评数',
    appraise_bad_count bigint comment '累积差评数',
    appraise_default_count bigint comment '累积默认评价数'
 )COMMENT '商品主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_sku_topic/'
tblproperties ("parquet.compression"="lzo");

7.4.2 导入数据

with 
t3 as
(SELECT 
    id ,spu_id 
from dwd_dim_sku_info 
where dt='2020-10-10'),
t2 as
(SELECT 
    sku_id,
    sum(order_count ) order_last_30d_count,
    sum(order_num ) order_last_30d_num,
    sum(order_amount ) order_last_30d_amount,
    sum(payment_count ) payment_last_30d_count,
    sum(payment_num ) payment_last_30d_num,
    sum(payment_amount ) payment_last_30d_amount,
    sum(refund_count ) refund_last_30d_count,
    sum(refund_num ) refund_last_30d_num,
    sum(refund_amount ) refund_last_30d_amount,
    sum(cart_count ) cart_last_30d_count,
    sum(favor_count ) favor_last_30d_count,
    sum(appraise_good_count ) appraise_last_30d_good_count,
    sum(appraise_mid_count ) appraise_last_30d_mid_count,
    sum(appraise_bad_count ) appraise_last_30d_bad_count,
    sum(appraise_default_count ) appraise_last_30d_default_count
from dws_sku_action_daycount 
where dt BETWEEN  date_sub('2020-10-10',29) and '2020-10-10'
GROUP by sku_id ),
-- dwt_sku_topic old +  dws_sku_daycount new
t1 as
(SELECT 
    nvl(old.sku_id,new.sku_id ) sku_id,
    nvl(old.order_count,0 ) + nvl(new.order_count,0) order_count,
    nvl(old.order_num,0 ) + nvl(new.order_num,0) order_num,
    nvl(old.order_amount,0.0 ) + nvl(new.order_amount,0.0) order_amount,
    nvl(old.payment_count,0 ) + nvl(new.payment_count,0) payment_count,
    nvl(old.payment_num,0 ) + nvl(new.payment_num,0) payment_num,
    nvl(old.payment_amount,0.0 ) + nvl(new.payment_amount,0.0) payment_amount,
    nvl(old.refund_count,0 ) + nvl(new.refund_count,0) refund_count,
    nvl(old.refund_num,0 ) + nvl(new.refund_num,0) refund_num,
    nvl(old.refund_amount,0.0 ) + nvl(new.refund_amount,0.0) refund_amount,
    nvl(old.cart_count,0 ) + nvl(new.cart_count,0) cart_count,
    nvl(old.favor_count,0 ) + nvl(new.favor_count,0) favor_count,
    nvl(old.appraise_good_count,0 ) + nvl(new.appraise_good_count,0) appraise_good_count,
    nvl(old.appraise_mid_count,0 ) + nvl(new.appraise_mid_count,0) appraise_mid_count,
    nvl(old.appraise_bad_count,0 ) + nvl(new.appraise_bad_count,0) appraise_bad_count,
    nvl(old.appraise_default_count,0 ) + nvl(new.appraise_default_count,0) appraise_default_count
from dwt_sku_topic old
full join 
(select
    *
from dws_sku_action_daycount 
where dt='2020-10-10') new
on old.sku_id  = new.sku_id )
insert overwrite table dwt_sku_topic 
select 
    t1.sku_id,
    spu_id, 
    nvl(order_last_30d_count,0), 
    nvl(order_last_30d_num,0),
    nvl(order_last_30d_amount,0), 
    order_count, order_num, order_amount, 
    nvl(payment_last_30d_count,0), 
    nvl(payment_last_30d_num,0),
    nvl(payment_last_30d_amount,0.0), payment_count, payment_num,
    payment_amount, 
    nvl(refund_last_30d_count,0), 
    nvl(refund_last_30d_num,0),
    nvl(refund_last_30d_amount,0.0), refund_count, refund_num, refund_amount,
    nvl(cart_last_30d_count,0), cart_count,
    nvl(favor_last_30d_count,0),
    favor_count, 
    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), appraise_good_count, 
    appraise_mid_count, appraise_bad_count, appraise_default_count    
from t1 left join t2 on t1.sku_id=t2.sku_id
left join t3 on t1.sku_id = t3.id

7.5 活动主题宽表——dwt_activity_topic

7.5.1 创建表

drop table if exists dwt_activity_topic;
create external table dwt_activity_topic(
    `id` string COMMENT '编号',
    `activity_name` string  COMMENT '活动名称',
    `activity_type` string  COMMENT '活动类型',
    `start_time` string  COMMENT '开始时间',
    `end_time` string  COMMENT '结束时间',
    `create_time` string  COMMENT '创建时间',   
    `display_day_count` bigint COMMENT '当日曝光次数',
    `order_day_count` bigint COMMENT '当日下单次数',
    `order_day_amount` decimal(20,2) COMMENT '当日下单金额',
    `payment_day_count` bigint COMMENT '当日支付次数',
    `payment_day_amount` decimal(20,2) COMMENT '当日支付金额',
    `display_count` bigint COMMENT '累积曝光次数',
    `order_count` bigint COMMENT '累积下单次数',
    `order_amount` decimal(20,2) COMMENT '累积下单金额',
    `payment_count` bigint COMMENT '累积支付次数',
    `payment_amount` decimal(20,2) COMMENT '累积支付金额'
) COMMENT '活动主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_activity_topic/'
tblproperties ("parquet.compression"="lzo");

7.5.2 导入数据

insert overwrite table dwt_activity_topic 
SELECT 
    nvl(old.id , new.id) id,
    nvl(old.activity_name , new.activity_name) activity_name,
    nvl(old.activity_type , new.activity_type) activity_type,
    nvl(old.start_time , new.start_time) start_time,
    nvl(old.end_time , new.end_time) end_time,
    nvl(old.create_time , new.create_time) create_time,
    nvl(new.display_count,0) display_day_count,
    nvl(new.order_count,0) order_day_count,
    nvl(new.order_amount,0.0) order_day_amount,
    nvl(new.payment_count,0) payment_day_count,
    nvl(new.payment_amount,0.0) payment_day_amount,
    nvl(old.display_count,0) + nvl(new.display_count,0) display_count,
    nvl(old.order_count,0) + nvl(new.order_count,0) order_count,
    nvl(old.order_amount,0.0) + nvl(new.order_amount,0.0) order_amount,
    nvl(old.payment_count,0) + nvl(new.payment_count,0) payment_count,
    nvl(old.payment_amount,0.0) + nvl(new.payment_amount,0.0) payment_amount  
from dwt_activity_topic old
full join 
(select
    *
from dws_activity_info_daycount 
where dt='2020-10-10') new
on old.id =new.id

7.6 地区主题宽表——dwt_area_topic

7.6.1 创建表

drop table if exists dwt_area_topic;
create external table dwt_area_topic(
    `id` bigint COMMENT '编号',
    `province_name` string COMMENT '省份名称',
    `area_code` string COMMENT '地区编码',
    `iso_code` string COMMENT 'iso编码',
    `region_id` string COMMENT '地区ID',
    `region_name` string COMMENT '地区名称',
    `login_day_count` string COMMENT '当天活跃设备数',
    `payment_day_count` bigint COMMENT '当天支付次数',
    `payment_day_amount` decimal(16,2) COMMENT '当天支付金额',
    `order_day_count` bigint COMMENT '当天下单次数',
    `order_day_amount` decimal(16,2) COMMENT '当天下单金额',
    `login_last_30d_count` string COMMENT '最近30天活跃设备数',
    `order_last_30d_count` bigint COMMENT '最近30天下单次数',
    `order_last_30d_amount` decimal(16,2) COMMENT '最近30天下单金额',
    `payment_last_30d_count` bigint COMMENT '最近30天支付次数',
    `payment_last_30d_amount` decimal(16,2) COMMENT '最近30天支付金额'
) COMMENT '地区主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_area_topic/'
tblproperties ("parquet.compression"="lzo");

7.6.2 导入数据

insert overwrite table dwt_area_topic 
SELECT
     t1.id, province_name, area_code, iso_code, region_id,
    region_name, login_day_count,
    nvl(login_last_30d_count,0), order_day_count,
    order_day_amount, 
    nvl(order_last_30d_count,0),
    nvl(order_last_30d_amount,0.0), payment_day_count,
    payment_day_amount, 
    nvl(payment_last_30d_count,0), 
    nvl(payment_last_30d_amount,0)
from 
(SELECT 
    nvl(old.id ,new.id) id,
    nvl(old.province_name ,new.province_name) province_name,
    nvl(old.area_code ,new.area_code) area_code,
    nvl(old.iso_code ,new.iso_code) iso_code,
    nvl(old.region_id ,new.region_id) region_id,
    nvl(old.region_name ,new.region_name) region_name,
    nvl(new.login_count,0) login_day_count,
    nvl(new.payment_count,0) payment_day_count,
    nvl(new.payment_amount,0.0) payment_day_amount,
    nvl(new.order_count,0) order_day_count,
    nvl(new.order_amount,0.0) order_day_amount  
from dwt_area_topic old
full join 
(
    select
        *
    from dws_area_stats_daycount 
    where dt='2020-10-10'
)new
on old.id =new.id) t1
left join   
(SELECT 
       id,
       sum(login_count) login_last_30d_count,
       sum(order_count) order_last_30d_count,
       sum(order_amount) order_last_30d_amount,
       sum(payment_count) payment_last_30d_count,
       sum(payment_amount) payment_last_30d_amount
from dws_area_stats_daycount 
where dt BETWEEN  date_sub('2020-10-10',29) and '2020-10-10'
GROUP by id) t2
on t1.id=t2.id

7.7 总结

–数据导入到方式:
dwt_xxx(old) full join dws_xxx(dt = ‘最新时间’)(new)
新老数据更替,某些情况,需要结合逻辑判断!
将数据覆盖到原表。

–注意:
①.累积xxx数量
old + new

最新的30天的数据

	从dws层选取距离当前日期30天的数据,分组之后进行sum求和操作即可

当前的累积数据

		取之前累积的结果和最新的数据相加

( 
		--nvl(old数据,0) + nvl(new数据,0)
			解释:
			
			如果之前没累积数据,则最新的数据为累加的数据,

			如果之前有数据,则之前累积数据加上最新数据,作为最新的累积数据

)

--注意所有的字段都需要进行nvl判空处理


八、ADS层

8.1 简介

ADS层直接和需求相关!通常是一个(类)需求一张表!

ADS层可以使用分区表,但是没必要,数据量少!通常是全量表!

ADS层每一个统计的结果必须有一个对应的日期属性!

ADS层的数据,从DWS和DWT层导入而来,如果查询某N天的数据,从DWS层取!如果取截至到目前的累积数据,从DWT层取!

8.2 设备主题

日活:当日活跃的 设备数

周活:当周活跃的 设备数

月活:当月活跃的 设备数

活跃:在指定的日期范围内,至少启动过一次APP!

8.2.1 活跃设备数

'从设备主题宽表取:'
	统计日期:统计数据的日期
	当日用户数量:末次活跃时间是今天
	当周用户数量:末次活跃时间介于本周一和本周日之间的活跃设备数求和,只需要大于本周一,说明本周活跃  
		--本周一:date_sub(next_day('2020-10-10','Mon'),7)  下周一减7天就是本周一
	当月用户数量:说明本月活跃,将设备末次活跃时间跟统计数据日期在同一个月的设备求和
		--某个日期的本月最后一天:last_day(日期)
		--例如:last_day(login_date_last) = last_day('2020-10-10')
	Y,N是否是周末,用于得到本周最终结果:
		--本周末:date_sub(next_day('日期' , 'Mon'),1)
	Y,N是否是月末,用于得到本月最终结果:
		--月末:last_day('日期')
8.2.1.1 建表
drop table if exists ads_uv_count;
create external table ads_uv_count(
    `dt` string COMMENT '统计日期',
    `day_count` bigint COMMENT '当日用户数量',
    `wk_count`  bigint COMMENT '当周用户数量',
    `mn_count`  bigint COMMENT '当月用户数量',
    `is_weekend` string COMMENT 'Y,N是否是周末,用于得到本周最终结果',
    `is_monthend` string COMMENT 'Y,N是否是月末,用于得到本月最终结果' 
) COMMENT '活跃设备数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_uv_count/';

8.2.1.2 导入
insert into table ads_uv_count
SELECT 
    '2020-10-10' dt,
    -- 如果 login_date_last = 今天,说明今日活跃
    sum(if(login_date_last ='2020-10-10',1,0)) day_count,
    --  login_date_last 介于本周一和周日之间,说明本周活跃   
    sum(if(login_date_last >= date_sub(next_day('2020-10-10','Mon'),7),1,0)) wk_count,
    -- login_date_last 在本月,说明本月活跃过
    sum(if(last_day(login_date_last) = last_day('2020-10-10'),1,0 )) mn_count,
    if(date_sub(next_day('2020-10-10','Mon'),1)='2020-10-10','Y','N') is_weekend,
    if(last_day('2020-10-10') = '2020-10-10','Y','N' ) is_monthend
from dwt_uv_topic 

8.2.2 每日新增设备

'从设备主题宽表取:'
	创建时间
	新增设备数量:
		--新增设备数量即是设备首次活跃时间在今天的
		--只需要过滤出来再求数量即可
8.2.2.1 建表
create external table ads_new_mid_count
(
    `create_date`     string comment '创建时间' ,
    `new_mid_count`   BIGINT comment '新增设备数量' 
)  COMMENT '每日新增设备数量'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_new_mid_count/';

8.2.2.2 导入
insert into table ads_new_mid_count 
select
  '2020-10-10',
  count(*) 
from dwt_uv_topic 
where login_date_first = '2020-10-10'

8.2.3 留存率

日期:新增的日期 , 留存天数 , 留存N天后的日活日期

​ 新增日期 + 留存天数 = 留存当天的活跃日期

统计:2020-10-10日留存1天的留存率

​ 新增日期:2020-10-10,统计当天新增的人数 : x

​ 留存日期:1天

​ 统计的活跃日期:2020-10-11 , x中 ,在2020-10-11 日期,活跃的设备数: y

2020-10-10日新用户留存1天的人数:y

2020-10-10日新用户留存1天的留存率 : y / x

	①统计数据的日期:自己定

	②设备新增的日期:

	③截止当前日期留存天数:	2 这个数留存几天就定几天

'从dwt层的设备主题宽表取:'	
	④留存数量:前几天新增的,统计日期时间活跃的设备数量就是留存数量
		--2020-10-8日新增的,在2020-10-10活跃的设备,即是过滤出首次活跃时间在统计数据日期前一天【这个天数为截止当前日期留存天数】,和末次活跃时间在统计数据日期这一天做count(*)统计数量where login_date_first = date_sub('2020-10-10',2【截止当前日期留存天数】)
        		and 
        		login_date_last = '2020-10-10' )'从ads层的每日新增设备表取'
     ⑤设备新增数量:当天新增的设备数量
     	--创建时间等于统计数据日期前一天where create_date=date_sub('2020-10-10',1)'从以上两表做join操作所得的表取:'      
      ⑥留存率: 留存数量 /  设备新增数量
      	-- cast(retention_count / new_mid_count * 100 as decimal(16,2)) retention_ratio
      	
      	
分别求出一天留存率,两天留存率,三天留存率做union all操作
8.2.3.1 建表
drop table if exists ads_user_retention_day_rate;
create external table ads_user_retention_day_rate 
(
     `stat_date`          string comment '统计数据的日期',
     `create_date`       string  comment '设备新增日期',
     `retention_day`     int comment '截止当前日期留存天数',
     `retention_count`    bigint comment  '留存数量',
     `new_mid_count`     bigint comment '设备新增数量',
     `retention_ratio`   decimal(16,2) comment '留存率'
)  COMMENT '留存率'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_user_retention_day_rate/';

8.2.3.2 导入
insert into table ads_user_retention_day_rate
select
    '2020-10-10'   stat_date,
     date_sub('2020-10-10',1) create_date,
     1 retention_day,
     retention_count,
     new_mid_count,
     cast(retention_count / new_mid_count * 100 as decimal(16,2)) retention_ratio
from
(select
       -- 已经有2020-10-10的日活, 
       '2020-10-10' dt,
     count(*) retention_count
from dwt_uv_topic 
-- 2020-10-9日新增的,在2020-10-10活跃的设备
where login_date_first = date_sub('2020-10-10',1)
        and 
        login_date_last = '2020-10-10' ) t1
join
(select
    '2020-10-10' dt,
    new_mid_count
from ads_new_mid_count 
where create_date=date_sub('2020-10-10',1) ) t2
on t1.dt = t2.dt
union all
select
    '2020-10-10'   stat_date,
     date_sub('2020-10-10',2) create_date,
     2 retention_day,
     retention_count,
     new_mid_count,
     cast(retention_count / new_mid_count * 100 as decimal(16,2)) retention_ratio
from
(select
       -- 已经有2020-10-10的日活, 
       '2020-10-10' dt,
     count(*) retention_count
from dwt_uv_topic 
-- 2020-10-8日新增的,在2020-10-10活跃的设备
where login_date_first = date_sub('2020-10-10',2)
        and 
        login_date_last = '2020-10-10' ) t1
join
(select
    '2020-10-10' dt,
    new_mid_count
from ads_new_mid_count 
where create_date=date_sub('2020-10-10',2) ) t2
on t1.dt = t2.dt
union all
select
    '2020-10-10'   stat_date,
     date_sub('2020-10-10',3) create_date,
     3 retention_day,
     retention_count,
     new_mid_count,
     cast(retention_count / new_mid_count * 100 as decimal(16,2)) retention_ratio
from
(select
       -- 已经有2020-10-10的日活, 
       '2020-10-10' dt,
     count(*) retention_count
from dwt_uv_topic 
-- 2020-10-7日新增的,在2020-10-10活跃的设备
where login_date_first = date_sub('2020-10-10',3)
        and 
        login_date_last = '2020-10-10' ) t1
join
(select
    '2020-10-10' dt,
    new_mid_count
from ads_new_mid_count 
where create_date=date_sub('2020-10-10',3) ) t2
on t1.dt = t2.dt

8.2.4 沉默用户数

沉默用户数: 仅仅在安装当天启动过应用,并且安装的当天距离现在已经是7天之前

仅仅在安装当天启动过应用: 从dwt层,login_date_last=login_date_first=安装当天

​ login_date_last=login_date_first <= date_sub(当前日期,7)

'从设备主题宽表取'
	统计日期
	沉默设备数:首次活跃时间等于末次活跃时间
			并且首次活跃时间小于等于末次活跃时间减7,过滤出来之后直接count(*
8.2.4.1 建表
drop table if exists ads_silent_count;
create external table ads_silent_count( 
    `dt` string COMMENT '统计日期',
    `silent_count` bigint COMMENT '沉默设备数'
) COMMENT '沉默用户数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_silent_count';

8.2.4.2 导入
insert into table ads_silent_count
select
  '2020-10-10',
   count(*) 
from dwt_uv_topic 
where login_date_first = login_date_last 
        and
        login_date_first  <= date_sub('2020-10-10',7)

8.2.5 本周回流用户数

本周回流用户: 本周的活跃老用户 ,上周没有活跃

本周回流用户: 本周活跃的老用户 -(取差集) 上周活跃的老用户

SQL取差集合: a left join b on xxx where b.id is null

'从dwt层的设备主题宽表取'
	本周回流用户 = 本周活跃老用户 - 上周活跃老用户
	本周活跃老用户:
		--本周活跃用户就是末次活跃时间大于本周一
		--本周活跃老用户就是首次活跃时间小于本周一
		【
		login_date_last >= date_sub(next_day('2020-10-10','MO'),7)
        AND 
        login_date_first  < date_sub(next_day('2020-10-10','MO'),7)
		】这里求得是本周活跃的老用户
		
'从dws层的每日设备表取'	
	上周活跃老用户:
		--因为每日设备表记录单是每天活跃的设备
		--所以上周活跃的设备就是取日期在上周的设备idwhere dt between date_sub(next_day('2020-10-10',Mon),14)
			and
			date_sub(next_day('2020-10-10',Mon),7)'从以上两表的left join表取'
	统计日期,
	统计日期所在的周:
		--concat(date_sub(next_dy('2020-10-10','Mon'),7) , '_' , date_sub(next_day('2020-10-10','Mon'),1)) 
	回流设备数:
		--取本表设备id不为空的字段做count(*)操作
	
8.2.5.1 建表
drop table if exists ads_back_count;
create external table ads_back_count( 
    `dt` string COMMENT '统计日期',
    `wk_dt` string COMMENT '统计日期所在周',
    `wastage_count` bigint COMMENT '回流设备数'
) COMMENT '本周回流用户数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_back_count';

8.2.5.2 导入
insert into table ads_back_count 
select 
    '2020-10-10' dt,
    concat(date_sub(next_day('2020-10-10','MO'),7),'_',date_sub(next_day('2020-10-10','MO'),1)) wk_dt,
    count(*) wastage_count
from 
--  本周活跃的老用户 可以从dwt层取
(select 
    mid_id 
from dwt_uv_topic 
-- 本周活跃 :  login_date_last >= 本周一
-- 本周老用户 :  login_date_first < 本周一
where  login_date_last >= date_sub(next_day('2020-10-10','MO'),7)
        AND 
        login_date_first  < date_sub(next_day('2020-10-10','MO'),7)) t1
left join
--   上周活跃的老用户
-- 举例: 有用户 a ,首次登录时间为2020-1-1,一定是老用户,假设上周2020-10-1日登录
--  2020-10-10 又登录了,此时 login_date_last更新为 2020-10-10
-- 无法确定a,是否在上周活跃,无法从dwt层获取此数据,只能从dws层取,取上周所有的数据     
(SELECT 
    DISTINCT mid_id 
from dws_uv_detail_daycount 
where dt  BETWEEN date_sub(next_day('2020-10-10','MO'),14)
            and date_sub(next_day('2020-10-10','MO'),8) ) t2
on t1.mid_id = t2.mid_id
where t2.mid_id is null

8.2.6 流失用户

流失用户:最近7天未活跃的设备

​ 最后一次登录的时间,距离现在已经7天了!

​ 从dwt层取,login_date_last <= date_sub(当前日期,7)

'从dwt层的设备主题宽表取'
	流失设备数:
		--最后一次登录时间距离现在已经7天了
		--即末次活跃时间小于等于当前日期减七天where login_date_last <= date_sub('2020-10-10,7')
8.2.6.1 建表
drop table if exists ads_wastage_count;
create external table ads_wastage_count( 
    `dt` string COMMENT '统计日期',
    `wastage_count` bigint COMMENT '流失设备数'
) COMMENT '流失用户数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_wastage_count';

8.2.6.2 导入
insert into table ads_wastage_count
select 
	'2020-10-10',
	count(*)
from dwt_uv_topin
where login_date_last <= date_sub('2020-10-10',7)

8.2.7 最近连续三周活跃用户数

连续三周活跃: 最近连续三周,每周都需要活跃一次!

取本周,所有的周活用户:t1

取最近前1周,所有的周活用户:t2

取最近前2周,所有的周活用户:t3

t1,t2,t3都需要去重

t1 union all t2 union all t3

group by mid_id

count(*) = 3

'从dws层的每日设备行为表取'
	本周周活用户
		--where dt between date_sub(next_day('2020-10-10','Mon'),7) and date_sub(next_day('2020-10-10','Mon'),1)
	上周周活用户
		--where dt between date_sub(next_day('2020-10-10','Mon'),14) and date_sub(next_day('2020-10-10','Mon'),8)
	上上周周活用户
		--where dt between date_sub(next_day('2020-10-10','Mon'),21) and date_sub(next_day('2020-10-10','Mon'),15)
'从以上三个表union all 的表取'
	按设备分组,
	过滤出每组的设备数等于3的设备
'从过滤后的表取'
	统计日期,
	持续时间:
		--concat(date_sub(next_day('2020-10-10','Mon'),21), '_' ,'2020-10-10')
	连续活跃三周的设备数:
		--就是过滤出的表的行数,count(*)
8.2.7.1 建表
drop table if exists ads_continuity_wk_count;
create external table ads_continuity_wk_count( 
    `dt` string COMMENT '统计日期,一般用结束周周日日期,如果每天计算一次,可用当天日期',
    `wk_dt` string COMMENT '持续时间',
    `continuity_count` bigint COMMENT '活跃次数'
) COMMENT '最近连续三周活跃用户数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_continuity_wk_count';

8.2.7.2 导入
insert into table ads_continuity_wk_count
select
    '2020-10-10',
    concat(date_sub(next_day('2020-10-10','MO'),21),'_','2020-10-10'),
    count(*)
from
(select 
    mid_id
from
(--求本周的周活用户
select
    DISTINCT mid_id 
from dws_uv_detail_daycount 
where dt BETWEEN date_sub(next_day('2020-10-10','MO'),7)
            and date_sub(next_day('2020-10-10','MO'),1)
 UNION all          
--求上周的周活用户
select
    DISTINCT mid_id 
from dws_uv_detail_daycount 
where dt BETWEEN date_sub(next_day('2020-10-10','MO'),14)
            and date_sub(next_day('2020-10-10','MO'),8)
  UNION all            
--求上上周的周活用户
select
    DISTINCT mid_id 
from dws_uv_detail_daycount 
where dt BETWEEN date_sub(next_day('2020-10-10','MO'),21)
            and date_sub(next_day('2020-10-10','MO'),15) ) tmp
            GROUP  by mid_id
            having COUNT(*) = 3 ) tmp2

8.2.8 最近七天内连续三天活跃用户数

连续三天

连续的数据都有以下特征:

A列,以a为初始值,每次递增x,连续递增;

B列,以b为初始值,每次递增y,连续递增;

此时A,B两列差值连续两行的差值,总是相等的!

ABA-BA-B列相邻两行的差值
aba-bx-y
a+xb+ya-b+(x-y)x-y
a+2xb+2ya-b+2(x-y)

如果A,B两列的增量一样, X=Y,此时A-B列相邻两行的差值总为0!

依照此规律,求连续3天,可以这样求

ID日期A参照列B row_numberA-B
12020-08-1012020-08-09
12020-08-1122020-08-09
12020-08-1232020-08-09

只需要找一个和A列 增量一致的参照列!

做差!

根据ID和差值分组,分组后统计图内数据的行数 >=3 即复合连续3天!

'从dws层的每日设备行为表取'
	设备id,时间,
	以按设备id分区,时间排序的开窗函数
		--过滤出七天内的时间【date_sub('2020-10-10',7)】 t1
'从t1取'
	设备id,时间,开窗结果,统计日期减开窗结果  t2
'从t2表取'
	按设备id和统计日期减开窗结果分组
	取设备id  t3
'从t3表取'
	统计日期;
	最近7天日期;
	连续三天活跃用户数   
			--每个分区做count(*)操作
8.2.8.1 建表
drop table if exists ads_continuity_uv_count;
create external table ads_continuity_uv_count( 
    `dt` string COMMENT '统计日期',
    `wk_dt` string COMMENT '最近7天日期',
    `continuity_count` bigint
) COMMENT '最近七天内连续三天活跃用户数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_continuity_uv_count';

8.2.8.2 导入
insert into table ads_continuity_uv_count 
select
    '2020-10-10' ,
    concat(date_sub('2020-10-10',7),'_','2020-10-10') ,
    COUNT(DISTINCT mid_id )  
from 
(select  
    mid_id 
from
(select 
    mid_id,dt,rn, date_sub(dt,rn) datediff 【差值】
from
(select
    mid_id,dt , ROW_NUMBER() over(PARTITION  by mid_id order by dt) rn 
from dws_uv_detail_daycount 
where dt > date_sub('2020-10-10',7) ) t1 ) t2
GROUP  by mid_id , datediff
having COUNT(*) >=3  ) t3

8.3 会员主题

8.3.1 会员信息

会员: userid进行标识

会员活跃率: 活跃会员数 / 会员总数

会员付费率: 总付费会员数 / 会员总数

会员新鲜度: 新增用户数 / 活跃用户数

'从会员主题宽表取'
	统计日期;
	活跃会员数;
			--取会员主题宽表末次登录时间是统计日期同一天的记1,在做求和操作
	新增会员数;
			--取会员主题宽表首次的呢过路时间等于统计日期的记1,再做求和操作
	新增消费会员数;
			--取会员主题宽表首次支付时间等于统计日期的记1,再做求和操作
	总付费会员数;
			--取会员主题宽表末次支付时间非空的记1,再做求和操作
	总会员数;
			--count(*)
	会员活跃率;
			--活跃会员数 / 会员总数
	会员付费率;
			--总付费会员数 /  会员总数
	会员新鲜度;
			--新增用户数  /  活跃用户数
8.3.1.1建表
drop table if exists ads_user_topic;
create external table ads_user_topic(
    `dt` string COMMENT '统计日期',
    `day_users` string COMMENT '活跃会员数',
    `day_new_users` string COMMENT '新增会员数',
    `day_new_payment_users` string COMMENT '新增消费会员数',
    `payment_users` string COMMENT '总付费会员数',
    `users` string COMMENT '总会员数',
    `day_users2users` decimal(16,2) COMMENT '会员活跃率',
    `payment_users2users` decimal(16,2) COMMENT '会员付费率',
    `day_new_users2users` decimal(16,2) COMMENT '会员新鲜度'
) COMMENT '会员信息表'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_user_topic';

8.3.1.2 导入
insert into table ads_user_topic

select
	'2020-10-10',
	sum(if(login_date_last = '2020-10-10',1,0)) day_users,
	sum(if(login_date_first = '2020-10-10',1,0)) day_new_users,
	sum(if(payment_date_first = '2020-10-10',1,0)) day_new_payment_users,
	sum(if(payment_date_last is not null,1,0)) payment_users,
	count(*) users,
	--会员活跃率:  活跃会员数 / 会员总数
	cast(sum(IF (login_date_last='2020-10-10',1,0)) / count(*) * 100 as decimal(16,2)) day_users2users,
	
	--会员付费率:  综服非会员数 / 会员总数2
	cast(sum(if(payment_date_last is not null,1,0)) / count(*) * 100 as decimal(16,2)) payment_users2users,
	
	--会员新鲜度:  新增用户数 / 活跃用户数
	case(sum(if(login_date_first = '2020-10-10',1,0)) / sum(if(login_date_last = '2020-10-10',1,0)) * 100 as decimal(16,2))
	

from dwt_user_topic

8.3.2 ads_user_topic

漏斗分析: 用来分析转换率!

'dwd层取页面日志表'
	浏览首页人数;
			--取页面日志表过滤出page_id等于home的数据,user_id去重后,做count操作就是浏览首页的人数
	浏览商品详情页人数;
			----取页面日志表过滤出page_id等于good_detail的数据,user_id去重后,做count操作就是浏览首页的人数
'dws层取每日会员行为表'
	加入购物车人数;
			--如果加入购物车次数大于0,则记1,否则记0,作求和操作
	下单人数;
			--如果下单次数大于0,则记1,否则记0,作求和操作
	支付人数;
			--如果支付次数大于0,则记1,否则记0,作求和操作
			
'以上两表作join操作'
	首页到商品详情转化率;
		--浏览商品详情页人数 / 浏览首页人数 
	商品详情页到加入购物车转化率;
		--加入购物车人数  /  浏览商品详情页人数
	加入购物车到下单转化率;
		--支付人数  /  下单人数
	
8.3.2.1建表
drop table if exists ads_user_action_convert_day;
create external  table ads_user_action_convert_day(
    `dt` string COMMENT '统计日期',
    `home_count`  bigint COMMENT '浏览首页人数',
    `good_detail_count` bigint COMMENT '浏览商品详情页人数',
    `home2good_detail_convert_ratio` decimal(16,2) COMMENT '首页到商品详情转化率',
    `cart_count` bigint COMMENT '加入购物车的人数',
    `good_detail2cart_convert_ratio` decimal(16,2) COMMENT '商品详情页到加入购物车转化率',
    `order_count` bigint     COMMENT '下单人数',
    `cart2order_convert_ratio`  decimal(16,2) COMMENT '加入购物车到下单转化率',
    `payment_amount` bigint     COMMENT '支付人数',
    `order2payment_convert_ratio` decimal(16,2) COMMENT '下单到支付的转化率'
) COMMENT '漏斗分析'
row format delimited  fields terminated by '\t'
location '/warehouse/gmall/ads/ads_user_action_convert_day/';

8.3.2.2 导入
insert into table ads_user_action_convert_day
select
    t1.dt,
    home_count,good_detail_count,
    cast(good_detail_count / home_count * 100 as decimal(16,2)) home2good_detail_convert_ratio,
    cart_count,
    cast(cart_count /good_detail_count * 100 as decimal(16,2)) good_detail2cart_convert_ratio,
    order_count,
    cast(order_count /cart_count * 100 as decimal(16,2)) cart2order_convert_ratio,
    payment_amount,
    cast(payment_amount / order_count * 100 as decimal(16,2)) order2payment_convert_ratio 
from
(select
    '2020-10-10' dt,
    count(DISTINCT user_id ) home_count
from dwd_page_log 
where dt='2020-10-10' and page_id ='home'
     and  user_id  is not NULL ) t1
join
(select
    '2020-10-10' dt,
    count(DISTINCT user_id ) good_detail_count
from dwd_page_log 
where dt='2020-10-10' and page_id ='good_detail'
     and  user_id  is not NULL ) t2
  on t1.dt = t2.dt   
 join    
(SELECT 
    '2020-10-10' dt,
    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_amount
from dws_user_action_daycount 
where dt='2020-10-10') t3
on t1.dt=t3.dt

8.4 商品主题

8.4.1 商品信息

'dwd层取商品维度表'
	过滤出统计日期那天sku_id的数量和spu_id的数量
8.4.1.1 建表
drop table if exists ads_product_info;
create external table ads_product_info(
    `dt` string COMMENT '统计日期',
    `sku_num` string COMMENT 'sku个数',
    `spu_num` string COMMENT 'spu个数'
) COMMENT '商品个数信息'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_product_info';

8.4.1.2 导入
insert into table ads_product_info

select
	'2020-10-10',
	count(*),
	count(distinct spu_id)
from dwd_dim_sku_info
where dt = '2020-10-10'

8.4.2 商品销量排名

'dwt层取商品主题宽表'
	取累积支付件数
8.4.2.1 建表
drop table if exists ads_product_sale_topN;
create external table ads_product_sale_topN(
    `dt` string COMMENT '统计日期',
    `sku_id` string COMMENT '商品ID',
    `payment_amount` bigint COMMENT '销量'
) COMMENT '商品销量排名'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_product_sale_topN';

8.4.2.1 导入
insert into table ads_product_sale_topN

select
	'2020-10-10',
	sku_id,
	payment_num
from dwt_sku_topic
where payment_num > 0
order by payment_num desc
limit 10

8.4.3 商品收藏排名

'dwt层取商品主题宽表'
	取累积被收藏件数
8.4.3.1 建表
drop table if exists ads_product_favor_topN;
create external table ads_product_favor_topN(
    `dt` string COMMENT '统计日期',
    `sku_id` string COMMENT '商品ID',
    `favor_count` bigint COMMENT '收藏量'
) COMMENT '商品收藏排名'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_product_favor_topN';

8.4.3.2 导入
insert into table ads_product_favor_topN
select
    '2020-10-10',
    sku_id ,
    favor_count 
from dwt_sku_topic 
where favor_count > 0
order by favor_count  desc
limit 10

8.4.3 商品加入购物车排名

'dwt层取商品主题宽表'
	取累积被加入购物车次数
8.4.3.1 建表
drop table if exists ads_product_cart_topN;
create external table ads_product_cart_topN(
    `dt` string COMMENT '统计日期',
    `sku_id` string COMMENT '商品ID',
    `cart_count` bigint COMMENT '加入购物车次数'
) COMMENT '商品加入购物车排名'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_product_cart_topN';

8.4.3.2 导入
insert into table ads_product_cart_topN
select
    '2020-10-10',
    sku_id ,
    cart_count 
from dwt_sku_topic 
where cart_count > 0
order by cart_count  desc
limit 10

8.4.4 商品退款率排名(最近30天)

'dwt层取商品主题宽表'
	退款率;
		--取累积30天退款次数  /  累积30天支付次数
		
	过滤出累积30天退款次数大于0,和累积30谈支付次数大于0的数据
	按退款率排名
8.4.4.1 建表
drop table if exists ads_product_refund_topN;
create external table ads_product_refund_topN(
    `dt` string COMMENT '统计日期',
    `sku_id` string COMMENT '商品ID',
    `refund_ratio` decimal(16,2) COMMENT '退款率'
) COMMENT '商品退款率排名'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_product_refund_topN';

8.4.4.2 导入
insert into table ads_product_refund_topN
select
    '2020-10-10',
    sku_id ,
    cast( refund_last_30d_count  / payment_last_30d_count * 100 as decimal(16,2))  refund_ratio
from dwt_sku_topic 
where payment_last_30d_count > 0 and refund_last_30d_count > 0
order by refund_ratio  desc
limit 10

8.4.5 商品差评率排名

'dwt层取商品主题宽表'
	退款率;
		--取累积差评数  /  累积差评数 + 累积好评数 + 累积默认评数
		
	过滤出累积30天退款次数大于0,和累积30谈支付次数大于0的数据
	按退款率排名
8.4.5.1 建表
drop table if exists ads_appraise_bad_topN;
create external table ads_appraise_bad_topN(
    `dt` string COMMENT '统计日期',
    `sku_id` string COMMENT '商品ID',
    `appraise_bad_ratio` decimal(16,2) COMMENT '差评率'
) COMMENT '商品差评率'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_appraise_bad_topN';

8.4.5.2 导入
insert into table ads_appraise_bad_topN
select
    '2020-10-10',
    sku_id ,
    cast( appraise_bad_count  / (appraise_bad_count+ appraise_good_count+appraise_mid_count+appraise_default_count) * 100 as decimal(16,2))  appraise_bad_ratio
from dwt_sku_topic 
where appraise_bad_count > 0 
order by appraise_bad_ratio  desc
limit 10

8.5 营销主题

8.5.1 下单数目统计

'dws层中每日会员行为表'
	单日下单笔数:
		--每位会员下单次数求和
	单日下单金额:
		--每位会员下单金额求和
	单日下单用户数
		--该表的会员数量
		
过滤出下单次数大于0的数据
8.5.1.1 建表
drop table if exists ads_order_daycount;
create external table ads_order_daycount(
    dt string comment '统计日期',
    order_count bigint comment '单日下单笔数',
    order_amount bigint comment '单日下单金额',
    order_users bigint comment '单日下单用户数'
) comment '下单数目统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_order_daycount';
8.5.1.2 导入
insert into table ads_order_daycount
select
    '2020-10-10',
    sum(order_count ) ,
    sum(order_amount),
    count(*)
from dws_user_action_daycount 
where dt='2020-10-10'
    and order_count  > 0

8.5.2 支付信息统计

'dws层每日会员行为表取'
	单日支付笔数;
		--过滤出统计日期那天支付了的会员的支付次数求和
	单日支付金额;
		--过滤出统计日期那天支付了的会员的每个会员的支付金额求和
	单日支付人数;
		--过滤支付次数大于0的数据统计

'dws层每日商品行为表取'
	单日支付商品数;
		--过滤出被支付件数求和
		
'dwd层订单事实表取'
	下单到支付的平均时长,取分钟数
		--( 支付的秒数 - 下单的秒数 ) /   订单数量 / 60 
		unix_timestamp('时间',时间格式)  : 求的是时间戳(当前时间距离197011日的秒数),单位为秒
		【
		cast(sum(unix_timestamp(payment_time ) - unix_timestamp(create_time )) / count(*) / 60  as decimal(16,2)) payment_avg_time
		】
8.5.2.1 建表
drop table if exists ads_payment_daycount;
create external table ads_payment_daycount(
    dt string comment '统计日期',
    order_count bigint comment '单日支付笔数',
    order_amount bigint comment '单日支付金额',
     payment_sku_count bigint comment '单日支付商品数',
    payment_user_count bigint comment '单日支付人数',
    payment_avg_time decimal(16,2) comment '下单到支付的平均时长,取分钟数'
) comment '支付信息统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_payment_daycount';
8.5.2.2 导入
insert into table ads_payment_daycount
select
    t1.dt,
    order_count,order_amount,payment_sku_count,payment_user_count,payment_avg_time
from 
(select
        '2020-10-10' dt,
        sum(payment_count ) order_count,
        sum(payment_amount ) order_amount,
        count(*) payment_user_count
from dws_user_action_daycount 
where dt='2020-10-10'
       and  payment_count > 0) t1
  join      
(SELECT 
    '2020-10-10' dt,
    sum(payment_num) payment_sku_count
from dws_sku_action_daycount 
where dt='2020-10-10') t2
on t1.dt = t2.dt
join
(select    
        '2020-10-10' dt,
        cast(sum(unix_timestamp(payment_time ) - unix_timestamp(create_time )) / count(*) / 60  as decimal(16,2)) payment_avg_time
from dwd_fact_order_info 
where date_format(payment_time,'yyyy-MM-dd') = '2020-10-10' ) t3
on t1.dt = t3.dt

8.5.3 品牌复购率

统计的是一个品牌在当前月的复够率!

复购率: 商品被重复购买的频率!

单次复购率: 两次以上购买人数 / 购买过的总人数

多次复购率: 三次以上购买人数 / 购买过的总人数

'dwd层订单明细事实表取' --一笔订单的一个商品是一条
	订单id,用户id,商品id     【t1】
'dwd层支付事实表取'  --一比订单是一条
	订单id     				【t2】
'dwd层商品维度表取'
	商品id,品牌id   			【t3】
	
1,2,3三表join操作 按商品id和品牌id分组	


8.5.3.1 建表
drop table ads_sale_tm_category1_stat_mn;
create external table ads_sale_tm_category1_stat_mn
(  
    tm_id string comment '品牌id',
    category1_id string comment '1级品类id ',
    category1_name string comment '1级品类名称 ',
    buycount   bigint comment  '购买人数',
    buy_twice_last bigint  comment '两次以上购买人数',
    buy_twice_last_ratio decimal(16,2)  comment  '单次复购率',
    buy_3times_last   bigint comment   '三次以上购买人数',
    buy_3times_last_ratio decimal(16,2)  comment  '多次复购率',
    stat_mn string comment '统计月份',
    stat_date string comment '统计日期' 
) COMMENT '品牌复购率统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_sale_tm_category1_stat_mn/';

8.5.3.2 导入
insert into table ads_sale_tm_category1_stat_mn
select
    t4.tm_id,
    category1_id, category1_name, buycount,
buy_twice_last, buy_twice_last_ratio,
buy_3times_last, buy_3times_last_ratio,
date_format('2020-10-10','yyyy-MM') stat_mn, '2020-10-10' stat_date
from
(select
     tm_id,
     sum(if(buytimes > 0,1,0)) buycount,
     sum(if(buytimes > 1,1,0)) buy_twice_last,
     cast(sum(if(buytimes > 1,1,0)) / sum(if(buytimes > 0,1,0)) * 100 as decimal(16,2))  buy_twice_last_ratio,
     sum(if(buytimes > 2,1,0)) buy_3times_last,
     cast(sum(if(buytimes > 2,1,0)) /  sum(if(buytimes > 0,1,0)) * 100 as decimal(16,2)) buy_3times_last_ratio
from
--求出,每个用户购买每个品牌的记录,进行分组
(select
    --求出当前月,所有用户购买的商品的数据
    user_id, tm_id,
    count(*)  buytimes
from
-- 取dwd层 dwd_fact_order_detail join dwd 层 dwd_fact_payment_info
-- 先求出这个月中,一共支付的所有订单的详情数据
(select
    order_id ,user_id ,sku_id 
-- 一笔订单的一个商品是一条
from dwd_fact_order_detail 
where last_day(dt) = last_day('2020-10-10') ) t1
join 
(select
    order_id 
    --一笔订单是一条
from  dwd_fact_payment_info 
where last_day(dt) = last_day('2020-10-10')) t2
on t1.order_id = t2.order_id
join
(select
    id,tm_id 
from dwd_dim_sku_info 
where dt='2020-10-10') t3
on t1.sku_id = t3.id
GROUP  by t1.user_id,t3.tm_id ) tmp
group by tm_id) t4
join
(SELECT 
    tm_id ,
    concat_ws('|',collect_set(category1_id)) category1_id,
    concat_ws('|',collect_set(category1_name)) category1_name 
from dwd_dim_sku_info 
where dt='2020-10-10' 
group by tm_id ) t5
on t4.tm_id = t5.tm_id

8.6地区主题

8.6.1 地区主题信息

取地区主题表的所有字段
8.6.1.1 建表
drop table if exists ads_area_topic;
create external table ads_area_topic(
    `dt` string COMMENT '统计日期',
    `id` bigint COMMENT '编号',
    `province_name` string COMMENT '省份名称',
    `area_code` string COMMENT '地区编码',
    `iso_code` string COMMENT 'iso编码',
    `region_id` string COMMENT '地区ID',
    `region_name` string COMMENT '地区名称',
    `login_day_count` bigint COMMENT '当天活跃设备数',
    `order_day_count` bigint COMMENT '当天下单次数',
    `order_day_amount` decimal(16,2) COMMENT '当天下单金额',
    `payment_day_count` bigint COMMENT '当天支付次数',
    `payment_day_amount` decimal(16,2) COMMENT '当天支付金额'
) COMMENT '地区主题信息'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_area_topic/';

8.6.1.2 导入
insert into table ads_area_topic
select
    dt,
    id,province_name ,area_code ,iso_code ,region_id ,region_name ,
    login_count  login_day_count,
    order_count ,
    order_amount ,
    payment_count ,
    payment_amount 
from dws_area_stats_daycount 
where dt='2020-10-10'

九、导出数据到MySQL

9.1创建MySql数据库和表

9.1.1 创建数据库

image-20201027113523260

CREATE DATABASE `gmall_report` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

9.1.2 创建表

(1)创建用户主题表

DROP TABLE IF EXISTS `ads_user_topic`;
CREATE TABLE `ads_user_topic`  (
  `dt` date NOT NULL,
  `day_users` bigint(255) NULL DEFAULT NULL,
  `day_new_users` bigint(255) NULL DEFAULT NULL,
  `day_new_payment_users` bigint(255) NULL DEFAULT NULL,
  `payment_users` bigint(255) NULL DEFAULT NULL,
  `users` bigint(255) NULL DEFAULT NULL,
  `day_users2users` double(255, 2) NULL DEFAULT NULL,
  `payment_users2users` double(255, 2) NULL DEFAULT NULL,
  `day_new_users2users` double(255, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`dt`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

(2)创建地区主题表

DROP TABLE IF EXISTS `ads_area_topic`;
CREATE TABLE `ads_area_topic`  (
  `dt` date NOT NULL,
  `id` int(11) NULL DEFAULT NULL,
  `province_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `area_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `iso_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `region_id` int(11) NULL DEFAULT NULL,
  `region_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `login_day_count` bigint(255) NULL DEFAULT NULL,
  `order_day_count` bigint(255) NULL DEFAULT NULL,
  `order_day_amount` double(255, 2) NULL DEFAULT NULL,
  `payment_day_count` bigint(255) NULL DEFAULT NULL,
  `payment_day_amount` double(255, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`dt`, `iso_code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

9.2 Sqoop导出脚本

(1) 在在/home/atguigu/bin目录下创建脚本hdfs_to_mysql.sh

[atguigu@hadoop102 bin]$ vim hdfs_to_mysql.sh

​ 在脚本中填写如下内容

#!/bin/bash

hive_db_name=gmall
mysql_db_name=gmall_report

export_data() {
/opt/module/sqoop/bin/sqoop export \
-Dmapreduce.job.queuename=hive \
--connect "jdbc:mysql://hadoop102:3306/${mysql_db_name}?useUnicode=true&characterEncoding=utf-8"  \
--username root \
--password 000000 \
--table $1 \
--num-mappers 1 \
--export-dir /warehouse/$hive_db_name/ads/$1 \
--input-fields-terminated-by "\t" \
--update-mode allowinsert \
--update-key $2 \
--input-null-string '\\N'    \
--input-null-non-string '\\N'
}

case $1 in
  "ads_uv_count")
     export_data "ads_uv_count" "dt"
;;
  "ads_user_action_convert_day") 
     export_data "ads_user_action_convert_day" "dt"
;;
  "ads_user_topic")
     export_data "ads_user_topic" "dt"
;;
  "ads_area_topic")
     export_data "ads_area_topic" "dt,iso_code"
;;
   "all")
     export_data "ads_user_topic" "dt"
     export_data "ads_area_topic" "dt,iso_code"
     #其余表省略未写
;;
esac

注意:

​ 关于导出update还是insert的问题:

--update-mode:
	updateonly   只更新,无法插入新数据
	allowinsert   允许新增
	
--update-key:[通常设为主键]   
		
		允许更新的情况下,指定哪些字段匹配视为同一条数据,进行更新而不增加。多个字段用逗号分隔。

--input-null-string  和  --input-null-non-string:
	分别表示,将字符串和非字符串列的空串和“null”转义。
	
	Hive中的NULL在底层是以“\N”来存储,而MySQL中的Null在底层就是Null,为了保证数据两端的一致性。
	
	在导出数据时采用 --input-null-string  和  --input-null-string两个参数。
	导入数据时采用  --null-string  和  --null-non-string

(2) 执行操作

hdfs_to_mysql.sh all

十、Azkaban调度

10.1 编译写Azkaban工作流程配置文件

1)编写azkaban.project文件,内容如下

azkaban-flow-version: 2.0

2)编写gmall.flow文件,内容如下

nodes:
 
 \- name: hdfs_to_ods_log

  type: command

  config:

   command: /home/atguigu/bin/hdfs_to_ods_log.sh ${dt}

   

 \- name: hdfs_to_ods_db

  type: command

  config: 

   command: /home/atguigu/bin/hdfs_to_ods_db.sh all ${dt}

   

 \- name: ods_to_dwd_log

  type: command

  dependsOn: 

   \- hdfs_to_ods_log

  config: 

   command: /home/atguigu/bin/ods_to_dwd_log.sh ${dt}

  

 \- name: ods_to_dwd_db

  type: command

  dependsOn: 

   \- hdfs_to_ods_db

  config: 

   command: /home/atguigu/bin/ods_to_dwd_db.sh  ${dt}

  

 \- name: dwd_to_dws

  type: command

  dependsOn:

   \- ods_to_dwd_log

   \- ods_to_dwd_db

  config:

   command: /home/atguigu/bin/dwd_to_dws.sh ${dt}

  

 \- name: dws_to_dwt

  type: command

  dependsOn:

   \- dwd_to_dws

  config:

   command: /home/atguigu/bin/dws_to_dwt.sh ${dt}

  

 \- name: dwt_to_ads

  type: command

  dependsOn: 

   \- dws_to_dwt

  config:

   command: /home/atguigu/bin/dwt_to_ads.sh ${dt}

   

 \- name: hdfs_to_mysql

  type: command

  dependsOn:

   \- dwt_to_ads

  config:

   command: /home/atguigu/bin/hdfs_to_mysql.sh ads_user_topic

10.2 Azkaban多Executor模式下注意事项

--注意每次启动executor在mysql中的executors表各个executor的id都会改变

Azkaban多Executor模式是指,在集群中多个节点部署Executor。在这种模式下, Azkaban web Server会根据策略,选取其中一个Executor去执行任务。

由于我们需要交给Azkaban调度的脚本,以及脚本需要的hive,sqoop等应用只在hadoop102部署了,为保证任务顺利执行,我们须在以下两种方案任选其一,推荐使用方案二。

方案一:指定特定的Executor(hadoop102)去执行任务。

1)在MySQL中azkaban数据库executors表中,查询hadoop102上的Executor的id。

mysql> use azkaban;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> select * from executors;

+----+-----------+-------+--------+

| id | host        | port  | active |

+----+-----------+-------+--------+

|  1  | hadoop103 | 35985 |    1 |

|  2  | hadoop104 | 36363 |    1 |

| ***\*3\****  | hadoop102 | 12321 |    1 |

+----+-----------+-------+--------+
3 rows in set (0.00 sec)

2)在执行工作流程时加入useExecutor属性,如下

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DECOBN6K-1604590441929)(C:\Users\Zephyr\AppData\Local\Temp\ksohtml13964\wps25.jpg)]

方案二:在Executor所在所有节点部署任务所需脚本和应用。

1)分发脚本、hive以及sqoop

[atguigu@hadoop102 ~]$ xsync /home/atguigu/bin/

[atguigu@hadoop102 ~]$ xsync /opt/module/hive

[atguigu@hadoop102 ~]$ xsync /opt/module/sqoop
  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值