如何用SQL按20秒时间窗口分组Timestamp并添加分组标识

我现有如下SQL查询，可返回指定时间范围内的data_timestamp列表：

select data_timestamp 
  from table1
 where data_timestamp > :input_start_ts
   and data_timestamp < :input_end_ts;

输出结果：

2024-07-10 10:21:10
2024-07-10 10:21:18
2024-07-10 10:21:21
2024-07-10 10:21:22
2024-07-10 10:21:23
2024-07-10 10:21:25
2024-07-10 10:21:26
2024-07-10 10:21:29
2024-07-10 10:21:30
2024-07-10 10:21:31
2024-07-10 10:21:32
2024-07-10 10:21:40
2024-07-10 10:21:49
2024-07-10 10:21:55
2024-07-10 10:21:56
2024-07-10 10:22:01
2024-07-10 10:22:40

我需要将这些Timestamp按20秒窗口分组，并添加一个分组标识列，预期输出如下：

2024-07-10 10:21:10    1
2024-07-10 10:21:18    1
2024-07-10 10:21:21    1
2024-07-10 10:21:22    1
2024-07-10 10:21:23    1
2024-07-10 10:21:25    1
2024-07-10 10:21:26    1
2024-07-10 10:21:29    1
2024-07-10 10:21:30    2
2024-07-10 10:21:31    2
2024-07-10 10:21:32    2
2024-07-10 10:21:40    2
2024-07-10 10:21:49    2
2024-07-10 10:21:55    3
2024-07-10 10:21:56    3
2024-07-10 10:22:01    3
2024-07-10 10:22:40    4

实现方案 #

核心思路是将每个data_timestamp转换为对应的20秒窗口起始时间，再根据该起始时间生成连续的分组标识。以下是主流数据库的具体实现：

1. MySQL/MariaDB #

利用UNIX_TIMESTAMP()将时间转为秒数，除以20取整后作为窗口标识，再通过DENSE_RANK()生成分组ID：

SELECT 
  data_timestamp,
  DENSE_RANK() OVER (ORDER BY FLOOR(UNIX_TIMESTAMP(data_timestamp)/20)) AS group_id
FROM table1
WHERE data_timestamp > :input_start_ts
  AND data_timestamp < :input_end_ts
ORDER BY data_timestamp;

2. PostgreSQL #

通过EXTRACT(EPOCH FROM ...)获取时间的秒数，结合FLOOR()计算所属窗口，再生成排名：

SELECT 
  data_timestamp,
  DENSE_RANK() OVER (ORDER BY FLOOR(EXTRACT(EPOCH FROM data_timestamp)/20)) AS group_id
FROM table1
WHERE data_timestamp > :input_start_ts
  AND data_timestamp < :input_end_ts
ORDER BY data_timestamp;

3. SQL Server #

使用DATEDIFF()计算从基准时间到当前时间的秒数，除以20取整后作为窗口依据：

SELECT 
  data_timestamp,
  DENSE_RANK() OVER (ORDER BY DATEDIFF(second, '1970-01-01', data_timestamp)/20) AS group_id
FROM table1
WHERE data_timestamp > :input_start_ts
  AND data_timestamp < :input_end_ts
ORDER BY data_timestamp;

4. Oracle #

通过截断分钟时间，再加上按20秒分段的偏移量确定窗口起始时间，最后生成分组ID：

SELECT 
  data_timestamp,
  DENSE_RANK() OVER (ORDER BY 
    TRUNC(data_timestamp, 'MI') + 
    FLOOR(EXTRACT(SECOND FROM data_timestamp)/20)*NUMTODSINTERVAL(20, 'SECOND')
  ) AS group_id
FROM table1
WHERE data_timestamp > :input_start_ts
  AND data_timestamp < :input_end_ts
ORDER BY data_timestamp;

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