精华内容
下载资源
问答
  • InfluxDB 聚合函数实用案例文章大纲InfluxDB 简介InfluxDB是GO语言编写的分布式时间序列化数据库,非常适合对数据(跟随时间变化而变化的数据)的跟踪、监控和分析。在我们的项目中,主要是用来收集设备实时上传的值。...

    InfluxDB 聚合函数实用案例

    文章大纲

    InfluxDB 简介

    InfluxDB是GO语言编写的分布式时间序列化数据库,非常适合对数据(跟随时间变化而变化的数据)的跟踪、监控和分析。在我们的项目中,主要是用来收集设备实时上传的值。从而分析该设备值的趋势图和各个设备的能耗占比等一系列功能。InfluxDB的功能很强大,文档也很详细。可美中不足的是,它的单机性能并不是很理想。因为InfluxDB存储的数据量本身是非常巨大的,在执行一些时间范围比较大的sql语句,耗时会很长,甚至直接崩溃。而开源的InfluxDB目前已经不再支持集群。若要通过搭建集群提升性能问题,可以考虑企业版。当然,我们写的程序也有很大的性能优化空间。

    能耗趋势图分析

    需求:统计指定设备、指定区域、指定分项或者指定能耗类型的能耗趋势图。如下图所示,纵坐标是能耗值,横坐标是时刻(每小时、每天、每周、每月)。

    分析:获取某个区间时刻的值,可以用GROUP BY time 进行时间分组。再用聚合函数LAST或者SUM统计。但这个看似很简单的需求却暗藏杀机。SQL语句如下

    SELECT LAST("currentValue"), * FROM "$TABLE_NAME"

    WHERE time > '$startTime' AND time <= '$endTime' AND id = '$id'

    GROUP BY time($timeSpan)

    ORDER BY time DESC

    第一:先要清楚,数据是通过什么规则保存到InfluxDB数据库

    为了记录设备能耗的实时数据,我们会通过订阅MQTT通道,当值发生变化后存储到InfluxDB数据库中,或者在指定时间范围内没有变化也会上传。这样做的好处可以避免一些冗余数据,同时也埋下了一个坑。

    例如:一台设备在InfluxDB数据库中最后一次记录的时间是15分钟前。但是sql语句是从5分钟前开始统计。这会导致该设备的其点值就是null。简单来说:设备的存储的值正好在分组统计的时间范围外。解决方法有很多:比如用FILL(previous)函数填充;比如使用time(time_interval,offset_interval)进行时间推移等。但是我比较推荐下面的方法:

    先获取指定开始时间之前的最后值(lastValue),然后再根据返回值是否为null,来决定是否替换或者更新lastValue。伪代码如下。

    ## 获取该设备的最后记录值

    val lastValue = "SELECT LAST("currentValue") FROM "$TABLE_NAME" WHERE time <= '$startTime'"

    ## 遍历查询结果,将currentValue为 null的值替换

    "SELECT LAST("currentValue"), * FROM "$TABLE_NAME"

    WHERE time > '$startTime' AND time <= '$endTime' AND id = '$id'

    GROUP BY time($timeSpan)

    ORDER BY time DESC".forEach {

    lastValue = currentValue?: lastValue

    result[time] = currentValue?: lastValue

    }

    你以为这样就结束了吗?还不够,返回的time格式化后,你会发现有8小时的时区问题。

    第二:解决InfluxDB时区问题

    InfluxDB 默认以UTC时间存储并返回时间戳,查询返回的时间戳对应的也是UTC时间。我们需要通过tz()子句指定时区名称,比如Asia/Shanghai。若InfluxDB安装在Windows环境上,可能还会出现 error parsing query: unable to find time zone 错误,解决方法是安装GO语言环境,文章也详细介绍过。

    SELECT LAST("currentValue"), * FROM "$TABLE_NAME"

    WHERE time > '$startTime' AND time <= '$endTime' AND id = '$id'

    GROUP BY time($timeSpan)

    ORDER BY time DESC tz('Asia/Shanghai')

    实用tz() 子句后,返回的时间格式:"2019-11-18T13:50:00+08:00"。需要通过 "yyyy-MM-dd'T'HH:mm:ss" 将其格式化。

    第三:GROUP BY time 自然月

    group by time 支持秒、分钟、小时、天和周,却唯独不支持自然月。如果对数据的精准性要求不高,可以考虑使用30d实现。或者分12次统计。或者有更好的方法,请不吝赐教

    展开全文
  • InfluxDB函数详解

    千次阅读 2018-11-23 11:51:56
    Influxdb函数分为聚合函数,选择函数,转换函数,预测函数等。除了与普通数据库一样提供了基本操作函数外,还提供了一些特色函数以方便数据统计计算,下面将一一介绍其中一些常用的特色函数。 聚合函数:FILL()、...

    特色函数

    Influxdb函数分为聚合函数,选择函数,转换函数,预测函数等。除了与普通数据库一样提供了基本操作函数外,还提供了一些特色函数以方便数据统计计算,下面将一一介绍其中一些常用的特色函数。

    • 聚合函数:FILL()、INTEGRAL()、SPREAD(),MEAN(),MEDIAN()等。
    • 选择函数: SAMPLE()、PERCENTILE()、FIRST()、 LAST()、TOP()、BOTTOM()等。
    • 转换函数: DERIVATIVE()、DIFFERENCE()等。
    • 预测函数:HOLT_WINTERS()。
    数据准备
    > SELECT * FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z'
    name: h2o_feet
    time                 level description    location     water_level
    ----                 -----------------    --------     -----------
    2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
    2015-08-18T00:00:00Z below 3 feet         santa_monica 2.064
    2015-08-18T00:06:00Z between 6 and 9 feet coyote_creek 8.005
    2015-08-18T00:06:00Z below 3 feet         santa_monica 2.116
    2015-08-18T00:12:00Z between 6 and 9 feet coyote_creek 7.887
    2015-08-18T00:12:00Z below 3 feet         santa_monica 2.028
    2015-08-18T00:18:00Z between 6 and 9 feet coyote_creek 7.762
    2015-08-18T00:18:00Z below 3 feet         santa_monica 2.126
    2015-08-18T00:24:00Z between 6 and 9 feet coyote_creek 7.635
    2015-08-18T00:24:00Z below 3 feet         santa_monica 2.041
    2015-08-18T00:30:00Z between 6 and 9 feet coyote_creek 7.5
    2015-08-18T00:30:00Z below 3 feet         santa_monica 2.051
    
    GROUP BY,FILL()

    如下语句中

    • GROUP BY time(12m),*:表示以每12分钟和tag(location) 分组(如果是GROUP BY time(12m)则表示仅每12分钟分组,GROUP BY 参数只能是time和tag)。
    • fill(200):表示如果这个时间段没有数据,以200填充,
    • mean(field_key):求该范围内数据的平均值(注意:这是依据series来计算。其他还有 sum 求和,median 求中位数)。
    • LIMIT 7:表示限制返回的 point(记录数 ) 最多为7条
    • SLIMIT 1:则是限制返回的 series 为1个。

    这里的时间区间,起始时间为整点前包含这个区间第一个12m的时间,比如这里为 2015-08-17T:23:48:00Z,第一条为 2015-08-17T23:48:00Z <= t < 2015-08-18T00:00:00Z这个区间的location=coyote_creek的water_level的平均值,这里没有数据,于是填充的200。第二条为 2015-08-18T00:00:00Z <= t < 2015-08-18T00:12:00Z区间的location=coyote_creek的water_level平均值,这里为 (8.12+8.005)/ 2 = 8.0625,其他以此类推。

    ## GROUP BY time(12m)
    > SELECT mean("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),* fill(200) LIMIT 7 SLIMIT 1
    name: h2o_feet
    tags: location=coyote_creek
    time                 mean
    ----                 ----
    2015-08-17T23:48:00Z 200
    2015-08-18T00:00:00Z 8.0625
    2015-08-18T00:12:00Z 7.8245
    2015-08-18T00:24:00Z 7.5675
    

    而GROUP BY time(10m)则表示以10分钟分组,起始时间为包含这个区间的第一个10m的时间,即 2015-08-17T23:40:00Z。默认返回的是第一个series,如果要计算另外那个series,可以在SQL语句后面加上 SOFFSET 1。

    ## GROUP BY time(10m),SOFFSET设置为1
    > SELECT mean("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(10m),* fill(200) LIMIT 7 SLIMIT 1 SOFFSET 1
    name: h2o_feet
    tags: location=santa_monica
    time                 mean
    ----                 ----
    2015-08-17T23:40:00Z 200
    2015-08-17T23:50:00Z 200
    2015-08-18T00:00:00Z 2.09
    2015-08-18T00:10:00Z 2.077
    2015-08-18T00:20:00Z 2.041
    2015-08-18T00:30:00Z 2.051
    

    而GROUP BY time(10m)则表示以10分钟分组,起始时间为包含这个区间的第一个10m的时间,即 2015-08-17T23:40:00Z。默认返回的是第一个series,如果要计算另外那个series,可以在SQL语句后面加上 SOFFSET 1。

    INTEGRAL(field_key, unit)

    计算数值字段值覆盖的曲面的面积值并得到面积之和。测试数据如下:

    > SELECT "water_level" 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                   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
    

    使用INTERGRAL计算面积。注意,这个面积就是这些点连接起来后与时间围成的不规则图形的面积,注意unit默认是以1秒计算,所以下面语句计算结果为3732.66=2.028*1800+分割出来的梯形和三角形面积。如果unit改为1分,则结果为3732.66/60 = 62.211。unit为2分,则结果为3732.66/120 = 31.1055。以此类推。

    # unit为默认的1秒
    > SELECT INTEGRAL("water_level") 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                 integral
    ----                 --------
    1970-01-01T00:00:00Z 3732.66
    
    # unit为1分
    > SELECT INTEGRAL("water_level", 1m) 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                 integral
    ----                 --------
    1970-01-01T00:00:00Z 62.211
    
    SPREAD(field_key)

    计算数值字段的最大值和最小值的差值。

    > SELECT SPREAD("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),* fill(18) LIMIT 3 SLIMIT 1 SOFFSET 1
    name: h2o_feet
    tags: location=santa_monica
    time                 spread
    ----                 ------
    2015-08-17T23:48:00Z 18
    2015-08-18T00:00:00Z 0.052000000000000046
    2015-08-18T00:12:00Z 0.09799999999999986
    
    STDDEV(field_key)

    **计算字段的标准差。influxdb用的是贝塞尔修正的标准差计算公式 ,如下:

    • mean=(v1+v2+…+vn)/n;
    • stddev = math.sqrt(
      ((v1-mean)2 + (v2-mean)2 + …+(vn-mean)2)/(n-1)
      )
    > SELECT STDDEV("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),* fill(18) SLIMIT 1;
    name: h2o_feet
    tags: location=coyote_creek
    time                 stddev
    ----                 ------
    2015-08-17T23:48:00Z 18
    2015-08-18T00:00:00Z 0.08131727983645186
    2015-08-18T00:12:00Z 0.08838834764831845
    2015-08-18T00:24:00Z 0.09545941546018377
    
    PERCENTILE(field_key, N)

    选取某个字段中大于N%的这个字段值。

    如果一共有4条记录,N为10,则10%*4=0.4,四舍五入为0,则查询结果为空。N为20,则 20% * 4 = 0.8,四舍五入为1,选取的是4个数中最小的数。如果N为40,40% * 4 = 1.6,四舍五入为2,则选取的是4个数中第二小的数。由此可以看出N=100时,就跟MAX(field_key)是一样的,而当N=50时,与MEDIAN(field_key)在字段值为奇数个时是一样的。

    > SELECT PERCENTILE("water_level",20) FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
    name: h2o_feet
    time                 percentile
    ----                 ----------
    2015-08-17T23:48:00Z 
    2015-08-18T00:00:00Z 2.064
    2015-08-18T00:12:00Z 2.028
    2015-08-18T00:24:00Z 2.041
    
    > SELECT PERCENTILE("water_level",40) FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
    name: h2o_feet
    time                 percentile
    ----                 ----------
    2015-08-17T23:48:00Z 
    2015-08-18T00:00:00Z 2.116
    2015-08-18T00:12:00Z 2.126
    2015-08-18T00:24:00Z 2.051
    
    SAMPLE(field_key, N)

    随机返回field key的N个值。如果语句中有GROUP BY time(),则每组数据随机返回N个值。

    > SELECT SAMPLE("water_level",2) FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z';
    name: h2o_feet
    time                 sample
    ----                 ------
    2015-08-18T00:00:00Z 2.064
    2015-08-18T00:12:00Z 2.028
    
    > SELECT SAMPLE("water_level",2) FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m);
    name: h2o_feet
    time                 sample
    ----                 ------
    2015-08-18T00:06:00Z 2.116
    2015-08-18T00:06:00Z 8.005
    2015-08-18T00:12:00Z 7.887
    2015-08-18T00:18:00Z 7.762
    2015-08-18T00:24:00Z 7.635
    2015-08-18T00:30:00Z 2.051
    

    CUMULATIVE_SUM(field_key)

    计算字段值的递增和。

    > SELECT CUMULATIVE_SUM("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:30:00Z';
    name: h2o_feet
    time                 cumulative_sum
    ----                 --------------
    2015-08-18T00:00:00Z 8.12
    2015-08-18T00:00:00Z 10.184
    2015-08-18T00:06:00Z 18.189
    2015-08-18T00:06:00Z 20.305
    2015-08-18T00:12:00Z 28.192
    2015-08-18T00:12:00Z 30.22
    2015-08-18T00:18:00Z 37.982
    2015-08-18T00:18:00Z 40.108
    2015-08-18T00:24:00Z 47.742999999999995
    2015-08-18T00:24:00Z 49.78399999999999
    2015-08-18T00:30:00Z 57.28399999999999
    2015-08-18T00:30:00Z 59.334999999999994
    
    DERIVATIVE(field_key, unit) 和 NON_NEGATIVE_DERIVATIVE(field_key, unit)

    计算字段值的变化比。unit默认为1s,即计算的是1秒内的变化比。

    如下面的第一个数据计算方法是 (2.116-2.064)/(6*60) = 0.00014…,其他计算方式同理。虽然原始数据是6m收集一次,但是这里的变化比默认是按秒来计算的。如果要按6m计算,则设置unit为6m即可。

    > SELECT DERIVATIVE("water_level") 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 0.00002777777777777842
    
    > SELECT DERIVATIVE("water_level", 6m) 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.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
    

    而DERIVATIVE结合GROUP BY time,以及mean可以构造更加复杂的查询,如下所示:

    > SELECT DERIVATIVE(mean("water_level"), 6m) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' group by time(12m), *
    name: h2o_feet
    tags: location=coyote_creek
    time                 derivative
    ----                 ----------
    2015-08-18T00:12:00Z -0.11900000000000022
    2015-08-18T00:24:00Z -0.12849999999999984
    
    name: h2o_feet
    tags: location=santa_monica
    time                 derivative
    ----                 ----------
    2015-08-18T00:12:00Z -0.00649999999999995
    2015-08-18T00:24:00Z -0.015499999999999847
    

    这个计算其实是先根据GROUP BY time求平均值,然后对这个平均值再做变化比的计算。因为数据是按12分钟分组的,而变化比的unit是6分钟,所以差值除以2(12/6)才得到变化比。如第一个值是 (7.8245-8.0625)/2 = -0.1190。

    > SELECT mean("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' group by time(12m), *
    name: h2o_feet
    tags: location=coyote_creek
    time                 mean
    ----                 ----
    2015-08-18T00:00:00Z 8.0625
    2015-08-18T00:12:00Z 7.8245
    2015-08-18T00:24:00Z 7.5675
    
    name: h2o_feet
    tags: location=santa_monica
    time                 mean
    ----                 ----
    2015-08-18T00:00:00Z 2.09
    2015-08-18T00:12:00Z 2.077
    2015-08-18T00:24:00Z 2.0460000000000003
    

    NON_NEGATIVE_DERIVATIVE与DERIVATIVE不同的是它只返回的是非负的变化比:

    > SELECT DERIVATIVE(mean("water_level"), 6m) FROM "h2o_feet" WHERE location='santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' group by time(6m), *
    name: h2o_feet
    tags: location=santa_monica
    time                 derivative
    ----                 ----------
    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
    
    > SELECT NON_NEGATIVE_DERIVATIVE(mean("water_level"), 6m) FROM "h2o_feet" WHERE location='santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' group by time(6m), *
    name: h2o_feet
    tags: location=santa_monica
    time                 non_negative_derivative
    ----                 -----------------------
    2015-08-18T00:06:00Z 0.052000000000000046
    2015-08-18T00:18:00Z 0.09799999999999986
    2015-08-18T00:30:00Z 0.010000000000000231
    

    连续查询

    基本语法

    连续查询(CONTINUOUS QUERY,简写为CQ)是指定时自动在实时数据上进行的InfluxQL查询,查询结果可以存储到指定的measurement中。基本语法格式如下:

    CREATE CONTINUOUS QUERY <cq_name> ON <database_name>
    BEGIN
      <cq_query>
    END
    
    cq_query格式:
    SELECT <function[s]> INTO <destination_measurement> FROM <measurement> [WHERE <stuff>] GROUP BY time(<interval>)[,<tag_key[s]>]
    

    CQ操作的是实时数据,它使用本地服务器的时间戳、GROUP BY time()时间间隔以及InfluxDB预先设置好的时间范围来确定什么时候开始查询以及查询覆盖的时间范围。注意CQ语句里面的WHERE条件是没有时间范围的,因为CQ会根据GROUP BY time()自动确定时间范围。

    CQ执行的时间间隔和GROUP BY time()的时间间隔一样,它在InfluxDB预先设置的时间范围的起始时刻执行。如果GROUP BY time(1h),则单次查询的时间范围为 now()-GROUP BY time(1h)到 now(),也就是说,如果当前时间为17点,这次查询的时间范围为 16:00到16:59.99999。

    下面看几个示例,示例数据如下,这是数据库transportation中名为bus_data的measurement,每15分钟统计一次乘客数和投诉数。数据文件bus_data.txt如下:

    # DDL
    CREATE DATABASE transportation
    
    # DML
    # CONTEXT-DATABASE: transportation 
    
    bus_data,complaints=9 passengers=5 1472367600
    bus_data,complaints=9 passengers=8 1472368500
    bus_data,complaints=9 passengers=8 1472369400
    bus_data,complaints=9 passengers=7 1472370300
    bus_data,complaints=9 passengers=8 1472371200
    bus_data,complaints=7 passengers=15 1472372100
    bus_data,complaints=7 passengers=15 1472373000
    bus_data,complaints=7 passengers=17 1472373900
    bus_data,complaints=7 passengers=20 1472374800
    

    导入数据,命令如下:

    [root@f216e9be15bf:/]# influx -import -path=bus_data.txt -precision=s
    [root@f216e9be15bf:/]# influx -precision=rfc3339 -database=transportation
    Connected to http://localhost:8086 version 1.3.5
    InfluxDB shell version: 1.3.5
    > select * from bus_data
    name: bus_data
    time                 complaints passengers
    ----                 ---------- ----------
    2016-08-28T07:00:00Z 9          5
    2016-08-28T07:15:00Z 9          8
    2016-08-28T07:30:00Z 9          8
    2016-08-28T07:45:00Z 9          7
    2016-08-28T08:00:00Z 9          8
    2016-08-28T08:15:00Z 7          15
    2016-08-28T08:30:00Z 7          15
    2016-08-28T08:45:00Z 7          17
    2016-08-28T09:00:00Z 7          20
    
    示例1 自动缩小取样存储到新的measurement中

    对单个字段自动缩小取样并存储到新的measurement中。

    CREATE CONTINUOUS QUERY "cq_basic" ON "transportation"
    BEGIN
      SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
    END
    

    这个CQ的意思就是对bus_data每小时自动计算取样数据的平均乘客数并存储到 average_passengers中。那么在2016-08-28这天早上会执行如下流程:

    At 8:00 cq_basic 执行查询,查询时间范围 time >= '7:00' AND time < '08:00'.
    cq_basic写入一条记录到 average_passengers:
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:00:00Z   7
    At 9:00 cq_basic 执行查询,查询时间范围 time >= '8:00' AND time < '9:00'.
    cq_basic写入一条记录到 average_passengers:
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T08:00:00Z   13.75
    
    # Results
    > SELECT * FROM "average_passengers"
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:00:00Z   7
    2016-08-28T08:00:00Z   13.75
    
    示例2 自动缩小取样并存储到新的保留策略(Retention Policy)中
    CREATE CONTINUOUS QUERY "cq_basic_rp" ON "transportation"
    BEGIN
      SELECT mean("passengers") INTO "transportation"."three_weeks"."average_passengers" FROM "bus_data" GROUP BY time(1h)
    END
    

    与示例1类似,不同的是保留的策略不是autogen,而是改成了three_weeks(创建保留策略语法 CREATE RETENTION POLICY “three_weeks” ON “transportation” DURATION 3w REPLICATION 1)。

    > SELECT * FROM "transportation"."three_weeks"."average_passengers"
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:00:00Z   7
    2016-08-28T08:00:00Z   13.75
    
    示例3 使用后向引用(backreferencing)自动缩小取样并存储到新的数据库中
    CREATE CONTINUOUS QUERY "cq_basic_br" ON "transportation"
    BEGIN
      SELECT mean(*) INTO "downsampled_transportation"."autogen".:MEASUREMENT FROM /.*/ GROUP BY time(30m),*
    END
    

    使用后向引用语法自动缩小取样并存储到新的数据库中。语法 :MEASUREMENT 用来指代后面的表,而 /.*/则是分别查询所有的表。这句CQ的含义就是每30分钟自动查询transportation的所有表(这里只有bus_data一个表),并将30分钟内数字字段(passengers和complaints)求平均值存储到新的数据库 downsampled_transportation中。

    最终结果如下:

    > SELECT * FROM "downsampled_transportation."autogen"."bus_data"
    name: bus_data
    --------------
    time                   mean_complaints   mean_passengers
    2016-08-28T07:00:00Z   9                 6.5
    2016-08-28T07:30:00Z   9                 7.5
    2016-08-28T08:00:00Z   8                 11.5
    2016-08-28T08:30:00Z   7                 16
    
    示例4 自动缩小取样以及配置CQ的时间范围
    CREATE CONTINUOUS QUERY "cq_basic_offset" ON "transportation"
    BEGIN
      SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h,15m)
    END
    

    与前面几个示例不同的是,这里的GROUP BY time(1h, 15m)指定了一个时间偏移,也就是说 cq_basic_offset执行的时间不再是整点,而是往后偏移15分钟。执行流程如下:

    At 8:15 cq_basic_offset 执行查询的时间范围 time >= '7:15' AND time < '8:15'.
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:15:00Z   7.75
    At 9:15 cq_basic_offset 执行查询的时间范围 time >= '8:15' AND time < '9:15'.
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T08:15:00Z   16.75
    

    最终结果

    > SELECT * FROM "average_passengers"
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:15:00Z   7.75
    2016-08-28T08:15:00Z   16.75
    

    高级语法

    InfluxDB连续查询的高级语法如下:

    CREATE CONTINUOUS QUERY <cq_name> ON <database_name>
    RESAMPLE EVERY <interval> FOR <interval>
    BEGIN
      <cq_query>
    END
    

    与基本语法不同的是,多了RESAMPLE关键字。高级语法里CQ的执行时间和查询时间范围则与RESAMPLE里面的两个interval有关系。

    高级语法中CQ以EVERY interval的时间间隔执行,执行时查询的时间范围则是FOR interval来确定。如果FOR interval为2h,当前时间为17:00,则查询的时间范围为15:00-16:59.999999。RESAMPLE的EVERY和FOR两个关键字可以只有一个。

    示例的数据表如下,比之前的多了几条记录为了示例3和示例4的测试:

    name: bus_data
    --------------
    time                   passengers
    2016-08-28T06:30:00Z   2
    2016-08-28T06:45:00Z   4
    2016-08-28T07:00:00Z   5
    2016-08-28T07:15:00Z   8
    2016-08-28T07:30:00Z   8
    2016-08-28T07:45:00Z   7
    2016-08-28T08:00:00Z   8
    2016-08-28T08:15:00Z   15
    2016-08-28T08:30:00Z   15
    2016-08-28T08:45:00Z   17
    2016-08-28T09:00:00Z   20
    
    示例1 只配置执行时间间隔
    CREATE CONTINUOUS QUERY "cq_advanced_every" ON "transportation"
    RESAMPLE EVERY 30m
    BEGIN
      SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
    END
    

    这里配置了30分钟执行一次CQ,没有指定FOR interval,于是查询的时间范围还是GROUP BY time(1h)指定的一个小时,执行流程如下:

    At 8:00, cq_advanced_every 执行时间范围 time >= '7:00' AND time < '8:00'.
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:00:00Z   7
    At 8:30, cq_advanced_every 执行时间范围 time >= '8:00' AND time < '9:00'.
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T08:00:00Z   12.6667
    At 9:00, cq_advanced_every 执行时间范围 time >= '8:00' AND time < '9:00'.
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T08:00:00Z   13.75
    

    需要注意的是,这里的 8点到9点这个区间执行了两次,第一次执行时时8:30,平均值是 (8+15+15)/ 3 = 12.6667,而第二次执行时间是9:00,平均值是 (8+15+15+17) / 4=13.75,而且最后第二个结果覆盖了第一个结果。InfluxDB如何处理重复的记录可以参见这个文档。

    最终结果:

    > SELECT * FROM "average_passengers"
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:00:00Z   7
    2016-08-28T08:00:00Z   13.75
    
    示例2 只配置查询时间范围
    CREATE CONTINUOUS QUERY "cq_advanced_for" ON "transportation"
    RESAMPLE FOR 1h
    BEGIN
      SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(30m)
    END
    

    只配置了时间范围,而没有配置EVERY interval。这样,执行的时间间隔与GROUP BY time(30m)一样为30分钟,而查询的时间范围为1小时,由于是按30分钟分组,所以每次会写入两条记录。执行流程如下:

    At 8:00 cq_advanced_for 查询时间范围:time >= '7:00' AND time < '8:00'.
    写入两条记录。
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:00:00Z   6.5
    2016-08-28T07:30:00Z   7.5
    At 8:30 cq_advanced_for 查询时间范围:time >= '7:30' AND time < '8:30'.
    写入两条记录。
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:30:00Z   7.5
    2016-08-28T08:00:00Z   11.5
    At 9:00 cq_advanced_for 查询时间范围:time >= '8:00' AND time < '9:00'.
    写入两条记录。
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T08:00:00Z   11.5
    2016-08-28T08:30:00Z   16
    

    需要注意的是,cq_advanced_for每次写入了两条记录,重复的记录会被覆盖。

    最终结果:

    > SELECT * FROM "average_passengers"
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:00:00Z   6.5
    2016-08-28T07:30:00Z   7.5
    2016-08-28T08:00:00Z   11.5
    2016-08-28T08:30:00Z   16
    
    示例3 同时配置执行时间间隔和查询时间范围
    CREATE CONTINUOUS QUERY "cq_advanced_every_for" ON "transportation"
    RESAMPLE EVERY 1h FOR 90m
    BEGIN
      SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(30m)
    END
    

    这里配置了执行间隔为1小时,而查询范围90分钟,最后分组是30分钟,每次插入了三条记录。执行流程如下:

    At 8:00 cq_advanced_every_for 查询时间范围 time >= '6:30' AND time < '8:00'.
    插入三条记录
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T06:30:00Z   3
    2016-08-28T07:00:00Z   6.5
    2016-08-28T07:30:00Z   7.5
    At 9:00 cq_advanced_every_for 查询时间范围 time >= '7:30' AND time < '9:00'.
    插入三条记录
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T07:30:00Z   7.5
    2016-08-28T08:00:00Z   11.5
    2016-08-28T08:30:00Z   16
    

    最终结果:

    > SELECT * FROM "average_passengers"
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T06:30:00Z   3
    2016-08-28T07:00:00Z   6.5
    2016-08-28T07:30:00Z   7.5
    2016-08-28T08:00:00Z   11.5
    2016-08-28T08:30:00Z   16
    
    示例4 配置查询时间范围和FILL填充
    CREATE CONTINUOUS QUERY "cq_advanced_for_fill" ON "transportation"
    RESAMPLE FOR 2h
    BEGIN
      SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h) fill(1000)
    END
    

    在前面值配置查询时间范围的基础上,加上FILL填充空的记录。执行流程如下:

    At 6:00, cq_advanced_for_fill 查询时间范围:time >= '4:00' AND time < '6:00',没有数据,不填充。
    
    At 7:00, cq_advanced_for_fill 查询时间范围:time >= '5:00' AND time < '7:00'. 写入两条记录,没有数据的时间点填充1000。
    ------------------------
    time                   mean
    2016-08-28T05:00:00Z   1000          <------ fill(1000)
    2016-08-28T06:00:00Z   3             <------ average of 2 and 4
    
    […] At 11:00, cq_advanced_for_fill 查询时间范围:time >= '9:00' AND time < '11:00'.写入两条记录,没有数据的点填充1000。
    name: average_passengers
    ------------------------
    2016-08-28T09:00:00Z   20            <------ average of 20
    2016-08-28T10:00:00Z   1000          <------ fill(1000)     
    
    At 12:00, cq_advanced_for_fill 查询时间范围:time >= '10:00' AND time < '12:00'。没有数据,不填充。
    

    最终结果:

    > SELECT * FROM "average_passengers"
    name: average_passengers
    ------------------------
    time                   mean
    2016-08-28T05:00:00Z   1000
    2016-08-28T06:00:00Z   3
    2016-08-28T07:00:00Z   7
    2016-08-28T08:00:00Z   13.75
    2016-08-28T09:00:00Z   20
    2016-08-28T10:00:00Z   1000
    

    显示所有已存在的连续查询

    查询所有连续查询可以使用如下语句:

    > SHOW CONTINUOUS QUERIES
    name: telegraf
    --------------
    name    query
    cq_30m    CREATE CONTINUOUS QUERY cq_30m ON telegraf
    BEGIN SELECT mean(used) INTO telegraf."default".mem_used_30m FROM telegraf."default".mem
    GROUP BY time(30m) END
     
    name: _internal
    ---------------
    name    query
    

    删除Continuous Queries

    删除连续查询的语句如下:

    DROP CONTINUOUS QUERY <cq_name> ON <database_name>
    
    展开全文
  • InfluxDB常用函数

    千次阅读 2020-06-13 20:30:51
    InfluxDB常用函数及Grafana参数

    常用函数

    • 聚合类
      1、COUNT() :返回一个field中的非空值数量
      2、MEAN() 函数:求平均值,字段类型必须是整型或浮点型
      3、SPREAD()函数:返回字段的最小值和最大值的差值,字段类型必须是整型或浮点型
      4、SUM()函数:求和,字段类型必须是整型或浮点型
    • 选择类
      TOP()函数:返回字段中最大的前N个值,字段必须是整型或浮点型
      BOTTOM()函数:返回字段中最小的N个值,使用方法同上
      MAX()函数:返回字段中最大的值,该字段类型必须是长整型,float64,或布尔类型。
      MIN()函数:返回一个字段中的最小值。该字段类型必须是长整型,float64,或布尔类型。
    • 转换类
      DIFFERENCE()函数:返回一个字段中连续的时间值之间的差异,字段类型必须是长整型或float64。
    • 预测类
      HOLT_WINTERS():返回N个预测字段值

    参数
    $__interval:group time(interval)会对查询结果按照interval进行聚合,例如,time(5m),interval=5m, 则会将数据每隔5m进行聚合

    展开全文
  • 1)count()函数返回一个(field)字段中的非空值的数量。SELECT COUNT() FROM [WHERE ] [GROUP BY ]例子1计算非空water_level数量>SELECT COUNT(water_level) FROM h2o_feet结果name: h2o_feet--------------time co...

    1)count()函数

    返回一个(field)字段中的非空值的数量。

    SELECT COUNT() FROM [WHERE ] [GROUP BY ]

    例子1

    计算非空water_level数量

    >SELECT COUNT(water_level) FROM h2o_feet

    结果

    name: h2o_feet

    --------------

    time count

    1970-01-01T00:00:00Z 15258

    说明 water_level这个字段在 h2o_feet表中共有15258条数据。

    注意:InfluxDB中的函数如果没有指定时间的话,会默认以 epoch 0 (1970-01-01T00:00:00Z) 作为时间。

    可以在where 中加入时间条件,如下:

    例子2

    计算非空值water_level在4天时间间隔里的数量

    SELECT COUNT(water_level) FROM h2o_feet WHERE time >= ‘2015-08-18T00:00:00Z‘ AND time < ‘2015-09-18T17:00:00Z‘ GROUP BY time(4d)

    结果

    name: h2o_feet

    --------------

    time count

    2015-08-17T00:00:00Z 1440

    2015-08-21T00:00:00Z 1920

    2015-08-25T00:00:00Z 1920

    2015-08-29T00:00:00Z 1920

    2015-09-02T00:00:00Z 1915

    2015-09-06T00:00:00Z 1920

    2015-09-10T00:00:00Z 1920

    2015-09-14T00:00:00Z 1920

    2015-09-18T00:00:00Z 335

    这样结果中会包含时间结果。

    2、DISTINCT()函数

    返回一个字段(field)的唯一值。

    语法:

    SELECT DISTINCT() FROM [WHERE ] [GROUP BY ]

    例子1

    在level description选择唯一的值

    SELECT DISTINCT("level description") FROM h2o_feet

    结果

    name: h2o_feet

    --------------

    time distinct

    1970-01-01T00:00:00Z between 6 and 9 feet

    1970-01-01T00:00:00Z below 3 feet

    1970-01-01T00:00:00Z between 3 and 6 feet

    1970-01-01T00:00:00Z at or greater than 9 feet

    这个例子显示level description这个字段共有四个值,然后将其显示了出来,时间为默认时间。

    注:聚合函数返回的时代0(1970-01-01t00:00:00z)为时间戳,除非您指定一个下界的时间范围。然后返回下界作为时间戳。

    例子2

    选择唯一的值在leve description 以location tag分组

    SELECT DISTINCT("level description") FROM h2o_feet GROUP BY location

    结果

    name: h2o_feet

    tags: location=coyote_creek

    time distinct

    ---- --------

    1970-01-01T00:00:00Z between 6 and 9 feet

    1970-01-01T00:00:00Z between 3 and 6 feet

    1970-01-01T00:00:00Z below 3 feet

    1970-01-01T00:00:00Z at or greater than 9 feet

    name: h2o_feet

    tags: location=santa_monica

    time distinct

    ---- --------

    1970-01-01T00:00:00Z below 3 feet

    1970-01-01T00:00:00Z between 3 and 6 feet

    1970-01-01T00:00:00Z between 6 and 9 feet

    例子3

    聚合函数DISTINCT() 在count(),以location分组获得level_description的唯一数量

    SELECT COUNT(DISTINCT("level description")) FROM h2o_feet GROUP BY location

    结果

    name: h2o_feet

    tags: location = coyote_creek

    time count

    ---- -----

    1970-01-01T00:00:00Z 4

    name: h2o_feet

    tags: location = santa_monica

    time count

    ---- -----

    1970-01-01T00:00:00Z 3

    3)MEAN() 函数

    返回一个字段(field)中的值的算术平均值(平均值)。字段类型必须是长整型或float64。

    语法格式

    SELECT MEAN() FROM [WHERE ] [GROUP BY ]

    例子1

    计算water_level的平均值

    SELECT MEAN(water_level) FROM h2o_feet结果

    name: h2o_feet

    --------------

    time mean

    1970-01-01T00:00:00Z 4.286791371454075

    解释:

    (时间为默认时间,当然,你也可以加入where条件。)

    注意:

    聚合函数返回的时代0(1970-01-01t00:00:00z)为时间戳,除非您指定一个下界的时间范围。然后他们回到下界的时间戳。

    在float64点同一套执行mean()可能会产生稍微不同的结果。

    InfluxDB之前不适用的功能,结果在那些小差异排序分。

    例子2、计算wate_level以4天为间隔的平均值

    SELECT MEAN(water_level) FROM h2o_feet WHERE time >= ‘2015-08-18T00:00:00Z‘ AND time < ‘2015-09-18T17:00:00Z‘ GROUP BY time(4d)

    结果

    name: h2o_feet

    --------------

    time mean

    2015-08-17T00:00:00Z 4.322029861111125

    2015-08-21T00:00:00Z 4.251395512375667

    2015-08-25T00:00:00Z 4.285036458333324

    2015-08-29T00:00:00Z 4.469495801899061

    2015-09-02T00:00:00Z 4.382785378590083

    2015-09-06T00:00:00Z 4.28849666349042

    2015-09-10T00:00:00Z 4.658127604166656

    2015-09-14T00:00:00Z 4.763504687500006

    2015-09-18T00:00:00Z 4.232829850746268

    4、MEDIAN()函数

    从单个字段(field)中的排序值返回中间值(中位数)。字段值的类型必须是长整型或float64格式。

    语法:

    SELECT MEDIAN() FROM [WHERE ] [GROUP BY ]

    注:median()几乎相当于百分位数(field_key,50),除了median()返回两个中间值的平均值,如果该字段包含点偶数。

    例子1

    选择water_level的中间值

    SELECT MEDIAN(water_level) from h2o_feet

    结果

    name: h2o_feet

    --------------

    time median

    1970-01-01T00:00:00Z 4.124

    解释:

    说明表中 water_level字段的中位数是 4.124

    注:聚合函数返回的时代0(1970-01-01t00:00:00z)为时间戳,除非您指定一个下界的时间范围。然后返回下界作为时间戳。

    例子2

    选择时间在2015年8月18日和8月18日30分,以location分组water_level的中间值

    SELECT MEDIAN(water_level) FROM h2o_feet WHERE time >= ‘2015-08-18T00:00:00Z‘ AND time < ‘2015-08-18T00:36:00Z‘ GROUP BY location

    结果

    name: h2o_feet

    tags: location = coyote_creek

    time median

    ---- ------

    2015-08-18T00:00:00Z 7.8245

    name: h2o_feet

    tags: location = santa_monica

    time median

    ---- ------

    2015-08-18T00:00:00Z 2.0575

    5)SPREAD()函数

    返回字段的最小值和最大值之间的差值。数据的类型必须是长整型或float64。

    语法

    SELECT SPREAD() FROM [WHERE ] [GROUP BY ]

    例子1

    计算water_level的最小值 与最大值 之间差

    SELECT SPREAD(water_level) FROM h2o_feet

    结果

    name: h2o_feet

    --------------

    time spread

    1970-01-01T00:00:00Z 10.574

    注意:

    聚合函数返回的时代0(1970-01-01t00:00:00z)为时间戳,除非您指定一个下界的时间范围。然后他们回到下界的时间戳。

    在float64点同一套执行spread()可能会产生稍微不同的结果。InfluxDB之前不适用的功能,结果在那些小差异排序分。

    例子2

    计算water_level的最小值 与最大值以30分钟间隔,指定location为santa_monica,和一个时间范围

    SELECT SPREAD(water_level) FROM h2o_feet WHERE location = ‘santa_monica‘ AND time >= ‘2015-09-18T17:00:00Z‘ AND time < ‘2015-09-18T20:30:00Z‘ GROUP BY time(30m)

    结果

    name: h2o_feet

    --------------

    time spread

    2015-09-18T17:00:00Z 0.16699999999999982

    2015-09-18T17:30:00Z 0.5469999999999997

    2015-09-18T18:00:00Z 0.47499999999999964

    2015-09-18T18:30:00Z 0.2560000000000002

    2015-09-18T19:00:00Z 0.23899999999999988

    2015-09-18T19:30:00Z 0.1609999999999996

    2015-09-18T20:00:00Z 0.16800000000000015

    6)SUM()函数

    返回一个字段中的所有值的和。字段的类型必须是长整型或float64。

    语法:

    SELECT SUM() FROM [WHERE ] [GROUP BY ]

    例子1

    计算water_level的所有值的和

    SELECT SUM(water_level) FROM h2o_feet

    结果

    name: h2o_feet

    --------------

    time sum

    1970-01-01T00:00:00Z 67777.66900000002

    注意:

    聚合函数返回的时代0(1970-01-01t00:00:00z)为时间戳,除非您指定一个下界的时间范围。然后他们回到下界的时间戳。

    在float64点同一套执行sum()可能会产生稍微不同的结果。InfluxDB之前不适用的功能,结果在那些小差异排序分。

    例子2

    计算以5天为分组,water_level的和

    SELECT SUM(water_level) FROM h2o_feet WHERE time >= ‘2015-08-18T00:00:00Z‘ AND time < ‘2015-09-18T17:00:00Z‘ GROUP BY time(5d)

    结果:

    --------------

    time sum

    2015-08-18T00:00:00Z 10334.908999999983

    2015-08-23T00:00:00Z 10113.356999999995

    2015-08-28T00:00:00Z 10663.683000000006

    2015-09-02T00:00:00Z 10451.321

    2015-09-07T00:00:00Z 10871.817999999994

    2015-09-12T00:00:00Z 11459.00099999999

    2015-09-17T00:00:00Z 3627.762000000003

    展开全文
  • 一、count()函数返回一个(field)字段中的非空值的数量。语法:SELECT COUNT() FROM [WHERE ] [GROUP BY ]示例:>SELECT COUNT(water_level) FROM h2o_feetname: h2o_feet--------------time count1970-01-01T00.....
  • 1)DERIVATIVE()函数 作用:返回一个字段在一个series中的变化率。 InfluxDB会计算按照时间进行排序的字段值之间的差异,并将这些结果转化为单位变化率。其中,单位可以指定,默认为1s。 语法: SELECTDERIVATIVE(&...
  • 1)count()函数 返回一个(field)字段中的非空值的数量。 语法: SELECTCOUNT(<field_key>)FROM<measurement_name>[WHERE<stuff>][GROUPBY<stuff>] 示例: >SELECTCOUNT(water_l...
  • 1)BOTTOM()函数 作用:返回一个字段中最小的N个值。字段类型必须是长整型或float64类型。 语法: SELECTBOTTOM(<field_key>[,<tag_keys>],<N>)[,<tag_keys>]FROM<measurement_name>...

空空如也

空空如也

1 2 3 4 5 ... 16
收藏数 309
精华内容 123
关键字:

influxdb函数