MYSQL之实际应用

留存

• 留存是什么
  留存用户量，指在注册的新用户，一段时间后再次使用的用户数。
  0101这天，新用户数：1000
  0102这天，昨天的新用户再次使用的人数：900（次留：900）
  0108这天，0101那批新用户中，再次使用的人数：500（七日留：500）

  留存率：就是 一段时间后再次使用的用户数 / 注册的新用户数
  次留率 = 次留量/新用户量（900/1000 = 90%）

• 为什么关注留存¹
  一个产品如何确保用户数量的增长？一、有新用户；二、流失用户少；三、用户走了但又回来。
  这不难理解，，就类似于储水
  

  用户留存就是用来衡量用户流失和复苏的，提升用户的留存一般也就要分析这两个方面：减少新用户的流失和提升老用户的复苏率。

• 如何做呢?
  留存一般代表了用户对于产品的喜爱和认可度。对于新用户，产品要能让用户快速了解产品，找到产品价值，持续使用；对于老用户，我们可以分析常用的老用户的行为，分析出产品哪些部分可以优化，从而让老用户更加认可产品。理论分析如此，但是不同的产品是不一样的，如何进行分析找出产品的核心价值就是这重中之重。如何确定产品核心价值，何时找准时机进行推广，可以去看《增长黑客》。

• SQL算留存：
  1.自连接：
  Create view temp_user_cmp as
  Select a.user_id,a.dates as cur_dates,b.dates as after_dates
  from temp_user_act a left join user_act b
  on a.user_id = b.user_id
  Where b.dates > a.dates or b.dates = a.dates
  2.计算留存人数
  SELECT cur_dates AS dates,
  COUNT(DISTINCT IF( DATEDIFF( after_dates, cur_dates )= 0, user_id, NULL )) AS device_v,
  COUNT(DISTINCT IF( DATEDIFF( after_dates, cur_dates )= 1, user_id, NULL )) AS day1_count,
  COUNT(DISTINCT IF( DATEDIFF( after_dates, cur_dates )= 2, user_id, NULL )) AS day2_count,
  COUNT(DISTINCT IF( DATEDIFF( after_dates, cur_dates )= 3, user_id, NULL )) AS day3_count,
  COUNT(DISTINCT IF( DATEDIFF( after_dates, cur_dates )= 7, user_id, NULL )) AS day8_count
  FROM temp_user_cmp
  GROUP BY cur_dates;

环比与同比

1. 理解环比和同比的概念，首先要先确定一个时间维度

• 比如选择年，比较月份
  环比：今年两个相邻月份的比
  比如 （2020年6月销售额-2020年5月销售额）/2020年5月销售额
  同比：今年和去年同一个月的比
  比如 （2020年6月销售额-2019年6月销售额）/2019年6月销售额

• 比如选择年，比较周
  环比：今年两个相邻周的比
  比如 （2020年第二周销售额-2020年第一周销售额）/2020年第一周销售额
  同比：今年和去年同一个周的比
  比如 （2020年第二周销售额-2019年第二周销售额）/2019年第二周销售额

• 比如选择月，比较周
  环比：这个月两个相邻周的比
  比如 （2020年6月第四周 销售额-2020年6月第三周销售额）/2020年6月第三周销售额
  同比：这个月和上个月同一个周的比
  比如 （2020年6月第四周 销售额-2020年5月第四周 销售额）/2020年5月第四周销售额

• 如果选择周，还比较周，环比=同比

2. 环比和同比有什么意义？

• 环比
  一般来说，环比是这个周期较上一个周期的变化情况，可以观察出一个连续的增长走势，比如销售额一年的连续变动情况，但是本身就有淡旺季来说，销售额的环比无法区分。
• 同比
  同比可以反应不同年份同期的对比，可以忽略这种淡旺季影响，对比上一年的一个增长情况。

连续打卡思路

• mysql 中变量定义： @变量名 := 值
• 连续打卡的思路：
  求user_id= 1 ，这个人最大的连更天数？
  user_date表

user_id	dates	pre_dates	变量@row
1	20200101		0
1	20200102	20200101	1
1	20200104	20200102	0
1	20200105	20200104	1
1	20200106	20200105	2
1	20200107	20200106	3
1	20200109	20200107	0
1	20200113	20200109	0
1	20200115	20200113	0
1	20200120	20200115	0
1	20200121	20200120	1
1	20200122	20200121	2

1. 首先使用lead函数将作者下一次更新日期提出来
   create temp_view as
   select user_id , dates,lag(dates ,1,’’) over (partition by user_id order by dates) as pre_dates from user_date;
2. 然后加入连续更新天数变量，计算，，如果两次更新差1天，就说明是连续更新，连续更新天数变量就加1；否则就置为0；
   select user_id ,dates,pre_dates, if(DATEDIFF(dates,predates) == 1,@row:=@row+1,@row:=0) from temp_view ;

---

1. https://www.zhihu.com/question/295297125/answer/776788110 ↩︎