Mysql查询用户留存/留存率问题--用户n日(内)留存、某日新增用户n日(内)留存

Mysql查询用户留存/留存率语法

• 计算某日的客户在第n日再次出现的概率--用户n日留存率。
• 计算某日的客户在某个时间段内再次出现的概率--用户n日内留存率。
• 计算某日新增的用户在第n日再次出现的概率--新用户n日留存率。
• 计算某日新增的用户在某个时间段内再次出现的概率--新用户n日内留存率。

1.用户n日留存/留存率

计算某日的客户在第n日再次出现的概率--用户n日留存率。

• 表名:订单总表;字段:客户编号,下单时间

-- 1.用户n日留存/留存率
-- 用户次日、3日、7日、30日,...留存/留存率

select *,
 concat(round(100*次日留存用户/日活跃用户数,2),'%')  次日留存率,
 concat(round(100*三日留存用户/日活跃用户数,2),'%')  三日留存率,
 concat(round(100*七日留存用户/日活跃用户数,2),'%')  七日留存率
from (
 select 
  date(a.下单时间) as 日期,
  count(distinct a.客户编号) as 日活跃用户数,
  count(distinct b.客户编号) as 次日留存用户,
  count(distinct c.客户编号) as 三日留存用户,
  count(distinct d.客户编号) as 七日留存用户
 from 订单总表 a 
  left join 订单总表 b 
   on a.客户编号 = b.客户编号 
   and date(b.下单时间) = date(a.下单时间) + 1
  left join 订单总表 c 
   on a.客户编号 = c.客户编号 
   and date(c.下单时间) = date(a.下单时间) + 3
  left join 订单总表 d 
   on a.客户编号 = d.客户编号 
   and date(d.下单时间) = date(a.下单时间) + 7
 where date(a.下单时间) between "2020/09/01" and "2020/09/05" 
 group by date(a.下单时间)
) p;

2.用户n日内留存/留存率

计算某日的客户在某个时间段内再次出现的概率--用户n日内留存率。

-- 2.用户n日内留存/留存率
-- 用户次日内、3日内、7日内、30日内,...留存/留存率

select *,
 concat(round(100*次日内留存用户/日活跃用户数,2),'%')  次日内留存率,
 concat(round(100*三日内留存用户/日活跃用户数,2),'%')  三日内留存率,
 concat(round(100*七日内留存用户/日活跃用户数,2),'%')  七日内留存率
from (
 select 
  date(a.下单时间) as 日期,
  count(distinct a.客户编号) as 日活跃用户数,
  count(distinct b.客户编号) as 次日内留存用户,
  count(distinct c.客户编号) as 三日内留存用户,
  count(distinct d.客户编号) as 七日内留存用户
 from 订单总表 a 
  left join 订单总表 b 
   on a.客户编号 = b.客户编号 
   and date(b.下单时间) = date(a.下单时间) + 1
  left join 订单总表 c 
   on a.客户编号 = c.客户编号 
   and (date(c.下单时间) BETWEEN date(a.下单时间) + 1 AND date(a.下单时间) + 3)
  left join 订单总表 d 
   on a.客户编号 = d.客户编号 
   and (date(d.下单时间) BETWEEN date(a.下单时间) + 1 AND date(a.下单时间) + 7)
 where date(a.下单时间) between "2020/09/01" and "2020/09/05" 
 group by date(a.下单时间)
) p;

3.新增用户n日留存/留存率

计算某日新增的用户在第n日再次出现的概率--新用户n日留存率。

## 3.某日新增用户留存/留存率
## 计算某日新增的用户在次日、3日、7日的留存率--新用户n日留存

select *,
 concat(round(100*次日留存用户/日新增用户数,2),'%')  次日留存率,
 concat(round(100*三日留存用户/日新增用户数,2),'%')  三日留存率,
 concat(round(100*七日留存用户/日新增用户数,2),'%')  七日留存率
from (
 select 
  date(a.下单时间) as 新增日期,
  count(distinct a.客户编号) as 日新增用户数,
  count(distinct b.客户编号) as 次日留存用户,
  count(distinct c.客户编号) as 三日留存用户,
  count(distinct d.客户编号) as 七日留存用户
 from 
 (
  select  
   date(t1.下单时间) as 下单时间,
   t1.客户编号 
  from 订单总表 as t1
   left join 订单总表 as t2
   on t1.客户编号=t2.客户编号
   and t2.下单时间<t1.下单时间
  where 
   (date(t1.下单时间) between "2020/09/01" and "2020/09/05") 
  and t2.下单时间 is Null  
 ) as a 
  left join 订单总表 b 
   on a.客户编号 = b.客户编号 
   and date(b.下单时间) = date(a.下单时间) + 1
  left join 订单总表 c 
   on a.客户编号 = c.客户编号 
   and date(c.下单时间) = date(a.下单时间) + 3
  left join 订单总表 d 
   on a.客户编号 = d.客户编号 
   and date(d.下单时间) = date(a.下单时间) + 7
 group by date(a.下单时间)
) p;

4.新增用户n日内留存率

计算某日新增的用户在次日内、3日内、7日内的留存率--新用户n日内留存率。

## 4.新增用户n日内留存率
## 计算某日新增的用户在次日内、3日内、7日内的留存率--新用户n日内留存率。
select *,
 concat(round(100*次日留存用户/日新增用户数,2),'%')  次日留存率,
 concat(round(100*三日留存用户/日新增用户数,2),'%')  三日留存率,
 concat(round(100*七日留存用户/日新增用户数,2),'%')  七日留存率
from (
 select 
  date(a.下单时间) as 新增日期,
  count(distinct a.客户编号) as 日新增用户数,
  count(distinct b.客户编号) as 次日留存用户,
  count(distinct c.客户编号) as 三日留存用户,
  count(distinct d.客户编号) as 七日留存用户
 from 
 (
  select  
   date(t1.下单时间) as 下单时间,
   t1.客户编号 
  from 订单总表 as t1
   left join 订单总表 as t2
   on t1.客户编号=t2.客户编号
   and t2.下单时间<t1.下单时间
  where 
   (date(t1.下单时间) between "2020/09/01" and "2020/09/05") 
  and t2.下单时间 is Null  
 ) as a 
  left join 订单总表 b 
   on a.客户编号 = b.客户编号 
   and date(b.下单时间) = date(a.下单时间) + 1
  left join 订单总表 c 
   on a.客户编号 = c.客户编号 
   and (date(c.下单时间) BETWEEN date(a.下单时间) + 1 AND date(a.下单时间) + 3)
  left join 订单总表 d 
   on a.客户编号 = d.客户编号 
   and (date(d.下单时间) BETWEEN date(a.下单时间) + 1 AND date(a.下单时间) + 7)
 group by date(a.下单时间)
) p;