SQL查詢用戶連續(xù)N天登錄
業(yè)務中常見分析之一是分析用戶連續(xù)登錄使用情況,這也對應著SQL常見面試題——用戶連續(xù)N天登錄問題。
我們假設現在有一張用戶登錄信息表user_login_info,表中字段有用戶id(uid)、登錄時間(login_time)。表中數據如下所示:
現在要求查詢出連續(xù)登錄N天的用戶。
(1) 首先我們要對用戶登錄表進行去重操作,以避免用戶當天多次登錄情況對查詢結果產生影響。這里用到了distinct關鍵詞。
select uid, distinct date(login_time) as login_time from user_login_info
(2)其次我們使用窗口排名函數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
查詢結果如圖所示:
(3)之后用date_sub函數計算登錄時間login_time一列加上rk天之后生成新的一列sub_date。假如在表格中,同一用戶的sub_date相同則說明相同sub_date數據的行是連續(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
查詢結果如圖所示:
(4)之后我們對得到的查詢結果,按照用戶id,登錄時間進行分組計數,得到的計數結果就是用戶連續(xù)登錄多少天的情況記錄。
select uid, count(*) as 連續(xù)登錄天數 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)之后我們可以在此查詢結果上,根據需要用having條件就可以篩選出我們想要得知的連續(xù)N天登錄的用戶id
完整代碼如下:
select uid, count(*) as 連續(xù)登錄天數 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ù)登錄天數 = N
到此這篇關于SQL查詢用戶連續(xù)N天登錄的文章就介紹到這了,更多相關SQL查詢連續(xù)N天登錄內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
SQL Server免費版的安裝以及使用SQL Server Management Studio(SSMS)連接數據庫的
這篇文章主要介紹了SQL Server免費版的安裝以及使用SQL Server Management Studio(SSMS)連接數據庫的圖文方法,需要的朋友可以參考下2020-02-02Oracle、MySQL和SqlServe三種數據庫分頁查詢語句的區(qū)別介紹
這篇文章主要介紹了Oracle、MySQL和SqlServe三種數據庫分頁查詢語句的區(qū)別介紹 的相關資料,需要的朋友可以參考下2016-05-05