关于用户sql留存率计算

login为登录表，users为用户表, 查询库为clickhouse, 相关函数自己转换为mysql的就可以了，大表查询意义不大，建议使用离线计算结果保存

SELECT
    regtime,
    sum(diff0) AS diff0,
    sum(diff1) AS diff1,
    sum(diff3) AS diff3,
    sum(diff7) AS diff7,
    sum(diff8) AS diff8,
    sum(diff14) AS diff14,
    sum(diff30) AS diff30
FROM
(
    SELECT DISTINCT
        (regtime, uid, diff14, diff30),
        regtime,
        diff0,
        diff1,
        diff3,
        diff7,
        diff8,
        diff14,
        diff30
    FROM
    (
        SELECT
            a.uid,
            b.regtime AS regtime,
            DATEDIFF('day', b.regtime, a.datetime) AS diff,
            if(diff = 0, 1, 0) AS diff0,
            if(diff = 1, 1, 0) AS diff1,
            if(diff = 3, 1, 0) AS diff3,
            if(diff = 7, 1, 0) AS diff7,
            if(diff = 8, 1, 0) AS diff8,
            if((diff > 8) AND (diff < 14), 1, 0) AS diff14,
            if((diff > 14) AND (diff < 30), 1, 0) AS diff30
        FROM login AS a
        ,
        (
            SELECT
                toDate(t.addtime) AS regtime,
                toInt32(t.userId) AS new_user_guid
            FROM users AS t
            WHERE (toDate(t.addtime) >= (toDate(1525745778) - 30)) AND (toDate(t.addtime) <= toDate(1525745778))
        ) AS b
        WHERE (a.uid = b.new_user_guid) AND (a.datetime >= regtime) AND (a.datetime <= (regtime + 30))
    )
) AS logdiffs
GROUP BY regtime
ORDER BY regtime ASC