Firebird 2.5.3中计算同操作员相邻ENTRY_TIME的时间差

Alright, let's figure out how to calculate the time interval between consecutive shift log entries for each operator in Firebird 2.5.3. Since Firebird 2.5 doesn't support LEAD() window functions (those arrived in version 3.0), we'll use a correlated subquery approach to fetch the next entry's timestamp for each record.

核心思路 #

For each entry in SHIFT_LOG_ENTRY, we need to:

• 找到同一操作员（匹配SHIFT_LOG_DET_ID）的下一条时间顺序条目
• 计算当前条目时间与下一条条目时间的秒数差值
• 不对ENDED类型条目计算时间差，因为它没有后续条目

完整查询语句 #

以下是包含时间差计算的修改版查询：

SELECT 
  ent.ID as ENT_ID,
  det.ID as DET_ID,
  usr.CODE as USR_ID,
  ent.SHIFT_LOG_DET_ID,
  ent.ENTRY_TYPE,
  -- 转换条目类型为可读文本
  IIF(ent.ENTRY_TYPE = 0, 'ADDED',
      IIF(ent.ENTRY_TYPE = 1, 'STARTED',
          IIF(ent.ENTRY_TYPE = 2, 'ON-BREAK',
              IIF(ent.ENTRY_TYPE = 3, 'JOINED',
                  IIF(ent.ENTRY_TYPE = 4, 'ENDED', 'UNKNOWN ENTRY'))))) as ENTRY_TYPE_VALUE,
  -- 转换为标准时间戳格式
  ent.ENTRY_TIME + CAST('31.12.1899' AS TIMESTAMP) as ENTRY_TIME,
  -- 计算耗时（仅针对非ENDED条目）
  CASE 
    WHEN ent.ENTRY_TYPE != 4 THEN 
      DATEDIFF(SECOND, ent.ENTRY_TIME + CAST('31.12.1899' AS TIMESTAMP), next_entry.next_time)
    ELSE NULL 
  END AS INTERVAL_SECONDS
FROM SHIFT_LOG_ENTRY ent
LEFT JOIN SHIFT_LOG_DET det ON det.ID = ent.SHIFT_LOG_DET_ID
LEFT JOIN SHIFT_LOG log ON log.ID = det.SHIFT_LOG_ID
LEFT JOIN USERS usr ON usr.USERID = det.OPERATOR_ID
-- 关联子查询获取同一操作员的下一条条目时间
LEFT JOIN (
  SELECT 
    SHIFT_LOG_DET_ID,
    ENTRY_TIME + CAST('31.12.1899' AS TIMESTAMP) AS next_time,
    -- 为每个操作员的条目按时间编号，确保只获取紧邻的下一条
    ROW_NUMBER() OVER (PARTITION BY SHIFT_LOG_DET_ID ORDER BY ENTRY_TIME) AS rn
  FROM SHIFT_LOG_ENTRY
) next_entry ON next_entry.SHIFT_LOG_DET_ID = ent.SHIFT_LOG_DET_ID
              AND next_entry.rn = (
                SELECT ROW_NUMBER() OVER (PARTITION BY SHIFT_LOG_DET_ID ORDER BY ENTRY_TIME) 
                FROM SHIFT_LOG_ENTRY ent2 
                WHERE ent2.ID = ent.ID
              ) + 1
WHERE log.ID = 1
-- 按操作员和条目时间排序，方便查看
ORDER BY usr.CODE, ent.ENTRY_TIME;

关键细节说明 #

1. 关联子查询获取下一条条目：
   我们用ROW_NUMBER()按SHIFT_LOG_DET_ID（每个操作员的班次详情）分组，为每个条目分配序号，然后通过序号关联到下一条条目，确保拿到的是时间上紧邻的后续记录。

2. 时间差计算：
   使用DATEDIFF(SECOND, 开始时间, 结束时间)函数计算两个时间戳的秒数差，并用CASE语句对ENDED类型返回NULL，符合需求。

3. 时间戳转换：
   保留了你原查询中ent.ENTRY_TIME + CAST('31.12.1899' AS TIMESTAMP)的转换逻辑，确保时间计算的准确性。

4. 排序优化：
   移除了原查询中多余的GROUP BY（因为没有使用聚合函数），替换为ORDER BY usr.CODE, ent.ENTRY_TIME，让结果按操作员和时间顺序排列，便于验证时间差的合理性。

简化版替代方案 #

如果你的数据中同一操作员没有重复时间戳的条目，也可以用更简洁的MIN()子查询来获取下一条时间：

SELECT 
  ent.ID as ENT_ID,
  det.ID as DET_ID,
  usr.CODE as USR_ID,
  ent.SHIFT_LOG_DET_ID,
  ent.ENTRY_TYPE,
  IIF(ent.ENTRY_TYPE = 0, 'ADDED',
      IIF(ent.ENTRY_TYPE = 1, 'STARTED',
          IIF(ent.ENTRY_TYPE = 2, 'ON-BREAK',
              IIF(ent.ENTRY_TYPE = 3, 'JOINED',
                  IIF(ent.ENTRY_TYPE = 4, 'ENDED', 'UNKNOWN ENTRY'))))) as ENTRY_TYPE_VALUE,
  ent.ENTRY_TIME + CAST('31.12.1899' AS TIMESTAMP) as ENTRY_TIME,
  CASE 
    WHEN ent.ENTRY_TYPE != 4 THEN 
      DATEDIFF(SECOND, ent.ENTRY_TIME + CAST('31.12.1899' AS TIMESTAMP), 
        (SELECT MIN(ent2.ENTRY_TIME + CAST('31.12.1899' AS TIMESTAMP))
         FROM SHIFT_LOG_ENTRY ent2
         WHERE ent2.SHIFT_LOG_DET_ID = ent.SHIFT_LOG_DET_ID
           AND ent2.ENTRY_TIME > ent.ENTRY_TIME))
    ELSE NULL 
  END AS INTERVAL_SECONDS
FROM SHIFT_LOG_ENTRY ent
LEFT JOIN SHIFT_LOG_DET det ON det.ID = ent.SHIFT_LOG_DET_ID
LEFT JOIN SHIFT_LOG log ON log.ID = det.SHIFT_LOG_ID
LEFT JOIN USERS usr ON usr.USERID = det.OPERATOR_ID
WHERE log.ID = 1
ORDER BY usr.CODE, ent.ENTRY_TIME;

这个方案更简洁，但如果存在同一操作员同一时间的多条记录，还是用ROW_NUMBER()的方案更可靠，能确保获取到顺序上的下一条条目。

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