基于时间间隔分区：如何对15分钟内Datetime值运行窗口函数

这需求挺实用的——不用硬套固定的时间切片（比如每小时的0-15分、15-30分），而是让记录自动“抱团”：只要两条记录的时间差在15分钟内，就归为同一个分组，之后再在分组上跑窗口函数对吧？我给你分享一个高效的实现思路，以你提供的SQL Server风格表结构为例：

先看你的示例数据 #

CREATE TABLE my_table(ID VARCHAR(5), in_time DATETIME) 
INSERT INTO my_table (ID, in_time) VALUES 
('4844', '2017-04-06 10:15:00.000'), 
('5221', '2017-11-24 11:18:00.000'), 
('5221', '2017-11-24 11:18:00.000'), 
('5221', '2017-11-25 14:23:00.000');

核心实现步骤 #

我们可以用窗口函数+累积求和的方式动态生成分组ID，全程只需要两次扫描，效率拉满：

1. 计算每条记录与前一条的时间差 #

首先按ID分组（如果不需要按ID，直接去掉PARTITION BY ID即可）、按in_time排序，用LAG函数拿到上一条记录的时间，计算两者的分钟差：

WITH ranked_data AS (
    SELECT 
        ID,
        in_time,
        -- 计算当前记录和前一条的时间差（单位：分钟）
        DATEDIFF(MINUTE, LAG(in_time) OVER (PARTITION BY ID ORDER BY in_time), in_time) AS time_diff
    FROM my_table
)

这里第一条记录的time_diff会是NULL，因为没有前序记录。

2. 动态生成分组ID #

接着用累积求和的窗口函数，只要时间差超过15分钟（或者是第一条记录），就新建一个分组，否则延续上一个分组的ID：

, grouped_data AS (
    SELECT 
        ID,
        in_time,
        -- 累积生成分组：满足条件就+1，否则继承上一个分组的编号
        SUM(CASE 
                WHEN time_diff > 15 OR time_diff IS NULL THEN 1 
                ELSE 0 
            END) OVER (
                PARTITION BY ID 
                ORDER BY in_time 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS group_id
    FROM ranked_data
)

这样一来，所有连续时间间隔在15分钟内的记录，都会拥有同一个group_id。

3. 在分组上运行窗口函数 #

现在你就可以基于ID + group_id这个分区键，任意使用窗口函数了——比如给每个分组内的记录编序号、统计分组内的记录数等等：

SELECT 
    ID,
    in_time,
    group_id,
    -- 每个分组内的行号
    ROW_NUMBER() OVER (PARTITION BY ID, group_id ORDER BY in_time) AS row_in_group,
    -- 分组内的总记录数
    COUNT(*) OVER (PARTITION BY ID, group_id) AS total_in_group
FROM grouped_data;

适配不同数据库的小细节 #

如果你的数据库不是SQL Server，只需要调整时间差的计算方式：

• PostgreSQL：用EXTRACT(EPOCH FROM (in_time - LAG(in_time) OVER (...)))/60来获取分钟差
• MySQL：用TIMESTAMPDIFF(MINUTE, LAG(in_time) OVER (...), in_time)计算分钟差

注意事项 #

• 如果不需要按ID分组，直接去掉所有PARTITION BY ID的子句，所有记录会按全局时间差来分组
• 这种方法的时间复杂度是O(n)，比递归CTE或者自连接高效得多，适合百万级以上的大表
• 要注意时间精度：如果你的时间字段带毫秒，计算时差时会自动忽略毫秒部分，不影响15分钟的判断

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