sql - 与表中定义的多个范围相匹配的列的子查询

Question

我有一个非常常见的地址数据库设置:person 通过连接表与 company 相关联，company 可以有一个 address 等等。

所有这些都非常标准化且易于使用。但是为了搜索性能，我创建了一个物化的、非规范化的 View 。我只需要一组非常有限的信息和快速查询。通常通过连接表完成的大部分事情现在都在一个数组中。根据查询，我可以直接搜索它或通过 unnest 加入它。

作为对我的 zipcodes 列 (varchar[]) 的补充，我想添加一个 states 列，其中包含 ( German fedaral) 状态已经预先计算，因此我不必转换查询以包含各种范围比较。

我的映射日期在这样的表格中:

CREATE TABLE zip2state (
    state TEXT NOT NULL,
    range_start CHARACTER VARYING(5) NOT NULL,
    range_end CHARACTER VARYING(5) NOT NULL
)

每个州都有多个范围，范围可以重叠(一个邮政编码可以代表两个不同的州)。一些范围具有 range_start = range_end。

现在，我对如何一次性将其全部转化为物化 View 有些不知所措。通常，我很想迭代地执行它(通过触发器或在应用程序级别)。或者当我们只讨论 5 位数时，我可以创建一个大表映射 zip 直接映射到 state，而不是通过一个范围来实现(我目前最喜欢的，但有些丑陋到让我问是否有更好的方法)

有什么方法可以在 SQL 中使用上面的表(或类似的表)来做到这一点？我在 postgres 9.3，允许所有功能......

为了完整起见，这里是邮政编码的子查询:

    (select array_agg(distinct address.zipcode)
      from  affiliation
      join  company
        on  affiliation.ins_id = company.id
      join  address
        on  address.com_id = company.id
     where  affiliation.per_id = person.id) AS zipcodes,

Answer 1

我建议使用 LATERAL 连接代替相关子查询，以便同时计算两列。可能看起来像这样:

SELECT p.*, z.*
FROM   person p
LEFT   JOIN LATERAL (
   SELECT array_agg(DISTINCT d.zipcode) AS zipcodes
        , array_agg(DISTINCT z.state)   AS states
   FROM   affiliation    a
   -- JOIN   company     c ON a.ins_id = c.id  -- suspect you don't need this
   JOIN   address        d ON d.com_id = a.ins_id  -- c.id
   LEFT   JOIN zip2state z ON d.zipcode BETWEEN z.range_start AND z.range_end
   WHERE  a.per_id = p.id
   ) z ON true;

如果引用完整性得到保证，则根本不需要连接到表company。我走了捷径。

请注意 varchar 或 text 的行为与预期的数字不同。例如:'333' > '0999'。如果所有 邮政编码都有 5 位数字，则没问题。

相关:

• What is the difference between LATERAL and a subquery in PostgreSQL?