如何修复Postgres理发店预约排班函数的可用时段漏显问题？

项目背景 #

正在开发的理发店客户预约排期项目，店内提供的服务及时长如下：

name              service_time
______________________________
| Haircut         | 00:30:00 |
| Beard           | 00:15:00 |
| Eyebrow         | 00:45:00 |
------------------------------

原有PostgreSQL数据库函数用于生成客户可选的预约时段，函数逻辑遵循以下规则：

• 仅展示营业时间范围内的时段
• 排除理发师午餐休息时段
• 排除周日的所有时段
• 剔除已有预约占用的时段，避免预约冲突

原有函数代码如下：

CREATE OR REPLACE FUNCTION free_hours(start_work TIME, start_launch_time TIME, stop_launch_time TIME, stop_work TIME)
RETURNS TABLE(step INTERVAL, free_hours TIMESTAMP WITHOUT TIME ZONE)
AS $$
  SELECT step::INTERVAL, free_hours FROM
  UNNEST(ARRAY ['15 min', '30 min', '45 min', '60 min']) AS t(step)
  CROSS JOIN LATERAL
  GENERATE_SERIES(
     '2021-10-20'::date + start_work,
     '2021-10-20'::date + stop_work + INTERVAL '1 day',
     step::INTERVAL
  ) AS free_hours
  WHERE free_hours::TIME NOT BETWEEN start_launch_time AND stop_launch_time
  AND free_hours::TIME >= start_work AND free_hours::TIME <= stop_work
  AND EXTRACT(dow FROM free_hours) <> 0 -- Sunday
  AND free_hours NOT IN (
    SELECT GENERATE_SERIES(
      a.created_at::TIMESTAMP - (step::INTERVAL - '1 min'::INTERVAL)::TIME,
      a.created_at,
      INTERVAL '1 min'
    ) as hours_to_remove
    FROM appointments a
    INNER JOIN services s ON a.service_id = s.id
  )
  AND free_hours NOT IN (
    SELECT GENERATE_SERIES(
      a.created_at,
      a.created_at::TIMESTAMP + s.service_time::TIME - '00:01:00'::TIME,
      INTERVAL '1 min'
    ) as hours_to_remove
    FROM appointments a
    INNER JOIN services s ON a.service_id = s.id
  )
$$
LANGUAGE SQL
IMMUTABLE

存在的问题 #

原有函数逻辑存在缺陷：函数会按所有服务时长对应的步长（15min、30min、45min、60min）分别生成时间序列，比如45分钟的服务就按45分钟为间隔生成可选时点。
举个实际场景：如果预约了09:30开始的Haircut服务，时长30分钟，10:00结束，此时10:00本可以预约时长45分钟的Eyebrow服务，但因为45分钟步长的序列是按间隔45分钟生成时点，10:00不在该序列范围内，仅会展示10:30的可选时段，导致合理的可用时段无法正常展示。

优化方案 #

核心思路 #

所有服务时长均为15分钟的整数倍，因此统一使用最小粒度15分钟作为步长生成所有备选时间点，再为每个时间点匹配所有可预约的服务时长，即可覆盖所有合法的预约起始点，不会出现遗漏。

优化后的函数代码 #

CREATE OR REPLACE FUNCTION free_hours(start_work TIME, start_launch_time TIME, stop_launch_time TIME, stop_work TIME)
RETURNS TABLE(service_duration INTERVAL, available_time TIMESTAMP WITHOUT TIME ZONE)
AS $$
WITH all_valid_time_points AS (
  -- 统一按最小步长15分钟生成所有符合基础规则的时间点
  SELECT free_hours
  FROM GENERATE_SERIES(
     '2021-10-20'::date + start_work,
     '2021-10-20'::date + stop_work + INTERVAL '1 day',
     INTERVAL '15 min'
  ) AS free_hours
  WHERE free_hours::TIME NOT BETWEEN start_launch_time AND stop_launch_time
  AND free_hours::TIME >= start_work 
  AND free_hours::TIME <= stop_work
  AND EXTRACT(dow FROM free_hours) <> 0 -- 排除周日
),
occupied_time AS (
  -- 收集所有已被预约占用的时间点
  SELECT DISTINCT GENERATE_SERIES(
    a.created_at,
    a.created_at + s.service_time - INTERVAL '1 min',
    INTERVAL '1 min'
  ) as occupied
  FROM appointments a
  INNER JOIN services s ON a.service_id = s.id
),
available_base_points AS (
  -- 过滤出未被占用的基础时间点
  SELECT free_hours FROM all_valid_time_points
  WHERE free_hours NOT IN (SELECT occupied FROM occupied_time)
),
all_service_durations AS (
  -- 提取所有支持的服务时长
  SELECT UNNEST(ARRAY ['15 min'::INTERVAL, '30 min'::INTERVAL, '45 min'::INTERVAL, '60 min'::INTERVAL]) AS duration
)
-- 匹配每个时间点可支持的服务时长：检查从该点开始的服务时长范围内没有被占用的时间
SELECT s.duration as service_duration, a.free_hours as available_time
FROM available_base_points a
CROSS JOIN all_service_durations s
WHERE NOT EXISTS (
  SELECT 1 FROM occupied_time o
  WHERE o.occupied BETWEEN a.free_hours AND a.free_hours + s.duration - INTERVAL '1 min'
)
-- 额外校验服务结束时间不超过当天打烊时间
AND (a.free_hours + s.duration)::TIME <= stop_work
$$
LANGUAGE SQL
STABLE;

优化点说明 #

1. 统一用15分钟最小粒度生成时间点，不会遗漏任何合法的预约起始时间，彻底解决非步长倍数时点被漏掉的问题
2. 预计算所有被占用的时间点，后续校验直接复用，避免重复计算，性能更优
3. 对每个可用的基础时间点，逐一校验是否能容纳对应时长的服务，确保服务全程没有时段冲突，同时新增校验服务结束时间不超过当天打烊时间，逻辑更严谨
4. 原函数标注为IMMUTABLE不符合PostgreSQL函数属性规范，因为函数依赖appointments表的数据，数据变更后结果会变，调整为STABLE更合理

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