SQL销售数据分析

Orange

分析工具：Navicat Premium（客户端工具） MySQL（服务端数据库）

数据分为两个表：orderinfo(订单信息)和userinfo(用户信息)

oderinfo一共539414条数据，包含5个字段，分别是：

orderId：订单Id
userId：用户Id，与userinfo表的userId对应
isPaid：支付状态
price：消费金额
paidTime：消费时间

userinfo一共101536条数据，包含3个字段，分别是：

userId ：用户Id，与 oderinfo 表的userId对应
sex：用户性别
birth：用户生日

需要解决的问题有：

1、统计不同月份的下单人数

2、统计用户三月份的回购率和复购率

3、统计男女用户三月份的消费频次是否有差异

4、统计多次消费的用户，第一次和最后一次消费间隔了多久？

5、统计不同年龄段，用户的消费频次是否有差异？

6、统计消费的二八法则，消费的top 20%用户，贡献了多少额度？

接下来，让我们逐一解决上面的问题。

第一题：统计不同月份的下单人数

SELECT month(paidTime),count(DISTINCT userId) FROM orderinfo 
WHERE isPaid = '已支付'
GROUP BY month(paidTime)

代码剖析：

month(paidTime)：因为数据里面只有一个年度的，所以这里用month不受影响，如果数据量比较多，有多个年份的，就需要将年份加上，这是就需要这样用了date_format(paidTime,'%Y-%m')

count(DISTINCT userId)：userId中存在多次下单的用户，所以统计人数的时候需要用DISTINCT 函数进行去重操作。

结果说明：第五月的下单人数只有6人，这个应该是脏数据，实际工作中应该清除。

第二题：统计用户三月份的回购率和复购率

首先来看一下回购率和复购率的概念

回购率：指上次购买后在下一个统计周期里继续购买的占比，即三月份购买了四月依旧购买的人数占比。
复购率：指在这个月份中，所有的消费人数中有多少个是消费了一次以上的比率，即重复购买的人数占比。

复购率：

第一步：通过GROUP BY和WHERE函数算出3月份用户消费的次数。

SELECT userId,count(userId) FROM orderinfo 
WHERE isPaid = '已支付' 
AND MONTH(paidTime) = 3
GROUP BY userId

第二步：算出消费次数大于1的用户的占比

SELECT count(co),
count(if(co > 1,1,NULL)) as ct,
count(if(co > 1,1,NULL)) / count(co) as per 
FROM (
	SELECT userId,count(userId) as co FROM orderinfo 
	WHERE isPaid = '已支付' 
	AND MONTH(paidTime) = 3
	GROUP BY userId) as t

代码剖析：

将第一步的查询结果作为子查询，注意：作为子查询，后面需要添加一个别名，否则无法执行

count(if(co > 1,1,NULL))：求出消费次数大于1的人数。NULL不参与计数

因此，用户三月份的复购率为30.8%。

回购率：

第一步：求出用户在哪些月份中有消费。

SELECT userId,DATE_FORMAT(paidTime,'%Y-%m-01') as ym FROM orderinfo
WHERE isPaid = '已支付'
GROUP BY userId,DATE_FORMAT(paidTime,'%Y-%m-01')

结果说明：userId为1的用户只在3月份有消费，而userId为5的用户在三月和四月都有消费。

第二步：通过left join 将第一步求出的表联结起来

SELECT * FROM (
	SELECT userId,DATE_FORMAT(paidTime,'%Y-%m-01') as ym FROM orderinfo
	WHERE isPaid = '已支付'
	GROUP BY userId,DATE_FORMAT(paidTime,'%Y-%m-01')) t1
LEFT JOIN (
	SELECT userId,DATE_FORMAT(paidTime,'%Y-%m-01') as ym FROM orderinfo
	WHERE isPaid = '已支付'
	GROUP BY userId,DATE_FORMAT(paidTime,'%Y-%m-01')) t2
on t1.userId = t2.userId

结果说明：通过将第一步的表联结，上图中红框数据就是我们想要的。

第三步：添加判断，筛选出类似上图红框的数据。

SELECT t1.ym,
count(t1.ym),
count(t2.ym), 
count(t2.ym)/count(t1.ym) 
FROM (
	SELECT userId,DATE_FORMAT(paidTime,'%Y-%m-01') as ym FROM orderinfo
	WHERE isPaid = '已支付'
	GROUP BY userId,DATE_FORMAT(paidTime,'%Y-%m-01')) t1
LEFT JOIN (
	SELECT userId,DATE_FORMAT(paidTime,'%Y-%m-01') as ym FROM orderinfo
	WHERE isPaid = '已支付'
	GROUP BY userId,DATE_FORMAT(paidTime,'%Y-%m-01')) t2
on t1.userId = t2.userId 
AND t1.ym = DATE_SUB(t2.ym,INTERVAL 1 MONTH)
GROUP BY t1.ym

代码剖析： t1.ym = DATE_SUB(t2.ym,INTERVAL 1 MONTH)：t1的月份等于t2的月份减去一个月，通过这个条件，筛选出哪些用户有回购。然后通过count计算出总消费人数和回购的人数，进而求出回购率。

结果说明：上图红框中即为三月的回购率，四、五月份因为脏数据导致计算不准确，实际业务中需要删除或者修改脏数据。

第三题：统计男女用户三月份的消费频次是否有差异

第一步：通过join 将orderinfo和userinfo联结起来，同时删除性别为空的行

SELECT * FROM orderinfo o
JOIN (
	SELECT * FROM userinfo
	WHERE sex != '' ) as u
on o.userId = u.userId

第二步：运用GROUP BY，统计出带上性别的userId消费次数。

SELECT o.userId,sex,count(1) FROM orderinfo o
JOIN (
	SELECT * FROM userinfo
	WHERE sex != '' ) as u
on o.userId = u.userId
GROUP BY userId,sex

代码说明：

o.userId：join后，有两个表中都含有userId字段，所以这里需要指明是哪个userId

第三步：将第二步求出的表作为子查询，筛选出男女消费的频次

SELECT sex,avg(ct)FROM (
	SELECT o.userId,sex,count(1) as ct  FROM orderinfo o
	JOIN (
		SELECT * FROM userinfo
		WHERE sex != '' ) as u
	on o.userId = u.userId
	GROUP BY userId,sex) as t
GROUP BY sex

结果说明：频次即平均值，通过计算，可以看出，男女消费频次几乎一样高。

第四题：统计多次消费的用户，第一次和最后一次消费间隔了多久？

SELECT avg(diff) FROM(
	SELECT userId,
	max(paidTime),
	min(paidTime),
	DATEDIFF(max(paidTime),min(paidTime)) as diff
	FROM orderinfo
	WHERE isPaid = '已支付'
	GROUP BY userId 
	HAVING count(1)> 1) as t

代码剖析：

子查询是求出消费两次以上用户的最后一次消费时间和第一次消费时间，以及这两次的间隔时间。

DATEDIFF(max(paidTime),min(paidTime))：以天为单位计算时间差。

结果说明：最后以平均值作为用户的生命周期，有些不妥当，这里只是作为SQL的一个练习。

第五题：统计不同年龄段，用户的消费频次是否有差异？

第一步：用birth计算出用户的年龄，然后通过CASE WHEN 函数将年龄分段。

SELECT userId,year(now()) - year(birth),
WHEN year(now()) - year(birth) < 10 THEN 0 
WHEN year(now()) - year(birth) < 20 THEN 1 
WHEN year(now()) - year(birth) < 30 THEN 2 
WHEN year(now()) - year(birth) < 40 THEN 3
WHEN year(now()) - year(birth) < 50 THEN 4
WHEN year(now()) - year(birth) < 60 THEN 5 
WHEN year(now()) - year(birth) < 70 THEN 6
WHEN year(now()) - year(birth) < 80 THEN 7 
WHEN year(now()) - year(birth) < 90 THEN 8
ELSE 9
FROM userinfo
WHERE birth > '1901-00-00'

结果说明：

0阶段：表示0~9岁

1阶段：表示10~19岁依次类推 9阶段表示90岁（含）以上的

第二步：用join 通过userId将orderinfo和userinfo关联起来，并计算出用户的消费次数。

SELECT o.userId,age,count(o.userId) as ct FROM orderinfo as o 
JOIN (
	SELECT userId,year(now()) - year(birth),
	WHEN year(now()) - year(birth) < 10 THEN 0 
	WHEN year(now()) - year(birth) < 20 THEN 1 
	WHEN year(now()) - year(birth) < 30 THEN 2 
	WHEN year(now()) - year(birth) < 40 THEN 3
	WHEN year(now()) - year(birth) < 50 THEN 4
	WHEN year(now()) - year(birth) < 60 THEN 5 
	WHEN year(now()) - year(birth) < 70 THEN 6
	WHEN year(now()) - year(birth) < 80 THEN 7 
	WHEN year(now()) - year(birth) < 90 THEN 8
	ELSE 9
	END as age
	FROM userinfo
	WHERE birth > '1901-00-00') as t
ON o.userId = t.userId
WHERE isPaid = '已支付'
GROUP BY o.userId,age

第三步：计算出按年龄段分组后的消费频次

SELECT age,avg(ct) FROM (
	SELECT o.userId,age,count(o.userId) as ct FROM orderinfo as o 
	JOIN (
		SELECT userId,year(now()) - year(birth),
		WHEN year(now()) - year(birth) < 10 THEN 0 
		WHEN year(now()) - year(birth) < 20 THEN 1 
		WHEN year(now()) - year(birth) < 30 THEN 2 
		WHEN year(now()) - year(birth) < 40 THEN 3
		WHEN year(now()) - year(birth) < 50 THEN 4
		WHEN year(now()) - year(birth) < 60 THEN 5 
		WHEN year(now()) - year(birth) < 70 THEN 6
		WHEN year(now()) - year(birth) < 80 THEN 7 
		WHEN year(now()) - year(birth) < 90 THEN 8
		ELSE 9
		END as age
		FROM userinfo
		WHERE birth > '1901-00-00') as t
	ON o.userId = t.userId
	WHERE isPaid = '已支付'
	GROUP BY o.userId,age) as t1
GROUP BY age

结果说明：

90岁以上的消费频次为88，这个有些不合理，应该为脏数据。

从分析结果可以看出：30~49岁之间的用户是主流消费群体，50岁以上就逐渐下降了。

第六题：统计消费的二八法则，消费的top 20%用户，贡献了多少额度？

第一步：统计出每个用户消费的金额。

SELECT userId,sum(price) as total FROM orderinfo as o 
WHERE isPaid = '已支付'
GROUP BY userId
ORDER BY total desc

结果说明：userId为11211的用户消费了2.3亿，有可能是脏数据。

第二步：计算出20%的用户量以及所有用户的消费金额

SELECT count(userId)*0.2,sum(total) FROM(
	SELECT userId,sum(price) as total FROM orderinfo as o 
	WHERE isPaid = '已支付'
	GROUP BY userId
	ORDER BY total desc) as t

第三步：统计第二步求出的20%用户的消费总金额。

SELECT count(userId),sum(total) FROM(