快捷導(dǎo)航

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

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

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

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

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

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

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

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

（2）其次我們使用窗口排名函數(shù)row_number對(duì)同一用戶的不同登錄時(shí)間進(jìn)行排名，得到新一列為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ù)計(jì)算登錄時(shí)間login_time一列加上rk天之后生成新的一列sub_date。假如在表格中，同一用戶的sub_date相同則說(shuō)明相同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）之后我們對(duì)得到的查詢結(jié)果，按照用戶id，登錄時(shí)間進(jìn)行分組計(jì)數(shù)，得到的計(jì)數(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

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

您可能感興趣的文章: