查找未包含在任何范围内的数字 [英] Find numbers not included in any range

问题描述

我理解为什么如果结果是两个不相交的范围，范围的差值运算符为什么会失败。但是我不确定解决方法是什么。

I understand why the difference operator for ranges fails if the result would be two disjoint ranges. But I'm not sure what the workaround is.

一个简单的概念性示例是，如果我保留日历并为每次会议记录并带有时间戳范围存储会议时间的字段；生成该人在一天中有空的时间的列表的一种简单方法是什么？

A simple conceptual example would be if I was keeping a calendar and had a record for each meeting, with a timestamp range field storing the time of the meeting; what would be a simple way to generate a list of times that the person was free on a given day?

似乎与范围相关的简单，基本的事情，但是我想不出一种看起来似乎并不复杂的方法。

It seems like a simple, basic thing to do with ranges, but I can't come up with a way to do it that isn't unseemingly complicated.

推荐答案

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, '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, '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;

select range_exclude(numrange(8,17), array[numrange(10,11), numrange(13,20)]);

测试：

select range_exclude(numrange(1,10), numrange(5,6));
select range_exclude(numrange(8,17), array[numrange(10,11), numrange(13,15)]);

结果：

{"[1,5)","[6,10)"}
{"[8,10)","[11,13)","[15,17)"}

与时间戳相同：

select range_exclude(
  tstzrange('2016-07-28 8:00','2016-07-28 17:00'), 
  array[
    tstzrange('2016-07-28 10:00','2016-07-28 11:00'),
    tstzrange('2016-07-28 13:00','2016-07-28 15:00')]);

结果：

{"[\"2016-07-28 08:00:00+03\",\"2016-07-28 10:00:00+03\")","[\"2016-07-28 11:00:00+03\",\"2016-07-28 13:00:00+03\")","[\"2016-07-28 15:00:00+03\",\"2016-07-28 17:00:00+03\")"}

这篇关于查找未包含在任何范围内的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！