influx 多个 measurement influxdb count

转载

mob64ca140088a9 2024-04-08 20:12:31

参考

使用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
 >

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。