在时间框架内识别如果没有足够的资源

Question

我需要一个解决以下问题的方法。

假设我有一个具有给定时间框架(8:00-12:00)的组，我可以为它分配资源(人员)。每个资源都可以有一个自定义的时间框架(比如9-10，9-12,8-12等等)可能会被分配多次。

表格

群组

ID，标题，START_TIME，END_TIME，REQUIRED_PEOPLE:INTEGER

PeopleAssignments

ID，USER_ID，GROUP_ID，START_TIME，END_TIME

现在，我有一个规则，在任何给定的时间，在小组的时间框架内，必须有大约4人被分配。否则我想得到警告。

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

有没有一种不迭代整个时间框架和检查计数(赋值)> REQUIRED_PEOPLE的优雅方法吗？

Answer 1

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

范围类型提供了很好的计算函数与算子。

当存在子范围时，这些解决方案将为您提供行，其中有来自给定组的一些缺少的人员(它将给出准确的子范围&从所需的数字中丢失了多少人)。

简易方式：

你想试试类似的东西。您需要选择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

The hard way

您可以使用自定义聚合体或递归CTE实时累积结果。

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

Answer 2

在任何情况下，都需要查询DB中的赋值。没有其他方法可以找到一个组分配给人们多少次。

可能有一些方法可以找到分配的数量，但最终您必须向DB发出一个查询。

@group = Group.find(id)
if @group.people_assignments.count >= REQUIRED_PEOPLE
  pus 'warning'
end

您可以在组中添加额外的列，这些列保存该组分配给人员多少次的信息。这样，对服务器的一个查询就减少了。

@group = Group.find(id)
if @group.count_people_assigned >= REQUIRED_PEOPLE
  puts 'warning'
end

在第二种情况下，count_people_assigned是列，所以不会执行额外的查询，而在第一种情况下，people_assignments是关联的，因此会触发一个额外的查询。

但在第二种情况下，每次将组分配给其他人时，都需要更新组。最终是额外的查询。您选择要减少查询的位置。

我的意见是第二种情况，它将发生比第一次罕见。