在日常工作或者面試時(shí)，我們經(jīng)常會(huì)遇到這樣的問題，比如：“統(tǒng)計(jì)下用戶最長(zhǎng)連續(xù)登錄的天數(shù)”，“統(tǒng)計(jì)下連續(xù)登錄超過10天的用戶”，“統(tǒng)計(jì)下連續(xù)3天交易額超過100W的店鋪”。這種問題，其實(shí)都是一個(gè)套路，我們這里簡(jiǎn)單介紹一種解決思路。
用戶連續(xù)登錄天數(shù)
我們先來看下用戶連續(xù)登錄問題，我們簡(jiǎn)化下數(shù)據(jù)，只保留用戶ID和登錄時(shí)間：
其實(shí)做數(shù)據(jù)來說，我們的思路一定要清晰，知道我們要什么樣的數(shù)據(jù)，就像這個(gè)連續(xù)問題，如果我們知道怎樣來判斷連續(xù)，只要再轉(zhuǎn)化成SQL就行了。所以，我們先思考下，怎樣來判斷用戶是不是連續(xù)登錄。
連續(xù)其實(shí)就是這樣，我今天登錄了，然后昨天也登錄了，說明我就連續(xù)兩天登錄了，我們也經(jīng)常會(huì)在APP或者網(wǎng)站上遇到什么簽到領(lǐng)積分，簽到多少天領(lǐng)獎(jiǎng)品之類的，其實(shí)都是產(chǎn)品為了提高日活提高用戶留存的套路。
1.根據(jù)上次登錄日期判斷
我們回過頭，繼續(xù)看這個(gè)連續(xù)的問題，上面的數(shù)據(jù)已經(jīng)有了，如果要手工判斷用戶登錄是否連續(xù)的話，會(huì)怎樣來呢？比如，用戶今天登錄了，我們只需要用戶上一次登錄是什么時(shí)候就可以了，如果用戶昨天也登錄了，就說明用戶是連續(xù)登錄第2天了，所以，我們將數(shù)據(jù)處理一下。
現(xiàn)在我們有了用戶本次登錄日期和上次登錄日期，只要用戶本次登錄和上次登錄的間隔天數(shù)是1，就說明用戶連續(xù)登錄了，我們來看看
這樣，我們就可以看到，我們根據(jù)本次登錄時(shí)間和上次登錄時(shí)間，計(jì)算出間隔天數(shù)，間隔為1的都是連續(xù)的日期，首次登錄的時(shí)候，我們可以填入默認(rèn)值0。這樣看上去標(biāo)注黃色的都是連續(xù)的日期，但是要注意，第一個(gè)標(biāo)黃的日期表示2天。那我們?cè)撛鯓咏y(tǒng)計(jì)出用戶每次連續(xù)的天數(shù)呢？如果只把間隔天數(shù)為1的記錄拿出來，好像又沒有辦法統(tǒng)計(jì)哪些天是連續(xù)一起的，我們?cè)賹?duì)這個(gè)間隔天數(shù)做個(gè)排序
這樣，我們根據(jù)排序的序號(hào)，就可以將間隔天數(shù)為1的記錄拿出來聚合了。
下面我們就需要將上面的想法轉(zhuǎn)化成SQL：
由于我們要使用mysql，mysql中沒有開窗函數(shù)，寫起來很麻煩，需要多次使用變量；
pg版可以參考之前的文章：SQL筆試題-連續(xù)登錄天數(shù)
-- 1. 獲取間隔天數(shù)
-- 初始化變量
set @pre_login_date:=null,@pre_user_id:=null;
drop table if exists tmp_20180415_1;
create table tmp_20180415_1 as
select
user_id,
login_date,
pre_login_date,
-- 計(jì)算本次登錄和上次登錄的差值
coalesce(datediff(login_date,pre_login_date),0) as diff_days
from (
select
-- 當(dāng)前記錄
user_id, -- 當(dāng)前user_id
login_date, -- 當(dāng)前l(fā)ogin_date
-- 上一條記錄
@pre_user_id as pre_user_id,
if(@pre_user_id=user_id , @pre_login_date , null) as pre_login_date,
-- 初始化上一條記錄
@pre_login_date:=login_date as cur_login_date,
@pre_user_id:=user_id? as cur_user_id
from
tm_login_log
order by
user_id,login_date
) x
order by user_id,login_date;
-- 2.對(duì)間隔天數(shù)進(jìn)行排序
set @pre_user_id:=null,@pre_diff_days:=0,@rn:=1;
select
user_id,rn , min(pre_login_date) as from_login_date,max(login_date) as to_login_date
from (
select
user_id,
login_date,
pre_login_date,
diff_days,
if(@pre_user_id=user_id,if(@pre_diff_days=diff_days,@rn:=@rn,@rn:=@rn+1),@rn:=1) as rn,
@pre_user_id:=user_id,
@pre_diff_days:=diff_days
from tmp_20180415_1
order by user_id,login_date
) x
where diff_days=1
group by user_id,rn;
結(jié)果時(shí)這樣的，用戶ID和他連續(xù)登錄的日期，但是這個(gè)rn并沒有什么用，如果要看連續(xù)登錄天數(shù)的話，我們可以重新根據(jù)開始時(shí)間和結(jié)束時(shí)間進(jìn)行計(jì)算。繼續(xù)延伸的話，我們還可以統(tǒng)計(jì)用戶最長(zhǎng)的登錄天數(shù)。
2.根據(jù)登錄日期排序
還有一種類似的思路，就是首先根據(jù)登錄日期排序，這樣我們獲得的排序序號(hào)就是連續(xù)的，然后再統(tǒng)計(jì)每個(gè)登錄日期和一個(gè)初始日期的間隔天數(shù)，如果登錄連續(xù)的話，2個(gè)值相減之后也可以用來判斷是否連續(xù)。
感興趣的同學(xué)可以試試MySQL的實(shí)現(xiàn)。
后記
其實(shí)大家主要了解思路就行了，使用mysql來實(shí)現(xiàn)，比較麻煩，不會(huì)寫也沒問題，當(dāng)然應(yīng)該還有更簡(jiǎn)便的方法，只是我還沒有想到。在日常工作中，其實(shí)還有很多別的方式來更方便的實(shí)現(xiàn)，比如，我們可以創(chuàng)建一張用戶每天登錄的表，然后在刷新當(dāng)天數(shù)據(jù)的同時(shí)，去判斷昨天該用戶有沒有登錄，有的話則計(jì)入連續(xù)；Hive中也支持各種開窗函數(shù)，寫起來很簡(jiǎn)單。
