如何在SQL表mytable中新增timetable列，按同用户20分钟间隔自动分组

核心判定规则 #

• 分组严格绑定同一用户维度，跨用户的时间间隔不参与分组计算
• 同用户下按记录时间升序排序，相邻两条记录的时间间隔≤20分钟时，归入同一个timetable分组
• 相邻记录时间间隔>20分钟时，自动开启新分组，分组ID全局连续不重复

操作步骤 #

第一步：为现有表新增timetable列 #

执行DDL语句为mytable表添加目标列，初始值设为NULL后续填充：

ALTER TABLE mytable ADD COLUMN timetable INT NULL;

第二步：计算分组值并回写更新 #

该需求属于典型的**间隙与岛屿（Gaps and Islands）**场景，通过窗口函数实现分组逻辑，核心计算逻辑为：

1. 用LAG()窗口函数按用户分组、时间排序，取出每个记录对应的同用户上一条记录的时间
2. 逐行判定是否需要开启新分组：无前置记录、与上一条同用户记录的时间差>20分钟时，标记为新组起点
3. 对所有新组起点做全局累计求和，得到连续的全局分组ID，回写到timetable列
   适配MySQL 8.0+、PostgreSQL等主流支持窗口函数的数据库的更新语句如下：

WITH grouped_calc AS (
    SELECT
        Name,
        `Date/Time`,
        SUM(
            CASE
                -- 第一条记录直接标记为新组起点
                WHEN LAG(`Date/Time`) OVER (PARTITION BY Name ORDER BY `Date/Time`) IS NULL THEN 1
                -- 同用户相邻记录差超过20分钟，标记为新组起点
                WHEN TIMESTAMPDIFF(MINUTE, LAG(`Date/Time`) OVER (PARTITION BY Name ORDER BY `Date/Time`), `Date/Time`) > 20 THEN 1
                -- 其余情况和上一条同组
                ELSE 0
            END
        ) OVER (ORDER BY `Date/Time`, Name) AS calc_group_id
    FROM mytable
)
UPDATE mytable t
-- 若表有独立主键，优先替换为主键关联，避免重复时间记录关联错误
INNER JOIN grouped_calc g
    ON t.Name = g.Name AND t.`Date/Time` = g.`Date/Time`
SET t.timetable = g.calc_group_id;

不同数据库的时间差函数略有差异，SQL Server环境下可将TIMESTAMPDIFF(MINUTE, 前置时间, 当前时间)替换为DATEDIFF(MINUTE, 前置时间, 当前时间)，逻辑完全一致。

结果验证 #

对照示例数据执行逻辑后，输出结果与预期完全匹配：

注意：累计求和的窗口为全局时间排序，不会出现同一用户分组ID重复计数的问题，保证timetable列值全局连续，符合示例输出要求。

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