如何按记录间隔小于15分钟的规则对数据进行分组

针对你需要将时间间隔小于15分钟的连续记录归为一组并做聚合的需求，以下是具体的SQL实现（以SQL Server为例，其他数据库可微调语法）：

完整查询语句 #

WITH GroupedRecords AS (
    SELECT 
        TimeStamp,
        -- 标记是否为新组的起始：当前记录与上一条间隔超过15分钟则为1，否则为0
        CASE 
            WHEN DATEDIFF(MINUTE, LAG(TimeStamp) OVER (ORDER BY TimeStamp), TimeStamp) > 15 
            THEN 1 
            ELSE 0 
        END AS IsNewGroup
    FROM Batch
    WHERE Product = '1203'
      AND TimeStamp BETWEEN '2024-04-17 06:00:00.000' AND '2024-04-17 15:00:00.000'
),
AssignedGroups AS (
    SELECT 
        TimeStamp,
        -- 累加IsNewGroup值，得到唯一的组ID
        SUM(IsNewGroup) OVER (ORDER BY TimeStamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM GroupedRecords
)
SELECT 
    MIN(TimeStamp) AS GroupStartTime,
    MAX(TimeStamp) AS GroupEndTime,
    COUNT(TimeStamp) AS RecordCount
FROM AssignedGroups
GROUP BY GroupID
ORDER BY GroupStartTime;

关键步骤说明 #

• CTE1: GroupedRecords：用LAG()窗口函数获取当前记录的上一条时间，计算时间差是否超过15分钟，标记新组的起始点。
• CTE2: AssignedGroups：通过累加新组标记值，为每一组生成唯一的GroupID，连续的非新组记录会得到相同的GroupID。
• 最终聚合：按GroupID分组，统计每组的起始时间、结束时间和记录数量。

其他数据库适配提示 #

• MySQL：将DATEDIFF(MINUTE, ...)替换为TIMESTAMPDIFF(MINUTE, ...)，窗口函数语法一致。
• PostgreSQL：时间差计算可用EXTRACT(EPOCH FROM (TimeStamp - LAG(TimeStamp) OVER (...)))/60 > 15来判断是否超过15分钟。

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