时序函数
time_bucket_gapfill
描述
IoT 应用场景下,IoT 设备发送的数据中,数据时间戳的准确度其实是一个很不好解决的问题。一种最直接的方案是假设 IoT 设备有自己的时钟,这样数据可以包含设备自身时钟的时间戳(不采用服务器端接收时间),那么所有 IoT 设备的“时钟同步”会是一个复杂的问题,有些很小的设备甚至都没有内置时钟。另一种方案是采用服务器端接受时间,但由于网络传输等原因,会有一定的时间延迟。因此,时间戳对齐处理是时序数据库必须具备的能力,而且是时序数据库区别于其他数据库的特征之一。
函数声明
time_bucket_gapfill(bucket_width, time_column, start_time, finish_time)
time_bucket_gapfill(bucket_width, time_column)
参数声明
参数名 | 参数含义 | 示例 |
---|---|---|
bucket_width | 指定填充间隔 | 例如:’1 sec’、’1 min’ 等,单位还支持 ‘hour’、’day’、’week’、’mon’、’year’ |
time_column | 时间列 | ts |
start_time | 填充开始时间 | 1568116800000 |
end_time | 填充结束时间 | 1568116805000 |
使用示例
测试数据集
select * from table3 order by device_id, time;
+--------------------+--------------+--------+--------------------------------------+
| time | device_id | value | tags |
+--------------------+--------------+--------+--------------------------------------+
| 1568116800000 | 1 | 0.5 | {"app": "app1", "host": "host1"} |
| 1568116801000 | 1 | 0.7 | {"app": "app1", "host": "host1"} |
| 1568116803000 | 1 | 0.6 | {"app": "app1", "host": "host1"} |
| 1568116800000 | 2 | 0.8 | {"app": "app2", "host": "host1"} |
| 1568116803000 | 2 | 0.9 | {"app": "app2", "host": "host1"} |
+--------------------+--------------+--------+--------------------------------------+
按照 1 秒采集周期进行填值
select
time_bucket_gapfill('1 sec', time,1568116800000, 1568116805000) as ts,
device_id
from table3
group by ts, device_id
order by device_id, ts;
+--------------------+-------------+
| ts | device_id |
+--------------------+-------------+
| 1568116800000 | 1 |
| 1568116801000 | 1 |
| 1568116802000 | 1 |
| 1568116803000 | 1 |
| 1568116804000 | 1 |
| 1568116805000 | 1 |
| 1568116800000 | 2 |
| 1568116801000 | 2 |
| 1568116802000 | 2 |
| 1568116803000 | 2 |
| 1568116804000 | 2 |
| 1568116805000 | 2 |
+--------------------+-------------+
interpolate
描述
对缺失的数值进行插值操作,可以配合 time_bucket_gapfill 函数使用。
函数声明
interpolate ( time_column, expression, policy, fixed_value )
OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
[rows_range_clause]
)
参数声明
参数名 | 参数含义 | 示例 |
---|---|---|
time_column | 时间列 | ts |
expression | 数值列 | value |
policy | 插值策略 | 可选参数,默认 linear 。支持的插值策略有 none 、nan 、null 、zero 、linear 、previous 、near 、after 和 fixed |
fixed_value | 当 policy 是 fixed 的时候,需要提供具体填充的数值 |
0 |
使用示例
测试数据集
select * from table3 order by device_id, time;
+--------------------+--------------+---------+--------------------------------------+
| time | device_id | value | tags |
+--------------------+--------------+---------+--------------------------------------+
| 1568116800000 | 1 | 0.5 | {"app": "app1", "host": "host1"} |
| 1568116801000 | 1 | 0.7 | {"app": "app1", "host": "host1"} |
| 1568116802000 | 1 | NULL | NULL |
| 1568116803000 | 1 | 0.6 | {"app": "app1", "host": "host1"} |
| 1568116800000 | 2 | 0.8 | {"app": "app2", "host": "host1"} |
| 1568116803000 | 2 | 0.9 | {"app": "app2", "host": "host1"} |
| 1568116805000 | 3 | 0.4 | {"app": "app1", "host": "host2"} |
| 1568116806000 | 3 | 0.5 | {"app": "app1", "host": "host2"} |
| 1568116809000 | 3 | 0.7 | {"app": "app1", "host": "host2"} |
| 1568116818000 | 3 | 1.2 | {"app": "app1", "host": "host2"} |
+--------------------+--------------+---------+--------------------------------------+
配合 time_bucket_gapfill 使用
select date_format('%Y-%m-%d %H:%i:%s',t.ts) fmt_ts,t.* from
(
select
time_bucket_gapfill('1 sec', time, '2019-09-10 12:00:01', '2019-09-10 12:00:10') as ts,
device_id,
interpolate(time_bucket_gapfill('1 sec', time, '2019-09-10 12:00:01', '2019-09-10 12:00:10'), sum(value)) over(partition by device_id) as interp,
sum(value),
count(value),
max(value)
from table3
group by ts, device_id
having device_id>0
order by device_id, ts
) as t;
+---------------------+---------------+-----------+--------------------+------------+--------------+------------+
| fmt_ts | ts | device_id | interp | sum(value) | count(value) | max(value) |
+---------------------+---------------+-----------+--------------------+------------+--------------+------------+
| 2019-09-10 12:00:00 | 1568116800000 | 1 | 0.5 | 0.5 | 1 | 0.5 |
| 2019-09-10 12:00:01 | 1568116801000 | 1 | 0.7 | 0.7 | 1 | 0.7 |
| 2019-09-10 12:00:02 | 1568116802000 | 1 | 0.65 | NULL | NULL | NULL |
| 2019-09-10 12:00:03 | 1568116803000 | 1 | 0.6 | 0.6 | 1 | 0.6 |
| 2019-09-10 12:00:04 | 1568116804000 | 1 | 0.6 | NULL | NULL | NULL |
| 2019-09-10 12:00:05 | 1568116805000 | 1 | 0.6 | NULL | NULL | NULL |
| 2019-09-10 12:00:06 | 1568116806000 | 1 | 0.6 | NULL | NULL | NULL |
| 2019-09-10 12:00:07 | 1568116807000 | 1 | 0.6 | NULL | NULL | NULL |
| 2019-09-10 12:00:08 | 1568116808000 | 1 | 0.6 | NULL | NULL | NULL |
| 2019-09-10 12:00:09 | 1568116809000 | 1 | 0.6 | NULL | NULL | NULL |
| 2019-09-10 12:00:10 | 1568116810000 | 1 | 0.6 | NULL | NULL | NULL |
| 2019-09-10 12:00:00 | 1568116800000 | 2 | 0.8 | 0.8 | 1 | 0.8 |
| 2019-09-10 12:00:01 | 1568116801000 | 2 | 0.8333333333333334 | NULL | NULL | NULL |
| 2019-09-10 12:00:02 | 1568116802000 | 2 | 0.8666666666666667 | NULL | NULL | NULL |
| 2019-09-10 12:00:03 | 1568116803000 | 2 | 0.9 | 0.9 | 1 | 0.9 |
| 2019-09-10 12:00:04 | 1568116804000 | 2 | 0.9 | NULL | NULL | NULL |
| 2019-09-10 12:00:05 | 1568116805000 | 2 | 0.9 | NULL | NULL | NULL |
| 2019-09-10 12:00:06 | 1568116806000 | 2 | 0.9 | NULL | NULL | NULL |
| 2019-09-10 12:00:07 | 1568116807000 | 2 | 0.9 | NULL | NULL | NULL |
| 2019-09-10 12:00:08 | 1568116808000 | 2 | 0.9 | NULL | NULL | NULL |
| 2019-09-10 12:00:09 | 1568116809000 | 2 | 0.9 | NULL | NULL | NULL |
| 2019-09-10 12:00:10 | 1568116810000 | 2 | 0.9 | NULL | NULL | NULL |
| 2019-09-10 12:00:01 | 1568116801000 | 3 | 0.4 | NULL | NULL | NULL |
| 2019-09-10 12:00:02 | 1568116802000 | 3 | 0.4 | NULL | NULL | NULL |
| 2019-09-10 12:00:03 | 1568116803000 | 3 | 0.4 | NULL | NULL | NULL |
| 2019-09-10 12:00:04 | 1568116804000 | 3 | 0.4 | NULL | NULL | NULL |
| 2019-09-10 12:00:05 | 1568116805000 | 3 | 0.4 | 0.4 | 1 | 0.4 |
| 2019-09-10 12:00:06 | 1568116806000 | 3 | 0.5 | 0.5 | 1 | 0.5 |
| 2019-09-10 12:00:07 | 1568116807000 | 3 | 0.5666666666666667 | NULL | NULL | NULL |
| 2019-09-10 12:00:08 | 1568116808000 | 3 | 0.6333333333333333 | NULL | NULL | NULL |
| 2019-09-10 12:00:09 | 1568116809000 | 3 | 0.7 | 0.7 | 1 | 0.7 |
| 2019-09-10 12:00:10 | 1568116810000 | 3 | 0.7 | NULL | NULL | NULL |
+---------------------+---------------+-----------+--------------------+------------+--------------+------------+
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论