几种留存计算

留存计算

项目中遇到留存计算,主要为n日留存和n日内留存。计算该指标需要用到未来n天(内)的数据。不同的计算方案,计算复杂度不同。

n日留存

指标定义:当日用户在未来第n日的留存。

指标计算:(当日活跃的用户且在第n日还活跃的用户数)/当日的活跃用户数

计算思路:如果采用大宽表计算,则需要重刷n天内的数据。如果采用竖表方式,就避免重刷n日的数据。

demo表:

result_table(
    `dt` varchar(8)    COMMENT '统计日期' ,
    `num` bigint(11)    COMMENT '留存数量' ,
    `flag` varchar(2)    COMMENT '日期标识' 
)COMMENT '结果表'

temp_table(
    `dt` varchar(8)    COMMENT '统计日期' ,
    `mid` int(10)    COMMENT '当日活跃用户id' 
)COMMENT '取数表'

参考sql

-- 统计n天前的活跃用户在n天还活跃的数量
select
    {cur_dt - n} AS dt,
    count(*) AS num,
    {n} AS flag
from
    (
    -- n日天前活跃的mid
    select
    mid
    from
    temp_table i
    where dt = {cur_dt-n})t1
inner join
    (
    -- 当日活跃mid
    select
    mid
    from
    temp_table i
    where dt = {cur_dt})t2
on t1.mid = t2.mid

参考链接:一种计算用户留存的方法

n日内留存

指标定义:当日用户在未来近n日内的还活跃的用户数。

指标计算:(当日活跃的用户且在近n日还活跃的用户数)/当日的活跃用户数

计算思路:该指标由于是未来n日内还活跃的用户，因此需要重刷历史n日内的数据，无法像n日那样通过竖表来避免重刷历史数据。计算该指标利用笛卡尔积,将n日内的数据和自身inner join

with base_table as(
select
    dt,
    mid
from
    temp_table
where dt between {cur_dt-n} and {cur_dt}
)
-- 对n天内的数据做笛卡尔积,通过t1.dt<=t2.dt来获取未来n天内还活跃的用户
select
    dt,
    count(distinct if(date_diff between 1 and n, mid, null)) AS nday_num
from
    (select
        t1.mid,
        t1.dt,
        datediff(
      DATE_FORMAT(FROM_UNIXTIME(unix_timestamp(t2.dt, 'yyyyMMdd')), 'yyyy-MM-dd'),
      DATE_FORMAT(FROM_UNIXTIME(unix_timestamp(t1.dt, 'yyyyMMdd')), 'yyyy-MM-dd')
      ) AS date_diff
    from
        base_table t1
    inner join
        base_table t2
    on t1.mid = t2.mid
    and t1.dt <= t2.dt
    )r
group by dt