全部产品
云市场

时序函数

更新时间:2019-12-30 16:03:36

time_bucket_gapfill

描述

 IoT 应用场景下,IoT 设备发送的数据中,数据时间戳的准确度其实是一个很不好解决的问题。一种最直接的方案是假设 IoT 设备有自己的时钟,这样数据可以包含设备自身时钟的时间戳(不采用服务器端接收时间),那么所有 IoT 设备的“时钟同步”会是一个复杂的问题,有些很小的设备甚至都没有内置时钟。另一种方案是采用服务器端接受时间,但由于网络传输等原因,会有一定的时间延迟。因此,时间戳对齐处理是时序数据库必须具备的能力,而且是时序数据库区别于其他数据库的特征之一。

函数声明

  1. time_bucket_gapfill(bucket_width, time_column, start_time, finish_time)
  2. 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

使用示例

测试数据集

  1. select * from table3 order by device_id, time;
  1. +--------------------+--------------+--------+--------------------------------------+
  2. | time | device_id | value | tags |
  3. +--------------------+--------------+--------+--------------------------------------+
  4. | 1568116800000 | 1 | 0.5 | {"app": "app1", "host": "host1"} |
  5. | 1568116801000 | 1 | 0.7 | {"app": "app1", "host": "host1"} |
  6. | 1568116803000 | 1 | 0.6 | {"app": "app1", "host": "host1"} |
  7. | 1568116800000 | 2 | 0.8 | {"app": "app2", "host": "host1"} |
  8. | 1568116803000 | 2 | 0.9 | {"app": "app2", "host": "host1"} |
  9. +--------------------+--------------+--------+--------------------------------------+

按照 1 秒采集周期进行填值

  1. select
  2. time_bucket_gapfill('1 sec', time,1568116800000, 1568116805000) as ts,
  3. device_id
  4. from table3
  5. group by ts, device_id
  6. order by device_id, ts;
  1. +--------------------+-------------+
  2. | ts | device_id |
  3. +--------------------+-------------+
  4. | 1568116800000 | 1 |
  5. | 1568116801000 | 1 |
  6. | 1568116802000 | 1 |
  7. | 1568116803000 | 1 |
  8. | 1568116804000 | 1 |
  9. | 1568116805000 | 1 |
  10. | 1568116800000 | 2 |
  11. | 1568116801000 | 2 |
  12. | 1568116802000 | 2 |
  13. | 1568116803000 | 2 |
  14. | 1568116804000 | 2 |
  15. | 1568116805000 | 2 |
  16. +--------------------+-------------+

interpolate

描述

 对缺失的数值进行插值操作,可以配合 time_bucket_gapfill 函数使用。

函数声明

  1. interpolate ( time_column, expression, policy, fixed_value )
  2. OVER (
  3. [PARTITION BY partition_expression, ... ]
  4. ORDER BY sort_expression [ASC | DESC], ...
  5. [rows_range_clause]
  6. )

参数声明

参数名 参数含义 示例
time_column 时间列 ts
expression 数值列 value
policy 插值策略 可选参数,默认 linear。支持的插值策略有 nonenannullzerolinearpreviousnearafterfixed
fixed_value 当 policy 是 fixed 的时候,需要提供具体填充的数值 0

使用示例

测试数据集

  1. select * from table3 order by device_id, time;
  1. +--------------------+--------------+---------+--------------------------------------+
  2. | time | device_id | value | tags |
  3. +--------------------+--------------+---------+--------------------------------------+
  4. | 1568116800000 | 1 | 0.5 | {"app": "app1", "host": "host1"} |
  5. | 1568116801000 | 1 | 0.7 | {"app": "app1", "host": "host1"} |
  6. | 1568116802000 | 1 | NULL | NULL |
  7. | 1568116803000 | 1 | 0.6 | {"app": "app1", "host": "host1"} |
  8. | 1568116800000 | 2 | 0.8 | {"app": "app2", "host": "host1"} |
  9. | 1568116803000 | 2 | 0.9 | {"app": "app2", "host": "host1"} |
  10. | 1568116805000 | 3 | 0.4 | {"app": "app1", "host": "host2"} |
  11. | 1568116806000 | 3 | 0.5 | {"app": "app1", "host": "host2"} |
  12. | 1568116809000 | 3 | 0.7 | {"app": "app1", "host": "host2"} |
  13. | 1568116818000 | 3 | 1.2 | {"app": "app1", "host": "host2"} |
  14. +--------------------+--------------+---------+--------------------------------------+

配合 time_bucket_gapfill 使用

  1. select date_format('%Y-%m-%d %H:%i:%s',t.ts) fmt_ts,t.* from
  2. (
  3. select
  4. time_bucket_gapfill('1 sec', time, '2019-09-10 12:00:01', '2019-09-10 12:00:10') as ts,
  5. device_id,
  6. 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,
  7. sum(value),
  8. count(value),
  9. max(value)
  10. from table3
  11. group by ts, device_id
  12. having device_id>0
  13. order by device_id, ts
  14. ) as t;
  1. +---------------------+---------------+-----------+--------------------+------------+--------------+------------+
  2. | fmt_ts | ts | device_id | interp | sum(value) | count(value) | max(value) |
  3. +---------------------+---------------+-----------+--------------------+------------+--------------+------------+
  4. | 2019-09-10 12:00:00 | 1568116800000 | 1 | 0.5 | 0.5 | 1 | 0.5 |
  5. | 2019-09-10 12:00:01 | 1568116801000 | 1 | 0.7 | 0.7 | 1 | 0.7 |
  6. | 2019-09-10 12:00:02 | 1568116802000 | 1 | 0.65 | NULL | NULL | NULL |
  7. | 2019-09-10 12:00:03 | 1568116803000 | 1 | 0.6 | 0.6 | 1 | 0.6 |
  8. | 2019-09-10 12:00:04 | 1568116804000 | 1 | 0.6 | NULL | NULL | NULL |
  9. | 2019-09-10 12:00:05 | 1568116805000 | 1 | 0.6 | NULL | NULL | NULL |
  10. | 2019-09-10 12:00:06 | 1568116806000 | 1 | 0.6 | NULL | NULL | NULL |
  11. | 2019-09-10 12:00:07 | 1568116807000 | 1 | 0.6 | NULL | NULL | NULL |
  12. | 2019-09-10 12:00:08 | 1568116808000 | 1 | 0.6 | NULL | NULL | NULL |
  13. | 2019-09-10 12:00:09 | 1568116809000 | 1 | 0.6 | NULL | NULL | NULL |
  14. | 2019-09-10 12:00:10 | 1568116810000 | 1 | 0.6 | NULL | NULL | NULL |
  15. | 2019-09-10 12:00:00 | 1568116800000 | 2 | 0.8 | 0.8 | 1 | 0.8 |
  16. | 2019-09-10 12:00:01 | 1568116801000 | 2 | 0.8333333333333334 | NULL | NULL | NULL |
  17. | 2019-09-10 12:00:02 | 1568116802000 | 2 | 0.8666666666666667 | NULL | NULL | NULL |
  18. | 2019-09-10 12:00:03 | 1568116803000 | 2 | 0.9 | 0.9 | 1 | 0.9 |
  19. | 2019-09-10 12:00:04 | 1568116804000 | 2 | 0.9 | NULL | NULL | NULL |
  20. | 2019-09-10 12:00:05 | 1568116805000 | 2 | 0.9 | NULL | NULL | NULL |
  21. | 2019-09-10 12:00:06 | 1568116806000 | 2 | 0.9 | NULL | NULL | NULL |
  22. | 2019-09-10 12:00:07 | 1568116807000 | 2 | 0.9 | NULL | NULL | NULL |
  23. | 2019-09-10 12:00:08 | 1568116808000 | 2 | 0.9 | NULL | NULL | NULL |
  24. | 2019-09-10 12:00:09 | 1568116809000 | 2 | 0.9 | NULL | NULL | NULL |
  25. | 2019-09-10 12:00:10 | 1568116810000 | 2 | 0.9 | NULL | NULL | NULL |
  26. | 2019-09-10 12:00:01 | 1568116801000 | 3 | 0.4 | NULL | NULL | NULL |
  27. | 2019-09-10 12:00:02 | 1568116802000 | 3 | 0.4 | NULL | NULL | NULL |
  28. | 2019-09-10 12:00:03 | 1568116803000 | 3 | 0.4 | NULL | NULL | NULL |
  29. | 2019-09-10 12:00:04 | 1568116804000 | 3 | 0.4 | NULL | NULL | NULL |
  30. | 2019-09-10 12:00:05 | 1568116805000 | 3 | 0.4 | 0.4 | 1 | 0.4 |
  31. | 2019-09-10 12:00:06 | 1568116806000 | 3 | 0.5 | 0.5 | 1 | 0.5 |
  32. | 2019-09-10 12:00:07 | 1568116807000 | 3 | 0.5666666666666667 | NULL | NULL | NULL |
  33. | 2019-09-10 12:00:08 | 1568116808000 | 3 | 0.6333333333333333 | NULL | NULL | NULL |
  34. | 2019-09-10 12:00:09 | 1568116809000 | 3 | 0.7 | 0.7 | 1 | 0.7 |
  35. | 2019-09-10 12:00:10 | 1568116810000 | 3 | 0.7 | NULL | NULL | NULL |
  36. +---------------------+---------------+-----------+--------------------+------------+--------------+------------+