按ID分组二进制哑变量并计算连续序列的最小/最大日期

原始数据集 #

现有包含日期、员工编号、二进制哑变量三个字段的数据集如下：

需求说明 #

按员工维度分组，对Dummy取值为1的不间断连续日期序列进行归类，统计每个序列对应的最小日期、最大日期以及员工内的分组编号，期望输出如下：

实现思路 #

该需求属于典型的时间序列孤岛问题，核心逻辑是为每一段连续的有效（Dummy=1）序列生成唯一的公共标识，再基于该标识聚合统计即可。

常用实现方案 #

1. SQL实现（基于窗口函数，兼容MySQL 8.0+/PostgreSQL/SQL Server等支持窗口函数的数据库） #

WITH filtered_data AS (
    -- 筛选有效记录，按员工分组、日期排序生成行号
    SELECT 
        `Emp ID`,
        STR_TO_DATE(Date, '%d.%m.%Y') AS format_date,
        ROW_NUMBER() OVER (PARTITION BY `Emp ID` ORDER BY STR_TO_DATE(Date, '%d.%m.%Y') ASC) AS rn
    FROM employee_table
    WHERE Dummy = 1
),
group_tag AS (
    -- 连续日期的「日期 - 行号天数」差值相同，以此作为分组标识
    SELECT 
        `Emp ID`,
        format_date,
        DATE_SUB(format_date, INTERVAL rn DAY) AS group_id
    FROM filtered_data
)
-- 聚合得到最终结果
SELECT 
    `Emp ID`,
    DATE_FORMAT(MIN(format_date), '%d.%m.%Y') AS `Min Date`,
    DATE_FORMAT(MAX(format_date), '%d.%m.%Y') AS `Max Date`,
    ROW_NUMBER() OVER (PARTITION BY `Emp ID` ORDER BY MIN(format_date) ASC) AS `Group Number`
FROM group_tag
GROUP BY `Emp ID`, group_id
ORDER BY `Emp ID`, `Min Date`;

注：不同数据库的日期处理函数略有差异，可根据实际使用的数据库调整日期转换、运算函数即可。

2. Python Pandas实现 #

import pandas as pd

# 1. 加载数据并格式化日期
df = pd.read_csv('your_data_path.csv') # 也可从其他数据源读取，比如数据库、Excel
df['Date'] = pd.to_datetime(df['Date'], format='%d.%m.%Y')
df = df.sort_values(by=['Emp ID', 'Date']).reset_index(drop=True)

# 2. 筛选有效记录，生成分组标识
df_filter = df[df['Dummy'] == 1].copy()
# 相邻日期间隔不等于1天则标记为新分组的起点
df_filter['is_new_group'] = df_filter.groupby('Emp ID')['Date'].diff().dt.days.ne(1)
# 累加新分组标记得到分组ID
df_filter['group_id'] = df_filter.groupby('Emp ID')['is_new_group'].cumsum()

# 3. 聚合统计结果
result = df_filter.groupby(['Emp ID', 'group_id']).agg(
    Min_Date=('Date', 'min'),
    Max_Date=('Date', 'max')
).reset_index()

# 4. 生成员工内的分组编号，转换日期格式回原样式
result['Group Number'] = result.groupby('Emp ID').cumcount() + 1
result['Min Date'] = result['Min_Date'].dt.strftime('%d.%m.%Y')
result['Max Date'] = result['Max_Date'].dt.strftime('%d.%m.%Y')
result = result[['Emp ID', 'Min Date', 'Max Date', 'Group Number']]

# 输出结果
print(result)

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