Сохраняем диапазон в виде box типа

В прошлой статье «Пример использования диапазонного типа данных» я на реальном примере рассмотрел, чем может быть полезен специальный тип для хранения диапазонов которые существует в PostgreSQL. В комментариях поступило предложение пойти дальше и воспользоваться типом box. Т.е. сохранить диапазон в виде объекта геометрии. Немного непривычно. Но сказано — сделано! Плюсы и минусы хранения КВС ОСАГО в виде box рассмотрю в заметке. Публикация является дополнением к указанной статье. Так же я подготовил все 4 вариант схем внутри демки с docker, поэтому примеры можно позапускать у себя. Кому ближе видео версия, то в конце заметки есть ссылка на полное видео данных публикаций на Youtube.

Предметная область

Кратко напомню, что КВС – коэффициент страховых тарифов в зависимости от возраста и стажа водителя (КВС). Зависимость представлена в таблице:

Здесь мы видим диапазоны чисел по входным критериям (стаж и возраст) и попробуем их сохранить в box типе. Итак четвертый вариант хранения диапазонов.

Схема D: Структура базы с геометрией

Как я уже упоминал данная схема появилась уже после выхода первой статьи. @ptr128в комментарии предложил пойди еще дальше и свести схему к таблице всего лишь с 2 колонками. С переходом на геометрию и использование тип данных box. Но для полного понимания как это работает нужно пояснение, каким образом данные в таблице превращаются в прямоугольники на плоскости. Для этого я нарисовал такой вот схему:

Для создания box нужно взять всего лишь две противоположные вершины. В таблице для величины КВС 1,87 есть диапазон возраста 16-21 и диапазон стажа 0-2. Мы можем задать две точки с координатами (16, 0) и (21, 2). Значение КВС внутри этой зоны 1,87. Следуя этой логике я перевел все значения в набор прямоугольников. Вот эти прямоугольники мы с базу и сохраняем.

Схема базы при использовании типа box получается такой:

CREATE TABLE pg_range_d__kvs (     "age_experience" box           not null,     "value"          numeric(4, 2) not null,      exclude using gist (age_experience WITH &&) ); comment on column pg_range_d__kvs.age_experience is 'Возраст-Стаж'; comment on column pg_range_d__kvs.value is 'Значение КВС';

Ограничение-исключения EXCLUDE USING GIST убережет нас от ошибочной вставки пересекающихся диапазонов. Производим вставку данных:

Синтаксис вставки прямоугольников (порядок вершин не имеет значения):

INSERT INTO pg_range_d__kvs VALUES ('((16,0),(21,2))', 1.87);

Как видим, в этом случае получается 20 записей (их можно сократить до 15). Запрос на получение коэффициента имеет вид:

SELECT value FROM pg_range_d__kvs WHERE age_experience @> POINT(22,3);

Сравнение вариантов

Привожу таблицу сравнения всех 4 вариантов схемы базы данных.Как вы уже могли убедиться, при использовании диапазонного типа данных у нас получается минимальное количество строк, простой запрос и контроль «непересечения» диапазонов. Для большей наглядности я сделал сводную таблицу:

Вариант с box на практике для решения данной задачи я использовать не рекомендую и привожу его чисто из спортивного интереса.

Демка с базами запакованными в docker: https://github.com/alekciy/demo#пример-1-postgresql-диапазонный-тип-данных-для-квс

Видео версия публикаций и вопросов: https://youtu.be/LOtEC68d1Aw