SQL窗口函数实战：IoT场景下设备累计运行时间计算与边界处理

在生产环境中，设备通过 IoT 采集状态数据并存储到本地数据库，数据结构通常如下所示：

device_status_table

采集规则是：设备每变更一次状态，就记录一条数据。状态共有四种：运行中、待机、关机、故障。也就是说，当设备从关机切换到开始生产时，会记录一条“运行中”状态；如果生产状态持续数小时甚至数天，直到下一次状态变更（例如转为待机）时，才会记录下一条数据。因此，相邻两条记录的间隔时间可能只有几秒，也可能相隔数日。

现在，我们需要编写一段 SQL，查询任意时间段内设备的运行总时间。这不仅是常见的业务需求，也是一道经典的数据库面试题。

问题拆解与示例

假设我们有以下示例数据，要求计算设备 AS01 在 2025年3月的总运行时间：

从数据中可以看出，在 2025-03-01 00:00:00 至 2025-03-31 00:00:00 期间，“运行中”状态可分为三段：

1. 从 2025-03-01 00:00:00（查询开始时间）到 2025-03-10 16:00:00，共 232 小时。
2. 从 2025-03-12 16:00:00 到 2025-03-20 16:00:00，共 216 小时。
3. 从 2025-03-28 16:00:00 到 2025-03-31 00:00:00（查询结束时间），共 80 小时。

合计总运行时间为：232 + 216 + 80 = 528 小时。

这个计算的关键在于正确处理日期边界。例如，第一段运行的开始时间并非表中记录的“2025-02-15 16:00:00”，而是查询周期的开始时间“2025-03-01 00:00:00”。同样，最后一段的结束时间也需要用查询周期的结束时间进行截断。

SQL 解决方案

下面是一个利用 SQL 窗口函数解决此问题的完整示例。此方案兼容 Oracle、PostgreSQL 等支持标准窗口函数的数据库系统。

WITH all_periods AS (
  -- 获取查询时间段内及之前的一条记录（用于处理开始边界）
  SELECT asset_code, status_code, shift_date
  FROM (
    SELECT 
      asset_code, 
      status_code, 
      shift_date,
      ROW_NUMBER() OVER (PARTITION BY asset_code ORDER BY shift_date DESC) as rn
    FROM device_status_table
    WHERE shift_date <= TO_DATE('2024-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  ) WHERE rn = 1

  UNION ALL

  -- 获取查询时间段内的所有记录
  SELECT asset_code, status_code, shift_date
  FROM device_status_table
  WHERE shift_date BETWEEN TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
                      AND TO_DATE('2024-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
),
status_periods AS (
  SELECT 
    asset_code,
    status_code,
    shift_date,
    LEAD(shift_date) OVER (PARTITION BY asset_code ORDER BY shift_date) as next_shift_date
  FROM all_periods
),
running_time AS (
  SELECT 
    asset_code,
    SUM(
      CASE 
        WHEN status_code = '运行' 
        THEN 
          (COALESCE(next_shift_date, TO_DATE('2024-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) - 
           GREATEST(shift_date, TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) * 24 * 60 * 60
        ELSE 0
      END
    ) as running_seconds
  FROM status_periods
  WHERE shift_date < TO_DATE('2024-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
  GROUP BY asset_code
)
SELECT 
  asset_code,
  running_seconds,
  NUMTODSINTERVAL(running_seconds, 'SECOND') as running_interval
FROM running_time
WHERE running_seconds > 0;

思路解析

1. 构建完整时间序列 (all_periods)：首先，我们需要一个包含查询时间段内所有状态记录，以及恰好前一条记录的集合。UNION ALL 的第一部分通过 ROW_NUMBER 窗口函数获取每条设备在查询开始时间之前的最新一条记录，确保能计算出跨越周期起点的运行时段。
2. 形成连续时间段 (status_periods)：使用 LEAD 窗口函数，为每条记录获取下一条状态记录的时间点，从而形成从当前 shift_date 到 next_shift_date 的一个个连续时间段。
3. 计算运行时长 (running_time)：核心计算在这里进行。对于每个时间段，我们判断其状态是否为“运行中”。
   • 如果是，则计算该时间段的长度。这里使用了 GREATEST 和 COALESCE 函数来处理边界：
     • GREATEST(shift_date, 查询开始时间)：确保时间段的起点不会早于查询开始时间。
     • COALESCE(next_shift_date, 查询结束时间)：如果当前是最后一条记录（next_shift_date 为 NULL），则用查询结束时间作为时间段的终点。
   • 将时间差（单位为天）乘以 24*60*60 转换为秒数进行累加。
4. 格式化输出：最后，将计算出的总秒数转换为更易读的时间间隔格式（如 Oracle 的 NUMTODSINTERVAL）。

通过这个方案，你可以灵活地替换查询的起止日期，快速计算出任意设备在任意时间窗口内的精确运行时间。这类涉及状态持续时长计算的问题，在设备效能分析、生产报表生成等场景中非常普遍，掌握窗口函数的组合运用是关键。

希望这个实例能帮助你更好地理解 SQL 窗口函数在解决复杂时序计算问题上的威力。如果你有更巧妙的解法或想探讨其他数据库优化技巧，欢迎在技术社区交流分享。