MySQL查询：自动计算事件标签的结束时间（基于后续标签）

表结构 #

两张表的建表语句如下：

CREATE TABLE `event_tag` (
    `event_tag_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `start` DATETIME NULL DEFAULT NULL,
    `end` DATETIME NULL DEFAULT NULL,
    `tag` VARCHAR(12) NULL DEFAULT NULL,
    `event_id` INT(10) NOT NULL,
    PRIMARY KEY (`event_tag_id`) USING BTREE,
    INDEX `fk_event_id` (`event_id`) USING BTREE,
    CONSTRAINT `fk_event_id` FOREIGN KEY (`event_id`) REFERENCES `event` (`event_id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
ENGINE=InnoDB
;

CREATE TABLE `event` (
    `event_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `start` DATETIME NOT NULL,
    `end` DATETIME NOT NULL,
    `event_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY (`event_id`) USING BTREE
)
ENGINE=InnoDB
;

业务规则 #

• event表存储事件的起始和结束时间
• 每个事件可绑定0个或多个标签，同一时间仅生效一个标签
• 标签自身有独立的起止时间，若event_tag无对应条目或tag为NULL，表示事件（或对应时段）无标签
• 标签存在堆叠规则：新条目会覆盖旧条目的重叠时间区间

现有数据 #

事件数据 #

event_id    start               end                 event_name
1234        2023-10-20 12:00:00 2023-10-20 20:00:00 bob

初始标签数据 #

event_tag_id start              end                  tag   event_id
1            NULL               NULL                 foo   1234

新增标签后的数据 #

新增一条标签（事件开始2小时后生效，持续至事件结束）：

event_tag_id start               end                  tag   event_id
2            2023-10-20 14:00:00 NULL                 bar   1234

原查询的问题 #

原查询语句：

SELECT
    et.event_tag_id,
    IFNULL(et.start, e.start) AS start,
    IFNULL(et.end, e.end) AS end_date,
    et.tag,
    et.event_id
FROM
    event_tag et
    LEFT JOIN event e 
        ON et.event_id = e.event_id
WHERE
    et.event_id = 1234
;

返回结果：

event_tag_id start               end                 tag    event_id
1            2023-10-20 12:00:00 2023-10-20 20:00:00 foo    1234
2            2023-10-20 14:00:00 2023-10-20 20:00:00 bar    1234

但期望结果是：

event_tag_id start               end                 tag    event_id
1            2023-10-20 12:00:00 2023-10-20 14:00:00 foo    1234
2            2023-10-20 14:00:00 2023-10-20 20:00:00 bar    1234

即较早条目的结束时间应为下一条目的开始时间，需要按规则计算标签的有效结束时间。

需求明确 #

需要计算每个标签的结束时间，规则如下：

• a. 若标签的end字段已设置，直接返回该时间
• b. 若end为NULL，返回同event_id下，晚于当前标签开始时间的下一个最早标签的start
• c. 若end为NULL且无后续标签，返回对应事件的end时间

MySQL实现方案 #

可以使用窗口函数LEAD()来获取同事件下下一个标签的起始时间，再结合IFNULL()嵌套处理三个规则：

SELECT
    et.event_tag_id,
    IFNULL(et.start, e.start) AS start,
    -- 按规则计算结束时间
    IFNULL(
        et.end,
        IFNULL(
            -- 获取同事件下下一个标签的最早start（晚于当前标签的start）
            LEAD(IFNULL(et.start, e.start)) OVER (
                PARTITION BY et.event_id 
                ORDER BY IFNULL(et.start, e.start) ASC
            ),
            -- 无后续标签则取事件的end
            e.end
        )
    ) AS end_date,
    et.tag,
    et.event_id
FROM
    event_tag et
LEFT JOIN event e 
    ON et.event_id = e.event_id
WHERE
    et.event_id = 1234
-- 按标签的有效起始时间排序，保证LEAD()取到正确的下一个标签
ORDER BY IFNULL(et.start, e.start) ASC;

说明 #

1. LEAD(IFNULL(et.start, e.start)) OVER (PARTITION BY et.event_id ORDER BY IFNULL(et.start, e.start) ASC)：按事件分组，以标签的有效起始时间排序，获取当前标签的下一个标签的起始时间
2. 外层的IFNULL()先判断标签自身的end是否存在，存在则直接使用；不存在则判断是否有下一个标签的起始时间，有则使用该时间；无则使用事件的end
3. 最后按标签的有效起始时间排序，确保结果顺序符合时间逻辑

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