Spotfire实时计算：如何剔除停机时间计算实际工作时长？

核心逻辑：识别连续工作时段，剔除停机间隔 #

你的数据里，停机时段的核心特征是时间戳不连续（比如2023/01/01之后直接跳至2023/01/04），也可以结合深度、工作时长的变化辅助验证。以下是具体落地步骤：

步骤1：为每条记录关联前一条的时间戳 #

先将数据集按日期时间戳升序排序，然后给每条记录增加一列存储上一条的时间戳。不同工具的实现方式如下：

• SQL（用窗口函数LAG()）：

SELECT 
  日期时间戳,
  深度,
  总工作时长,
  LAG(日期时间戳) OVER (ORDER BY 日期时间戳) AS prev_timestamp
FROM your_dataset

• Pandas（用shift()）：

import pandas as pd

# 先转成datetime格式
df['日期时间戳'] = pd.to_datetime(df['日期时间戳'])
# 关联前一条时间戳
df['prev_timestamp'] = df['日期时间戳'].shift(1)

步骤2：计算时间间隔，筛选有效工作时长 #

计算当前记录与前一条的时间差，若差值等于你的数据采集间隔（比如1分钟，约0.0167小时），则属于有效工作时段；若差值远大于采集间隔，则判定为停机时间，直接剔除。

以Pandas为例：

# 计算间隔（单位：小时）
df['interval_hours'] = (df['日期时间戳'] - df['prev_timestamp']).dt.total_seconds() / 3600

# 定义有效间隔阈值（这里按1分钟采集间隔设置）
valid_threshold = 1/60

# 累加所有有效间隔，得到总实际工作时长
actual_total_hours = df.loc[df['interval_hours'] <= valid_threshold, 'interval_hours'].sum()

步骤3：结合业务特征辅助验证（可选） #

如果担心时间戳存在误差，可以叠加深度变化或工作时长增长的判断：

• 连续工作时，深度应持续变化，总工作时长也会稳步增长
• 停机时，深度保持不变，总工作时长不再更新

示例代码（Pandas）：

# 同时满足时间间隔有效 + 深度变化
valid_mask = (df['interval_hours'] <= valid_threshold) & (df['深度'] != df['深度'].shift(1))
actual_total_hours = df.loc[valid_mask, 'interval_hours'].sum()

备选思路：提取连续工作时段的起止时间 #

1. 标记所有“断点”：即时间间隔超过阈值的记录，这些记录的日期时间戳是复工时间，前一条的日期时间戳是上一轮工作结束时间。
2. 按连续时段分组，计算每个时段的时长后累加。

SQL示例：

WITH grouped_data AS (
  SELECT 
    *,
    -- 生成分组ID：遇到断点则新建分组
    SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, prev_timestamp, 日期时间戳) > 1 THEN 1 ELSE 0 END) OVER (ORDER BY 日期时间戳) AS work_group
  FROM (
    SELECT 
      *,
      LAG(日期时间戳) OVER (ORDER BY 日期时间戳) AS prev_timestamp
    FROM your_dataset
  ) t
)
SELECT 
  work_group,
  MIN(日期时间戳) AS period_start,
  MAX(日期时间戳) AS period_end,
  TIMESTAMPDIFF(HOUR, MIN(日期时间戳), MAX(日期时间戳)) AS period_hours
FROM grouped_data
GROUP BY work_group
-- 最后将所有period_hours求和，就是总实际工作时长

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