SQL查询练习(销售订单分析：复购率、回购率…)

SQL查询练习

创建两个数据表

--创建订单明细表
CREATE TABLE ORDERINFO  ( 
    ORDERID     varchar(10) NULL,--订单ID，主键
    USERID      varchar(10) NULL,--用户ID，可以和用户表进行关联
    ISPAID      varchar(10) NULL,--是否支付
    PRINCE      decimal(5,0) NULL,--订单价格
    PAIDTIME   date NULL --订单支付时间
    )

--创建用户信息表
CREATE TABLE USERINFO  ( 
    USERID  varchar(10) NULL,--用户ID，主键
    SEX     varchar(10) NULL,--性别
    BIRTH   varchar(10) NULL --出生日期
    )

DELETE FROM ORDERINFO --清除订单明细表数据

数据准备

分析目标

• 1-统计不同月份的下单人数
• 2-统计用户三月份的回购率和复购率
• 3-统计男女的消费频次是否有差异
• 4-统计多次消费的用户，第一次和最后一次消费时间的间隔
• 5-统计不同年龄段的用户消费金额是否有差异
• 6-统计消费的二八法则，消费的top20%用户，贡献了多少额度

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

--查询各月份支付成功单数
select CAST(YEAR(PAIDTIME) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(PAIDTIME) AS VARCHAR(2)), 2) AS 年月 ,COUNT(USERID) as 单数
from  [窗口函数].[dbo].[ORDERINFO] 
where ISPAID = '是'
group by  CAST(YEAR(PAIDTIME) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(PAIDTIME) AS VARCHAR(2)), 2)

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

复购率

--下面是对最后一步的分解，最终代码在第三步

--统计三月支付成功的用户的数量
select  USERID 用户ID  ,count(USERID) 订单数量   from ORDERINFO
where ISPAID = '是' and MONTH(PAIDTIME)=3
group by USERID

--统计订单大于一次的用户列表
select 
t.USERID 用户id,
 case when t.USERID_count>1 then 1 else null end 是否符合条件
from 
(
select  USERID  ,count(USERID)  USERID_count from ORDERINFO
where ISPAID = '是' and MONTH(PAIDTIME)=3
group by USERID
 )T


 --复购率是在本月消费中多少人消费一次以上的占比
select count(tt.USERID) '三月购买的总用户数',count(tt.USERID_count) '多次购买用户数',FORMAT(count(tt.USERID_count)*0.01/count(tt.USERID),'P') '复购率'from

(select 
t.USERID USERID,
 case when t.USERID_count>1 then 1 else null end USERID_count
from 
(
select  USERID  ,count(USERID)  USERID_count from ORDERINFO
where ISPAID = '是' and MONTH(PAIDTIME)=3
group by USERID
 ) T

)tt

复购率

--回购率是三月份购买的人数四月份依旧购买

select
 CAST(YEAR(a.PAIDTIME) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(a.PAIDTIME) AS VARCHAR(2)), 2) AS 年月,count(distinct a.USERID) '本月消费的用户数量',count(distinct b.USERID)'本月回购的用户数' 
 from
(
	select  userid,paidtime from ORDERINFO
		where ISPAID = '是'
		group by userid,paidtime) a

	left join 
	(
	select  userid,paidtime from ORDERINFO
		where ISPAID = '是'
		group by userid,paidtime) b 
		on a.userid = b.userid and month(a.paidtime) =  month(b.paidtime)-1

group by  CAST(YEAR(a.PAIDTIME) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(a.PAIDTIME) AS VARCHAR(2)), 2)

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

--先统计每个用户的消费频次在统计，在统计男女的平均消费频次


select aa.sex '性别',round(avg(aa.订单数量),2) '消费频次(订单数量/人数)'
from 

(select a.USERID,b.sex,count(a.ORDERID) '订单数量' 
	from 
	ORDERINFO a
inner join (
	select * from USERINFO where SEX is not null)b 

	on a.USERID = B.USERID

	group by a.USERID,b.sex)aa

group by aa.sex

--下方为查询分解

select a.USERID,b.sex '性别',count(a.ORDERID) '订单数量' 
	from 
	ORDERINFO a
inner join (
	select * from USERINFO where SEX is not null)b 

	on a.USERID = B.USERID

	group by a.USERID,b.sex

4-统计多次消费的用户，第一次和最后一次消费时间的间隔

--（可以大概理解为生命周期、多次消费的用户：消费频次超过2次的用户；先求每个用户的第一次和最后一次的消费时间间隔，在求平均消费间隔）
select 
	用户ID,
	消费次数, 
	最后消费日期,
	第一次消费日期,
	datediff(dd,第一次消费日期,最后消费日期) '第一次最后一次消费时间差(天)' from

	(
	select  USERID 用户ID,count(ORDERID) 消费次数,max(paidtime)  最后消费日期,min(paidtime) 第一次消费日期 from ORDERINFO

	where ISPAID = '是'

	group by USERID having count(ORDERID)>1)aa

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

--统计不同年龄段的用户消费金额是否有差异
--由用户生日计算出年龄，然后通过用户ID和订单明细表联结，
select aa.年龄段 年龄段,round(avg(aa.订单金额),2)  平均消费金额 
from
	(
	select  a.ORDERID,a.USERID,cast(a.PRINCE as float) 订单金额,
	case  when b.AGE between 10 and 19 then '10-19岁'  when b.AGE between 20 and 29 then '20-29岁' 
	when b.AGE between 30 and 39 then '30-39岁' when b.AGE between 40 and 49 then '40-49岁' when b.AGE between 50 and 59 then '50-59岁' 
	when b.AGE between 60 and 69 then '60-69岁'when b.AGE between 70 and 79 then '70-79岁'
	else null end 年龄段,	b.AGE 
	from 
		(
		select * from  ORDERINFO 
		where ISPAID = '是'
		)a
	inner join 
		(
		select USERID,year(getdate())-year(BIRTH) age 
		from USERINFO 
		where BIRTH is not null) b 
	on a.USERID = b.USERID
		)aa 
group by aa.年龄段 having aa.年龄段 is not null

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

--统计消费的二八法则，消费的top20%用户，贡献了多少额度
select sum(单用户消费金额合计) 'top20%用户贡献的总额度' from 
(
select *,row_number()over(order by 单用户消费金额合计 desc) as '排序' --开窗函数
from 
	(
	select  userid,sum(prince) 单用户消费金额合计 from  ORDERINFO 
	where ISPAID = '是' 
	group by USERID
	) aa) tt

where 
排序<= (
select  count(distinct USERID)* 0.2 from  ORDERINFO where ISPAID = '是')

数据库备份bak

原文链接

SQL练习2：用户消费行为分析(回购率和复购率) – 简书