INFLUXDB Aggregate class functions

1) count () function

Returns the number of non-null values in an (field) field.

SELECT COUNT(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]
例子1
计算非空water_level数量

>select COUNT (water_level) from H2o_feet
Results Name:h2o_feet--------------time                           count1970-01-01t00:00:00z     15258

Description Water_level This field has a total of 15,258 data in the H2o_feet table.

Note: If the function in Influxdb does not have a specified time, it will default to epoch 0 ( 1970-01-01T00:00:00Z ) as the time.

You can add a time condition to the where, as follows:

Example 2

Calculates the number of non-null values water_level in 4-day intervals

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               count2015-08-17T00:00:00Z 14402015-08-21T00:00:00Z 19202015-08-25T00:00:00Z 19202015-08-29T00:00:00Z 19202015-09-02T00:00:00Z 19152015-09-06T00:00:00Z 19202015-09-10T00:00:00Z 19202015-09-14T00:00:00Z 19202015-09-18T00:00:00Z 335

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

2. DISTINCT () functionreturns the unique value of a field.

Grammar:

SELECT DISTINCT (<field_key>) from <measurement_name> [WHERE <stuff>] [GROUP by <stuff>]

例子1
在level description选择唯一的值

SELECT DISTINCT("level description") FROM h2o_feet
结果

name: h2o_feet--------------time               distinct1970-01-01T00:00:00Z between 6 and 9 feet1970-01-01T00:00:00Z below 3 feet1970-01-01T00:00:00Z between 3 and 6 feet1970-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_feettags: location=coyote_creektime                distinct----                --------1970-01-01T00:00:00Z  between 6 and 9 feet1970-01-01T00:00:00Z  between 3 and 6 feet1970-01-01T00:00:00Z  below 3 feet1970-01-01T00:00:00Z  at or greater than 9 feetname: h2o_feettags: location=santa_monicatime                distinct----                --------1970-01-01T00:00:00Z  below 3 feet1970-01-01T00:00:00Z  between 3 and 6 feet1970-01-01T00:00:00Z  between 6 and 9 feet

Example 3
aggregate function distinct () in count (), gets the unique number of level_description in the location group

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

结果

name: h2o_feettags: location = coyote_creektime               count----               -----1970-01-01T00:00:00Z 4name: h2o_feettags: location = santa_monicatime               count----               -----1970-01-01T00:00:00Z 3

3) MEAN () function

Returns the arithmetic mean (average) of a value in a field. The field type must be Long integer or float64.

Syntax format

SELECT MEAN(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]
例子1
计算water_level的平均值

SELECT MEAN(water_level) FROM h2o_feet
Results

name: h2o_feet--------------time               mean1970-01-01T00:00:00Z 4.286791371454075

Explain:
(Time is the default time, of course, you can also join the Where condition.) ）

Attention:

The aggregation function returns the time stamp of age 0 (1970-01-01t00:00:00z) unless you specify a lower bound time range. Then they go back to the time stamp of the Nether.
At Float64 Point the same set of execution mean () may produce slightly different results.
Influxdb before the function is not applicable, results in those small difference sorting points.

例子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               mean2015-08-17T00:00:00Z 4.3220298611111252015-08-21T00:00:00Z 4.2513955123756672015-08-25T00:00:00Z 4.2850364583333242015-08-29T00:00:00Z 4.4694958018990612015-09-02T00:00:00Z 4.3827853785900832015-09-06T00:00:00Z 4.288496663490422015-09-10T00:00:00Z 4.6581276041666562015-09-14T00:00:00Z 4.7635046875000062015-09-18T00:00:00Z 4.232829850746268

4. MEDIAN () function

Returns the median (median) value from the sorted value in a single field. The type of the field value must be a long integer or float64 format.

Grammar:

SELECT MEDIAN(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]

注：median()几乎相当于百分位数（field_key，50），除了median()返回两个中间值的平均值，如果该字段包含点偶数。
例子1
选择water_level的中间值

SELECT MEDIAN(water_level) from h2o_feet

结果

name: h2o_feet--------------time               median1970-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_feettags: location = coyote_creektime               median----               ------2015-08-18T00:00:00Z 7.8245name: h2o_feettags: location = santa_monicatime               median----               ------2015-08-18T00:00:00Z 2.0575

5) SPREAD () function

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

Grammar

SELECT SPREAD(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]

Example 1
Calculates the difference between the minimum and maximum values of the Water_level

SELECT SPREAD(water_level) FROM h2o_feet
Results

name: h2o_feet--------------time                spread1970-01-01T00:00:00Z  10.574

Attention:

The aggregation function returns the time stamp of age 0 (1970-01-01t00:00:00z) unless you specify a lower bound time range. Then they go back to the time stamp of the Nether.
At Float64 Point the same set of execution spread () may produce slightly different results. Influxdb before the function is not applicable, results in those small difference sorting points.

Example 2
Calculates the minimum and maximum values of the Water_level at 30-minute intervals, specifying the location assanta_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)
Results

name: h2o_feet--------------time                spread2015-09-18T17:00:00Z  0.166999999999999822015-09-18T17:30:00Z  0.54699999999999972015-09-18T18:00:00Z  0.474999999999999642015-09-18T18:30:00Z  0.25600000000000022015-09-18T19:00:00Z  0.238999999999999882015-09-18T19:30:00Z  0.16099999999999962015-09-18T20:00:00Z  0.16800000000000015

6) SUM () function

Returns the and of all the values in a field. The type of the field must be long integer or float64.

Grammar:

SELECT SUM(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]

例子1
计算water_level的所有值的和

SELECT SUM(water_level) FROM h2o_feet

 结果

name: h2o_feet--------------time               sum1970-01-01T00:00:00Z 67777.66900000002

注意：

The aggregation function returns the time stamp of age 0 (1970-01-01t00:00:00z) unless you specify a lower bound time range. Then they go back to the time stamp of the Nether.
The same set of execution sum () at the float64 point may produce slightly different results. Influxdb before the function is not applicable, results in those small difference sorting points.

Example 2

The calculations are grouped in 5 days, Water_level and

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)

Results:

--------------time               sum2015-08-18T00:00:00Z 10334.9089999999832015-08-23T00:00:00Z 10113.3569999999952015-08-28T00:00:00Z 10663.6830000000062015-09-02T00:00:00Z 10451.3212015-09-07T00:00:00Z 10871.8179999999942015-09-12T00:00:00Z 11459.000999999992015-09-17T00:00:00Z 3627.762000000003

INFLUXDB Aggregate class functions