计算留存率的SQL（梯形视图）

代码及说明

使用with..as..语句计算留存率，可以提高计算的复用率，A1,A2两个表完全可以相同，分开是因为可以设置不同的活跃和留存事件，否则可以进一步简化。还有用户的统计口径，这里用client_id，可以替换为user_id以及client_id+user_id。

A1,A2取出时间范围内每个用户的活跃天数，B1计算出A1中各个用户在哪几天产生留存（retention）事件。C1再根据日期和留存天数算出留存用户数。其实大部分场景下到这里就可以结束了，剩下的计算可以由后续的数据可视化工具来完成。但是因为我用的QuickBI没有这么灵活的操作，于是C2从C1提取0日数据做为日活，直接在模型中计算出留存率。

BI上用这个表结构，把dt放在列，把day_of_retention作为行，再把需要的指标放进去即可实现梯形视图下的留存率表格。

WITH
    A1 AS (
       -- 初始行为
        SELECT dt,
               client_id
        FROM ods_user_behavior_fixed
        WHERE dt >= '${report_date_ph.get(0)}'
        AND dt <= '${report_date_ph.get(1)}'
        AND client_id != ''
        AND event = 'display'
        GROUP BY dt, client_id
    ),
    A2 AS (
    -- 后续行为
        SELECT dt,
               client_id
        FROM ods_user_behavior_fixed
        WHERE dt >= '${report_date_ph.get(0)}'
        AND dt <= '${report_date_ph.get(1)}'
        AND client_id != ''
        AND event = 'display'
        GROUP BY dt, client_id
    ),
    B1 AS (
       -- 时间段内每个用户的留存天数
        SELECT A1.client_id,
               A1.dt,
               DATEDIFF(TO_DATE(A2.dt, 'yyyymmdd'), TO_DATE(A1.dt, 'yyyymmdd'), 'dd') AS day_of_retention
        FROM  A1 INNER JOIN  A2 ON A1.client_id = A2.client_id AND A1.dt <= A2.dt
    ),
    C1 AS (
       -- 每个初始日期，每个留存日的留存人数
        SELECT dt,
               day_of_retention,
               COUNT(distinct client_id) AS cnt
        FROM B1
        GROUP BY dt, day_of_retention
    ),
    C2 AS (
       -- 0日留存等于日活
        SELECT *
        FROM C1
        WHERE day_of_retention = 0
    )
SELECT C1.dt,
       C1.day_of_retention,
       C2.cnt AS dau,
       C1.cnt AS retentions,
       C1.cnt / C2.cnt AS retention_rate
FROM C1
LEFT JOIN C2
    ON C1.dt = C2.dt
ORDER BY C1.dt, C1.day_of_retention
LIMIT 10000;

TODO

关于梯形视图，梯形视图是因为这个表格通常用来看最近的n天数据，但是时间越靠近今天缺失的数据就越多。比如T-1日，他一定不会有次日留存，T-2日一定不会有三日留存，所以产生了梯形。可是如果这个时间再整体往前推n天，那么所有的日期都应该有n日留存，那就可以不是梯形了，但是这个例子还是会输出梯形，你可以通过修改A2的dt范围条件来实现这个更好的体验。