如何在MySQL中按JobId、StartTime、EndTime分组连续日期并聚合Id？

解决方案 #

这是典型的日期「孤岛」类分组需求，你使用的MySQL 8.0版本原生支持窗口函数，可直接使用如下查询实现，假设你的原始表名为job_records，如果实际表名不同请自行替换：

-- 可以提前调整GROUP_CONCAT长度限制，避免ID数量较多时被截断
-- SET SESSION group_concat_max_len = 102400;

WITH ranked_records AS (
    SELECT 
        Id,
        JobId,
        Date,
        StartTime,
        EndTime,
        -- 按核心分组字段分区后按日期排序，连续日期会得到相同的分组标记
        DATE_SUB(Date, INTERVAL ROW_NUMBER() OVER (
            PARTITION BY JobId, StartTime, EndTime 
            ORDER BY Date ASC
        ) DAY) AS group_flag
    FROM job_records
),
grouped_result AS (
    SELECT 
        JobId,
        MIN(Date) AS StartDate,
        MAX(Date) AS EndDate,
        StartTime,
        EndTime,
        GROUP_CONCAT(Id ORDER BY Id SEPARATOR ',') AS Ids,
        COUNT(*) AS date_cnt
    FROM ranked_records
    GROUP BY JobId, StartTime, EndTime, group_flag
)
-- 过滤掉仅含单个日期、不构成连续区间的分组
SELECT JobId, StartDate, EndDate, StartTime, EndTime, Ids
FROM grouped_result
WHERE date_cnt >= 2;

逻辑说明 #

1. 第一层CTEranked_records对相同JobId、StartTime、EndTime的记录按日期升序编号，用日期减去编号对应的天数得到分组标记，连续的日期会生成完全相同的标记，以此自动识别连续日期区间。
2. 第二层CTEgrouped_result按核心字段+分组标记聚合，得到每个连续区间的起止日期、聚合后的ID列表，同时统计区间覆盖的日期总数。
3. 最后过滤掉仅含1天的分组，符合你要求的仅保留连续区间的规则，如果允许单个日期的分组可以去掉最后的WHERE date_cnt >=2条件。

如果你的Date字段是datetime类型，需要在所有用到Date的地方替换为DATE(Date)先转为日期类型即可。

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