sql - 识别在时间范围内是否没有足够的资源

Question

我需要知道如何解决以下问题。

假设我有一个给定时间范围 (8:00-12:00) 的小组，我可以为其分配资源(人员)。每个资源都可以有一个自定义时间范围(例如 9-10、9-12、8-12 等)，并且可以分配多次。

表格

组

身份证，标题，开始时间，时间结束，REQUIRED_PEOPLE:整数

人员分配

身份证，用户身份，GROUP_ID,开始时间，结束时间

现在我的规则是，在小组时间范围内的任何给定时间，必须分配 4 个人。否则我想收到警告。

我在这里使用 ruby​​ 和 sql (Postgres)。

有没有一种优雅的方法，无需遍历整个时间范围并检查 count(assignments) > REQUIRED_PEOPLE

Answer 1

你也可以只用 SQL 来解决这个问题(如果你对这样的答案感兴趣的话)。

Range types优惠很大functions and operators计算。

这些解决方案会给你行，当有子范围时，给定组中有一些缺失的人(它会告诉你它到底是哪个子范围以及所需数量中缺少多少人).

简单的方法:

您想尝试类似的东西。您需要选择一些间隔，其中 count()基于(我选择了 5 minutes ):

select     g.id group_id, i start_time, i + interval '5 minutes' end_time, g.required_people - count(a.id)
from       groups g
cross join generate_series(g.start_time, g.end_time, interval '5 minutes') i
left join  people_assignments a on a.group_id = g.id
where      tsrange(a.start_time, a.end_time) && tsrange(i, i + interval '5 minutes')
group by   g.id, i
having     g.required_people - count(a.id) > 0
order by   g.id, i

但请注意，当它们小于 5 minutes 时，您将无法检测到缺失的子范围。 . F.ex. user1 已分配给 11:00-11:56 user2 有一个 11:59-13:00 , 他们将出现在 11:00-13:00 的“组中” (因此 11:56-11:59 的缺失子范围将不会被注意到)。

注意:间隔越短(您选择的)结果就越精确(也越慢!)。

http://rextester.com/GRC64969

困难的方法:

您可以使用 custom aggregates 即时累积结果或 recursive CTEs

with recursive r as (
    -- start with "required_people" as "missing_required_people" in the whole range
    select 0 iteration,
           id group_id,
           array[]::int[] used_assignment_ids,
           -- build a json map, where keys are the time ranges
           -- and values are the number of missing people for that range
           jsonb_build_object(tsrange(start_time, end_time), required_people) required_people_per_time_range
    from   groups
    where  required_people > 0
    and    id = 1 -- query parameter
  union all
    select r.iteration + 1,
           r.group_id,
           r.used_assignment_ids || a.assignment_id,
           d.required_people_per_time_range
    from   r
    -- join a single assignment to the previous iteration, where
    -- the assigment's time range overlaps with (at least one) time range,
    -- where there is still missing people. when there are no such time range is
    -- found in assignments, the "recursion" (which is really just a loop) stops
    cross join lateral (
      select     a.id assignment_id, tsrange(start_time, end_time) time_range
      from       people_assignments a
      cross join (select key::tsrange time_range from jsonb_each(r.required_people_per_time_range)) j
      where      a.group_id = r.group_id
      and        a.id <> ALL (r.used_assignment_ids)
      and        tsrange(start_time, end_time) && j.time_range
      limit      1
    ) a
    -- "partition" && accumulate all remaining time ranges with
    -- the one found in the previous step
    cross join lateral (
      -- accumulate "partition" results
      select jsonb_object_agg(u.time_range, u.required_people) required_people_per_time_range
      from   (select key::tsrange time_range, value::int required_people
              from   jsonb_each_text(r.required_people_per_time_range)) j
      cross join lateral (
        select u time_range, j.required_people - case when u && a.time_range then 1 else 0 end required_people
        -- "partition" the found time range with all existing ones, one-by-one
        from   unnest(case
                 when j.time_range @> a.time_range
                 then array[tsrange(lower(j.time_range), lower(a.time_range)), a.time_range, tsrange(upper(a.time_range), upper(j.time_range))]
                 when j.time_range && a.time_range
                 then array[j.time_range * a.time_range, j.time_range - a.time_range]
                 else array[j.time_range]
               end) u
        where not isempty(u)
      ) u
    ) d
),
-- select only the last iteration
l as (
  select   group_id, required_people_per_time_range
  from     r
  order by iteration desc
  limit    1
)
-- unwind the accumulated json map
select     l.group_id, lower(time_range) start_time, upper(time_range) end_time, missing_required_people
from       l
cross join lateral (
  select key::tsrange time_range, value::int missing_required_people
  from   jsonb_each_text(l.required_people_per_time_range)
) j
-- select only where there is still some missing people
-- this is optional, if you omit it you'll also see row(s) for sub-ranges where
-- there is enough people in the group (these rows will have zero,
-- or negative amount of "missing_required_people")
where      j.missing_required_people > 0

http://rextester.com/GHPD52861