数据
> SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
name: h2o_feet
time water_level
---- -----------
2015-08-18T00:00:00Z 2.064
2015-08-18T00:06:00Z 2.116
2015-08-18T00:12:00Z 2.028
2015-08-18T00:18:00Z 2.126
2015-08-18T00:24:00Z 2.041
2015-08-18T00:30:00Z 2.051
fill
填充默认值
例子
select sum(*) from water_level where time > new() -5m group by time(1m) fill(3)
解释:
意思是group by time 的时间维度如果没有数据的话用 3填充
DERIVATIVE
返回相邻两组数据的变化率
语法:
SELECT DERIVATIVE(<field_key>, [<unit>]) FROM <measurement_name> [WHERE <stuff>]
其中,unit
取值可以为以下几种:
u --microseconds
s --seconds
m --minutes
h --hours
d --days
w --weeks
例子
> SELECT DERIVATIVE("water_level",3m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
name: h2o_feet
time derivative
---- ----------
2015-08-18T00:06:00Z 0.00014444444444444457
2015-08-18T00:12:00Z -0.00024444444444444465
2015-08-18T00:18:00Z 0.0002722222222222218
2015-08-18T00:24:00Z -0.000236111111111111
2015-08-18T00:30:00Z 2.777777777777842e-05
解释:
计算结果的原理:(2.116 - 2.064) / (6m / 3m)
变化率说明:(06分的值-00分的值) / (06分 到00分的间隔时间 / 3m)
DIFFERENCE
返回响铃两组数据的差值
> SELECT DIFFERENCE(water_level) FROM h2o_feet WHERE location='santa_monica' AND time >= '2015-08-18T00:00:00Z' and time <= '2015-08-18T00:36:00Z'
name: h2o_feet
--------------
time difference
2015-08-18T00:06:00Z 0.052000000000000046
2015-08-18T00:12:00Z -0.08800000000000008
2015-08-18T00:18:00Z 0.09799999999999986
2015-08-18T00:24:00Z -0.08499999999999996
2015-08-18T00:30:00Z 0.010000000000000231
2015-08-18T00:36:00Z 0.016000000000000014
解释:这个很好理解了,就是差值