如何用SQL将同月份连续日期合并为单行，聚合起止时间？

这是典型的**间隙与孤岛（Gaps and Islands）**问题，核心是识别同一IP、同月份下的连续日期区间，再合并每组的起止时间。以下是具体实现步骤：

假设原始表结构 #

假设你的数据表名为ip_time_logs，包含字段：

• ip: 客户端IP地址（字符串类型）
• start_time: 记录开始时间（datetime/timestamp类型）
• end_time: 记录结束时间（datetime/timestamp类型）

实现SQL（以MySQL为例） #

SELECT
    ip,
    DATE_FORMAT(start_time, '%Y-%m') AS month,
    MIN(start_time) AS group_start_time,
    MAX(end_time) AS group_end_time
FROM (
    SELECT
        ip,
        start_time,
        end_time,
        -- 计算分组标识：同一连续日期组的该值相同
        DATE_SUB(
            CAST(start_time AS DATE),
            INTERVAL ROW_NUMBER() OVER(
                PARTITION BY ip, DATE_FORMAT(start_time, '%Y-%m')
                ORDER BY start_time
            ) DAY
        ) AS group_id
    FROM ip_time_logs
) AS grouped
GROUP BY ip, month, group_id
ORDER BY ip, month, group_start_time;

代码解释 #

1. 内层子查询：

   • 用PARTITION BY ip, DATE_FORMAT(start_time, '%Y-%m')将数据按IP和月份拆分
   • 用ROW_NUMBER()对每个分组内的记录按start_time排序，生成递增行号
   • 通过DATE_SUB(日期, 行号天)计算group_id：连续日期的行号递增和日期递增同步，所以这个值会保持一致；如果日期出现间隙，该值会跳变，从而区分不同的孤岛组

2. 外层查询：

   • 按ip、month、group_id分组
   • 取每组的最小start_time和最大end_time，得到合并后的连续区间

其他数据库适配 #

• PostgreSQL：把DATE_FORMAT换成TO_CHAR(start_time, 'YYYY-MM')，DATE_SUB换成CAST(start_time AS DATE) - INTERVAL '1 day' * ROW_NUMBER() OVER(...)
• SQL Server：用FORMAT(start_time, 'yyyy-MM')，DATEADD(day, -ROW_NUMBER() OVER(...), CAST(start_time AS DATE))作为group_id

注意事项 #

• 如果你的start_time和end_time跨天，需要确保连续的判断逻辑符合业务需求（比如只要前一条的end_time日期等于后一条start_time日期就算连续，还是需要时间上直接衔接？如果是后者，需要调整分组逻辑，用LAG(end_time)来判断是否连续）
• 确保ip字段的格式统一（比如避免同一IP出现大小写或不同格式的存储）

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