如何筛选各unit最新插入记录前一小时内的所有相关数据？

核心思路 #

先提取每个unit对应的最新登录时间，再关联原表筛选出该unit中登录时间在最新时间往前1小时至最新时间区间内的所有记录，全程用聚合查询+关联实现，无需循环或游标。

SQL 实现代码 #

SELECT t.*
FROM your_table t
INNER JOIN (
    -- 子查询获取每个unit的最新登录时间
    SELECT unit, MAX(login_time_utc) AS latest_login_time
    FROM your_table
    GROUP BY unit
) unit_latest ON t.unit = unit_latest.unit
-- 筛选时间在最新时间往前1小时范围内的记录
WHERE t.login_time_utc >= DATE_SUB(unit_latest.latest_login_time, INTERVAL 1 HOUR)
  AND t.login_time_utc <= unit_latest.latest_login_time;

代码说明 #

1. 子查询unit_latest：通过GROUP BY unit分组，结合MAX(login_time_utc)得到每个设备的最新登录时间，这一步替代了循环遍历每个unit的操作。
2. 关联原表：将原表与子查询结果通过unit字段关联，确保只处理对应unit的数据。
3. 时间筛选：用DATE_SUB计算最新时间往前1小时的临界值，筛选出该时间区间内的所有记录。

针对示例数据的输出结果 #

运行上述SQL后，将得到以下符合要求的记录：

+---------+------+------------------------+ 
|   unit  | temp |     login_time_utc     |
+---------+------+------------------------+
|    1    |  53  |   2022-01-24 10:02:06  |
|    1    |  62  |   2022-01-24 10:10:01  |
|    2    |  65  |   2022-01-24 16:08:59  |
|    2    |  65  |   2022-01-24 16:03:56  |
|    2    |  74  |   2022-01-24 16:06:53  |
|    3    |  83  |   2022-01-24 17:09:49  |
|    3    |  73  |   2022-01-24 18:07:46  |
|    4    |  74  |   2022-01-24 18:11:43  |
+---------+------+------------------------+

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