MySQL 8.0 按条件查询关联行计算弹窗到确认指令耗时

测试基础环境 #

表结构与初始化数据 #

CREATE TABLE IF NOT EXISTS `events` (
  `GameID` int(8) unsigned NOT NULL,
  `EventNumber` int(3) unsigned NOT NULL,
  `CommandID` int(5) NOT NULL,
  `Timestamp` datetime NOT NULL,
  PRIMARY KEY (`eventNumber`)
) DEFAULT CHARSET=utf8;
INSERT INTO `events` (`GameID`, `EventNumber`, `CommandID`,`Timestamp`) VALUES
('1414485','23','228','2021-02-27 18:10:17.594000'),
('1414485','26','228','2021-02-27 18:10:21.294000'),
('1414485','28','1054','2021-02-27 18:10:22.803000'),
('1414485','30','228','2021-02-27 18:10:25.248000'),
('1414485','39','2078','2021-02-27 18:10:36.409000'),
('1414485','41','228','2021-02-27 18:11:44.306000'),
('1414485','44','1054','2021-02-27 18:11:46.926000');

字段说明 #

• GameID：指令所属的赛事ID，示例中为固定值
• EventNumber：赛事内的指令顺序行号，如值23代表该指令是赛事下发的第23条指令
• CommandID：指令类型标识，228为「生成自由球弹窗」，1054为「主队确认自由球」，2078为「客队确认自由球」
• Timestamp：指令下发时间

业务需求 #

统计弹窗生成时间到对应确认指令的间隔耗时，需兼容两类场景：

• 弹窗生成后未触发确认的无效场景
• EventNumber非连续自增（中间存在被过滤的无关指令）的实际数据情况

原查询的问题 #

原尝试使用的查询语句：

WITH cte AS
(
 SELECT *
   FROM events ev
  WHERE ev.GameID = 1414485
    AND ev.CommandID in(1054, 228, 2078)
   ORDER BY ev.Timestamp
)
SELECT LAG(cte.CommandID) OVER(ORDER BY cte.EventNumber) AS PreviousEvent,
       LAG(cte.Timestamp) OVER(ORDER BY cte.EventNumber) AS PreviousEventTimeCode,
       cte.CommandID AS CurrentEvent,
       cte.Timestamp AS CurrentEventTimeCode,
       cte.Timestamp - LAG(cte.Timestamp) OVER(ORDER BY cte.EventNumber) AS TimeDifference
  FROM cte
 WHERE cte.commandID in (1054, 2078);

核心错误：先执行筛选保留1054、2078两类确认指令，再计算LAG窗口值，导致作为前置关联条件的228弹窗行被提前过滤，窗口函数无法获取对应上一条228指令的时间，最终输出错误结果：

期望输出结果 #

正确实现逻辑 #

核心调整点：将窗口函数的计算动作前置到筛选确认指令的步骤之前，保证计算LAG时所有相关的弹窗行、确认行都在计算范围内。修正后的查询语句：

WITH event_order AS (
    SELECT 
        GameID,
        EventNumber,
        CommandID,
        Timestamp,
        -- 先在全量相关指令集合中，按赛事、事件顺序关联上一条指令的信息
        LAG(CommandID) OVER (PARTITION BY GameID ORDER BY EventNumber) AS PreviousCommandID,
        LAG(Timestamp) OVER (PARTITION BY GameID ORDER BY EventNumber) AS PreviousCommandTime
    FROM events
    WHERE GameID = 1414485
      AND CommandID IN (1054, 228, 2078)
)
SELECT
    GameID,
    PreviousCommandID AS PreviousEvent,
    PreviousCommandTime AS PreviousEventTimeCode,
    CommandID AS CurrentEvent,
    Timestamp AS CurrentEventTimeCode,
    -- 按秒计算时间差，匹配整数输出要求
    TIMESTAMPDIFF(SECOND, PreviousCommandTime, Timestamp) AS TimeDifference
FROM event_order
-- 最后一步再筛选出仅保留确认指令的行
WHERE CommandID IN (1054, 2078)
  -- 兜底校验：仅保留前置为228弹窗的关联行，避免无关前置数据误匹配
  AND PreviousCommandID = 228;

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