mysql 留存率_用mysql统计留存率

分析中留存是个非常重要的指标，很多数据分析平台都提供留存数据的web端展现服务

那么，如何基于一个用户行为表，用mysql得到留存数据呢？

基于一个日常的用户行为表(比如登陆，测量等)，用mysql直接求得用户的留存情况

这是我一直想做的一件事，之前一直没有好的方案，直到这次成功实践了一次。

我的分析按照月留存进行，掌握原理后，你可以轻松的改为日留存或周留存。

以下是总体思路的一个思维导图：

基础只要一张明细的测量表，记录用户每天的测量情况，包含用户sn测量值和测量时间等 table bak

总体的思路就是：

要得到一张最终的核心表，留存分析的sql代码，可以直接基于这个表运行

tmp_pressure_mea_detail，这个表包含所有需要分析的月份情况，比如2017年1月到2017年12月，用户数是1万，这个表就会有12万行数据.

month ,terminal_sn ,mea_cnt, first_mea_month ,diff_month_first_mea_month

月份，用户sn(用户的唯一标识),该月份的测量次数，diff_month_first_mea_month(当前月份是该用户开始测量月份后的第几个月，)

2017-07, sn001, 13 , 2017-01 ,7

就是指，2017年7月份，用户sn001的测量次数是13次，他开始测量的月份是2017-01，这个月份是他开始测量后的第7个月

当然过程中，会有一些小问题，都需要一个个解决，因为要得到这个最终的核心表，先需要创建几个其他的过程表，特别是t3 cross join的应用

以下代码表示：

每个月的月份，开始测量的用户数，第二个月的留存数，留存率

其他月份，只需要修改diff_month_first_mea_month 的值即可

select first_mea_month ,count(distinct(TERMINAL_SN)) as mea_pat_cnt

, sum(if(diff_month_first_mea_month = 2 and mea_cnt >0,1,0)) as 2ed_retenion_cnt

, concat(round( (sum(if(diff_month_first_mea_month = 2 and mea_cnt >0,1,0)) / count(distinct(TERMINAL_SN))) * 100,0),'%') as retent

from

( select *

from analysis.tmp_pressure_mea_detail

where diff_month_first_mea_month > 0

and mea_cnt is not null -- 等同于mea_cnt > 0

order by terminal_sn ,month

) aa

group by first_mea_month

完整的代码之前一直是免费的，碰到太多连个赞都不点的伸手党，

所以完整的sql代码，5元有偿分享了。