SQL查詢用戶連續(xù)N天登錄

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

業(yè)務中常見分析之一是分析用戶連續(xù)登錄使用情況,本文主要介紹了SQL查詢用戶連續(xù)N天登錄,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

業(yè)務中常見分析之一是分析用戶連續(xù)登錄使用情況，這也對應著SQL常見面試題——用戶連續(xù)N天登錄問題。

我們假設現(xiàn)在有一張用戶登錄信息表user_login_info，表中字段有用戶id（uid）、登錄時間（login_time）。表中數(shù)據(jù)如下所示：

現(xiàn)在要求查詢出連續(xù)登錄N天的用戶。

（1）首先我們要對用戶登錄表進行去重操作，以避免用戶當天多次登錄情況對查詢結(jié)果產(chǎn)生影響。這里用到了distinct關鍵詞。

select
	uid, 
	distinct date(login_time) as login_time 
from user_login_info

（2）其次我們使用窗口排名函數(shù)row_number對同一用戶的不同登錄時間進行排名，得到新一列為rk。

select
    uid, 
	login_time, 
	row_number() over(partition by uid order by login_time) as rk
from
(
	select
	    uid, 
		distinct date(login_time) as login_time 
	from user_login_info
) t1

查詢結(jié)果如圖所示：

（3）之后用date_sub函數(shù)計算登錄時間login_time一列加上rk天之后生成新的一列sub_date。假如在表格中，同一用戶的sub_date相同則說明相同sub_date數(shù)據(jù)的行是連續(xù)登錄使用的情況。

select *, DATE_SUB(login_time, INTERVAL rk DAY) as sub_date
from
(
	select
		uid, 
		login_time, 
		row_number() over(partition by uid order by login_time) as rk
    from
    (
	    select
			uid, 
			distinct date(login_time) as login_time 
		from user_login_info
	) t1
) t2

查詢結(jié)果如圖所示：

（4）之后我們對得到的查詢結(jié)果，按照用戶id，登錄時間進行分組計數(shù)，得到的計數(shù)結(jié)果就是用戶連續(xù)登錄多少天的情況記錄。

select uid, count(*) as 連續(xù)登錄天數(shù)
from
(
	SELECT *, DATE_SUB(login_time, INTERVAL rk DAY) AS sub_date
	from
	(
		select
			uid, 
			login_time, 
			row_number() over(partition by uid order by login_time) as rk
		from
		(
			select
				uid, 
				distinct date(login_time) as login_time 
			from user_login_info
		) t1
	) t2
) t3
group by uid, sub_date

（5）之后我們可以在此查詢結(jié)果上，根據(jù)需要用having條件就可以篩選出我們想要得知的連續(xù)N天登錄的用戶id

完整代碼如下：

select uid, count(*) as 連續(xù)登錄天數(shù)
from
(
	SELECT *, DATE_SUB(login_time, INTERVAL rk DAY) AS sub_date
	from
	(
		select
			uid, 
			login_time, 
			row_number() over(partition by uid order by login_time) as rk
		from
		(
			select
				uid, 
				distinct date(login_time) as login_time 
			from user_login_info
		) t1
	) t2
) t3
group by uid, sub_date
having 連續(xù)登錄天數(shù) = N

到此這篇關于SQL查詢用戶連續(xù)N天登錄的文章就介紹到這了,更多相關SQL查詢連續(xù)N天登錄內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: