sql - 存储和查询每行不同的定价结构？

Question

我一直在努力想出一种存储这些数据的好方法......

每一行都是医生的做法。每种做法的价格因年龄段而异。例如，一种做法可能具有这种定价结构，其中左边是年龄，右边是价格:

0: 0
13: 20
21: 35
35: 35
60: 20

但是结构因实践而异。所以另一种做法可能是这样的:

0: 5
25: 50
40: 35
60: 20

我需要能够获取任何给定年龄的价格。

目前，我将所有价格都存储在每一行的 JSONB 列中。我在 nodeJS 中获取 JSON 对象并运行算法来获取某个年龄段的价格。这当然不理想，但我想不出任何方法来在 postgres 中存储和查询它。

到目前为止，我已经考虑过使用另一个表格来存储费用，并像这样存储每个年龄/价格对:

name | age | price

但这看起来有点笨拙并且会创建数千行。我的另一个想法是在练习行中为每个可能的年龄设置成本列，如下所示:

name | 0 | 5 | 6 | 13 | 16 | 18 | 21 | 25 | 40 | 60

但这也显得非常笨拙和笨拙。

有什么想法吗？或者我最好只将它保存在 JSON 中？

Answer 1

我会将价格存储在一个单独的表中，该表定义价格有效的“间隔”:

create table practice
(
  id integer not null primary key,
  name text
);

create table prices
(
  practice_id integer not null references practice,
  from_age integer not null,
  to_age integer,
  price integer not null,
  constraint valid_interval check (from_age < to_age)
);

您还可以添加排除约束以防止重叠间隔:

CONSTRAINT no_overlap 
   EXCLUDE USING gist (practice_id WITH =, int4range(from_age, to_age)  WITH &&)

当然，除了使用 from_age 和 to_age 两列之外，您还可以将其放入单个 int4range 列中，这具有可索引的优点.由于您通常会根据只留下极少数行进行查询的做法进行查询，因此范围列上的索引不是必需的(或无济于事)

要查询价格，您只需执行以下操作:

select price
from prices
where practice_id = 42
and 25 between from_age and to_age

由于条件 practice_id = 42 已经将其缩小到仅 5 或 6 行，因此速度非常快。纵有千行。