SQL - Vertica: как создавать ежедневные строки с большинством данных предыдущей даты

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

score_upd (Upd_dt,Url,Score) AS (
          SELECT DATE '2019-07-26','A','x'
UNION ALL SELECT DATE '2019-07-26','B','alpha'
UNION ALL SELECT DATE '2019-08-01','A','y'
UNION ALL SELECT DATE '2019-08-01','B','beta'
UNION ALL SELECT DATE '2019-08-03','A','z'
UNION ALL SELECT DATE '2019-08-03','B','gamma'
)

   Upd_dt       URL    Score
 2019-07-26      A       x
 2019-07-26      B      alpha 
 2019-08-01      A       y
 2019-08-01      B      beta
 2019-08-03      A       z
 2019-08-03      B      gamma

И я хочу создать таблицу на уровне ежедневного URL, используя значение самой предыдущей даты для новых строк, результат должен выглядеть следующим образом:

score_upd (Upd_dt,Url,Score) AS (
          SELECT DATE '2019-07-26','A','x'
UNION ALL SELECT DATE '2019-07-26','B','alpha'
UNION ALL SELECT DATE '2019-07-27','A','x'
UNION ALL SELECT DATE '2019-07-27','B','alpha'
UNION ALL SELECT DATE '2019-07-28','A','x'
UNION ALL SELECT DATE '2019-07-28','B','alpha'
UNION ALL SELECT DATE '2019-07-29','A','x'
UNION ALL SELECT DATE '2019-07-29','B','alpha'
UNION ALL SELECT DATE '2019-07-30','A','x'
UNION ALL SELECT DATE '2019-07-30','B','alpha'
UNION ALL SELECT DATE '2019-07-31','A','x'
UNION ALL SELECT DATE '2019-07-31','B','alpha'
UNION ALL SELECT DATE '2019-08-01','A','y'
UNION ALL SELECT DATE '2019-08-01','B','beta'
UNION ALL SELECT DATE '2019-08-02','A','y'
UNION ALL SELECT DATE '2019-08-02','B','beta'
UNION ALL SELECT DATE '2019-08-03','A','z'
UNION ALL SELECT DATE '2019-08-03','B','gamma'
UNION ALL SELECT DATE '2019-08-04','A','z'
UNION ALL SELECT DATE '2019-08-04','B','gamma'
UNION ALL SELECT DATE '2019-08-05','A','z'
UNION ALL SELECT DATE '2019-08-05','B','gamma'
)

Что выглядит так:

   Upd_dt       URL    Score 
 2019-07-26      A       x
 2019-07-26      B      alpha 
 2019-07-27      A       x
 2019-07-27      B      alpha 
 2019-07-28      A       x
 2019-07-28      B      alpha 
 2019-07-29      A       x
 2019-07-29      B      alpha 
 2019-07-30      A       x
 2019-07-30      B      alpha 
 2019-07-31      A       x
 2019-07-31      B      alpha 
 2019-08-01      A       y
 2019-08-01      B      beta
 2019-08-02      A       y
 2019-08-02      B      beta
 2019-08-03      A       z
 2019-08-03      B      gamma
 2019-08-04      A       z
 2019-08-04      B      gamma
 2019-08-05      A       z
 2019-08-05      B      gamma
.
.
.

Текущий процесс: я построил таблицу ежедневных измерений с 26.07.2019 по сегодняшний день:

/* ВЫБРАТЬ ДАТЫ CAST(slice_time КАК ДАТА) ИЗ testcalendar mtc TIMESERIES slice_time как '1 день' ВЫШЕ (ORDER BY CAST(mtc.dates as TIMESTAMP)); */

так я получаю:

Даты

2019-07-26

2019-07-27

2019-07-28

2019-07-29

.

.

.

2019-10-12 (сегодня)

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

В результате не было создано строк за пропущенные дни.

Пожалуйста, дайте мне знать, есть ли у кого-нибудь лучшее представление об этом.

Спасибо!

Источник

1 ответ

Решение

В качестве первого предупреждения: храните "ежедневные фотографии" только тогда, когда они действительно необходимы. В моем прошлом у меня однажды получалось слишком много 364 строки в год, так как значения менялись только один раз в год. В Vertica это требует лицензии, а также процессора и времени для объединения и группировки...

Но в остальном - хорошее начало.

Но вы можете применять TIMESERIES, не создавая календаря.

Хитрость заключается в том, чтобы вручную "экстраполировать" то, что вы можете INTERPOLATE автоматически.

Добавьте встроенную таблицу заполнения, которая содержит новейшее значение для каждого URL-адреса, но укажите его CURRENT_DATEвместо самой новой фактической даты - с использованием специфической аналитической предельной оговорки Vertica LIMIT 1 OVER(PARTITION BY url ORDER BY upd_dt DESC).

UNION SELECT эту таблицу заполнения вашим вводом и примените предложение TIMESERIES к этому UNION SELECT.

Вот так:

WITH
-- your input ...
score_upd (Upd_dt,Url,Score) AS (
          SELECT DATE '2019-07-26','A','x'
UNION ALL SELECT DATE '2019-07-26','B','alpha'
UNION ALL SELECT DATE '2019-08-01','A','y'
UNION ALL SELECT DATE '2019-08-01','B','beta'
UNION ALL SELECT DATE '2019-08-03','A','z'
UNION ALL SELECT DATE '2019-08-03','B','gamma'
)
-- real WITH clause would start here ...                                                                                                                                                 
,
-- newest row per Url, just with current date
pad_newest AS (
SELECT
  CURRENT_DATE
, url 
, score
FROM score_upd
LIMIT 1 OVER(PARTITION BY url ORDER BY upd_dt DESC)
)   
,   
with_newest AS (
SELECT
  *   
FROM score_upd
UNION ALL 
SELECT *
FROM pad_newest
)   
SELECT
  ts_dt::DATE           AS upd_dt
, url                   AS url 
, TS_FIRST_VALUE(score) AS score
FROM with_newest
TIMESERIES ts_dt AS '1 day' OVER (
  PARTITION BY url ORDER BY upd_dt::TIMESTAMP
)   
ORDER BY 1,2 
;
Источник
Другие вопросы по тегам