如何计算客户ID连续登录最大次数及生成consecutive_day_flag列

原始数据表 #

一、生成consecutive_day_flag列的实现方法 #

按照你的思路，通过自连接+分组累计的方式生成目标列，SQL代码如下：

-- 步骤1：给每个用户的登录记录按日期排序
WITH ranked_logins AS (
    SELECT 
        customer_id,
        login,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login) AS rn
    FROM login_table
),
-- 步骤2：自连接匹配连续的前一天登录日期
login_with_prev AS (
    SELECT 
        r1.customer_id,
        r1.login,
        r2.login AS previous_date
    FROM ranked_logins r1
    LEFT JOIN ranked_logins r2 
        ON r1.customer_id = r2.customer_id 
        AND r1.rn = r2.rn + 1
        AND DATEADD(day, 1, r2.login) = r1.login -- 仅关联日期连续的记录
),
-- 步骤3：标记连续登录的分组
grouped_consecutive AS (
    SELECT 
        customer_id,
        login,
        previous_date,
        -- 遇到previous_date为null时，开启新分组
        SUM(CASE WHEN previous_date IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY customer_id ORDER BY login) AS group_id
    FROM login_with_prev
)
-- 步骤4：在每个分组内生成连续计数
SELECT 
    customer_id,
    login,
    previous_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id, group_id ORDER BY login) - 1 AS consecutive_day_flag
FROM grouped_consecutive
ORDER BY customer_id, login;

执行后会得到你预期的中间表，之后只需对consecutive_day_flag取最大值，就能得到连续登录的最高天数。

二、更优解决方案（无需生成中间标记列） #

可以直接通过日期与排序行号的差值来识别连续登录段，一步计算出最大连续天数，效率更高：

WITH login_groups AS (
    SELECT 
        customer_id,
        login,
        -- 连续登录的日期，减去排序行号后得到相同的分组键
        DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login), login) AS group_key
    FROM login_table
)
SELECT 
    MAX(login_count) AS highest_consecutive_day
FROM (
    SELECT 
        customer_id,
        COUNT(*) AS login_count
    FROM login_groups
    GROUP BY customer_id, group_key
) AS consecutive_counts;

原理：连续的日期按顺序排序后，每个日期减去它的行号会得到一个固定值（比如2016-03-01减1=2016-02-29，2016-03-02减2=2016-02-29），非连续的日期会生成不同的group_key。按customer_id和group_key分组后，每组的记录数就是该段连续登录的天数，最后取最大值即可。

内容的提问来源于stack exchange，提问作者Krishnam Vats