ADS-应用数据存储
application data store
提供即时的一些常用指标数据库
1. 设备主题
设备主题主要是针对设备查看设备的活跃状态
1.1 最近连续三周活跃用户数
连续三周活跃用户话不多说直接建表
1.1.1 直接建表
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';1.1.2 字段分析
首先连续三周其实是一个不好计算的东西
设备主题先分析
统计连续三周,首先设备主题宽表是可以覆盖的,所以最好使用每日设备行为表,这个表示每日一个数据,比较方便拿取
1.1.3 sql拉取
select
'2020-06-25' dt,
concat(date_add(next_day('2020-06-25', 'mo'),-7*3),'-',date_add(next_day('2020-06-25', 'mo'),-1)) wk_dt,
count(*)
from (
--第一周
select mid_id
from dws_uv_detail_daycount
where dt <= date_add(next_day('2020-06-25', 'mo'),-1)
and dt > date_add(next_day('2020-06-25', 'mo'),-7)
and login_count > 0
group by mid_id
)t1
join (--在这里直接使用join就可以
--前二周
select mid_id
from dws_uv_detail_daycount
where dt <= date_add(next_day('2020-06-25', 'mo'),-1-7)
and dt > date_add(next_day('2020-06-25', 'mo'),-7*2)
and login_count > 0
group by mid_id
) t2
on t1.mid_id=t2.mid_id
join (
--前三周
select mid_id
from dws_uv_detail_daycount
where dt <= date_add(next_day('2020-06-25', 'mo'),-1-7*2)
and dt > date_add(next_day('2020-06-25', 'mo'),-7*3)
and login_count > 0
group by mid_id
)t3
on t2.mid_id=t3.mid_id;1.2 最近七天内连续三天活跃用户数
1.2.1 需求分析
字段直接见下图
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';七天内连续三天的活跃用户
思想简而言之就是从dws_uv_detail_daycount(每日设备行为表)
拿取七天的登录信息,找出连续三天的login_count>1的设备id
1.2.2 求取sql
一
--先拿七天数据,然后对数据进行开窗排序
select
mid_id,
dt,
rank() over (partition by mid_id order by dt) rk
from dws_uv_detail_daycount
where dt > date_add('2020-06-25',-7)二
--对数据进行相减,如果连续得到连续相同的数据
select
mid_id,
dt,
rk,
date_add(dt,-rk) date_dif
from (
select mid_id,
dt,
rank() over (partition by mid_id order by dt) rk
from dws_uv_detail_daycount
where dt > date_add('2020-06-25', -7)
)t1;三
--对数据进行分组,得到连续的相同的数据
select
mid_id,
date_dif
from (
select mid_id,
dt,
rk,
date_add(dt, -rk) date_dif
from (
select mid_id,
dt,
rank() over (partition by mid_id order by dt) rk
from dws_uv_detail_daycount
where dt > date_add('2020-06-25', -7)
) t1
)t2
group by mid_id,date_dif
having count(*) >= 3四
去重,除掉一个七天内,存在两个连续三天登录导致产生了两个mid_id
使用group_by
select
'2020-06-25' dt,
concat('2020-06-25','-',date_add('2020-06-25',-7)) wk_dt,
count(*) continuity_count
from (select
mid_id
from (
select mid_id,
date_dif
from (
select mid_id,
dt,
rk,
date_add(dt, -rk) date_dif
from (
select mid_id,
dt,
rank() over (partition by mid_id order by dt) rk
from dws_uv_detail_daycount
where dt > date_add('2020-06-25',-7)
and dt <= '2020-06-25'
) t1
) t2
group by mid_id, date_dif
having count(*) >= 3
)t3
group by mid_id)t4五
最终的count
select
'2020-06-25' dt,
concat('2020-06-25','-',date_add('2020-06-25',-7)) wk_dt,
count(*) continuity_count
from (select
mid_id
from (
select mid_id,
date_dif
from (
select mid_id,
dt,
rk,
date_add(dt, -rk) date_dif
from (
select mid_id,
dt,
rank() over (partition by mid_id order by dt) rk
from dws_uv_detail_daycount
where dt > date_add('2020-06-25', -7)
) t1
) t2
group by mid_id, date_dif
having count(*) > 3
)t3
group by mid_id)t41.3 用户留存率
1.3.1 需求分析
计算问题分析:
就是当日,比如2020-06-25,能够计算,昨天的1日留存率,前天的2日留存率,大前天的是三日留存率
所以这个是要插入三条数据
1.3.2 直接建表
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/';1.3.3 拉取sql
--一日
select
'2020-06-25' stat_date,
date_add('2020-06-25',-1) create_date,
1 retention_day,
sum(`if`(login_date_last ='2020-06-25',1,0)) retention_count,
count(*) new_mid_count,
sum(`if`(login_date_last ='2020-06-25',1,0))/count(*) retention_countretention_ratio
from dwt_uv_topic
where login_date_first = date_add('2020-06-25',-1);
--二日
select
'2020-06-25' stat_date,
date_add('2020-06-25',-2) create_date,
2 retention_day,
sum(`if`(login_date_last ='2020-06-25',1,0)) retention_count,
count(*) new_mid_count,
sum(`if`(login_date_last ='2020-06-25',1,0))/count(*) retention_countretention_ratio
from dwt_uv_topic
where login_date_first = date_add('2020-06-25',-2);
--三日
select
'2020-06-25' stat_date,
date_add('2020-06-25',-3) create_date,
3 retention_day,
sum(`if`(login_date_last ='2020-06-25',1,0)) retention_count,
count(*) new_mid_count,
sum(`if`(login_date_last ='2020-06-25',1,0))/count(*) retention_countretention_ratio
from dwt_uv_topic
where login_date_first = date_add('2020-06-25',-3);复合之后直接insert
select
'2020-06-25' stat_date,
date_add('2020-06-25',-1) create_date,
1 retention_day,
sum(`if`(login_date_last ='2020-06-25',1,0)) retention_count,
count(*) new_mid_count,
sum(`if`(login_date_last ='2020-06-25',1,0))/count(*) retention_countretention_ratio --
from dwt_uv_topic
where login_date_first = date_add('2020-06-25',-1)--在选表的时候直接上过滤条件
union all
select
'2020-06-25' stat_date,
date_add('2020-06-25',-2) create_date,
2 retention_day,
sum(`if`(login_date_last ='2020-06-25',1,0)) retention_count,
count(*) new_mid_count,
sum(`if`(login_date_last ='2020-06-25',1,0))/count(*) retention_countretention_ratio
from dwt_uv_topic
where login_date_first = date_add('2020-06-25',-2)
union all
select
'2020-06-25' stat_date,
date_add('2020-06-25',-3) create_date,
3 retention_day,
sum(`if`(login_date_last ='2020-06-25',1,0)) retention_count,
count(*) new_mid_count,
sum(`if`(login_date_last ='2020-06-25',1,0))/count(*) retention_countretention_ratio
from dwt_uv_topic
where login_date_first = date_add('2020-06-25',-3)2. 会员主题
2.1会员信息
ads层是一个整体的应用,用于查看会员的整体状态,信息来源于会员主题宽表,会员主题宽表存储
2.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';2.1.2 拉取sql
select
'2020-06-25' dt,
sum(`if`(login_date_last='2020-06-25',1,0)) day_users,
sum(`if`(login_date_first = '2020-06-25',1,0)) day_new_users,
sum(`if`(login_date_first='2020-06-25' and payment_date_first='2020-06-25',1,0)) day_new_payment_users,
sum(`if`(payment_count>0,1,0)) payment_users,
count(*) users,
sum(`if`(login_date_last='2020-06-25',1,0))/count(*) day_users2users,
sum(`if`(payment_count>0,1,0))/count(*) payment_users2users,
sum(`if`(login_date_first = '2020-06-25',1,0))/count(*) day_new_users2users
from dwt_user_topic;这个题目比较简单,注意细微处的拿捏就可以了
2.2 漏斗分析
2.2.1 字段分析
统计“浏览首页->浏览商品详情页->加入购物车->下单->支付”的转化率
思路：统计各个行为的人数，然后计算比值。
浏览首页人数,只能去页面日志表里找
2.2.2 直接建表
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_count` 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/';2.2.3 拉取sql
一
分开求字段
--先求当日浏览首页人数
select sum(`if`(page_id = 'home', 1, 0)) home_count,--浏览首页人数
sum(`if`(page_id = 'good_detail', 1, 0)) good_detail_count,--商品详情人数
sum(`if`(page_id = 'good_detail', 1, 0)) / sum(`if`(page_id = 'home', 1, 0)) home2good_detail_convert_ratio,
sum(`if`(page_id = 'cart', 1, 0)) cart_count,--加购人数
sum(`if`(page_id = 'cart', 1, 0)) / sum(`if`(page_id = 'good_detail', 1, 0)) good_detail2cart_convert_ratio
from dwd_page_log--页面日志表
where dt = '2020-06-25';
--在求取下单人数
select
sum(`if`(order_count>0,1,0)) order_count, --下单人数,
sum(`if`(payment_count>0,1,0)) payment_count --支付人数
from dws_user_action_daycount
where dt='2020-06-25';二
合体
--先求当日浏览首页人数
with tmp_action as (
select '2020-06-25' dt,
sum(`if`(page_id = 'home', 1, 0)) home_count,--浏览首页人数
sum(`if`(page_id = 'good_detail', 1, 0)) good_detail_count,--商品详情人数
sum(`if`(page_id = 'good_detail', 1, 0)) / sum(`if`(page_id = 'home', 1, 0)) home2good_detail_convert_ratio,
sum(`if`(page_id = 'cart', 1, 0)) cart_count,--加购人数
sum(`if`(page_id = 'cart', 1, 0)) / sum(`if`(page_id = 'good_detail', 1, 0)) good_detail2cart_convert_ratio
from dwd_page_log--页面日志表
where dt = '2020-06-25'
),
tmp_user as (
--在求取下单人数
select '2020-06-25'dt,
sum(`if`(order_count > 0, 1, 0)) order_count, --下单人数,
sum(`if`(payment_count > 0, 1, 0)) payment_count --支付人数
from dws_user_action_daycount
where dt = '2020-06-25'
)
select
tmp_user.dt,
tmp_action.home_count,
tmp_action.good_detail_count,
tmp_action.home2good_detail_convert_ratio,
tmp_action.cart_count,
tmp_action.good_detail2cart_convert_ratio,
tmp_user.order_count,
tmp_user.order_count/tmp_action.cart_count cart2order_convert_ratio,
tmp_user.payment_count,
tmp_user.payment_count/tmp_user.order_count order2payemnt_convert_ratio
from tmp_action
join tmp_user
on tmp_action.dt=tmp_user.dt;3. 营销主题
3.1 品牌月复购率
3.1.1 直接建表
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/';3.1.2 分析加sql
单次复购率为品牌购买次数 一次的购买人数占据购买总人数的比例
多次复购率是品牌购买一次 以上购买人数占据购买总人数的比例
一
(buycount)
求出商品的购买次数(buycount),说白了就是求一个用户下了多少个包含此商品的订单
常规思路就是根据用户与商品id进行分组,然后统计订单数量,得到每个商品的下单数目
通过图片我们可以发现在每日会员行为里只有用户id,没有商品id,所以需要从蓝色框里拿出来
order_detail_stats(订单明细,其中包含了每用户的订单是一个struct,然后组成一个数组)
with tmp_order as (
select user_id,
order_detail_struct.sku_id sku_id, --这个商品的id
sum(order_detail_struct.order_count) order_count --这个商品的购买总数
from dws_user_action_daycount --每日行为统计
lateral view explode(order_detail_stats) tmp as order_detail_struct
-- 这个地方要注意,order_detail_stats里面存储的一个array
-- 里面存储的是每一个笔订单的细节
-- 其中包括 sku_id,order_count订单数,order_amount花费,sku_num数量
-- 炸开的过程可以解析为
-- 其他用户视角字段1,<{sku_id1,order_count1,order_amount1,sku_num1}, {sku_id2,order_count2,order_amount2,sku_num2}>,其他用户视角字段1
-- 变为下面两条信息
-- 其他用户视角字段1,<{sku_id1,order_count1,order_amount1,sku_num1},其他用户视角字段2
-- 其他用户视角字段1,{sku_id2,order_count2,order_amount2,sku_num2}>,其他用户视角字段2
where date_format(dt, 'yyyy-MM') = date_format('2020-06-25', 'yyyy-MM')
group by user_id, order_detail_struct.sku_id
)二
tm_id category1_id category1_name
求字段 品牌id 一级品类id 一级品类名称
--join一下就可以了
tmp_cate as (
select id,
tm_id,
category1_id,
category1_name
from dwd_dim_sku_info
where dt='2020-06-14'
)三
分析字段两次以上购买人数 count(buycount > 2)
单次复购率 count(buycount>2)/buycount
三次以上购买人数 count(buycount>3)
多次复购率 count(buycount>3)/buycount
统计月份 date_format stat_mn
统计日期 stat_date
select tmp_order.user_id user_id,
tmp_cate.tm_id tm_id,
tmp_cate.category1_id category1_id,
tmp_cate.category1_name category1_name,
sum(tmp_order.order_count) buycount--一个用户可能购买该品牌下多个商品
from tmp_order
join tmp_cate
on tmp_order.sku_id=tmp_cate.id
group by user_id,tm_id,category1_id,category1_name;
--可能对这个四个分组有点懵,题目是品牌的月复购率,但是按照建表语句其实是品牌的一级品类的复购率\
--这个题目的难点就在这里,所以你在用户group_by的时候需要按照先按照品牌进行分类,然后需要按照一级品类进行分类
--由此计算,用户购买该商品品牌的一级品类的商品的下单次数,计算复购率
--简单的join还是不能得到复购率.这样这样按照分组字段进行tm_id计算,可以选择二次select四
归并分析
with tmp_order as (
select user_id,
order_detail_struct.sku_id sku_id, --这个商品的id
sum(order_detail_struct.order_count) order_count --这个商品的购买总数
from dws_user_action_daycount --每日行为统计
lateral view explode(order_detail_stats) tmp as order_detail_struct
-- 这个地方要注意,order_detail_stats里面存储的一个array,里面存储的是每一个笔订单的细节
-- 每个商品的sku_id,包含订单数,花费,数量
where date_format(dt, 'yyyy-MM') = date_format('2020-06-25', 'yyyy-MM')
group by user_id, order_detail_struct.sku_id
),
tmp_cate as (
select id,
tm_id,
category1_id,
category1_name
from dwd_dim_sku_info
where dt='2020-06-14'
)
select
t1.tm_id,
t1.category1_id,
t1.category1_name,
sum(if(buycount>=1,1,0)) buycount,--一级品类下的购买人数
sum(if(buycount>=2,1,0)) buyTwiceLast,--单次复购人数
sum(if(buycount>=2,1,0))/sum( if(buycount>=1,1,0)) buyTwiceLastRatio,
sum(if(buycount>=3,1,0)) buy3timeLast,--三次复购人数
sum(if(buycount>=3,1,0))/sum( if(buycount>=1,1,0)) buy3timeLastRatio ,--三次复购率
date_format('2020-06-14' ,'yyyy-MM') stat_mn,
'2020-06-14' stat_date
from (
select tmp_order.user_id user_id,
tmp_cate.tm_id tm_id,
tmp_cate.category1_id category1_id,
tmp_cate.category1_name category1_name,
sum(tmp_order.order_count) buycount--一个用户可能购买该品牌下多个商品
from tmp_order
join tmp_cate
on tmp_order.sku_id = tmp_cate.id
group by user_id, tm_id, category1_id, category1_name
--可能对这个四个分组有点懵,题目是品牌的月复购率,但是按照建表语句其实是品牌的一级品类的复购率\
--这个题目的难点就在这里,所以你在用户group_by的时候需要按照先按照品牌进行分类,然后需要按照一级品类进行分类
--由此计算,用户购买该商品品牌的一级品类的商品的下单次数,计算复购率
) t1
group by category1_id, category1_name,tm_id;4. 商品主题
4.1 商品个数信息
4.1.1 建表语句
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';分析字段 ads_product_info
主要是分析当天的SKU与SPU的个数
这个不用说,使用商品主题宽表,group_by一下,count就可以了,很简单,小燃
4.1.2 拉取SQL
with tmp_sku as
(
select
'2020-06-25' dt,
count(*) sku_num
from dwt_sku_topic
),
tmp_spu as
(select
'2020-06-25' dt,
count(*) spu_num--还是要稍微注意下
from (
select
spu_id
from dwt_sku_topic
group by spu_id
)t1)
select
tmp_sku.dt,
tmp_sku.sku_num,
tmp_spu.spu_num
from
tmp_sku
join tmp_spu on tmp_sku.dt=tmp_spu.dt;4.1 商品销量排名
4.1.1 直接建表
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';4.1.2 拉取sql
