前后道配对

概述

案例来自 showa.line_state_change_log 去前后相邻的 型切、稼动，统计产线型切消耗的时长

create definer = root@`%` view usv_line_state_change as
select * from line_state_change_log where new_state in ('型切','稼动');


DELIMITER $$
SELECT
    a.id as id01,b.id as id02,
  a.line_id,
  DATE_FORMAT(a.create_time, '%Y/%m/%d %H:%i') AS change_time,
  DATE_FORMAT(b.create_time, '%Y/%m/%d %H:%i') AS running_time,
  CONCAT(TIMESTAMPDIFF(HOUR, a.create_time, b.create_time), '小时') AS duration
FROM (
  SELECT
    t.*,
    @rn := IF(@prev_line = line_id, @rn + 1, 1) AS rn,
    @prev_line := line_id
  FROM usv_line_state_change t
  CROSS JOIN (SELECT @rn := 0, @prev_line := NULL) vars
  where t.id >= 6349 and t.line_id=37
  ORDER BY line_id, id#STR_TO_DATE(create_time, '%Y/%m/%d %H:%i')
) a
JOIN (
  SELECT
    t.*,
    @rn2 := IF(@prev_line2 = line_id, @rn2 + 1, 1) AS rn,
    @prev_line2 := line_id
  FROM usv_line_state_change t
  CROSS JOIN (SELECT @rn2 := 0, @prev_line2 := NULL) vars
  where t.id >= 6349 and t.line_id=37
  ORDER BY line_id, id#STR_TO_DATE(create_time, '%Y/%m/%d %H:%i')
) b
ON a.line_id = b.line_id
  AND a.rn = b.rn - 1
  AND a.new_state = '型切'
  AND b.new_state = '稼动'
WHERE 1=1
#and a.id < b.id
# and STR_TO_DATE(a.create_time, '%Y/%m/%d %H:%i') < STR_TO_DATE(b.create_time, '%Y/%m/%d %H:%i');
;
$$