基于最新版本1.6.2,centOS
使用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
>
- 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
>
- median
> select median(value) from cpu_load_short
name: cpu_load_short
time median
---- ------
0 0.65
>
- spread
> select spread(value) from cpu_load_short
name: cpu_load_short
time spread
---- ------
0 1.45
>
- sum
> select sum(value) from cpu_load_short
name: cpu_load_short
time sum
---- ---
0 4.51
>
-bottom
> select bottom(value,2) from cpu_load_short
name: cpu_load_short
time bottom
---- ------
1422568543702900257 0.55
1434055562000000000 0.64
>
- max
> select max(value) from cpu_load_short
name: cpu_load_short
time max
---- ---
1422568543702900257 2
>
- top
> select top(value,2) from cpu_load_short
name: cpu_load_short
time top
---- ---
1422568543702900257 2
1536579271011551389 0.67
>
- 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
>
- ceil
> select ceil(value) from cpu_load_short
name: cpu_load_short
time ceil
---- ----
1422568543702900257 2
1422568543702900257 1
1434055562000000000 1
1536579271011551389 1
1536744482958458452 1
>
- 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
>
- 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
>
看懂了没有,就是传入的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
>