如何在SQL中生成符合时间条件的分组编号（类ROW_NUMBER逻辑）

这个需求属于典型的**间隙与孤岛（Gaps and Islands）**问题——我们要把同一Agent下时间间隔小于6小时的记录归为同一组，生成连续的分组编号，而不是单纯的行号。下面是具体的实现方案：

核心思路 #

我们可以通过两步逻辑来实现：

1. 用窗口函数LAG()获取同Agent的上一条记录的时间，判断当前记录是否需要开启新组（要么是该Agent的第一条记录，要么和上一条记录的时间差≥6小时）。
2. 对“新组标记”做累加求和，按Agent分区、时间排序，这样就能得到我们需要的分组编号。

通用SQL实现（以SQL Server为例） #

假设你的表名为agent_log，字段为name（Agent名称）、date_time（记录时间），SQL代码如下：

SELECT
    name,
    date_time,
    SUM(group_flag) OVER (PARTITION BY name ORDER BY date_time) AS group_number
FROM (
    SELECT
        name,
        date_time,
        -- 标记是否开启新组
        CASE
            -- 该Agent的第一条记录，直接开启新组
            WHEN LAG(date_time) OVER (PARTITION BY name ORDER BY date_time) IS NULL THEN 1
            -- 当前记录与上一条时间差≥6小时，开启新组
            WHEN DATEDIFF(HOUR, LAG(date_time) OVER (PARTITION BY name ORDER BY date_time), date_time) >= 6 THEN 1
            -- 否则属于当前组，标记为0
            ELSE 0
        END AS group_flag
    FROM agent_log
) AS subquery
ORDER BY name, date_time;

不同数据库的适配调整 #

不同SQL方言的时间差函数略有不同，这里给出常见数据库的适配版本：

MySQL版本 #

SELECT
    name,
    date_time,
    SUM(group_flag) OVER (PARTITION BY name ORDER BY date_time) AS group_number
FROM (
    SELECT
        name,
        date_time,
        CASE
            WHEN LAG(date_time) OVER (PARTITION BY name ORDER BY date_time) IS NULL THEN 1
            WHEN TIMESTAMPDIFF(HOUR, LAG(date_time) OVER (PARTITION BY name ORDER BY date_time), date_time) >= 6 THEN 1
            ELSE 0
        END AS group_flag
    FROM agent_log
) AS subquery
ORDER BY name, date_time;

PostgreSQL版本 #

SELECT
    name,
    date_time,
    SUM(group_flag) OVER (PARTITION BY name ORDER BY date_time) AS group_number
FROM (
    SELECT
        name,
        date_time,
        CASE
            WHEN LAG(date_time) OVER (PARTITION BY name ORDER BY date_time) IS NULL THEN 1
            WHEN EXTRACT(HOUR FROM date_time - LAG(date_time) OVER (PARTITION BY name ORDER BY date_time)) >= 6 THEN 1
            ELSE 0
        END AS group_flag
    FROM agent_log
) AS subquery
ORDER BY name, date_time;

效果验证 #

拿你给出的示例数据来说：

A 01/04/2018 10:00 → group_flag=1 → group_number=1
A 01/04/2018 13:00 → 和前一条差3小时 <6 → group_flag=0 → group_number=1
A 01/04/2018 14:00 → 和前一条差1小时 <6 → group_flag=0 → group_number=1
A 01/04/2018 15:00 → 和前一条差1小时 <6 → group_flag=0 → group_number=1
如果下一条是A 01/04/2018 21:00 → 和前一条差6小时 → group_flag=1 → group_number=2

完全符合你的需求。

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