求助：查询指定时间段内可用BookingSlots的SQL语句优化

问题分析 #

原SQL存在两个核心问题：

1. 运算符优先级错误：AND的优先级高于OR，原条件等价于 (条件1) OR (条件2 AND status='free')，而非预期的 (条件1 OR 条件2) AND status='free'，导致userId=2的free slot被错误筛选出来。
2. 未校验同一用户的后续slot状态：需求要求仅返回那些用户的当前查询时间所在slot及下一个15分钟slot均为free的记录，但原SQL未关联检查同一用户的其他slot状态。

修正后的SQL（通用版） #

以下方案通过子查询先筛选出符合条件的用户，再关联主表返回对应slot，同时动态计算目标slot的时间范围，避免硬编码：

SELECT bs.*
FROM "BookingSlot" bs
-- 子查询筛选出拥有连续两个目标free slot的用户
JOIN (
    SELECT userId
    FROM "BookingSlot"
    WHERE startTime IN (
        -- 计算查询时间所在的slot的startTime
        date_trunc('hour', '2022-01-01T01:08:00'::timestamp) 
        + interval '15 minutes' * floor(
            extract(minute from '2022-01-01T01:08:00'::timestamp)/15
        ),
        -- 计算下一个slot的startTime
        date_trunc('hour', '2022-01-01T01:08:00'::timestamp) 
        + interval '15 minutes' * ceil(
            extract(minute from '2022-01-01T01:08:00'::timestamp)/15
        )
    )
    AND status = 'free'
    GROUP BY userId
    HAVING COUNT(*) = 2 -- 确保两个slot都为free
) valid_users ON bs.userId = valid_users.userId
-- 限定返回目标时间范围内的slot
WHERE bs.startTime IN (
    date_trunc('hour', '2022-01-01T01:08:00'::timestamp) 
    + interval '15 minutes' * floor(
        extract(minute from '2022-01-01T01:08:00'::timestamp)/15
    ),
    date_trunc('hour', '2022-01-01T01:08:00'::timestamp) 
    + interval '15 minutes' * ceil(
        extract(minute from '2022-01-01T01:08:00'::timestamp)/15
    )
)
AND bs.status = 'free';

逻辑说明 #

1. 动态计算目标slot：通过date_trunc、floor和ceil函数，自动定位查询时间所在的15分钟slot及下一个slot的起始时间，无需手动指定时间点。
2. 筛选有效用户：子查询统计每个用户在目标slot中的free记录数，仅保留拥有2条free记录的用户（即两个slot均可用）。
3. 返回目标记录：关联主表，仅返回有效用户的目标slot记录，确保结果符合预期。

简化版（针对固定时间） #

如果不需要动态计算时间，也可以直接指定目标slot的起始时间，逻辑更直观：

SELECT bs.*
FROM "BookingSlot" bs
JOIN (
    SELECT userId
    FROM "BookingSlot"
    WHERE startTime IN ('2022-01-01T01:00:00', '2022-01-01T01:15:00')
    AND status = 'free'
    GROUP BY userId
    HAVING COUNT(*) = 2
) valid_users ON bs.userId = valid_users.userId
WHERE bs.startTime IN ('2022-01-01T01:00:00', '2022-01-01T01:15:00')
AND bs.status = 'free';

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