SQL实现按每日8点至次日8点班次分组为记录分配批次序号

你操作的表是inputtable，字段如下：

• equipment_id：int类型，存储设备ID
• telemetry_time：timestamp类型，存储遥测数据的上报时间

需要实现的计算规则：

1. 班次周期固定为当日早8:00 到 次日早8:00，跨自然日的同时间段记录归为同一个班次
2. 每个班次内的记录按telemetry_time从早到晚排序，从1开始分配连续整数，作为Batch字段的值

不用写复杂的多层时间判断，核心技巧是把每条记录的上报时间往前偏移8小时再取日期，就能自动把同个班次的记录归为同一组：

• 当日8点之后的记录，偏移8小时后还是当日日期
• 次日0点到8点的记录，偏移8小时后仍然是前一天的日期，自然和前一天8点后的记录分到同一组
  分组完成后用窗口函数按时间排序取行号，就是需要的连续Batch编号。

标准SQL写法（适配PostgreSQL、BigQuery等支持标准间隔语法的数据库） #

SELECT
  equipment_id,
  telemetry_time,
  ROW_NUMBER() OVER (
    PARTITION BY
      equipment_id,
      DATE(telemetry_time - INTERVAL '8 hour') -- 生成班次分组标识
    ORDER BY telemetry_time ASC
  ) AS Batch
FROM inputtable;

MySQL适配写法 #

SELECT
  equipment_id,
  telemetry_time,
  ROW_NUMBER() OVER (
    PARTITION BY
      equipment_id,
      DATE(DATE_SUB(telemetry_time, INTERVAL 8 HOUR)) -- 生成班次分组标识
    ORDER BY telemetry_time ASC
  ) AS Batch
FROM inputtable;

拿3条边界时间的记录举例，分组结果完全符合班次规则：

1. 记录时间：2024-05-20 07:59:00 → 减8小时为2024-05-19 23:59:00 → 归属5月19日班次（5月19日8:00-5月20日8:00）
2. 记录时间：2024-05-20 08:00:00 → 减8小时为2024-05-20 00:00:00 → 归属5月20日班次（5月20日8:00-5月21日8:00）
3. 记录时间：2024-05-21 07:59:00 → 减8小时为2024-05-20 23:59:00 → 归属5月20日班次，和第二条记录同组排序编号

如果你已经搭建好了样例测试环境，直接运行对应数据库版本的代码，就能得到和预期一致的输出结果。

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