统计指定时段内设备的最大同时预订数量（SQL）

问题需求 #

统计指定时间戳区间内，model_id为1047的设备的最大同时预订数量。

原查询的错误 #

最初的SQL查询逻辑有误，会将非重叠的订单也计入总数，导致结果不准确：

SELECT COUNT(*) 
FROM `equipment` 
WHERE model_id = 1047 
  AND wh_return >= 1678084200 
  AND wh_pickup <= 1678688999

举个例子：该查询返回3条记录，但实际最大同时预订数应为2条（id为27194和27378的订单无重叠）。尝试过EXISTS、JOIN等写法，均未得到正确结果。

正确的SQL实现 #

最终通过嵌套SQL解决了这个问题，代码如下：

select MAX(cnt) 
FROM (
   select *,
      (select count(*) 
          from equipment e2 
          where e1.wh_pickup between e2.wh_pickup and e2.wh_return
          and e2.wh_return >= 1678689000 AND e2.wh_pickup <= 1678861799
          and e1.model_id = e2.model_id
          and e2.wh_return_ok = 0
          and e2.state NOT IN ('deleted', 'preprod')) as "cnt"
   from equipment e1
   where e1.wh_return >= 1678689000 AND wh_pickup <= 1678861799
   and e1.wh_return_ok = 0
   and e1.state NOT IN ('deleted', 'preprod')
   and e1.model_id = 1047) x;

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