postgresql - Tsrange - 根据id字段减去两个表之间的差异

Question

我有两张 table 。 free_time 和 appointment。我想从空闲时间记录中减去所有约会记录。结果将是一组记录，其中还有空闲时间。约会保证存在于 free_time 范围内。我想进行此查询 WHERE doctor_id = 1。

他们有以下记录:

CREATE TABLE free_time
AS
  SELECT freetime::tsrange, doctor_id
  FROM ( VALUES
    ('[2017-04-19 09:00, 2017-04-19 12:30)', 1),
    ('[2017-04-19 13:30, 2017-04-19 15:30)', 1),
    ('[2017-04-20 08:30, 2017-04-20 16:30)', 1),
    ('[2017-04-19 09:00, 2017-04-19 16:30)', 2)
  ) AS t(freetime, doctor_id);


CREATE TABLE appointment
AS
  SELECT appointment::tsrange, doctor_id
  FROM ( VALUES
    ('[2017-04-19 10:30, 2017-04-19 11:30)', 1),
    ('[2017-04-19 13:30, 2017-04-19 14:30)', 1),
    ('[2017-04-20 10:30, 2017-04-20 13:30)', 1),
    ('[2017-04-20 14:30, 2017-04-20 16:30)', 1),
    ('[2017-04-19 10:30, 2017-04-19 11:30)', 2)
  ) AS t(appointment, doctor_id);

结果集应该是这样的:

["2017-04-19 09:00:00","2017-04-19 10:30:00"),
["2017-04-19 11:30:00","2017-04-19 12:30:00"),
["2017-04-19 14:30:00","2017-04-19 15:30:00"),
["2017-04-20 08:30:00","2017-04-20 10:30:00"),
["2017-04-20 13:30:00","2017-04-20 14:30:00"),

Answer 1

有两个函数(来 self 的 older answer 并做了小的修复):

create or replace function range_exclude(anyelement, anyelement) returns anyarray as $$
declare
  r1 text;
  r2 text;
begin
  -- Check input parameters
  if not pg_typeof($1) in ('numrange'::regtype, 'int8range'::regtype, 'daterange'::regtype, 'tsrange'::regtype, 'tstzrange'::regtype) then
    raise exception 'Function accepts only range types but got % type.', pg_typeof($1);
  end if;

  -- If result is single element
  if ($1 &< $2 or $1 &> $2) then
    return array[$1 - $2];
  end if;

  -- Else build array of two intervals
  if lower_inc($1) then r1 := '['; else r1 := '('; end if;
  r1 := r1 || lower($1) || ',' || lower($2);
  if lower_inc($2) then r1 := r1 || ')'; else r1 := r1 || ']'; end if;

  if upper_inc($2) then r2 := '('; else r2 := '['; end if;
  r2 := r2 || upper($2) || ',' || upper($1);
  if upper_inc($1) then r2 := r2 || ']'; else r2 := r2 || ')'; end if;
  return array[r1, r2];
end $$ immutable language plpgsql;

create or replace function range_exclude(anyelement, anyarray) returns anyarray as $$
declare
  i int;
  j int;
begin
  -- Check input parameters
  if not pg_typeof($1) in ('numrange'::regtype, 'int8range'::regtype, 'daterange'::regtype, 'tsrange'::regtype, 'tstzrange'::regtype) then
    raise exception 'Function accepts only range types but got % type.', pg_typeof($1);
  end if;

  if array_length($2,1) is null then
    return array[$1];
  end if;

  $0 := range_exclude($1,$2[array_lower($2,1)]);
  for i in array_lower($2,1) + 1 .. array_upper($2,1) loop
    select array(select x from (select unnest(range_exclude(x,$2[i])) from unnest($0) as t(x)) as t(x) where not isempty(x)) into $0;
  end loop;
  return $0;
end $$ immutable language plpgsql;

之后您的查询可能是:

with t as (
  select ft.doctor_id, freetime, range_exclude(freetime, array_agg(appointment)) as ex 
  from free_time ft join appointment ap on (ft.doctor_id = ap.doctor_id)
  group by ft.doctor_id, freetime)
select doctor_id, unnest(ex) from t order by 1,2;

结果:

╔═══════════╤═══════════════════════════════════════════════╗║ doctor_id │                    unnest                     ║╠═══════════╪═══════════════════════════════════════════════╣║         1 │ ["2017-04-19 09:00:00","2017-04-19 10:30:00") ║║         1 │ ["2017-04-19 11:30:00","2017-04-19 12:30:00") ║║         1 │ ["2017-04-19 14:30:00","2017-04-19 15:30:00") ║║         1 │ ["2017-04-20 08:30:00","2017-04-20 10:30:00") ║║         1 │ ["2017-04-20 13:30:00","2017-04-20 14:30:00") ║║         2 │ ["2017-04-19 09:00:00","2017-04-19 10:30:00") ║║         2 │ ["2017-04-19 11:30:00","2017-04-19 16:30:00") ║╚═══════════╧═══════════════════════════════════════════════╝