postgresql - 存储和使用值范围 (PostgreSQL)

Question

我有一个 PostgreSQL 数据库，其中物理事件存储特定的能量十进制值，例如

ACTIVITY   ENERGY
-----------------
Activity1  0.7
Activity2  1.3
Activity3  4.5

我有一个分类系统，将每个能量值分类为

Light:    0 - 2.9
Moderate: 3.0 - 5.9
Vigorous: >= 6.0

分类和能量值可能会发生变化。我需要一种方法来快速获取每个事件的类型。但是如何以一种易于检索的方式存储这些呢？

一个解决方案是定义“分类”类型的 MIN/MAX 查找——并提取所有可用的分类；然后做一个 CASE/WHEN 来检查每一个。

LOOKUP_ID   LOOKUP_NAME     LOOKUP_VALUE      LOOKUP_TYPE
---------------------------------------------------------
1           LIGHT_MIN       0                 CLASSIFICATION
2           LIGHT_MAX       2.9               CLASSIFICATION
3           MODERATE_MIN    3                 CLASSIFICATION
4           MODERATE_MAX    5.9               CLASSIFICATION
5           VIGOROUS_MIN    6                 CLASSIFICATION
6           VIGOROUS_MAX    null              CLASSIFICATION

但这对我来说并不容易——如果开发人员需要获得当前的分类，他们将不得不逐个检查不同的案例并进行比较。

是否有更好的策略来捕获这些范围，或者这是正确的策略吗？

Answer 1

使用 range type

create table classification 
(
  description text,
  energy numrange
);

insert into classification 
  (description, energy)
values
  ('Light',  numrange(0,3.0,'[)')),
  ('Moderate', numrange(3.0, 6.0, '[)')),
  ('Vigorous', numrange(6.0, null, '[)'));

然后您可以使用 <@ 连接这两个表运算符(operator):

select *
from activity a
  join classification c on a.energy <@ c.energy

---

范围类型的好处是您可以通过使用 exclusion constraint 来防止插入重叠范围。

alter table classification 
  add constraint check_range_overlap
  exclude using gist (energy with &&);

鉴于上述示例数据，以下插入将被拒绝:

insert into classification 
  (description, energy)
values 
  ('Strenuous', numrange(8.0, 11.0, '[)'));