强推良心公众号：猴子数据分析
资料来源：
如何分析复购用户？
人均付费如何分析？
今日要点：
- timestampdiff（返回的时间格式，起始时间，结束时间）
- case when ...then...else...end
- count（case ……）
- count（distinct）
image.png
select a.购买时间,
count(distinct a.用户id) 当日首次购买用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) <=1
then a.用户id else null end ) as 此月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =3
then a.用户id else null end ) as 第三月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =4
then a.用户id else null end ) as 第四月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =5
then a.用户id else null end ) as 第五月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =20
then a.用户id else null end ) as 第二十月复购用户数
from 课程订单表 as a
left join 课程订单表 as b
on a.`用户id` = b.`用户id`
where a.课程类型=2 and a.购买时间！=b.购买时间
group by a.购买时间;
timestampdiff与timediff的区别，前者可以返回时分秒的差，后者只能返回相差的天数
image.png
1、各地用户数（以后看到这个题目就要想到去重呀）
select 城市,count(distinct 用户id),sum(ARPU值)
from 各城市用户ARPU值
group by 城市
2、各城市各分段用户数是多少
select
count（distinct case when ARPU值>0 and ARPU值<30 then 1 else null end ）as '(0-30)'
count（distinct case when ARPU值>=30 and ARPU值<50 then 1 else null end ）as '[30-50)'
count（distinct case when ARPU值>=50 and ARPU值<80 then 1 else null end ）as '[50-80)'
count（distinct case when ARPU值>=80 then 1 else null end ）as '[80以上)'
from 各城市用户ARPU值
group by 城市
3、找出表2中重复的用户数
select 用户id
from 用户套餐费用表
group by 用户id
having count(用户id)>2
4.mysql不支持全连接，left+union+right
image.png
image.png
一句SQL取出所有用户对商品的行为特征，特征分为已购买、购买未收藏、收藏未购买、收藏且购买（输出结果如下表）
image.png
select o.user_id,o.item_id,
(case when o.pay_time is not null then 1 else null end) as 已购
(case when o.pay_time is not null and f.fav_time is null then 1 else null end）as 购买未收藏
(CASE when o.pay_time is null and f.fav_time is not null then 1 else 0 end) as 收藏未购买,
(CASE when o.pay_time is not null and f.fav_time is not null then 1 else 0 end) as 收藏且购买
from orders o
left join favourites f
on o.user_id = f.user_id and o.item_id = f.item_id
UNION
SELECT
f.user_id,f.item_id,
(CASE when o.pay_time is not null then 1 else 0 end) as '已购买',
(CASE when o.pay_time is not null and f.fav_time is null then 1 else 0 end) as '购买未收藏',
(CASE when o.pay_time is null and f.fav_time is not null then 1 else 0 end) as '收藏未购买',
(CASE when o.pay_time is not null and f.fav_time is not null then 1 else 0 end) as '收藏且购买'
FROM orders o
RIGHT JOIN favorites f
ON o.user_id = f.user_id
AND o.item_id = f.item_id
ORDER BY user_id, item_id;
