SQL实现：查询机器周期的起止时间及运行时长

数据表结构与数据 #

现有数据表 pec_za_starenje_prva_start_stop，结构及数据如下：

字段类型：id(int)、TimeLogged(DateTime)、Tag1(int)。其中Tag1=1代表机器周期启动，Tag1=0代表周期结束。

需求 #

生成包含周期ID、start_cycle（启动时间）、end_cycle（结束时间）、total_time（周期总时长）的结果表，示例如下：

总时长格式不限。

原SQL问题分析 #

用户尝试的SQL无法正确获取结束时间，核心问题是PARTITION BY ID错误——ID是每行的唯一主键，分区后每个分区仅包含一行数据，LEAD()函数无法获取到下一行的结束时间：

SELECT ID,  
       TimeLogged start_cycle,  
       next_TimeLogged end_cycle,  
       TIMEDIFF(next_TimeLogged, TimeLogged) total_time  
FROM (  
  SELECT *, LEAD(TimeLogged) OVER (PARTITION BY ID ORDER BY TimeLogged)  
 next_TimeLogged  
  FROM pec_za_starenje_prva_start_stop  
) t  
WHERE Tag1 = '1'  
ORDER BY start_cycle;  

正确SQL实现方案 #

去掉错误的PARTITION BY ID，直接按TimeLogged全局排序，用LEAD()获取当前启动记录的下一条记录（即对应结束时间），再筛选Tag1=1的启动行，最后计算总时长：

SELECT 
  ROW_NUMBER() OVER (ORDER BY t.TimeLogged) AS ID,
  t.TimeLogged AS start_cycle,
  t.next_TimeLogged AS end_cycle,
  CONCAT(TIMESTAMPDIFF(MINUTE, t.TimeLogged, t.next_TimeLogged), 'minutes') AS total_time
FROM (
  SELECT 
    *,
    LEAD(TimeLogged) OVER (ORDER BY TimeLogged) AS next_TimeLogged
  FROM pec_za_starenje_prva_start_stop
) t
WHERE t.Tag1 = 1
ORDER BY t.TimeLogged;

说明 #

1. LEAD(TimeLogged) OVER (ORDER BY TimeLogged)：全局按时间排序，获取当前行的下一行时间，确保启动记录（Tag1=1）能对应到紧随其后的结束记录（Tag1=0）。
2. ROW_NUMBER() OVER (ORDER BY t.TimeLogged)：生成新的周期ID，按启动时间顺序编号。
3. TIMESTAMPDIFF(MINUTE, ...)：计算两个时间的分钟差，拼接单位得到示例格式的总时长。

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