基于状态变化计算聊天机器人数据集时间差的技术求助

需求说明 #

需要为聊天机器人数据集新增Response Time (secs)列，规则为：当状态从IN切换为OUT时，计算当前OUT行与其之前最近的IN行的时间差（单位：秒）。同一个Contact_ID可能存在多组IN/OUT状态切换，需分组单独处理。

示例数据 #

解决方案 #

1. Python Pandas 处理

适合本地数据集处理，核心逻辑是按Contact_ID分组后，追踪每个组内最后一次IN的时间，遇到OUT时计算时间差：

import pandas as pd

# 读取或构造数据集
data = pd.DataFrame({
    'Contact_ID': ['a1H2H000005QCNNUA4', 'a1H2H000005QCNNUA4', 'a1H2H000005QCNNUA4',
                   'l3H2H000005QCNNUH3', 'l3H2H000005QCNNUH3', 'l3H2H000005QCNNUH3', 'l3H2H000005QCNNUH3'],
    'In_out': ['IN', 'IN', 'OUT', 'IN', 'IN', 'IN', 'OUT'],
    'Date': ['10/10/2022 14:00:00', '10/10/2022 14:00:01', '10/10/2022 14:02:01',
             '10/10/2022 18:10:00', '10/10/2022 18:10:01', '10/10/2022 18:10:02', '10/10/2022 18:15:02']
})

# 转换日期列为datetime类型
data['Date'] = pd.to_datetime(data['Date'], format='%d/%m/%Y %H:%M:%S')

# 分组计算响应时间
def calc_response(group):
    last_in = None
    resp_times = []
    for _, row in group.iterrows():
        if row['In_out'] == 'IN':
            last_in = row['Date']
            resp_times.append(None)
        elif row['In_out'] == 'OUT':
            resp_times.append((row['Date'] - last_in).total_seconds() if last_in else None)
        else:
            resp_times.append(None)
    group['Response Time (secs)'] = resp_times
    return group

data = data.groupby('Contact_ID', group_keys=False).apply(calc_response)
print(data)

2. SQL 处理（以MySQL为例）

适合直接在数据库中计算，利用子查询获取每个OUT行之前最近的IN时间：

SELECT 
    Contact_ID,
    In_out,
    Date,
    CASE 
        WHEN In_out = 'OUT' THEN 
            TIMESTAMPDIFF(SECOND, 
                          (SELECT MAX(Date) 
                           FROM your_table t2 
                           WHERE t2.Contact_ID = t1.Contact_ID 
                             AND t2.Date < t1.Date 
                             AND t2.In_out = 'IN'), 
                          t1.Date)
        ELSE NULL 
    END AS `Response Time (secs)`
FROM your_table t1
ORDER BY Contact_ID, Date;

如果你的数据库支持窗口函数（如PostgreSQL、SQL Server），也可以用LAG结合过滤逻辑简化，但上述子查询方法兼容性更强。

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