SQL实战训练

前言

本文数据来源于《七周成为数据分析师》，可在以下网盘链接获取本次分析数据：
链接：https://pan.baidu.com/s/14CvQiDa6blA2v1L5Xdu5hA
提取码：bmzp

数据格式浏览：
表order_info_utf包括了以下四个指标：
orderId：订单id，userId：用户id，price：购买价格，paidTime：支付时间

表user_info_utf包括了以下三个指标：
userId：用户id，sex：用户性别，birth：用户出生日期

一、用户消费趋势分析

1.每月消费总金额

select date_format(paidtime,'%Y-%m') as month,sum(price) 
from order_info_utf
where ispaid='已支付'
group by date_format(paidtime,'%Y-%m');

2.每月消费次数

select date_format(paidtime,'%Y-%m')as month,count(*) from order_info_utf
where ispaid='已支付'
group by date_format(paidtime,'%Y-%m');

3.每月下单人数

select date_format(paidtime,'%Y-%m')as month,count(DISTINCT userid)as number from order_info_utf
where ispaid='已支付'
group by date_format(paidtime,'%Y-%m');

二、用户消费特征分析

1.用户首次购买记录

用户首次购买(付费)有特别的意义，尤其对于可免费体验的产品。
首次付费模式（什么情景下有人付费）可反推至其他未付费的用户，以辅助运营决策，有利于激活用户付费。

select t1.* from order_info_utf t1
join 
(select userid,min(paidtime)as paidtime from order_info_utf
where ispaid='已支付'
group by userid) t2
on t1.userid=t2.userid and t1.paidtime=t2.paidtime 
group by userid
order by t1.userid;

2.用户消费时间段

可以根据顾客消费高峰期进行相应的推荐

select hour(paidtime),count(*)from order_info_utf
where ispaid='已支付'
group by hour(paidtime) 
order by count(*) desc;

3.男女消费频次

SELECT sex,avg(ct) FROM
(SELECT o.userId,sex,count(1) as ct FROM order_info_utf o 
inner join 
(SELECT * FROM user_info_utf where sex <> '') u 
on o.userId = u.userId
GROUP BY o.userId,sex) t
GROUP BY sex;

4.不同年龄段不同性别的用户消费金额

select sex,
sum(case when age_group='0-20' then avg_p else 0 end) as '0-20',
sum(case when age_group='20-40' then avg_p else 0 end) as '20-40',
sum(case when age_group='40-60' then avg_p else 0 end) as '40-60'