SQL Server 2019或Pandas中计算输液停止与后续事件的时间差

SQL 解决方案 #

针对仅需为每个分组（按InfusionID、SiteNumber、SerialNumber）中连续STOPPED序列的首个行计算时间差的需求，可通过窗口函数LAG判断当前STOPPED是否为该段序列的首个，结合子查询实现：

SELECT 
    InfusionStatus, 
    InfusionID, 
    EventDescription, 
    Time AS event_time,
    CASE 
        WHEN InfusionStatus = 'STOPPED' 
             AND LAG(InfusionStatus) OVER (PARTITION BY InfusionID, SiteNumber, SerialNumber ORDER BY Time) != 'STOPPED'
        THEN DATEDIFF(SECOND, Time, 
            ISNULL(
                (SELECT TOP 1 Time
                 FROM table1 t2
                 WHERE t2.InfusionID = t1.InfusionID
                   AND t2.SiteNumber = t1.SiteNumber
                   AND t2.SerialNumber = t1.SerialNumber
                   AND t2.Time > t1.Time
                   AND t2.InfusionStatus IN ('RUNNING', 'STOPPED_ALARM')
                 ORDER BY Time ASC),
                 Time
            )
        )
        ELSE 0
    END AS stop_run_event_duration_secs
FROM dbo.table1 t1
ORDER BY InfusionID, SiteNumber, SerialNumber, Time;

逻辑说明 #

• PARTITION BY按独立输液设备/任务分组，确保时间差计算在同一输液序列内进行
• LAG(InfusionStatus)获取当前行的前一个事件状态，判断当前STOPPED是否为连续序列的首个
• 仅对符合条件的首个STOPPED行执行时间差计算，其余STOPPED行及非STOPPED行填充0

Python（Pandas）解决方案 #

使用Pandas处理时，通过分组排序、标记首个STOPPED行，再结合merge_asof匹配后续目标事件：

import pandas as pd

# 假设数据已加载为DataFrame，且Time列已解析为时间类型
# df = pd.read_csv("your_data.csv", parse_dates=['Time'])

# 1. 按设备分组并按时间排序，保证事件顺序正确
df = df.sort_values(by=['InfusionID', 'SiteNumber', 'SerialNumber', 'Time'])

# 2. 标记每个分组内的首个STOPPED行
df['is_first_stopped'] = (
    df['InfusionStatus'] == 'STOPPED' 
    & (df.groupby(['InfusionID', 'SiteNumber', 'SerialNumber'])['InfusionStatus'].shift(1) != 'STOPPED')
)
# 处理分组第一行就是STOPPED的边界情况
df['is_first_stopped'] = df['is_first_stopped'].fillna(df['InfusionStatus'] == 'STOPPED')

# 3. 提取目标事件（RUNNING/STOPPED_ALARM）作为匹配表
target_events = df[df['InfusionStatus'].isin(['RUNNING', 'STOPPED_ALARM'])].copy()

# 4. 匹配每个首个STOPPED行之后最近的目标事件
result = pd.merge_asof(
    df,
    target_events[['InfusionID', 'SiteNumber', 'SerialNumber', 'Time']].rename(columns={'Time': 'next_target_time'}),
    on='Time',
    by=['InfusionID', 'SiteNumber', 'SerialNumber'],
    direction='forward'
)

# 5. 计算时间差，仅首个STOPPED行填充，其余为0
df['stop_run_event_duration_secs'] = df.apply(
    lambda row: (row['next_target_time'] - row['Time']).total_seconds() 
    if row['is_first_stopped'] else 0,
    axis=1
)

# 填充无后续目标事件的情况为0
df['stop_run_event_duration_secs'] = df['stop_run_event_duration_secs'].fillna(0)

逻辑说明 #

• 通过shift和分组操作精准标记连续STOPPED序列的首个行
• merge_asof高效匹配每个STOPPED行之后最近的目标事件，避免循环遍历
• 仅对标记行计算时间差，保证结果符合需求

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