Соединение таблицы с календарем vertica

Question

Помогите, пожалуйста, решить задачу. Есть первая таблица с данными:

ID мероприятие результат дата
999 xxx        yyy       01-01-2020
999 aaa        bbb       01-12-2020
... ...        ...       ..........

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

01-01-2020
01-02-2020
01-03-2020
..........

Предположим, по одному ID было всего 2 мероприятия за год, но мне нужно получить таблицу такого формата:

999 xxx        yyy       01-01-2020
999 xxx        yyy       01-02-2020
999 xxx        yyy       01-03-2020
999 xxx        yyy       01-04-2020
999 xxx        yyy       01-05-2020
999 xxx        yyy       01-06-2020
999 xxx        yyy       01-07-2020
999 xxx        yyy       01-08-2020
999 xxx        yyy       01-09-2020
999 xxx        yyy       01-10-2020
999 xxx        yyy       01-11-2020
999 aaa        bbb       01-12-2020

То есть сджойнить данные с календарем и если на дату из календаря не было мероприятий, то заполнить пропуск последним мероприятием. Как такое можно реализовать?

Answer 1

Можно примерно так:

select *
from calendar c
left join  
 (
  select  *
   ,lead(event_date,1,event_date)over(partition by id order by event_date) as next_date
  from events
  ) e
     on (c.bmonth>=event_date and c.bmonth<e.next_date)
      or(date_trunc('month',c.bmonth)=date_trunc('month',event_date))
where event_date is not null

Если нужно довести список до конкретной даты, нужно дополнить календарь как минимум до этой даты и выполнить запрос примерно так:

select *
from calendar c
left join  (
select  *
  ,lead(event_date,1,cast('2021-09-18' as date))
        over(partition by id order by event_date) as next_date
from events) e
   on (c.bmonth>=event_date and c.bmonth<e.next_date)
     or(date_trunc('month',c.bmonth)=date_trunc('month',event_date))
where event_date is not null 
  and event_date<=cast('2021-09-18' as date) --current_date

БЛОГ НА HUSL

Соединение таблицы с календарем vertica

Ответы (1 шт):