Блог

У меня есть таблица HIVE:

 device           timestamp            value
  d_1        2020-08-15 00:05:00       10
  d_1        2020-08-15 00:10:00       12
  d_1        2020-08-15 00:15:00       08
  d_2        2020-08-15 00:05:00       62
  d_2        2020-08-15 00:25:00       14
  d_2        2020-08-15 00:45:00       10
  

Обратите внимание, что устройство d_1 имеет другой временной интервал (5 секунд), чем устройство d_2 (20 секунд).

Для большой таблицы с миллионами строк, как я могу определить интервал выборки для всех уникальных устройств?

Я ищу вывод, подобный этому:

  device       sampling_interval(mins)   
  d_1                  5
  d_2                 20
  

Самым безопасным из возможных подходов было бы использовать lag() для вычисления продолжительности каждого интервала между последовательными точками данных одного и того же устройства, а затем подсчитывать, сколько раз выполнялся каждый интервал:

 select
    device,
    (unix_timestamp(ts) - unix_timestamp(lag_ts)) / 60 sampling_interval_minutes,
    count(*) no_hits
from (
    select 
        t.*, 
        lag(ts) over(partition by device order by ts) lag_ts
    from mytable t
) t
group by device, (unix_timestamp(ts) - unix_timestamp(lag_ts)) / 60 
order by device, no_hits desc
  

Это позволяет легко проверять согласованность интервалов и видеть, какой из них встречается чаще всего.

Вы можете получить среднюю разницу, используя:

 select device,
       (unix_timestamp(max(timestamp)) - unix_timestamp(min(timestamp))) / nullif(count(*) - 1, 0)
from t
group by device;
  

Это дает среднюю разницу во временных метках между строками.