转载自https://www.cnblogs.com/hider/p/12642452.html
https://blog.csdn.net/SeizeeveryDay/article/details/105424385
第一部分:SQL查询
一、活动运营数据分析
表1:订单表 orders,大概字段有(user_id‘用户编号’, order_pay‘订单金额’ , order_time‘下单时间’)
表2:活动报名表act_apply,大概字段有(act_id‘活动编号’, user_id‘报名用户’,act_time‘报名时间’)
需求:
1、统计每个活动对应所有用户在报名后产生的总订单金额,总订单数。(每个用户限报一个活动,题干默认用户报名后产生的订单均为参加活动的订单)。
2、统计每个活动从开始后到当天(考试日)平均每天产生的订单数,活动开始时间定义为最早有用户报名的时间。(涉及到时间的数据类型均为:datetime)。
解答:
-- 1.需求一
select b.act_id,
count(a.order_time) as num_order,
sum(a.order_pay) as sum_order
from
(
select user_id,
order_pay,
order_time
from orders
) a
inner join
(
select user_id,
act_id,
act_time
from act_apply
) b
on a.user_id = b.user_id
where a.order_time >= b.act_time
group by b.act_id;
说明:一定要审清题意,题中说的是orders表“大概”有user_id,order_pay,order_time三个字段,所以一定要使用嵌套子查询。同时需要注意的是 统计每个活动的用户在“”报名后“产生的总订单数、订单金额,所有这块涉及到了 时间比较 订单时间要晚于活动报名时间。
-- 2.需求二
select a.act_id,
count(order_time)/datediff(now(), a.begin_time)
from
(
select act_id,
user_id,
act_time,
min(act_time) over (partition by act_id) as begin_time
from act_apply
) a
inner join
(
select user_id,
order_time
from orders
) b
on a.user_id = b.user_id
where a.act_time between a.begin_time and now()
and b.order_time >= a.act_time -- 补充添加:订单支付时间 ≥ 活动报名时间
group by a.act_id;
说明:一个活动下会有多个用户报名,所以要使用窗口函数。Over函数可以和其它聚集函数、分析函数搭配,起到不同的作用。例如这里的SUM,max,min,avg,还有诸如Rank,Dense_rank等。(mysql支持,hive也支持)
二、用户行为分析
表1:用户行为表tracking_log,大概字段有(user_id‘用户编号’,opr_id‘操作编号’,log_time‘操作时间’)
需求:
1、计算每天的访客数和他们的平均操作次数。
2、统计每天符合以下条件的用户数:A操作之后是B操作,AB操作必须相邻。
-- 3.需求三
select date(log_time),
count(distinct user_id) as user_cnt,
avg(num_cnt) as avg_cnt
from
(
select date(log_time),
user_id,
count(opr_id) as num_cnt
from tracking_log
group by date(log_time),
user_id
)
group by date(log_time);
在第一问的基础上添加了限制:A操作之后是B操作,AB操作必须相邻。
lead()over()可以轻易的实现!
-- 4.需求四 用户、操作、时间
select date(log_time),
count(distinct user_id)
from
(
select user_id,
date(log_time),
opr_id,
lead(opr_id, 1) over (partition by user_id order by log_time) as opr_id_2
from tracking_log
)
where opr_id = 'A'
and opr_id_2 = 'B'
group by date(log_time)
-- lag 滞后 让数据向后移动
-- lead 超前 让数据向前移动
三、用户新增留存分析
表1:用户登陆表user_log,大概字段有(user_id‘用户编号’,log_time‘登陆时间’)
需求:
每天新增用户数,以及他们第2天、30天的回访比例。
如何定义新增用户:用户登陆表中最早的登陆时间所在的用户数为当天新增用户数;
第2天回访用户数:第一天登陆的用户中,第二天依旧登陆的用户;--次日留存率
第30天的回访用户数:第一天登陆用户中,第30天依旧登陆的用户;
-- 5.需求五 用户、登录时间
select date(a.user_begin),
count(distinct a.user_id) as '新增用户',
count(distinct b.user_id) as '第2日留存用户',
count(distinct c.user_id) as '第30日留存用户'
from
(
select user_id,
min(log_time) as user_begin
from user_log
group by user_id
) a
left join
(
select user_id,
log_time
from user_log
) b
on a.user_id = b.user_id
and date(b.log_time) = date(a.user_begin) + 1
left join
(
select user_id,
log_time
from user_log
) c
on a.user_id = c.user_id
and date(c.log_time) = date(a.user_begin) + 29
group by date(a.user_begin);
此处的 date
日期函数有待商榷,日常oracle中使用 trunc()
函数即可截取,hive中使用to_date()
亦可。
第二部分:计算器1道
贝叶斯公式的应用
已知A,B厂生产的产品的次品率分别是1%和2%,现在由A,B产品分别占60%、40%的样品中随机抽一件,若取到的是次品,求此次品是B厂生产的概率。
解答:
已知:P(A)=0.6,P(B)=0.4,P(次/A)=0.01,P(次/B)=0.02
求:P(B/次)
第三部分:综合分析题1道
ABtest
某网站优化了商品详情页,现在新旧两个版本同时运行,新版页面覆盖了10%的用户,旧版覆盖90%的用户。现在需要了解,新版页面是否能够提高商品详情页到支付页的转化率,并决定是否要覆盖旧版,你能为决策提供哪些信息,需要收集哪些指标,给出统计方法及过程。
解答:
使用A/B测试模型,分析两个版本在一段时间期限内,详情页面到支付页面的转化率变化,并计算转化率变化后引起的的GMV变化。
可选择的决策:①确定发布新版本;②调整分流比例继续测试;③优化迭代方案重新开发。
要统计的指标:期限内新、旧版本商品详情页到支付页转化率 ,支付金额。
要衡量的指标:转化率变化 t 在是可接受的置信区间内是否显著,同时参考收益提升率。
指标计算方法:转化率=从某详情页到支付页用户数/浏览该商品详情页用户数(取日平均和标准差)
支付金额=从某详情页到支付页到支付成功路径用户的本次支付金额(取日平均)
采用决策①的情况:本次页面改进在显著性水平内,证明了‘转化率提升的假设’。并且收益提升率达到预期水平。
采用决策②的情况:本次页面改进在显著性水平内,无法证明‘转化率提升的假设’。分析原因可能是新版本样本空间不足。
采用决策③的情况:本次页面改进在显著性水平内,证明了‘转化率提升的假设’。但是收益提升率没有达到预期水平。
使用AARR模型分析。