MySQL中计算非线性时间序列数据中目标值的持续时长

我完全懂你现在的困扰——面对这种时间点不规律、数值还反复切换的序列，靠数行数算时长根本行不通，得用针对性的方法来拆解。咱们先从你给出的例子入手，一步步理清楚怎么用MySQL解决这个问题。

首先先把你提供的示例数据整理成清晰的表格：

核心思路：抓住「连续相同值的区间」 #

这类问题本质上是SQL里的「岛屿与缺口」问题——我们需要把连续相同Value的行归为同一个“岛屿”，然后计算每个岛屿的持续时长，最后把目标值的所有岛屿时长加起来。

我给你提供两种实用的实现方案，你可以根据自己的业务需求选：

方案一：按连续值分组计算区间时长

这种方法会把所有连续相同Value的行合并成一个时间区间，计算从区间起始到下一个区间起始的时长，适合需要合并连续相同状态的场景。

WITH ranked AS (
  -- 给每行分配全局行号和分组内的行号，避免同时间排序混乱
  SELECT
    ID,
    Time,
    Value,
    ROW_NUMBER() OVER (ORDER BY Time, ID) AS global_rn,
    ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Time, ID) AS value_rn
  FROM your_table
),
value_groups AS (
  -- 用两个行号的差值标记连续相同值的组（同一个差值就是同一个“岛屿”）
  SELECT
    Time,
    Value,
    global_rn - value_rn AS group_id
  FROM ranked
),
group_intervals AS (
  -- 提取每个组的起始时间，以及下一个组的起始时间作为当前组的结束时间
  SELECT
    Value,
    MIN(Time) AS start_time,
    -- 找到下一个组的最早时间
    (SELECT MIN(Time) FROM value_groups g2 WHERE g2.group_id > g1.group_id) AS end_time
  FROM value_groups g1
  GROUP BY Value, group_id
  ORDER BY start_time
)
-- 计算每个Value的总持续时长，最后一组如果没有后续组就用表中最大时间
SELECT
  Value,
  SUM(TIMESTAMPDIFF(SECOND, start_time, COALESCE(end_time, (SELECT MAX(Time) FROM your_table)))) AS total_duration_seconds
FROM group_intervals
GROUP BY Value;

方案二：按相邻行时间差累加

这种方法更直接：计算每一行到下一行的时间差，只要当前行的Value是目标值，就把这个时间差计入总时长，适合认为“相邻行之间的状态和当前行一致”的场景。

WITH next_time_info AS (
  -- 为每行获取下一行的时间，用ID辅助排序避免同时间混乱
  SELECT
    Time,
    Value,
    LEAD(Time) OVER (ORDER BY Time, ID) AS next_time
  FROM your_table
)
-- 累加所有有效时间差，最后一行没有下一行则不计入
SELECT
  Value,
  SUM(CASE 
        WHEN next_time IS NOT NULL THEN TIMESTAMPDIFF(SECOND, Time, next_time) 
        ELSE 0 
      END) AS total_duration_seconds
FROM next_time_info
GROUP BY Value;

小提醒 #

• 排序一定要稳：如果存在时间完全相同的行，一定要加上ID辅助排序（比如ORDER BY Time, ID），否则分组或取相邻行时容易出错。
• 时间单位灵活换：TIMESTAMPDIFF里的SECOND可以换成MINUTE、HOUR甚至DAY，完全看你需要的时长粒度。
• 最后一行的特殊处理：如果最后一行的Value是目标值，且你需要把从该行时间到当前时刻的时长算进去，把方案里的COALESCE部分换成NOW()就行，但要结合业务场景判断是否合理。

备注：内容来源于stack exchange，提问作者Ys Guy