对面大批量的数据来说，SQL的处理速度会比Excel快上许多。今天我将使用一次几十万行的电商购买行为数据进行练习。

一、理解数据

4b55adfa0b8977bcdd784c77f9413a89.png

4b0801622fee6285c31cc23ea9d92c17.png

二、提出问题

· 统计不同月份下单人数

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

· 统计男女消费频次是否有差异

· 统计多次消费的用户，第一次购买和最后一次购买的消费间隔是多少？

· 统计不同年龄段，用户消费金额是否有差异？

· 统计消费的二八法则，消费top20%的用户，贡献了多少额度？

三、分析问题

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

select ispaid,paidTime,count(distinct userId) 下单人数 from orderinfo
where ispaid = "已支付"
group by month(paidTime)

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

①复购率

select count(if(test>=2,1,null))/count(test) 复购率 from (
	select userId, count(1) test from orderinfo
	where ispaid = "已支付"
	and month(paidTime) = 3
	GROUP BY userId
    ) t;

②回购率

select t1.m,COUNT(t1.m),COUNT(t2.m) from (
	select userId,date_format(paidTime,'%Y-%m-01') m from orderinfo
	where ispaid = '已支付'
	group by userId,date_format(paidTime,'%Y-%m-01')
    ) t1
left join 
(
	select userId,date_format(paidTime,'%Y-%m-01') m from orderinfo
	where ispaid = '已支付'
	group by userId,date_format(paidTime,'%Y-%m-01')
    ) t2
on t1.userId = t2.userId 
AND t1.m = date_sub(t2.m,interval 1 month)
group by t1.m

3、统计男女消费频次是否有差异

select sex,avg(ct) 消费频次 from (
	select o.userId,sex,count(1) ct from orderinfo o 
	join userinfo u 
	ON o.userId = u.userId and ispaid = '已支付' and sex in ('男','女')
	group by userId,sex ) t
group by sex

9f030edd222ad4abefa2866781ca13b7.png

4、统计多次消费的用户，第一次购买和最后一次购买的消费间隔是多少？

select userId,max(paidTime),min(paidTime),
datediff(max(paidTime),min(paidTime)) 消费间隔 from orderinfo
where ispaid = '已支付'
group by userId
having count(1) >= 2

c177a6344cc9ca59c52584c593616590.png

这里的消费间隔我们可以粗略知道一个用户的生命周期是多少。

5、统计不同年龄段，用户消费金额是否有差异

select round(datediff(curdate(),birth)/356,0) 年龄,
truncate(avg(price),2) 平均消费金额
from orderinfo o
inner join userinfo u
on o.userId = u.userId and month(birth) <> 00 and ispaid = '已支付'
group by round(datediff(curdate(),birth)/356,0)

ea09cab5cf02f1ce08f528f67a735c89.png

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

这里需要分两个步骤：

①首先将将消费金额TOP20%的用户数量找出来

select ceil(count(1)* 0.2) from (
select userId,SUM(price) from orderinfo 
where ispaid = '已支付'
group by userId) t

这里得出来的结果为17310是TOP20%用户的数量

②根据以上结果使用limit就可以算出来消费总金额

select sum(su) 消费总金额 from (
select userId,SUM(price) su from orderinfo 
where ispaid = '已支付'
group by userId
order by sum(price) desc
limit 17130) t

39684a8979ad8024576a2aa275eb40be.png

根据二八法则我们可以知道这20%的用户贡献了80%的消费金额，持续关注这些用户能够让我们业务事半工倍。

Logo
永洪数据分析社区

永洪科技，致力于打造全球领先的数据技术厂商，具备从数据应用方案咨询、BI、AIGC智能分析、数字孪生、数据资产、数据治理、数据实施的端到端大数据价值服务能力。

更多推荐

cover

java计算机毕业设计教师工作量统计系统 基于SpringBoot的高校教师绩效测算与可视化平台 教师教学任务与工作量智能汇总系统

avatar 永洪数据分析社区
cover

BI是报表？BI是可视化？BI到底是什么？

avatar 永洪数据分析社区

BackstopJS 与 Prettier 集成：配置文件格式化与团队规范统一

在当今的前端开发环境中，**BackstopJS** 作为一款强大的视觉回归测试工具，已经成为保障UI一致性的重要武器。然而，随着团队规模的扩大和项目的复杂度提升，如何确保所有开发人员遵循统一的配置规范成为了一个挑战。本文将为您详细介绍如何通过 Prettier 工具实现 BackstopJS 配置文件的自动格式化，从而提升团队协作效率和代码质量。💪## 为什么需要 BackstopJS 配

avatar 永洪数据分析社区