主页 / dba / 问题 / 318668
Accepted
ivan
Asked: 2022-10-25 19:06:02 +0800 CST

禁止循环（例如每周）计划中的重叠间隔

  • 772

我正在设计一个 postgres 表来记录每周计划中包含的间隔。它会保存多个企业的时间表，一个简单的示例数据集可能如下所示：

business_id  interval
-----------  -----------------------------------
1            Sunday   10:00:00 – Sunday 14:00:00
1            Sunday   22:00:00 – Monday 02:00:00
1            Friday   11:00:00 – Friday 16:00:00
1            Saturday 15:00:00 – Sunday 01:00:00

请注意，间隔可以跨越日期之间的界限。

一个企业不应该有重叠的时间间隔，我想以一种可以让我强制执行的方式设计表格。

我正在考虑将这些星期几 + 一天中的时间值映射到自一周开始以来的相应秒数，将间隔存储为int4range并使用排除约束来禁止重叠的整数范围，但这并不合适地址间隔围绕周末结束。

有没有一种好的方法可以对这种周期性数据进行建模并禁止重叠？

postgresql

2 个回答

  1. Best Answer

    将每个星期几 + 时间值映射到自间隔开始的一周开始以来的相应秒数，将间隔存储在一int4range列中，并添加两个排除约束。

    第一个排除约束禁止在周末不回绕的情况下重叠的时间间隔：

    ALTER TABLE weekly_intervals
ADD CONSTRAINT exclude_overlapping_intervals
EXCLUDE USING GIST (
  business_id WITH =,
  interval WITH &&
)
DEFERRABLE INITIALLY DEFERRED

    第二个排除约束禁止由于其中一个在周末结束而重叠的间隔：

    -- full week: 7 * 24 * 60 * 60 = 604800

ALTER TABLE weekly_intervals
ADD CONSTRAINT exclude_overlapping_intervals_wraparound
EXCLUDE USING GIST (
  business_id WITH =,
  (
    CASE WHEN upper(interval) > 604800
      THEN int4range(
             lower(interval) - 604800,
             upper(interval) - 604800,
            '[)'
           )
      ELSE interval
    END
  ) WITH &&
)
DEFERRABLE INITIALLY DEFERRED

    排除约束是有趣的部分，但为了完整起见，表本身看起来像这样：

    CREATE TABLE weekly_intervals (
    business_id bigint NOT NULL,
    interval int4range NOT NULL,
    CONSTRAINT interval_duration_max_one_week CHECK (upper(interval) <= lower(interval) + 604800),
    CONSTRAINT interval_left_closed CHECK (lower_inc(interval)),
    CONSTRAINT interval_right_open CHECK (NOT upper_inc(interval)),
    CONSTRAINT interval_start CHECK (lower(interval) <@ int4range(0, 604800, '[)'))
);
    • 1

  2. 这是 PostgreSQL 类型的完美用例RANGE。下面的所有代码都可以在此处的小提琴上找到。

    在这里可以找到一篇关于它们的优秀文章（作者 Dimitri Fontaine）——主要要注意的是：

    主要示例是 daterange 数据类型，它将 alower和upper范围的边界存储为单个值。

    需要注意的关键是范围是有序的——第一个值小于第二个值，所以不用担心在它们完成后开始的无意义的约会（而且我已经看到实际上允许这样做的系统！）。

    可以在此处找到一篇关于如何使用它们“将 100 行 SQL 变成 3 行”的有趣帖子（nb多范围类型）。

    简单示例表：

    CREATE TABLE test
(
  business_id INT NOT NULL,
  intval      TSTZRANGE,
  EXCLUDE USING GIST (business_id WITH =, intval WITH &&)
);

    所以，这告诉我们，没有人可以与另一个具有相同（人，资源......）的人intval重叠。 business_id

    去测试：

    INSERT INTO test VALUES
(1, '[2022-01-01 11:30, 2022-01-01 15:00)');

    接着：

    --
-- Non-overlapping
-- 

INSERT INTO test VALUES
(1, '[2022-01-01 16:30, 2022-01-01 18:00)');

    没问题！

    但！

    --
-- Overlapping!
--

INSERT INTO test VALUES
(1, '[2022-01-01 10:30, 2022-01-01 13:00)');

    我们得到（正确）：

    错误：冲突的键值违反了排除约束“test_business_id_intval_excl”详细信息：键（business_id，intval）=（1，[“2022-01-01 10:30:00 + 00”，“2022-01-01 13:00:00” +00")) 与现有密钥冲突 (business_id, intval)=(1, ["2022-01-01 11:30:00+00","2022-01-01 15:00:00+00")) .

    现在有一个不同的business_id，但与之前的时间表重叠：

    --
-- Interval overlaps, but business_id (person, other resource
-- doesn't - no problem!
--

INSERT INTO test VALUES
(2, '[2022-01-01 11:30, 2022-01-01 15:00)');

    结果（如预期）：

    INSERT 0 1

    无需担心周日、周一或时间间隔会超过日、周、月或年的界限。这些范围类型非常强大，非常值得去了解。有关函数和运算符的列表，请参见此处。

    根据 OP 的评论，这里有一些代码可以设置某种班次安排。它可能会用 PL/pgSQL 进行一些处理来整理它，对于重复的计划，作为基线，它可能会有所帮助。

    你的评论说（大概是作为一个例子）"Mondays 9AM to 5PM, Tuesdays 10AM to 6PM, ..，所以我已经迎合了那个例子 - 你显然可以为更复杂的场景添加你自己的代码。你也可以使用上面概述的范围数据类型。

    SELECT
  d.i,
  h.i,
 '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL AS "Slot",
  EXTRACT(DOW FROM  '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) AS "Day num",
  TO_CHAR( '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL, 'DAY') AS "Day name",
  CASE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   1 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >=  9 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 17 THEN TRUE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   2 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >= 10 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 18 THEN TRUE
    ELSE FALSE
  END AS "Shift"
FROM 
  GENERATE_SERIES(0,  6) AS d(i),  --  <<-- generates hourly slots for weeks/months... on end
  GENERATE_SERIES(0, 23) AS h(i)

LIMIT 150;                        -- don't want to overload db<>fiddle - this shows the example shifts.

    结果（为简洁起见被剪掉）：

    i   i   Slot    Day num     Day name    Shift
0   0   2022-10-31 00:00:00     1   MONDAY  f
0   1   2022-10-31 01:00:00     1   MONDAY  f
0   2   2022-10-31 02:00:00     1   MONDAY  f
0   3   2022-10-31 03:00:00     1   MONDAY  f
0   4   2022-10-31 04:00:00     1   MONDAY  f
0   5   2022-10-31 05:00:00     1   MONDAY  f
0   6   2022-10-31 06:00:00     1   MONDAY  f
0   7   2022-10-31 07:00:00     1   MONDAY  f
0   8   2022-10-31 08:00:00     1   MONDAY  f
0   9   2022-10-31 09:00:00     1   MONDAY  t
0   10  2022-10-31 10:00:00     1   MONDAY  t
0   11  2022-10-31 11:00:00     1   MONDAY  t
0   12  2022-10-31 12:00:00     1   MONDAY  t

    因此，您可以在这里看到（如果您检查小提琴）周一的 09:00 到 17:00 是轮班时间，然后是周二的 10:00 到 18:00 是轮班时间。

    • 0

相关问题