大家好，我是才哥。

上次我们根据用户创建账号和登录日志进行了《用SQL进行用户留存率计算》，今天我们继续用这份用户登录日志来计算用户连续登录天数。

这里我们用到的是用户登录的日志，其中用户每天可能存在多次登录。

用户登录日志

以上案例数据 后台回复 955 可以在SQL文件夹里data领取

2. 思路分析

其实，我们之前分享过几期类似的案例，大家感兴趣可以去看看。

《『数据分析』pandas计算连续行为天数的几种思路》

《利用Python统计连续登录N天或以上用户》

今天，我们用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的连续登录天数的最大值即可

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

结果：

以上就是本次全部内容了。。

后续我们会持续分享SQL和Python的实操案例哈，敬请期待！