強推良心公眾號：猴子數(shù)據(jù)分析
資料來源：
如何分析?復(fù)購用戶？
人均付費如何分析？
今日要點：
- timestampdiff（返回的時間格式，起始時間，結(jié)束時間）
- case when ...then...else...end
- count（case ……）
- count（distinct）
image.png
select a.購買時間,
count(distinct a.用戶id) 當(dāng)日首次購買用戶數(shù),
count(distinct case when timestampdiff(month,a.購買時間,b.購買時間) <=1
then a.用戶id else null end ) as 此月復(fù)購用戶數(shù),
count(distinct case when timestampdiff(month,a.購買時間,b.購買時間) =3
then a.用戶id else null end ) as 第三月復(fù)購用戶數(shù),
count(distinct case when timestampdiff(month,a.購買時間,b.購買時間) =4
then a.用戶id else null end ) as 第四月復(fù)購用戶數(shù),
count(distinct case when timestampdiff(month,a.購買時間,b.購買時間) =5
then a.用戶id else null end ) as 第五月復(fù)購用戶數(shù),
count(distinct case when timestampdiff(month,a.購買時間,b.購買時間) =20
then a.用戶id else null end ) as 第二十月復(fù)購用戶數(shù)
from 課程訂單表 as a
left join 課程訂單表 as b
on a.`用戶id` = b.`用戶id`
where a.課程類型=2 and a.購買時間！=b.購買時間
group by a.購買時間;
timestampdiff與timediff的區(qū)別，前者可以返回時分秒的差，后者只能返回相差的天數(shù)
image.png
1、各地用戶數(shù)（以后看到這個題目就要想到去重呀）
select 城市,count(distinct 用戶id),sum(ARPU值)
from 各城市用戶ARPU值
group by 城市
2、各城市各分段用戶數(shù)是多少
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中重復(fù)的用戶數(shù)
select 用戶id
from 用戶套餐費用表
group by 用戶id
having count(用戶id)>2
4.mysql不支持全連接，left+union+right
image.png
image.png
一句SQL取出所有用戶對商品的行為特征，特征分為已購買、購買未收藏、收藏未購買、收藏且購買（輸出結(jié)果如下表）
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;
