SQL/Pandas实现按30天间隔拆分日期链并聚合起止日期

核心逻辑是通过同id下相邻日期的间隔打分组标记，再按分组聚合起止日期，实现代码如下：

import pandas as pd

# 构造示例数据，实际使用时替换为读取自己的数据源即可
df = pd.DataFrame({
    'id': [1]*9,
    'date': pd.to_datetime(['2021-01-01','2021-01-02','2021-01-03','2021-01-10','2021-01-20','2021-03-20','2021-03-21','2021-03-22','2021-04-02'])
})

# 按id、日期排序
df = df.sort_values(['id','date'])
# 计算同id下相邻日期的间隔天数
df['day_diff'] = df.groupby('id')['date'].diff().dt.days
# 间隔超过30天的位置打标记，累加标记得到分组标识
df['group_id'] = df.groupby('id')['day_diff'].apply(lambda x: (x > 30).cumsum())
# 按id和分组标识聚合，取每个分组的最小/最大日期作为起止
result = df.groupby(['id','group_id'], as_index=False).agg(
    start_date = ('date', 'min'),
    end_date = ('date', 'max')
).drop('group_id', axis=1)

# 日期转字符串格式和示例输出对齐，不需要可以删除
result[['start_date','end_date']] = result[['start_date','end_date']].apply(lambda x: x.dt.strftime('%Y-%m-%d'))
print(result)

通用窗口函数逻辑，支持MySQL 8.0+、PostgreSQL、Hive、Spark SQL等主流SQL引擎，代码如下：

WITH step1 AS (
    -- 按id分组对日期排序，取同id上前一行的日期
    SELECT 
        id,
        date,
        LAG(date) OVER(PARTITION BY id ORDER BY date) AS prev_date
    FROM 你的业务表名
),
step2 AS (
    -- 相邻日期间隔超过30天打标记1，其余为0
    SELECT 
        id,
        date,
        CASE WHEN DATEDIFF(date, prev_date) > 30 THEN 1 ELSE 0 END AS split_flag
    FROM step1
),
step3 AS (
    -- 累加标记得到分组id，相同分组id属于同一条连续日期链
    SELECT 
        id,
        date,
        SUM(split_flag) OVER(PARTITION BY id ORDER BY date) AS group_id
    FROM step2
)
-- 聚合每个分组的起止日期
SELECT 
    id,
    MIN(date) AS start_date,
    MAX(date) AS end_date
FROM step3
GROUP BY id, group_id
ORDER BY id, start_date;

注：不同数据库的日期间差计算语法略有差异，比如PostgreSQL需替换为(date - prev_date) > interval '30 days'，逻辑通用可自行适配。

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