Подзапрос, который соответствует столбцу с несколькими диапазонами, определенными в таблице

Question

У меня есть довольно общие настройки для адресной базы данных: person привязан к company с соединительным столом, company может иметь address и так далее.

Все довольно нормализовано и удобно в использовании. Но для эффективности поиска я создаю материализованное, довольно денормализованное представление. Мне нужен только очень ограниченный набор информации и быстрых запросов. Большая часть всего, что обычно делается через таблицу соединений, теперь находится в массиве. В зависимости от запроса, я могу либо искать его напрямую, либо присоединиться к нему через unnest,

Как дополнение к моему zipcodes столбец (varchar[]), Я хотел бы добавить states Столбец с уже вычисленными состояниями (немецкий федарал), так что мне не нужно преобразовывать запрос, чтобы включить все виды сравнения диапазонов.

Моя дата отображения в таблице как это:

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,

Теперь я немного разбираюсь в том, как сразу представить это материализованному представлению. Обычно я чувствую соблазн просто делать это итеративно (через триггер или на уровне приложения). Или, поскольку мы говорим только о 5 цифрах, я мог бы создать большой почтовый индекс для отображения таблицы, вместо того, чтобы делать это через диапазон (мой текущий фаворит, но что-то достаточно уродливое, что побудило меня спросить, есть ли лучший способ)

Любой способ сделать это в 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 цифр, у вас все в порядке.

Связанные с:

• В чем разница между LATERAL и подзапросом в PostgreSQL?