Блог

У меня есть таблицы резервирования для каждого пользователя:

 reservations_development=# d reservations
                           Table "public.reservations"
   Column   |  Type   |                         Modifiers
------------ --------- -----------------------------------------------------------
 id         | integer | not null default nextval('reservations_id_seq'::regclass)
 user_id    | integer |
 occurrence | tsrange |
Indexes:
    "reservations_pkey" PRIMARY KEY, btree (id)
    "reservations_occurrence_user_id_excl" EXCLUDE USING gist (occurrence WITH amp;amp;, user_id WITH =)
 

Я пытаюсь создать представление пробелов / пробелов между резервированием для каждого пользователя, и в настоящее время у меня есть следующий запрос:

 CREATE OR REPLACE VIEW reservation_gaps AS (
        with user_mins as (select tsrange(LOCALTIMESTAMP, min(lower(occurrence))), user_id
                FROM (
                    SELECT user_id, occurrence
                    FROM reservations
                    WHERE lower(occurrence) >= LOCALTIMESTAMP
                ) as y
                GROUP BY user_id
        ),
        gaps as (select
                tsrange(upper(occurrence), lead(lower(occurrence),1, LOCALTIMESTAMP   interval '1 year') over (win_user_gaps)),
                user_id
                from (
                        select user_id, occurrence
                        from reservations
                ) as x
                WINDOW win_user_gaps AS (PARTITION BY user_id ORDER BY occurrence)
                UNION ALL SELECT * FROM user_mins
        )
        select *
        FROM gaps
        ORDER BY user_id, tsrange
);
 

В настоящее время он дает ожидаемые результаты, если у пользователя есть одно резервирование, но если пользователь новый и в настоящее время не зарезервирован, я получаю пустой результат.

Мне нужно каким-то образом добавить строку {tsrange(LOCALTIMESTAMP, LOCALTIMESTAMP интервал ‘1 год’), user_id} в представление для каждого пользователя без резервирования, но в настоящее время я в тупике относительно того, как это сделать.

Спасибо

Вы должны изменить CTE на a UNION ALL с искусственными строками, а затем использовать DISTINCT ON для выбора одной строки для каждого пользователя.

 with user_mins as (SELECT DISTINCT ON (user_id) user_id, tsrange FROM(
select tsrange(LOCALTIMESTAMP, min(lower(occurrence))) as tsrange, user_id, 1 as priotity
                FROM (
                    SELECT user_id, occurrence
                    FROM reservations
                    WHERE lower(occurrence) >= LOCALTIMESTAMP
                ) as y
                GROUP BY user_id
                UNION ALL
                SELECT user_id,  tsrange(LOCALTIMESTAMP, LOCALTIMESTAMP   interval '1 year'),                     
                0
                FROM users)
        ORDER BY user_id, priority DESC
)
 

SQL скрипка

 with this_year as (
    select tsrange(
        date_trunc('year', current_date)::timestamp,
        date_trunc('year', current_date)::timestamp   interval '1' year, '[)'
    ) as this_year
), gaps as (
    select
        user_id,
        this_year - tsrange(lower(occurrence), 'infinity', '[]') lower_range,
        this_year - tsrange('-infinity', upper(occurrence), '[]') upper_range,
        this_year
    from
        reservations
        cross join
        this_year
)
select *
from (
    select
        user_id,
        upper_range *
            lead (lower_range, 1, this_year)
            over (partition by user_id order by lower_range, upper_range)
        as gap
    from gaps
    union (
        select distinct on (user_id)
            user_id,
            tsrange(
                lower(this_year),
                coalesce(upper(lower_range), upper(this_year)),
                '[)'
            ) as gap
        from gaps
        order by user_id, lower_range
    )
) s
where gap != 'empty'
order by user_id, gap