按连续Product分组提取StartDate/EndDate的技术实现问询

这是典型的**间隙与岛屿（Gaps and Islands）**问题，核心是识别连续相同的产品序列，再对每个序列聚合时间范围。以下提供两种常用工具的实现方案：

SQL 实现 #

利用窗口函数标记产品切换的节点，生成唯一分组ID后聚合：

WITH grouped_data AS (
    SELECT 
        Product,
        Timestamp,
        -- 标记产品切换的行：当前行与上一行产品不同则记为1
        CASE WHEN LAG(Product) OVER (ORDER BY Timestamp) != Product THEN 1 ELSE 0 END AS group_flag
    FROM your_table
),
group_ids AS (
    SELECT 
        Product,
        Timestamp,
        -- 累加标记值，为每个连续产品组生成唯一ID
        SUM(group_flag) OVER (ORDER BY Timestamp) AS group_id
    FROM grouped_data
)
SELECT 
    Product,
    MIN(Timestamp) AS StartDate,
    MAX(Timestamp) AS EndDate
FROM group_ids
GROUP BY Product, group_id
ORDER BY StartDate;

逻辑说明 #

1. LAG(Product) OVER (ORDER BY Timestamp) 获取当前行的上一行产品，对比后标记切换点；
2. 对切换点标记累加，得到每个连续产品组的唯一ID；
3. 按产品+组ID分组，取时间的最小/最大值作为区间起止。

Python Pandas 实现 #

通过移位对比生成分组标识，再聚合计算时间范围：

import pandas as pd

# 假设数据已存入DataFrame（可替换为读取你的数据源）
df = pd.DataFrame({
    'Product': ['A', 'A', 'A', 'B', 'B', 'A', 'A', 'C', 'C', 'C', 'B', 'B'],
    'Timestamp': ['2023-12-27 22:37:44.717', '2023-12-27 22:38:39.403', '2023-12-27 22:39:34.447',
                  '2023-12-27 22:40:28.733', '2023-12-27 22:41:21.460', '2023-12-27 22:43:09.917',
                  '2023-12-27 22:44:06.500', '2023-12-27 22:44:59.263', '2023-12-27 22:45:53.230',
                  '2023-12-27 22:46:46.597', '2023-12-27 22:46:52.164', '2023-12-27 22:47:54.531']
})

# 转换时间列为datetime类型
df['Timestamp'] = pd.to_datetime(df['Timestamp'])

# 生成分组ID：当前行与上一行产品不同时标记为True，累加后得到唯一组ID
df['group_id'] = (df['Product'] != df['Product'].shift(1)).cumsum()

# 分组聚合计算起止时间
result = df.groupby(['Product', 'group_id']).agg(
    StartDate=('Timestamp', 'min'),
    EndDate=('Timestamp', 'max')
).reset_index(drop=True)

print(result)

逻辑说明 #

1. df['Product'].shift(1) 获取上一行产品，对比后生成布尔值标记切换点；
2. cumsum() 将布尔值转换为累加的组ID，每个连续产品组对应唯一ID；
3. 按产品+组ID分组，聚合得到每个组的时间区间。

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