数据库中存储可用时段的高效结构化可查询方案咨询

作为处理过多次排班、资源调度类需求的开发者，我来分享几个经过实践验证的方案，既能满足结构化存储要求，又能高效支持你需要的查询场景。

一、推荐的数据库表结构设计 #

1. 个人可用时段表（时间段式存储） #

这种方式空间利用率高，是大部分场景的首选，核心是存储起始-结束时间对：

CREATE TABLE person_availability (
    person_id INT NOT NULL,
    target_date DATE NOT NULL, -- 存储具体日期，若需每周重复可用day_of_week(0-6)替代
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    PRIMARY KEY (person_id, target_date, start_time),
    FOREIGN KEY (person_id) REFERENCES persons(id),
    CONSTRAINT chk_valid_time CHECK (start_time < end_time)
);

• 约束chk_valid_time确保时段逻辑合法；
• 复合主键避免同一人在同一天重复添加完全相同的起始时段。

2. 机构营业时间表 #

结构和个人表适配，支持固定或临时营业时间：

CREATE TABLE business_hours (
    business_id INT NOT NULL,
    target_date DATE NOT NULL, -- 同样，每周重复可用day_of_week替代
    opening_time TIME NOT NULL,
    closing_time TIME NOT NULL,
    PRIMARY KEY (business_id, target_date),
    FOREIGN KEY (business_id) REFERENCES businesses(id),
    CONSTRAINT chk_business_time CHECK (opening_time < closing_time)
);

备选：按小时粒度存储（查询更高效） #

如果你的系统需要极高频率的每小时统计查询，可以牺牲一点存储空间换取查询速度，按小时拆分存储：

CREATE TABLE person_availability_hourly (
    person_id INT NOT NULL,
    target_date DATE NOT NULL,
    hour TINYINT NOT NULL CHECK (hour BETWEEN 0 AND 23),
    is_available BOOLEAN NOT NULL DEFAULT TRUE,
    PRIMARY KEY (person_id, target_date, hour),
    FOREIGN KEY (person_id) REFERENCES persons(id)
);

• 比如8:00-12:00的可用时段，会生成hour=8,9,10,11四条记录；
• 查询每小时可用人数时直接COUNT(*)即可，无需复杂的时段重叠判断。

二、核心查询实现 #

1. 查询机构营业时间内的所有人员可用情况 #

需要找出个人可用时段与机构营业时间的交集部分，用SQL的时段重叠判断逻辑即可：

SELECT
    p.person_id,
    p.name,
    -- 计算实际可用的交集时段
    GREATEST(pa.start_time, bh.opening_time) AS available_start,
    LEAST(pa.end_time, bh.closing_time) AS available_end
FROM
    person_availability pa
JOIN
    persons p ON pa.person_id = p.id
JOIN
    business_hours bh ON pa.target_date = bh.target_date
WHERE
    bh.business_id = 123 -- 指定机构ID
    -- 判断时段是否重叠
    AND pa.start_time < bh.closing_time
    AND pa.end_time > bh.opening_time
ORDER BY
    available_start;

2. 统计机构营业时间内每小时的可用人员数量 #

这里需要一个小时维度的辅助表（提前插入00:00到23:00的24条记录）：

-- 先创建辅助表
CREATE TABLE hourly_slots (
    hour_num TINYINT NOT NULL PRIMARY KEY,
    hour_start TIME NOT NULL,
    hour_end TIME NOT NULL
);
-- 插入全时段数据
INSERT INTO hourly_slots VALUES
(0, '00:00:00', '01:00:00'),
(1, '01:00:00', '02:00:00'),
...
(23, '23:00:00', '00:00:00'); -- 23点的结束时间对应次日0点

然后关联辅助表完成统计：

SELECT
    hs.hour_num,
    hs.hour_start,
    COUNT(DISTINCT pa.person_id) AS available_person_count
FROM
    hourly_slots hs
JOIN
    business_hours bh ON 
        hs.hour_start < bh.closing_time 
        AND hs.hour_end > bh.opening_time
LEFT JOIN
    person_availability pa ON 
        pa.target_date = bh.target_date
        AND pa.start_time < hs.hour_end
        AND pa.end_time > hs.hour_start
WHERE
    bh.business_id = 123
    AND bh.target_date = '2024-05-20' -- 指定日期
GROUP BY
    hs.hour_num, hs.hour_start
ORDER BY
    hs.hour_num;

• 如果用的是按小时粒度存储的表，查询会更简单，直接关联person_availability_hourly的hour字段即可。

三、优化技巧 #

• 索引优化：给person_availability的target_date, start_time, end_time加联合索引；给business_hours的business_id, target_date加联合索引，大幅提升查询速度；
• 应用层校验：在插入数据时避免重复或重叠的时段（比如同一人同一天的8:00-12:00和10:00-14:00），减少查询时的复杂度；
• 缓存高频查询：如果每小时人员可用数的查询非常频繁，可以把结果缓存到Redis等缓存系统，定期更新。

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