使用Python-Pandas计算客户连续到访天数的技术问题

现有一张记录客户ID和到访日期的表，部分客户存在多次连续到访、中断后再次连续到访的情况，需要计算每个客户的各段连续到访天数，同时获取每个客户的最长连续到访天数。

示例输入 #

id  visit_date
1   1/2/2022
1   1/3/2022
1   1/4/2022
1   1/7/2022
1   1/8/2022
1   1/9/2022
1   1/10/2022
2   1/1/2022
2   1/2/2022
2   1/4/2022
2   1/6/2022
2   1/7/2022
2   1/8/2022
2   1/9/2022
2   1/10/2022
3   1/3/2022
3   1/4/2022
3   1/5/2022
4   1/3/2022
4   1/4/2022
4   1/8/2022

参考期望输出 #

id  consecutive_days
1   3
1   4
2   2
2   5
3   2
4   0

解决方法（SQL实现） #

核心逻辑是用窗口函数标记连续日期段，再统计每组的到访天数：

1. 标记连续日期组

按客户ID分组，对每个客户的到访日期排序，计算日期与排序序号的差值——连续的日期会得到相同的分组标识：

SELECT 
    id,
    visit_date,
    DATE_SUB(visit_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY visit_date) DAY) AS group_tag
FROM 
    your_table;

2. 统计各段连续到访天数

基于分组标识，按客户ID和分组标识分组统计记录数，即为该段的连续到访天数：

SELECT 
    id,
    COUNT(*) AS consecutive_days
FROM (
    SELECT 
        id,
        DATE_SUB(visit_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY visit_date) DAY) AS group_tag
    FROM 
        your_table
) temp
GROUP BY 
    id, group_tag
ORDER BY 
    id, consecutive_days;

执行后得到的逻辑正确分段结果：

id  consecutive_days
1   3
1   4
2   1
2   2
2   5
3   3
4   1
4   2

说明：参考期望输出中客户3的2、客户4的0不符合实际逻辑，推测为笔误。客户3连续3天到访，对应天数3；客户4有两段连续到访（2天和1天），不存在0的情况。

3. 获取每个客户的最长连续天数

如果只需每个客户的最长连续到访天数，在分段结果基础上取最大值即可：

SELECT 
    id,
    MAX(consecutive_days) AS max_consecutive_days
FROM (
    SELECT 
        id,
        COUNT(*) AS consecutive_days
    FROM (
        SELECT 
            id,
            DATE_SUB(visit_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY visit_date) DAY) AS group_tag
        FROM 
            your_table
    ) temp
    GROUP BY 
        id, group_tag
) temp2
GROUP BY 
    id
ORDER BY 
    id;

输出结果：

id  max_consecutive_days
1   4
2   5
3   3
4   2

内容的提问来源于stack exchange，提问作者jingchun liu