如何提取Motion表中不在对应工位预约时段内的datetime数据

核心问题梳理 #

你的需求是筛选所有不属于对应工位有效预约时间段的传感器采集记录，原有SQL存在两个核心问题：

1. 关联条件逻辑错误：仅判断了采集时间不小于预约开始时间的分钟部分，既没有校验采集时间是否早于预约结束时间，也没有处理跨天预约场景，多层EXTRACT拆分时间判断的写法效率极低且极易出错。
2. 缺少结果过滤逻辑：你要找的是没有对应有效预约的传感器数据，需要筛选右连接后预约表关联字段为空的记录。

修改后查询语句 #

query = """
SELECT 
  reservation.user_id, 
  motion.desk_id, 
  reservation.start_ts, 
  reservation.end_ts, 
  reservation.confirm_ts, 
  reservation.cancel_ts, 
  motion.datetime
FROM Reservation_table reservation
RIGHT JOIN Motion_table motion 
ON reservation.desk_id = motion.desk_id
-- 直接判断采集时间是否落在有效预约的时间区间内
AND motion.datetime BETWEEN reservation.start_ts AND reservation.end_ts
-- 排除已取消的预约，不需要该规则可直接删掉下一行
AND reservation.cancel_ts IS NULL
-- 筛选没有匹配到有效预约的记录，即目标结果
WHERE reservation.desk_id IS NULL
ORDER BY motion.datetime;
"""

补充说明 #

• 以上写法已经覆盖两种异常场景：员工未预约直接到岗、员工有预约但实际到岗时间不在预约区间内。
• 去掉EXTRACT拆分时间的写法可以大幅提升查询效率，同时避免跨天预约、夏令时等特殊场景的判断错误。

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