influxdb基础

InfluxDb基础语法

-- 查看所有的数据库
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')