SQL查詢用戶連續(xù)N天登錄
業(yè)務(wù)中常見分析之一是分析用戶連續(xù)登錄使用情況,這也對(duì)應(yīng)著SQL常見面試題——用戶連續(xù)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相同則說明相同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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
bak文件怎么打開 2000w數(shù)據(jù)怎么打開?
這篇文章主要介紹了sqlserver數(shù)據(jù)庫bak文件如何恢復(fù)打開的方法,需要的朋友可以參考下2014-02-02SQL Server免費(fèi)版的安裝以及使用SQL Server Management Studio(SSMS)連接數(shù)據(jù)庫的
這篇文章主要介紹了SQL Server免費(fèi)版的安裝以及使用SQL Server Management Studio(SSMS)連接數(shù)據(jù)庫的圖文方法,需要的朋友可以參考下2020-02-02SQL語句實(shí)例說明 方便學(xué)習(xí)mysql的朋友
我是在MySQL數(shù)據(jù)庫中做的測(cè)試,不同的數(shù)據(jù)庫有一定的差別方便學(xué)習(xí)mysql的朋友2012-09-09Oracle、MySQL和SqlServe三種數(shù)據(jù)庫分頁查詢語句的區(qū)別介紹
這篇文章主要介紹了Oracle、MySQL和SqlServe三種數(shù)據(jù)庫分頁查詢語句的區(qū)別介紹 的相關(guān)資料,需要的朋友可以參考下2016-05-05查看本地sql server數(shù)據(jù)庫的ip地址的方法指南
程序連線SQL數(shù)據(jù)庫,需要SQL Server實(shí)例的名稱或網(wǎng)絡(luò)地址,故本文給大家介紹了查看本地sql server數(shù)據(jù)庫的ip地址的方法指南,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-06-06Sql Server里刪除數(shù)據(jù)表中重復(fù)記錄的例子
這篇文章主要介紹了Sql Server里刪除數(shù)據(jù)表中重復(fù)記錄的例子,本文給出了3種操作方法,需要的朋友可以參考下2014-08-08將所有符合條件的結(jié)果拼接成一列并用逗號(hào)隔開的一個(gè)sql語句
把所有符合條件的結(jié)果拼接成一列,用逗號(hào)隔開的一個(gè)sql語句。2010-06-06實(shí)例學(xué)習(xí)SQL的Select命令
實(shí)例學(xué)習(xí)SQL的Select命令...2006-12-12sqlserver下將數(shù)據(jù)庫記錄的列記錄轉(zhuǎn)換成行記錄的方法
sqlserver下將數(shù)據(jù)庫記錄的列記錄轉(zhuǎn)換成行記錄的方法分享,需要的朋友可以參考下。2011-07-07