influxdb基础---常用函数

参考

使用InfluxQL函数聚合，选择，转换和预测数据
大部分函数与sql的类似，且命名方式比较接近自然用语，通过名称也能猜出大概意思^_^

聚合类
函数    描述    语法
COUNT()    返回一个字段中的非空值的数量    SELECT COUNT( [ * | <field_key> | /<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
DISTINCT()    返回一个字段去重后的唯一值    SELECT DISTINCT( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
INTEGRAL()    返回曲线下面的字段值    SELECT INTEGRAL( [ * | <field_key> | /<regular_expression>/ ] [ , <unit> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
MEAN()    返回一个字段中的值的算术平均值（平均值）。字段类型必须是长整型或float64    SELECT MEAN(<field_key>) FROM <measurement_name> [WHERE <stuff>] [GROUP BY <stuff>]
MEDIAN()    从单个字段中的排序值返回中间值（中位数）。中值是在一组数值中居于中间的数值。字段值的类型必须是长整型或float64格式    SELECT MEDIAN( [ * | <field_key> | /<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
MODE()    返回字段值列表中最常用的值    SELECT MODE( [ * | <field_key> | /<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
SPREAD()    返回字段的最小值和最大值之间的差值。数据的类型必须是长整型或float64    SELECT SPREAD( [ * | <field_key> | /<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
STDDEV()    返回字段值的标准偏差    SELECT STDDEV( [ * | <field_key> | /<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
SUM()    返回一个字段中的所有值的和。字段的类型必须是长整型或float64    SELECT SUM( [ * | <field_key> | /<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
 

选择类
函数    描述    语法
BOTTOM()    返回一个字段中最小的N个值。字段类型必须是长整型或float64类型    SELECT BOTTOM(<field_key>[,<tag_key(s)>],<N> )[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
FIRST()    返回具有最早时间戳的字段值    SELECT FIRST(<field_key>)[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
LAST()    返回具有最新时间戳的字段值    SELECT LAST(<field_key>)[,<tag_key(s)>|<field_keys(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
MAX()    返回一个字段中的最大值。该字段类型必须是长整型，float64，或布尔类型    SELECT MAX(<field_key>)[,<tag_key(s)>|<field__key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
MIN()    返回一个字段中的最小值。该字段类型必须是长整型，float64，或布尔类型    SELECT MIN(<field_key>)[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
PERCENTILE()    返回排序值排位为N%的值。字段的类型必须是长整型或float64    SELECT PERCENTILE(<field_key>, <N>)[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
SAMPLE()    返回字段值的随机N个样本。 SAMPLE()使用储层采样来生成随机点    SELECT SAMPLE(<field_key>, <N>)[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
TOP()    返回一个字段中最大的N个值，字段类型必须是长整型或float64类型    SELECT TOP( <field_key>[,<tag_key(s)>],<N> )[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
 

转换类
函数    描述    语法
ABS()    返回字段值的绝对值    SELECT ABS( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
ACOS()    返回字段值的反余弦（以弧度表示）。字段值必须介于-1和1之间    SELECT ACOS( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
ASIN()    返回字段值的反正弦（以弧度表示）。字段值必须介于-1和1之间    SELECT ASIN( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
ATAN()    返回字段值的反正切（以弧度表示）。字段值必须介于-1和1之间    SELECT ATAN( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
ATAN2()    返回y/x以弧度表示的反正切值    SELECT ATAN2( [ * | <field_key> | num ], [ <field_key> | num ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
CEIL()    返回四舍五入到最接近的整数的后续值    SELECT CEIL( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
COS()    返回字段值的余弦值    SELECT COS( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
CUMULATIVE_SUM()    返回后续字段值的总计    SELECT CUMULATIVE_SUM( [ * | <field_key> | /<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
DERIVATIVE()    返回一个字段在一个series中的变化率    SELECT DERIVATIVE( [ * | <field_key> | /<regular_expression>/ ] [ , <unit> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
DIFFERENCE()    返回一个字段中连续的时间值之间的差异。字段类型必须是长整型或float64    SELECT DIFFERENCE( [ * | <field_key> | /<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
ELAPSED()    返回一个字段在连续的时间间隔间的差异，间隔单位可选，默认为1纳秒    SELECT ELAPSED( [ * | <field_key> | /<regular_expression>/ ] [ , <unit> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
EXP()    返回字段值的指数    SELECT EXP( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
FLOOR()    返回向下舍入到最接近的整数的后续值    SELECT FLOOR( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
LN()    返回字段值的自然对数    SELECT LN( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
LOG()    返回基数为b的字段值的对数    SELECT LOG( [ * | <field_key> ], <b> ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
LOG2()    返回基数为2的字段值的对数    SELECT LOG2( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
LOG10()    返回基数为10的字段值的对数    SELECT LOG10( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
MOVING_AVERAGE()    返回一个连续字段值的移动平均值，字段类型必须是长整形或者float64类型    SELECT MOVING_AVERAGE( [ * | <field_key> | /<regular_expression>/ ] , <N> ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
NON_NEGATIVE_DERIVATIVE()    返回在一个series中的一个字段中值的变化的非负速率    SELECT NON_NEGATIVE_DERIVATIVE( [ * | <field_key> | /<regular_expression>/ ] [ , <unit> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
NON_NEGATIVE_DIFFERENCE()    返回后续字段值之间减法的非负结果。减法的非负结果包括正差异和等于零的差异    SELECT NON_NEGATIVE_DIFFERENCE( [ * | <field_key> | /<regular_expression>/ ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
POW()    返回字段值的x幂    SELECT POW( [ * | <field_key> ], <x> ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
ROUND()    返回四舍五入到最接近的整数的后续值    SELECT ROUND( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
SIN()    返回字段值的正弦值    SELECT SIN( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
SQRT()    返回字段值的平方根    SELECT SQRT( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
TAN()    返回字段值的正切值    SELECT TAN( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
 

预测
函数    描述    语法
HOLT_WINTERS()    返回N个预测字段值    SELECT HOLT_WINTERS[_WITH-FIT](<function>(<field_key>),<N>,<S>) [INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]
 

demo不一一给出，挑出几个代表吧~

count
> use mydb
Using database mydb
> select count(*) from cpu_load_short
name: cpu_load_short
time count_value
---- -----------
0    5
> 
1
2
3
4
5
6
7
8
distinct
> select * from cpu_load_short
name: cpu_load_short
time                direction host     region  value
----                --------- ----     ------  -----
1422568543702900257 in        server01 us-west 2
1422568543702900257           server02 us-west 0.55
1434055562000000000           server01 us-west 0.64
1536579271011551389           server02         0.67
1536744482958458452           server01 us-west 0.65
> select distinct(value) from cpu
> select distinct(value) from cpu_load_short
name: cpu_load_short
time distinct
---- --------
0    0.55
0    2
0    0.64
0    0.67
0    0.65
> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
median
> select median(value) from cpu_load_short
name: cpu_load_short
time median
---- ------
0    0.65
> 
1
2
3
4
5
6
spread
> select spread(value) from cpu_load_short
name: cpu_load_short
time spread
---- ------
0    1.45
>
1
2
3
4
5
6
sum
> select sum(value) from cpu_load_short
name: cpu_load_short
time sum
---- ---
0    4.51
> 
1
2
3
4
5
6
-bottom

> select bottom(value,2) from cpu_load_short
name: cpu_load_short
time                bottom
----                ------
1422568543702900257 0.55
1434055562000000000 0.64
> 
1
2
3
4
5
6
7
max
> select max(value) from cpu_load_short
name: cpu_load_short
time                max
----                ---
1422568543702900257 2
> 
1
2
3
4
5
6
top
> select top(value,2) from cpu_load_short
name: cpu_load_short
time                top
----                ---
1422568543702900257 2
1536579271011551389 0.67
> 
1
2
3
4
5
6
7
percentile
> select PERCENTILE(value,20) from cpu_load_short
name: cpu_load_short
time                percentile
----                ----------
1422568543702900257 0.55
> select PERCENTILE(value,40) from cpu_load_short
name: cpu_load_short
time                percentile
----                ----------
1434055562000000000 0.64
> 
1
2
3
4
5
6
7
8
9
10
11
ceil
> select ceil(value) from cpu_load_short
name: cpu_load_short
time                ceil
----                ----
1422568543702900257 2
1422568543702900257 1
1434055562000000000 1
1536579271011551389 1
1536744482958458452 1
> 
1
2
3
4
5
6
7
8
9
10
ln、log(,2)、log2
> select ln(value) from cpu_load_short
name: cpu_load_short
time                ln
----                --
1422568543702900257 0.6931471805599453
1422568543702900257 -0.5978370007556204
1434055562000000000 -0.4462871026284195
1536579271011551389 -0.40047756659712525
1536744482958458452 -0.4307829160924542
> select log(value,2) from cpu_load_short
name: cpu_load_short
time                log
----                ---
1422568543702900257 1
1422568543702900257 -0.8624964762500651
1434055562000000000 -0.6438561897747247
1536579271011551389 -0.5777669993169522
1536744482958458452 -0.6214883767462701
> select log2(value) from cpu_load_short
name: cpu_load_short
time                log2
----                ----
1422568543702900257 1
1422568543702900257 -0.862496476250065
1434055562000000000 -0.6438561897747246
1536579271011551389 -0.5777669993169522
1536744482958458452 -0.6214883767462701
> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
moving_average
> select * from cpu_load_short
name: cpu_load_short
time                direction host     region  value
----                --------- ----     ------  -----
1422568543702900257 in        server01 us-west 2
1422568543702900257           server02 us-west 0.55
1434055562000000000           server01 us-west 0.64
1536579271011551389           server02         0.67
1536744482958458452           server01 us-west 0.65
>
> select MOVING_AVERAGE(value,2) from cpu_load_short
name: cpu_load_short
time                moving_average
----                --------------
1422568543702900257 1.275
1434055562000000000 0.595
1536579271011551389 0.655
1536744482958458452 0.66
> select MOVING_AVERAGE(value,3) from cpu_load_short
name: cpu_load_short
time                moving_average
----                --------------
1434055562000000000 1.0633333333333332
1536579271011551389 0.62
1536744482958458452 0.6533333333333333
> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
看懂了没有，就是传入的N就是N个数相加然后平均值，比如MOVING_AVERAGE(value,2)，就是第一和第二个数相加然后平均，第二和第三个数平均……

pow
> select pow(value,2) from cpu_load_short
name: cpu_load_short
time                pow
----                ---
1422568543702900257 4
1422568543702900257 0.30250000000000005
1434055562000000000 0.4096
1536579271011551389 0.4489000000000001
1536744482958458452 0.42250000000000004
>