PostgreSQL: Пример использования предложений WITH для декомпозиции проблемы

Вот простой пример использования выражений WITH (они же CTE — Common Table Expressions) для декомпозиции проблемы. Вопрос с форума YugabyteDB касался хранения бронирований гостиничных номеров в таблице типа:


create table Rooms (PK int primary key, name text, roomNo int);

create table BookedRooms (PK int primary key
, checkInDate timestamptz, checkOutDate timestamptz
, roomId int references Rooms(PK))
;

insert into Rooms values (2,'Deluxe',102),(3,'King',103);
insert into BookedRooms values (1,'2022-05-26T00:00:00Z','2022-05-29T00:00:00Z',2),(2,'2022-05-29T00:00:00Z','2022-05-30T00:00:00Z',3);
Войти в полноэкранный режим Выйти из полноэкранного режима

Я предложил следующий запрос:

with 
input_month as (
 select 4 as month --> the input months (can be a parameter in a prepared statement)
),
day_generator as (
select 
 date_trunc('year', now())      --> I guess you query for the current year
 + m.month * interval '1 month' --> first day of the month
 + n * interval '1 day'         --> adding 31 days to cover any month
 as day
 from input_month m , generate_series(0,31) n
),
days_of_month as (
select *
from input_month m, day_generator d
 where d.day <                  --> removes the days in next month
 date_trunc('year', now())  + (m.month + 1) * interval '1 month' 
),
result as (
select distinct d.day, r.roomNo 
from days_of_month d, BookedRooms b join Rooms r on (b.roomId=r.PK)
where not(d.day between b.checkInDate and b.checkOutDate) --> remove non available rooms
order by day, roomno
) 
select * from result

Войти в полноэкранный режим Выйти из полноэкранного режима

Я начинаю с определения входных параметров как input_month. Его можно передавать как параметр, но мне кажется более удобным иметь его только в одном месте, в начале запроса, а не ссылаться на $1 в нескольких местах.

Затем мне нужно сгенерировать все дни для месяца, и я делаю это в два шага. day_geneator генерирует максимум (31 день в месяце), начиная с input_month. А days_of_month фильтрует, чтобы получить только дни месяца (для месяцев с менее чем 31 днем).

Наконец, при всем этом запрос представляет собой картезианское соединение между датами и бронированиями с фильтрацией по интервалу дат, чтобы показать только те, которые доступны. Я добавил DISTINCT на случай пересечений, но этого не должно происходить и должно проверяться с помощью ограничений. Это может быть темой для следующей статьи в блоге.

Я поместил окончательный запрос в result, чтобы основной select был самым простым, это облегчает отладку путем выделения каждого шага.

Я нахожу много преимуществ в декомпозиции на CTE:

  • легче читать и комментировать каждый шаг
  • легче обрабатывать планировщиком запросов (возможность материализовать или нет каждый блок запроса)
  • легче тестировать, просто изменив конечный selectНе бойтесь больших SQL-запросов, если они хорошо разработаны. Вы можете думать об этом как о выполнении команд Linux одна за другой, чтобы решить проблему небольшими шагами.

Оцените статью
Procodings.ru
Добавить комментарий