influxdb基础
<h5>InfluxDb基础语法</h5>
<pre><code>-- 查看所有的数据库
show databases;
-- 使用特定的数据库
use database_name;
-- 查看所有的measurement
show measurements;
-- 查询10条数据
select * from measurement_name limit 10;
-- 数据中的时间字段默认显示的是一个纳秒时间戳，改成可读格式
precision rfc3339; -- 之后再查询，时间就是rfc3339标准格式
-- 或可以在连接数据库的时候，直接带该参数
influx -precision rfc3339
-- 查看一个measurement中所有的tag key
show tag keys
-- 查看一个measurement中所有的field key
show field keys
-- 查看一个measurement中所有的保存策略(可以有多个，一个标识为default)
show retention policies;
#创建test数据库
create database test;
#查看所有表
show measurements;
#创建并插入库
#disk_free是表名 hostname 是tag(用逗号隔开) cpu是filed(跟在tag后边不用逗号隔开) 111是时间如果不写则自动生成
insert disk_free,hostname=localhost,version=1.0 cpu=abc 1111
#统计表数据行数,会统计不同的field,不会统计索引
select count(*) from disk_free;
name: disk_free
time count_b count_value count_version
---- ------- ----------- -------------
0 1 4 1
#查看field
show field keys;
name: disk_free
fieldKey fieldType
-------- ---------
address float
b float
ff boolean
value float
value integer
version float
#查看tag
> show tag keys;
name: disk_free
tagKey
------
a
age
dress
hostname
name
server
>
#条件查询
select * from disk_free where address=2 or b=777 limit 2;
name: disk_free
time a address age b dress ff hostname name server value version
---- - ------- --- - ----- -- -------- ---- ------ ----- -------
1603500050065049000 8888888888888 777 abc 1
1603501485873136000 2 26 jinxudong
>
#1000s内的时间
select * from disk_free where time > now()-1000s;
#查询series
series表示这个表里面的数据，可以在图表上画成几条线，series主要通过tags排列组合算出来。
show series from disk_free;
key
---
disk_free,a=2,hostname=abc,server=1
disk_free,a=8888888888888,hostname=abc,server=1
disk_free,age=26,name=jinxudong
disk_free,age=278,hostname=ff
disk_free,age=279,hostname=ff
disk_free,dress=fd,name=jinxudong
disk_free,hostname=server01
disk_free,name=jinxudong
#删除表
drop measurement disk_free;
#查平均数
> select mean(value) from free_disk;
name: free_disk
time mean
---- ----
0 299.3333333333333
#去重
> select distinct(value) from free_disk;
name: free_disk
time distinct
---- --------
0 209
0 300
0 301
0 302
0 303
0 304
0 305
0 306
0 307
0 308
0 309
#查询中位数
select median(value) from free_disk;
name: free_disk
time median
---- ------
0 306
#最大最小值
> select max(value) from free_disk;
name: free_disk
time max
---- ---
1603502961971177600 309
> select min(value) from free_disk;
name: free_disk
time min
---- ---
1603502179173818900 209
>
#求和
select sum(value) from free_disk;
name: free_disk
time sum
---- ---
0 4490
#返回最早 和 最晚数据
select first(value) from free_disk;
name: free_disk
time first
---- -----
1603502179173818900 209
> select last(value) from free_disk;
name: free_disk
time last
---- ----
1603503062427360700 309
#求一段时间内field的变化率 默认是1s
select derivative(value) from free_disk;
name: free_disk
time derivative
---- ----------
1603502857859840400 0.1340826201177329
1603502861778227100 0.2552070728496501
1603502864325414300 0.39258991251212316
1603502866108752100 0.5607462590654446
1603502867720197400 0.6205609337158389
1603502869380686800 0.6022320889251085
1603502871715984600 0.42821091168758
1603502873929609000 0.45174782135578195
1603502875872127600 0.5147955854837117
1603502961971177600 0.011614530009332274
1603503058446054800 0
1603503059377123100 0
1603503060446214700 0
1603503062427360700 0
> select derivative(value,2s) from free_disk;
name: free_disk
time derivative
---- ----------
1603502857859840400 0.2681652402354658
1603502861778227100 0.5104141456993002
1603502864325414300 0.7851798250242463
1603502866108752100 1.1214925181308892
1603502867720197400 1.2411218674316777
1603502869380686800 1.204464177850217
1603502871715984600 0.85642182337516
1603502873929609000 0.9034956427115639
1603502875872127600 1.0295911709674235
1603502961971177600 0.02322906001866455
1603503058446054800 0
1603503059377123100 0
1603503060446214700 0
1603503062427360700 0
#创建一个两小时的持久化策略
> create retention policy "2_hours" on "test" duration 2h replication 1 default;
#删除库上策略
drop retention POLICY "2_hours" ON "test"
#utc转上海时间
select * from free_disk where time > now()-1h limit 1 tz('Asia/Shanghai')</code></pre>