这6道sql题都很好，建议都过一遍；
考察知识点：
- 回购率、复购率的理解
- 子查询
- inner join
- 重点推荐第2题，第5题，第6题
- 理解需求、理解题意 （）
- datediff
- ceil 函数
- row_number() 、 子查询内容
- 二八定律的应用
– lulu_Course
附上源码：
-- lulu_Course
-- 1.统计不同月份的下单人数
select month(paidTime),count(distinct userid) from data.orderinfo
where isPaid = "未支付"
group by month(paidTime)
-- 2.统计用户三月份的回购率和复购率
/* 名词解释： 复购率：在这个月里面，所有的消费人数中有多少个是消费一次以上人数的占比； 回购率：上月购买的人数，在下一个月依旧购买； */
-- 复购率：
select count(ct)，count(if(ct>1,1,null))
from(select userid,count(userid) as ct
from order_info
where isPaid = "已支付"
and month(paidTime) = 3
group by userid)t
-- 回购率：涉及跨月份
# 法1：代码适合一次性需求
select count(1) from
where userid in (子查询，算出3月份的userid)
and month(paidTime) = 4
group by userid;
# 法2：
-- step1:
select * from(
select userid,date_format(paidTime,"%Y-%m-01") as m
from order_info where ispaid = "已支付"
group by userid,date_format(paidTime,"%Y-%m-01"))t1
left join(
select userid,date_format(paidTime,"%Y-%m-01") as m
from order_info 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)
-- where t1.m = date_sub(t2.m,interval 1 month)亦可
-- step2:
select t1.m,count(t1.m) as 购买人数,count(t2.m) as 回购人数 from(
select userid,date_format(paidTime,"%Y-%m-01") as m
from order_info where ispaid = "已支付"
group by userid,date_format(paidTime,"%Y-%m-01"))t1
left join(
select userid,date_format(paidTime,"%Y-%m-01") as m
from order_info 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)
-- where t1.m = date_sub(t2.m,interval 1 month)
group by t1.m
-- 3.统计男女用户的消费频次是否有差异
/* 理解：求消费频次？总计、求平均 (当然篇平均数未必是靠谱的，这只是一个其中的分析思路吧) */
select sex,avg(ct) from(
select t1.userid,sex,count(1) as ct from order_info t1
inner info(
select * from user_info
where sex <> "" )t2
on o.userid = t.userid
group by userid,sex)t3
group by sex；
-- 4.统计多次消费的用户，第一次和最后一次消费间隔是多少？
-- 操作1
select userid
,max(paidTime)
,min(paidTime)
from order_info
where ispaid = '已支付'
group by userid
having count(1)>1;
-- 操作2：（lulu：勉强估计一下生命周期）
select avg(interval) as avg_interval
from(select userid
,max(paidTime)
,min(paidTime)
,datediff(max(paidTime),min(paidTime)) as interval
from order_info
where ispaid = '已支付'
group by userid
having count(1)>1
) tepmt;
-- 5.统计不同年龄段，用户的消费金额是否有差异
-- 计算每个用户的消费频次
select age,avg(ct)
from（select o.userid
,age
,count(o.userid) as ct
from order_info o where ispaid = '已支付'
inner join(
select userid,ceil((year(now())-year(birth))/10) as age
from userinfo
where birth > '1901-00-00')t -- 过滤掉117
on o.userid = t.userid
group by o.userid,age）t2
group by age；
-- 补充知识：ceil()函数和floor()函数
-- ceil(n) 取大于等于数值n的最小整数；
-- floor(n) 取小于等于数值n的最小整数；
-- 6.统计消费的二八法则，消费的top20%用户，贡献了多少额度
-- 方法1：取巧做法，见lulu
-- 方法2：row_number/子查询方法
select sum(total) as 'top20%贡献额度'
from(select userid
,sum(price) as total
,row_number()over(order by sum(price) desc) as 排名
from order_info o where ispaid = '已支付'
group by userid
) t
where 排名 < (select count(1) from order_info where ispaid = '已支付' group by userid) * 0.2;
-- 取巧做法：select count(userid)*0.2 得到 17000m-- row_number()/ 注意临时表不能复用
本文地址：https://blog.csdn.net/weixin_44976611/article/details/112571526