Timescaledb 时序数据查询解决时间断档的插值方法

[jamiesun]

案例

在对网络设备的ping检测中， 由于网络不可达或者服务器宕机导致一段时间数据库里没有数据， 在前端查询数据展示曲线图时， 对于空白时间图表不能正确的展现， 对于空白时间会产生一个自动曲线， 而这不是期望的结果。

因此需要解决一个问题， 要对空白时间段在查询时自动插入一个值， 这个值需要根据实际情况， 比如上面的 ping 检测需求， 对于延时我们可以插0值，但是对于丢包值，插入100更合适， 这样我们可以看到空白时间段会以明显的丢包来展示， 我们可以一眼看出这个时间段的异常数据情况

SQL 范例

with t as (
		    SELECT sn,
                   time_bucket_gapfill('10 minutes'::interval, timestamp) as stat_time,
		           src || ' -> ' ||target as network,
		           max(p.max_rtt)                                 as max_rtt,
		           avg(p.avg_rtt)                                 as avg_rtt,
		           min(p.min_rtt)                                 as min_rtt,
		           avg(p.pkt_loss)                                as pkt_loss
		    FROM ts_net_ping p
		    WHERE timestamp >= '2022-03-14 00:00:00'
		      AND timestamp <= '2022-03-15 23:59:59'
		      AND sn = 'V0Z2-SQ3V'
		      AND src <> ''
		      AND target <> ''
		    GROUP BY sn, network, stat_time
		    order by sn, network, stat_time
		)
		select t.sn,
		       t.stat_time,
		       t.network,
		       coalesce(round(t.max_rtt,2), 0) as max_rtt,
		       coalesce(round(t.min_rtt,2),0) as min_rtt,
		       coalesce(round(t.avg_rtt, 2),0) as avg_rtt,
		       coalesce(round(t.pkt_loss,2),100) as pkt_loss,
		       coalesce(round(t.max_rtt-t.min_rtt, 2),0) as jetter
		from t

这里我们使用 time_bucket_gapfill 代替time_bucket， 默认会自动为空白时间赋值null， 我们通过 coalesce 来将 null值替换为我们想要的插值。

扩展用法

timescaledb 还提供了自动计算插值的方法， 具体可以参考 TimescaleDB 插值