根据HIVE表中的时间戳确定每个唯一值的采样间隔

Question

我有一个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

Answer 1

最安全的方法是使用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

这样可以很容易地检查间隔的一致性，并查看哪一个间隔发生得最多。

Answer 2

您可以使用获得平均差异

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

这给出了行之间时间戳的平均差异。