用SQL進(jìn)行用戶留存率計(jì)算

今天我們來分享一個常見案例，用SQL來計(jì)算用戶留存率。

目錄：

• 1. 案例數(shù)據(jù)

• 2. 思路分析

• 3. 完整代碼

這里我們一共兩份日志數(shù)據(jù)，分別是用戶賬號創(chuàng)建的日志以及用戶登錄的日志。

賬號創(chuàng)建日志

用戶登錄日志

所謂留存，就是指某日創(chuàng)建的賬號在后續(xù)自然日登錄的比例，比如3月1日新增賬號創(chuàng)建數(shù)為100，在3月2日這部分用戶登錄數(shù)為51，那么3月1日新增用戶的次日留存率為51/100=51%。

注意：我這里用的MYSQL環(huán)境

基于上述的理解，我們大概就有了以下思路：

• 考慮到用戶每天登錄的次數(shù)不一定只有一次，為了方面后續(xù)的數(shù)據(jù)處理，可以先對登錄數(shù)據(jù)按照日期和用戶id進(jìn)行去重DISTINCT處理

SELECT?DISTINCT
?STR_TO_DATE(?$part_date,?'%Y-%m-%d'?)?login_date,
?role_id?
FROM
?role_login

• 為了計(jì)算某條登錄日志是該用戶創(chuàng)建賬號后的第幾天登錄，我們可以用用戶登錄日志和賬號創(chuàng)建日志進(jìn)行inner join（這里考慮到不在統(tǒng)計(jì)周期內(nèi)的創(chuàng)建賬號的用戶數(shù)據(jù)也會記錄在用戶登錄日志里，所以去掉）

SELECT
?login_log.role_id,
?create_date,
?login_date
FROM
?((
??SELECT?DISTINCT
???STR_TO_DATE(?$part_date,?'%Y-%m-%d'?)?login_date,
???role_id?
??FROM
???role_login?
??)?login_log
?INNER?JOIN?(?SELECT?DISTINCT?STR_TO_DATE(?$part_date,?'%Y-%m-%d'?)?create_date,?role_id?FROM?role_create?)?create_log?ON?(?login_log.role_id?=?create_log.role_id?))

• 然后用登錄日期字段和創(chuàng)建賬戶字段進(jìn)行差值DATEDIFF獲取第幾天登錄

SELECT
?login_log.role_id,
?create_date,
?DATEDIFF(?login_date,?create_date?)?day_diff?
FROM
...

• 對于第0天登錄的數(shù)據(jù)則可以理解為新增用戶數(shù)，第N（≥1)天登錄的數(shù)據(jù)則為這批新增用戶后續(xù)有登錄的用戶數(shù)

SELECT
?create_date?
,?count((CASE?WHEN?(day_diff?=?0)?THEN?role_id?END))?新增用戶數(shù)
,?count((CASE?WHEN?(day_diff?=?1)?THEN?role_id?END))?次日留存
,?count((CASE?WHEN?(day_diff?=?2)?THEN?role_id?END))?3日留存
,?count((CASE?WHEN?(day_diff?=?7)?THEN?role_id?END))?7日留存
FROM
temp_1
GROUP?BY
?create_date

• 用第N天登錄的數(shù)據(jù) / 新增用戶數(shù) ?就是對應(yīng)第N天留存率

SELECT
??create_date
,?新增用戶數(shù)
,?concat(CAST(ROUND((100?*?次日留存)?/?新增用戶數(shù),2)?AS?char),?'%')?次日留存率
,?concat(CAST(ROUND((100?*?3日留存)?/?新增用戶數(shù),2)?AS?char),?'%')?3日留存率
,?concat(CAST(ROUND((100?*?7日留存)?/?新增用戶數(shù),2)?AS?char),?'%')?7日留存率
FROM
??(
???SELECT
?????create_date
???,?count((CASE?WHEN?(day_diff?=?0)?THEN?role_id?END))?新增用戶數(shù)
???,?count((CASE?WHEN?(day_diff?=?1)?THEN?role_id?END))?次日留存
???,?count((CASE?WHEN?(day_diff?=?2)?THEN?role_id?END))?3日留存
???,?count((CASE?WHEN?(day_diff?=?7)?THEN?role_id?END))?7日留存
???FROM
?????(
??????SELECT
????????login_log.role_id
??????,?create_date
??????,?DATEDIFF(login_date,?create_date)?day_diff
??????FROM
????????((
?????????SELECT?DISTINCT
???????????STR_TO_DATE($part_date,?'%Y-%m-%d')?login_date
?????????,?role_id
?????????FROM
???????????role_login
??????)??login_log
??????INNER?JOIN?(
?????????SELECT?DISTINCT
???????????STR_TO_DATE($part_date,?'%Y-%m-%d')?create_date
?????????,?role_id
?????????FROM
???????????role_create
??????)??create_log?ON?(login_log.role_id?=?create_log.role_id))
???)??temp_1
???GROUP?BY?create_date
)??temp_2
ORDER?BY?create_date?ASC