总结了几个使用sql语言对电商业务的问题的提数思路。因为网上公开的数据难以获取,这里是取自公开出版书籍中的随书资源,过程在自己的workbench里进行实操,重在展示解决问题的语句的思路。
mysql 5.7.17
1.计算用户的次日、3日、7日留存情况
现在有一张用户登录表user_login,这张表记录了每个用户每次登录的时间,包含uid(用户ID)、login_time(登录时间)两个字段。我们想看用户的次日留存数、三日留存数、七日留存数(只要用户首次登录以后再登录就算留存下来了)
select
sum(case when 间隔日>=7 then 1 else 0 end )as '7日留存',
sum(case when 3<=间隔日<7 then 1 else 0 end )as '3日留存',
sum(case when 1<=间隔日<3 then 1 else 0 end )as '次日留存'
from(
select t1.uid,datediff(last_login,first) as '间隔日'
from
(select
uid
,date(min(login_time)) as 'first_login'
from
duibi.user_login
group by
uid)t1
left join
(select
uid
,date(max(login_time)) as 'last_login'
from
duibi.user_login
group by
uid)t2
on t1.uid=t2.uid)t3
2.求累计和
现在有一张2019年一整年的订单表consum_order_table,这张表包含order_id(订单ID)、uid(用户ID)和amount(订单金额)三个字段,现在我们想看下80%的订单金额最少是由多少用户贡献的
因为这个版本的不支持over()函数,所以代码繁琐了一点
select sum(case when rate<0.8 then 1 else 0 end )as count_id
from
(select t3.upto_amount/(select sum(consum_order_table.amount) from consum_order_table)as rate
from
(select t1.uid,sum(user_amount2) as upto_amount
from
(select uid,sum(amount) as user_amount1
from duibi.consum_order_table
group by uid )as t1
join
(select uid,sum(amount) as user_amount2
from duibi.consum_order_table
group by uid )as t2
on t1.uid<=t2.uid
group by t1.uid
order by upto_amount desc) as t3)as t4
3.获取某段时间的新增用户数
现在有一张用户表user_reg_table,这张表包含uid(用户ID)、reg_time(注册时间)两个字段,我们想获取某一天的新增用户数,以及该天对应的过去7天内每天的平均新增用户数
select
count(if ('2019-12-29'=date(reg_time),uid,null) )as '某天新增用户数',
count(if(0<datediff('2019-12-29',date(reg_time))<=6,1,0)) /7 as '近7天平均新增用户数'
from duibi.`user_reg_table`
4.获取用户首次购买时间
现在有一张first_order_table表,这张表包含order_id(订单ID)、uid(用户ID)和order_time(订单时间)三个字段,想获取每个用户的首次购买时间,以及首次购买时间是否在最近7天内
select
uid,t1.首次购买时间,
if(0<datediff('2020-01-06',首次购买时间) <=6,'是', '否') as '是否在最近7天内'
from(
select
uid,min(order_time) as '首次购买时间'
from duibi.first_order_table
group by uid)as t1
5.同时获取用户和订单数据
使用的是用户表user_reg_table和first_order_table表,现在想获取过去7天每天的新增用户数、订单数、下单用户数
这题我的解法不是很好,也不是同时查询出需要的三个数据,此题解法待优化
6.获取沉默用户数
用user_reg_table和first_order_table,获取沉默用户数,已注册但最近30天内没有购买记录。
select count(uid)
from duibi.`user_reg_table`
where uid not in(
select distinct uid
from duibi.first_order_table
where datediff('2020-02-09',date(order_time))<=30)
7.获取新用户的订单数
用user_reg_table和first_order_table。获取最近7天注册的新用户在最近7天内的订单数是多少
select count(order_id)
from
(select uid
from duibi.`user_reg_table`
where datediff('2020-01-07',date(reg_time))<=7 )as t1
join
(select uid,order_id
from duibi.first_order_table
where datediff('2020-01-07',date(order_time))<=7 )as t2
on t1.uid=t2.uid