現(xiàn)有一份表格，記錄了用戶ID及其下單時間

我們使用navicat導入數(shù)據(jù)

選擇表格文件

導入成功后，我們就可以正式開始了

首先，我們找出每個用戶的最小日期，即首次下單時間

select uid,min(date(time)) as mtime from active group by uid;

out:

接著，我們?nèi)ヒ幌轮?/p>

select DISTINCT uid,date(time) as t from active;

out:

關(guān)聯(lián)起來，由此我們可以得到用戶ID，下單時間，首次下單時間三個字段

select b.uid,b.t,a.mtime from 
(select uid,min(date(time)) as mtime from active group by uid) a,
(select DISTINCT uid,date(time) as t from active) b 
where a.uid=b.uid;

out:

然后我們可以求出1號的人在各個日期有多少人，這樣就很容易求出1號的次日留存率，3日留存率，4日留存率....
方法是按最小日期和下單時間分組

select mtime,t,count(uid) as c from 
(select b.uid,b.t,a.mtime from (select uid,min(date(time)) as mtime from active group by uid) a,(select DISTINCT uid,date(time) as t from active) b where a.uid=b.uid) d 
group by mtime,t;

out:

其實到這步我們已經(jīng)一目了然

如果時間差為1，則為次日留存人數(shù)，為2，則為三日留存人數(shù)，為6，則為七日留存人數(shù)；次日留存人數(shù)/人數(shù)，則為次日留存率

select mtime,sum(if(datediff(t,mtime)=0,c,0)) as '人數(shù)',
             sum(if(datediff(t,mtime)=1,c,0)) as '次日留存人數(shù)',
             sum(if(datediff(t,mtime)=2,c,0)) as '三日留存人數(shù)',
             sum(if(datediff(t,mtime)=6,c,0)) as '七日留存人數(shù)',
                         
             sum(if(datediff(t,mtime)=1,c,0)) /sum(if(datediff(t,mtime)=0,c,0)) as '次日留存率',
             sum(if(datediff(t,mtime)=2,c,0)) /sum(if(datediff(t,mtime)=0,c,0)) as '三日留存率',
             sum(if(datediff(t,mtime)=6,c,0)) /sum(if(datediff(t,mtime)=0,c,0)) as '七日留存率' 
                         
from (select mtime,t,count(uid) as c from (select b.uid,b.t,a.mtime from (select uid,min(date(time)) as mtime from active group by uid) a,(select DISTINCT uid,date(time) as t from active) b where a.uid=b.uid) d group by mtime,t order by mtime,t) f 
group by mtime;

out:

二、使用python

現(xiàn)有一份表，記錄了用戶ID，購買日期，購買數(shù)量，購買金額

import pandas as pd

columns=['uid','time','order_products','order_amount']  #表頭為用戶ID，購買日期，購買數(shù)量，購買金額
df=pd.read_csv('CDNOW_master.txt',names=columns,sep='\s+')

我們只要用戶ID和下單時間這兩列信息，并且去重

#我們只要用戶ID和下單時間這兩列信息，并且去重
data=df[['uid','time']].drop_duplicates()
print(data.head())

out:

改時間列數(shù)據(jù)類型為時間

data['time']=pd.to_datetime(data['time'],format='%Y%m%d')    #修改時間列數(shù)據(jù)類型為時間
print(data.head())

out:

找出每個用戶的最小購買時間，即首次購買時間

#找出每個用戶的最小購買時間，即首次購買時間
data2=data['time'].groupby(data['uid']).min()
print(data2.head())

out:

把他們關(guān)聯(lián)起來，得到用戶ID，下單時間，首次下單時間

#把他們關(guān)聯(lián)起來，得到用戶ID，下單時間，首次下單時間
data3=pd.merge(data,data2,on='uid')
print(data3.head())
data3.columns=['uid','time','mtime']   #重命名列名

out:

計算留存人數(shù)，按最小時間、下單時間分組

#計算留存人數(shù)，按最小時間、下單時間分組
data4=data3.pivot_table('uid',index=['mtime','time'],aggfunc='count')
print(data4)

out:

現(xiàn)在我們可以清晰的看到1997-01-01的次日留存人數(shù)為3，留存率為3/209，三日留存人數(shù)為3，留存率為3/209，四日留存人數(shù)為2.......

#重置索引
data5=data4.reset_index()
print(data5)

out:

如果我們想看某一日的留存率，我們可以把它篩選出來

data6=data5[data5['mtime']=='1997-01-01']
print(data6)

out: