SQL計算用戶留存率問題

更新時間：2024年09月02日 11:23:49 作者：黑白交界

電商業(yè)務中需要計算用戶留存情況,在時間維度上可以分為用戶次日、多日后的留存情況,本文就來詳細的介紹一下如何計算,具有一定的參考價值,感興趣的可以了解一下

概念

電商業(yè)務中需要計算用戶留存情況，在時間維度上可以分為用戶次日、多日后的留存情況，用留存率表示。留存率計算方式如下：

次日留存率 = 當日新用戶在次日登錄的數(shù)量 / 當日新用戶注冊數(shù)量

三日留存率 = 當日新用戶在第四天登錄的數(shù)量 / 當日新用戶注冊數(shù)量

七日留存率 = 當日新用戶在第八天登錄的數(shù)量 / 當日新用戶注冊數(shù)量

其他時間的留存率計算同理。

情況一

假設現(xiàn)在有兩張表，一張是注冊信息表register_info，另一張是登錄信息表login_info，兩個表格數(shù)據(jù)如下。

現(xiàn)在分別計算出每日的新增用戶數(shù)，以及次日、三日、七日留存率。

首先，將兩張表做一個連接，用datediff函數(shù)選出用戶注冊后七日仍有登錄的數(shù)據(jù)（條件1），連接條件是用戶id相同（條件2）。查詢代碼如下：

select 
    r.uid,
	date(r.register_date) as rt,
	date(l.login_time) as lt,
	datediff(l.login_time, r.register_date) as tdiff
from register_info r
left join login_info l 
on r.uid=l.uid
and date(l.login_time) between date(r.register_date)+interval 1 day and date(r.register_date)+interval 7 day		
order by uid asc

查詢結(jié)果如下：（這里每個用戶的注冊時間只有一個，所以會得出每個用戶所有注冊登錄時間的排列組合便于后續(xù)計算）

之后利用case條件判斷和count函數(shù)計算出各種留存率情況。代碼如下：

select 
	rt as 日期,
	count(distinct uid) as 新增用戶數(shù),
	count(distinct case when tdiff=1 then uid end)/count(distinct uid) as 次日留存率,
	count(distinct case when tdiff=3 then uid end)/count(distinct uid) as 三日留存率,
	count(distinct case when tdiff=7 then uid end)/count(distinct uid) as 七日留存率
from 
(
	select 
		r.uid,
		date(r.register_date) as rt,
		date(l.login_time) as lt,
		datediff(l.login_time, r.register_date) as tdiff
	from register_info r
	left join login_info l 
	on r.uid=l.uid
	and date(l.login_time) between date(r.register_date)+interval 1 day and date(r.register_date)+interval 7 day		
	order by uid asc
) t1
group by rt

得到結(jié)果如下：

情況二

假如現(xiàn)在只有一張用戶登錄信息表user_login_info，其中默認每個用戶最早的登錄時間就是注冊時間，表格數(shù)據(jù)如下：

所以我們需要先用兩個子查詢得出類似（1）中的注冊表t1和登錄表t2，之后操作與（1）中相同，代碼如下：

select
	count(distinct uid) 新增用戶數(shù),
    count(distinct case when tdiff=1 then uid end)/count(distinct uid) as 次日留存率,
    count(distinct case when tdiff=3 then uid end)/count(distinct uid) as 三日留存率,
    count(distinct case when tdiff=7 then uid end)/count(distinct uid) as 七日留存率
from
(
	select t1.uid, t1.rt, t2.lt, datediff(t2.lt,t1.rt) as tdiff
	from
	(
		(select user_id as uid, min(date(login_time)) as rt from user_login_info group by user_id) t1
		LEFT JOIN
		(select user_id as uid, date(login_time) as lt from user_login_info) t2
		on t1.uid=t2.uid and t2.lt between t1.rt + interval 1 day and t1.rt + interval 7 day
	)
) t3
group by rt
order by rt asc

其中t3就是情況一中得到的t1.

查詢結(jié)果如下：

到此這篇關于SQL計算用戶留存率問題的文章就介紹到這了,更多相關SQL 用戶留存率內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

Mysql計算n日留存率的實現(xiàn)

ASP.NET下向SQLServer2008導入文件實例操作方法
在Microsoft SQL Server2008以后的版本中，將刪除image數(shù)據(jù)類型。在新的開發(fā)工作中將不適用此數(shù)據(jù)類型，并打算修改當前使用此數(shù)據(jù)類型的應用程序，改用varbinary(max)數(shù)據(jù)類型。
2010-09-09
sqlserver獲取當前日期的最大時間值
如果你有看到這篇，會看到Insus.NET在SQL Server2008使用最簡單的方法取到午夜時間值。
2011-09-09
SQL中Exists的用法
這篇文章主要介紹了SQL中Exists的用法,非常不錯，具有參考借鑒價值，需要的朋友可以參考下
2017-02-02
sqlserver 三種分頁方式性能比較[圖文]
第二種分頁方法和第一種分頁方法效率差不多，但第二種可用于老版本的sqlserver甚至access，最后一種別用
2011-08-08
SqlServer AS的用法
本篇文章簡要分析，在SQL SERVER中，AS的基本用法,需要的朋友可以參考下
2019-11-11
SQL Server 數(shù)據(jù)庫的設計詳解
這篇文章主要為大家介紹了SQLServer數(shù)據(jù)庫的設計，具有一定的參考價值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來幫助
2022-01-01
sql腳本查詢數(shù)據(jù)庫表,數(shù)據(jù),結(jié)構(gòu),約束等操作的方法
本文介紹了“sql腳本查詢數(shù)據(jù)庫表,數(shù)據(jù),結(jié)構(gòu),約束等操作的方法”，需要的朋友可以參考一下
2013-03-03
SQL Server中選出指定范圍行的SQL語句代碼
SQL Server中選出指定范圍行的SQL語句代碼寫法實例
2008-07-07
SQL?Server設置多個端口號的操作步驟
SQL?Server使用的默認端口號是TCP端口1433,這是為了連接到?Microsoft?SQL?Server?實例的標準網(wǎng)絡端口,如果你正在設置?SQL?Server?或者嘗試從其他應用程序連接到它,所以本文給大家介紹了SQL?Server如何設置多個端口號,需要的朋友可以參考下
2024-07-07
SqlServer 表連接教程(問題解析)
這篇文章主要介紹了SqlServer 表連接問題及解決方法，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下
2019-11-11