如何計算使用者連續登入天數？

編輯：才哥

彭友們好，我是老彭。在網際網路場景中，經常要計算使用者連續登陸日期，這是一個重要的标簽。營運的同學經常要用這個标簽篩選人群，對活躍的、沉睡的人群制定不同的營運政策。

今天分享兩種方式：SQL和Python，搞定連續登陸時長的統計。

SQL方式實作

1. 資料預覽

這裡我們用到的是

使用者登入

的日志，其中使用者每天可能存在多次登入。

使用者登入日志

使用者登入

以上案例資料可以在“大資料架構師”公衆号背景回複

，自行擷取。

2. 思路分析

用SQL來進行本次的操作，大緻分為以下幾步：

資料去重

SELECT DISTINCT
 role_id,
 $part_date date 
FROM
 role_login

進行使用者id分組并按照日期進行排序（擷取排序序号， 視窗函數 ）

SELECT
 role_id,
 date,
 row_number() OVER ( PARTITION BY role_id ORDER BY date ASC ) sort 
FROM
 ( SELECT DISTINCT 
   role_id
   , $part_date date 
   FROM 
   role_login ) temp_1

再用登入日期和排序序号進行內插補點計算（ DATE_SUB ），并按照用id和內插補點進行分組計數（這就是使用者的連續登入天數）

SELECT
 role_id
, DATE_SUB(date,INTERVAL sort DAY) d_group
, min(date) begin_date
, max(date) end_date
, count(1) continuous_days
FROM
 (
  SELECT
   role_id
  , date
  , row_number() OVER (PARTITION BY role_id ORDER BY date ASC) sort
  FROM
   (
   SELECT DISTINCT
    role_id
   , $part_date date
   FROM
    role_login
  )  temp_1
)  temp_2
GROUP BY role_id, DATE_SUB(date,INTERVAL sort DAY)

最後取每個使用者id的連續登入天數的最大值即可

3. 完整代碼

SELECT
  role_id
, begin_date
, end_date
, continuous_days max_continuous_days
FROM
  (
   SELECT
     *
   , row_number() OVER (PARTITION BY role_id ORDER BY continuous_days DESC) sort_continuous_days
   FROM
     (
      SELECT
        role_id
   , DATE_SUB(date,INTERVAL sort DAY) d_group
      , min(date) begin_date
      , max(date) end_date
      , count(1) continuous_days
      FROM
        (
         SELECT
           role_id
         , date
         , row_number() OVER (PARTITION BY role_id ORDER BY date ASC) sort
         FROM
           (
            SELECT DISTINCT
              role_id
            , $part_date date
            FROM
              role_login
         )  temp_1
      )  temp_2
      GROUP BY role_id, DATE_SUB(date,INTERVAL sort DAY)
   )  temp_3
)  temp_4
WHERE (sort_continuous_days = 1)
ORDER BY max_continuous_days DESC

結果：

這樣，就完成了！

Python方式實作

1.導入需要的庫

import pandas as pd
import numpy as np

2.導入資料

原始資料是一份csv檔案，我們用pandas的方法read_csv直接讀取

df = pd.read_csv(r"C:\Users\Gdc\Documents\登入日志.csv")
#讀取登入日志資料

這裡的登入日志隻有兩個字段：@timestamp和rold_id。前者是使用者登入的時間，後者是使用者的ID，考慮到時間的格式，我們需要做簡單處理去掉後面的時間保留日期。

3.資料預處理

資料預處理方面我們需要做的工作有三部分

時間隻取日期，去掉時間部分

我們使用info方法可以發現，時間字段的格式是object，并非時間格式

但是我們需要統計的時間機關是以日為周期，故而這裡可以先做簡單的去掉時間部分的處理方式

采用字元串的split方法，按照‘ ’(空格)進行切片，取第一部分即可

df['@timestamp']=df['@timestamp'].str.split(' ').str[0]
#因為日期資料為時間格式，可以簡單使用字元串按照空格切片後取第一部分

删除日志裡重複的資料（同一天玩家可以登入多次，故而隻需要保留一條即可）

我們看到上面處理過的資料，可以發現role_id為570837202的使用者在1月8日存在多條記錄，為友善後續計算，這裡需要進行去重處理。

采取drop_duplicate方案即可保留删除重複資料隻保留一條

df.drop_duplicates(inplace=True)
#因為玩家在某一天存在登入多次情況，這裡可以用去重過濾掉多餘資料

将時間字段列轉化為時間格式

同樣也是為了友善後續使用時間加減計算登入行為數，@timestamp字段需要調整為時間日期格式

采取to_datetime方法進行處理

df["@timestamp"] = pd.to_datetime(df["@timestamp"])
#将日期列轉化為 時間格式

4.分組排序

分組排序是指将每個使用者登入日期進行組内排序

采用groupby方法結合rank方法進行處理

df['輔助列'] = df["@timestamp"].groupby(df['role_id']).rank()
#分組排序

5.計算內插補點

這一步是輔助操作，使用第三步中的輔助列與使用者登入日期做內插補點得到一個日期，若某使用者某幾列該值相同，則代表這幾天屬于連續登入

因為輔助列是float型，我們在做時間差的時候需要用到to_timedelta且unit='d'用來表示減去的是天數，這樣獲得的內插補點就會是一個日期

df['date_sub'] = df['@timestamp'] - pd.to_timedelta(df['輔助列'],unit='d')
#計算登入日期與組内排序的內插補點（是一個日期）

6.分組計數

通過上一步，我們可以知道，計算每個使用者date_sub列出現的次數即可算出該使用者連續登入的天數

data = df.groupby(['role_id','date_sub']).count().reset_index()
#根據使用者id和上一步計算的內插補點 進行分組計數

自此，我們計算出了每個使用者連續登入天數

修改輔助列名稱

data = data[['role_id','date_sub','輔助列']].rename(columns={'輔助列':'連續登入天數'})
#修改輔助列名稱

7.計算每個使用者連續登入最大天數

這裡用到的是sort_values和first方法，對每個使用者連續登入天數做組内排序（降序），再取第一個值即為該使用者連續登入最大天數

data = data.sort_values(by='連續登入天數',ascending=False).groupby('role_id').first().reset_index()
#計算每個玩家連續登入最大天數

當我們計算出每個使用者在周期内的每個連續登入天數後，想計算連續登入N天或以上玩家清單就非常友善了，條件篩選即可。