如何基于时间与流程级别两列创建Index列，统计Status Key 1-6循环次数

实现逻辑说明 #

你要的Index列核心逻辑是按Product ID分组后，基于时间顺序判断1-6的阶段循环触发点，每次触发计数+1：

• 触发条件：当前行Status Key为1，且同分组内前一个时间点的行Status Key为6（如果是分组首行且Status Key为1也直接触发第一次计数）
• 非触发行直接继承最近一次的计数值即可
• 如果需要调整起始序号，最终计算结果加上对应偏移量即可

代码实现 #

1. SQL 实现（支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、Spark SQL等） #

SELECT 
    *,
    -- 加上起始偏移量即可自定义起始序号，比如起始为2就 +1
    SUM(cycle_trigger) OVER (PARTITION BY `Product ID` ORDER BY 时间戳) AS `Index`
FROM (
    SELECT 
        *,
        CASE 
            WHEN `Status Key` = 1 AND (LAG(`Status Key`) OVER (PARTITION BY `Product ID` ORDER BY 时间戳) = 6 OR LAG(`Status Key`) OVER (PARTITION BY `Product ID` ORDER BY 时间戳) IS NULL)
            THEN 1
            ELSE 0
        END AS cycle_trigger
    FROM 你的表名
) t

如果业务中存在未走完6就直接回退到1的情况（比如从3直接跳回1也算新循环），可以把触发条件修改为：

WHEN `Status Key` = 1 AND (LAG(`Status Key`) OVER (PARTITION BY `Product ID` ORDER BY 时间戳) != 1 OR LAG(`Status Key`) OVER (PARTITION BY `Product ID` ORDER BY 时间戳) IS NULL)

2. Python Pandas 实现 #

import pandas as pd

# 先确保数据按Product ID和时间戳排序
df = df.sort_values(by=['Product ID', '时间戳']).reset_index(drop=True)

# 生成循环触发标记
df['cycle_trigger'] = df.groupby('Product ID')['Status Key'].apply(
    lambda x: ((x == 1) & (x.shift(1) == 6 | x.shift(1).isna())).astype(int)
).reset_index(drop=True)

# 计算Index，加偏移量可调整起始值
df['Index'] = df.groupby('Product ID')['cycle_trigger'].cumsum()

效果示例 #

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