InfluxDB按日聚合系统状态持续时长的技术问询

嘿，这个统计每日状态时长的需求我太熟了！刚好可以用InfluxDB的窗口函数和条件判断来实现，咱们直接上解决方案：

核心思路 #

你的数据是状态变更的时间点，每个状态的持续时长=下一个状态的开始时间 - 当前状态的开始时间；当天最后一个状态的结束时间就是当天的24:00（次日00:00），所以要单独处理这个边界情况。

具体查询代码 #

假设你的measurement名叫system_status，状态字段是status，可以用这个InfluxQL查询：

SELECT sum(duration) AS total_duration
FROM (
  SELECT 
    time AS start_time,
    status,
    -- 用shift()获取下一条记录的时间，作为当前状态的结束时间
    shift(time, 1) AS end_time,
    -- 计算单条状态的持续时长，最后一条用当天结束时间填充
    CASE 
      WHEN end_time IS NULL THEN date_trunc('day', time) + interval '1d' - time
      ELSE end_time - time
    END AS duration
  FROM (
    -- 筛选目标日期的所有状态记录，按时间升序排列
    SELECT time, status
    FROM system_status
    WHERE time >= '2018-03-22T00:00:00Z' AND time < '2018-03-23T00:00:00Z'
    ORDER BY time ASC
  ) state_changes
) state_durations
GROUP BY status

代码解释 #

• 内层的state_changes子查询：先把当天所有的状态变更记录捞出来，按时间排序，确保顺序是正确的状态流转顺序
• 中间的state_durations子查询：
  • shift(time,1)是关键，它能把下一条记录的时间“挪”到当前行，这样我们就知道当前状态什么时候结束
  • CASE语句处理最后一条记录：因为最后一条没有下一条，所以用date_trunc('day', time)+1d得到当天的结束时间，减去当前状态的开始时间，就是从当前状态到当天结束的时长
• 最外层的GROUP BY status：把同一个状态的所有时长加起来，得到每日累计时长

扩展：统计所有日期的每日时长 #

如果要一次性统计所有日期的每日各状态时长，只需要在外层按status和日期分组就行：

SELECT sum(duration) AS total_duration
FROM (
  SELECT 
    time AS start_time,
    status,
    shift(time, 1) AS end_time,
    CASE 
      WHEN end_time IS NULL THEN date_trunc('day', time) + interval '1d' - time
      ELSE end_time - time
    END AS duration
  FROM (
    SELECT time, status
    FROM system_status
    ORDER BY time ASC
  ) state_changes
) state_durations
GROUP BY status, time(date_trunc('day', start_time))

注意事项 #

• 确保你的InfluxDB版本支持shift()函数（1.7及以上版本都支持，如果是更早的版本，可能需要用自连接的方式来实现类似逻辑）
• 查询时尽量用ISO标准时间格式（比如2018-03-22T00:00:00Z），避免因时间格式歧义导致的数据筛选错误

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