Учитывая таблицу с записями с нерегулярными отметками времени, разрывы должны быть вставлены через регулярные 5-минутные интервалы (связанные данные могут/будут иметь значение NULL).
Я думал о том, чтобы получить время начала, сделать подзапрос с оконной функцией и добавить 5-минутные интервалы к времени начала, но я мог думать только об использовании row_number для увеличения значений.
WITH data as(
select id, data,
cast(date_and_time as double) * 1000 as time_milliseconds
from t1), -- original data
start_times as(
select id, MIN(CAST(date_and_time as double) * 1000) as start_time
from t1
GROUP BY id
), -- first timestamp for each id
boundries as (
SELECT T1.id,(row_number() OVER (PARTITION BY T1.id ORDER BY T1.date_and_time)-1) *300000 + start_times.start_time
as boundry
from T1
INNER JOIN start_times ON start_times.id= T1.id
) -- increment the number of 5 min added on each row and later full join boundries table with original data
Однако это ограничивает меня количеством строк, присутствующих для идентификатора в исходной таблице данных, и если временные метки разбросаны, количество строк не может покрыть количество 5-минутных интервалов, которые необходимо добавить.
образец данных:
initial data:
|-----------|------------------|------------------|
| id | value | timestamp |
|-----------|------------------|------------------|
| 1 | 3 | 12:00:01.011 |
|-----------|------------------|------------------|
| 1 | 4 | 12:03:30.041 |
|-----------|------------------|------------------|
| 1 | 5 | 12:12:20.231 |
|-----------|------------------|------------------|
| 1 | 3 | 15:00:00.312 |
data after my query:
|-----------|------------------|------------------|
| id | value | timestamp (UNIX) |
|-----------|------------------|------------------|
| 1 | 3 | 12:00:01 |
|-----------|------------------|------------------|
| 1 | 4 | 12:03:30 |
|-----------|------------------|------------------|
| 1 | NULL | 12:05:01 | <-- Data from "boundries"
|-----------|------------------|------------------|
| 1 | NULL | 12:10:01 | <-- Data from "boundries"
|-----------|------------------|------------------|
| 1 | 5 | 12:12:20 |
|-----------|------------------|------------------|
| 1 | NULL | 12:15:01 | <-- Data from "boundries"
|-----------|------------------|------------------|
| 1 | NULL | 12:20:01 | <-- Data from "boundries"
|-----------|------------------|------------------| <-- Jumping directly to 15:00:00 (WRONG! :( need to insert more 5 min breaks here )
| 1 | 3 | 15:00:00 |
Я думал о создании временной таблицы внутри HIVE и заполнении ее строками x, представляющими 5-минутные интервалы от времени начала до времени окончания таблицы данных, но я не мог найти никакого способа сделать это.
Любой способ использования для циклов? Мы ценим любые предложения.
Спасибо
