SQL-经典的连续登录问题

SQL连续登录 问题可以说是SQL笔试题的常客了，一是因为工作中有不少应用场景，二是因为能初步考察数分人员的SQL功底。连续登录主要有2个方面的应用，其一是圈选客户，比如选取连续登录7天的客户，其二是客户标签，比如计算客户过去90天的最长连续登录天数作为衍生变量。本文就总结一下SQL连续登录问题的实现。

首先明确连续登录问题的底层处理逻辑，连续登录问题的核心是连续，连续的内层含义是日期递增1，我们主要就是从递增1这个点入手。

下面看看具体实例的代码实现：

假设我们有如下源表login_detail：

• 选取连续登录7天的客户：

--方法1：使用row_number()创建辅助列
with
--1：按客户号+登录日期去重排序
    tmp1 as (
        select
            cust_id,
            date(login_time) as login_date
        from login_detail
        group by 1,2
        order by 1,2
    ),

--2：按客户号分组添加行号
    tmp2 as (
        select
            cust_id,
            login_date,
            row_number() over (partition by cust_id order by login_date) as row_num
        from tmp1
    ),

--3：创建辅助列-登录日期减行号
    tmp3 as (
        select
            cust_id,
            login_date,
            date_add('day', -row_num, login_date) as login_date_2
        from tmp2
    )

--4：按客户号+辅助列分组计数并筛选出计数为7的客户
select
    cust_id
from tmp3
group by cust_id, login_date_2
having count(1) = 7;  --按需调整条件



--方法2：使用lag()创建辅助列
with
--1：按客户号+登录日期去重排序
    tmp1 as (
        select
            cust_id,
            date(login_time) as login_date
        from login_detail
        group by 1,2
        order by 1,2
    ),

--2：使用lag()创建辅助列
    tmp2 as (
        select
            cust_id,
            login_date,
            lag(login_date, 6) over (partition by cust_id order by login_date) as login_date_2,  --按需调整
            date_add('day', -6, login_date) as login_date_3  --按需调整
        from tmp1
    )

--3：筛选出客户
select
    cust_id
from tmp2
where login_date_2 = login_date_3;

最终筛选出的结果为客户A，能够看出方法2比方法1精简点，但方法1应用更灵活。

• 计算每个客户的最长连续登录天数：

with
--1：按客户号+登录日期去重排序
    tmp1 as (
        select
            cust_id,
            date(login_time) as login_date
        from login_detail
        group by 1,2
        order by 1,2
    ),

--2：按客户号分组添加行号
    tmp2 as (
        select
            cust_id,
            login_date,
            row_number() over (partition by cust_id order by login_date) as row_num
        from tmp1
    ),

--3：创建辅助列-登录日期减行号
    tmp3 as (
        select
            cust_id,
            login_date,
            date_add('day', -row_num, login_date) as login_date_2
        from tmp2
    ),

--4：按客户号+辅助列分组计数
    tmp4 as (
        select
            cust_id,
            login_date_2,
            count(1) as consecutive_login_days
        from tmp3
        group by 1,2
    )

--5：按客户号分组取最大连续登录天数
select
    cust_id,
    max(consecutive_login_days) as max_consecutive_login_days
from tmp4
group by 1;

结果如下：

代码使用Presto SQL实现，其他SQL可能在日期加减函数上会有差异。值得强调的是，连续登录问题的核心是如何判断连续，登录只是个特定行为，我们完全可以将登录行为换成其他用户行为，问题涉及的字段实际就2个：客户ID+日期，只要掌握了文中所示的处理方法，就可以做到举一反三了。

原创文章，欢迎转载，转载请注明出处并留下原文链接。