> select * from CPU_ALL where "time" = 1551694910000000000
name: CPU_ALL
time host name value
---- ---- ---- -----
1551694910000000000 qc_predepl_cms-ngx-02 CPUs 4
1551694910000000000 qc_predepl_cms-ngx-02 Idle% 99.7
1551694910000000000 qc_predepl_cms-ngx-02 Steal% 0
1551694910000000000 qc_predepl_cms-ngx-02 Sys% 0.1
1551694910000000000 qc_predepl_cms-ngx-02 User% 0.2
1551694910000000000 qc_predepl_cms-ngx-02 Wait% 0
>
> select * from CPU_ALL where time >= '2018-11-23 14:30:39' and time <= '2019-11-23 14:32:32' tz('Asia/Shanghai')
select * from table_name where "字段1" =~ /匹配值/
> select * from CPU_All3 limit 10
name: CPU_All3
time Cpus Idle% Steal% Sys% User% Wait% host
---- ---- ----- ------ ---- ----- ----- ----
1551689409000000000 4 94.5 0 0.9 0.7 3.9 qc_predepl_cms-ngx-02
1551689412000000000 4 99.8 0 0.2 0.1 0 qc_predepl_cms-ngx-02
1551689415000000000 4 99.5 0 0 0.1 0.4 qc_predepl_cms-ngx-02
1551689418000000000 4 99.4 0 0.1 0.1 0.4 qc_predepl_cms-ngx-02
1551689421000000000 4 99.7 0 0.2 0.2 0 qc_predepl_cms-ngx-02
1551689424000000000 4 99.7 0 0.1 0.1 0.2 qc_predepl_cms-ngx-02
1551689427000000000 4 99.5 0 0.2 0.2 0.2 qc_predepl_cms-ngx-02
1551689430000000000 4 99.7 0 0.2 0.2 0 qc_predepl_cms-ngx-02
1551689433000000000 4 99.7 0 0.1 0.2 0.1 qc_predepl_cms-ngx-02
1551689436000000000 4 99.8 0 0.1 0.1 0 qc_predepl_cms-ngx-02
>
>
> SELECT * FROM "CPU_All3" WHERE time < now() - 5m and "Idle%" =~ /94/
name: CPU_All3
time Cpus Idle% Steal% Sys% User% Wait% host
---- ---- ----- ------ ---- ----- ----- ----
1551689409000000000 4 94.5 0 0.9 0.7 3.9 qc_predepl_cms-ngx-02
1551694925000000000 4 94.8 0 3.5 1.3 0.4 qc_predepl_cms-ngx-02
1551694937000000000 4 94.2 0 4.3 1.3 0.3 qc_predepl_cms-ngx-02
>
> SELECT * FROM "CPU_All3" WHERE time < now() - 5m and "Idle%" =~ /94.5/
name: CPU_All3
time Cpus Idle% Steal% Sys% User% Wait% host
---- ---- ----- ------ ---- ----- ----- ----
1551689409000000000 4 94.5 0 0.9 0.7 3.9 qc_predepl_cms-ngx-02
>
>
> SELECT * FROM "CPU_All3" WHERE time < now() - 5m and "Idle%" =~ /94.5/ and host =~ /qc_predepl_cms/
name: CPU_All3
time Cpus Idle% Steal% Sys% User% Wait% host
---- ---- ----- ------ ---- ----- ----- ----
1551689409000000000 4 94.5 0 0.9 0.7 3.9 qc_predepl_cms-ngx-02
>
倒序查询
> select * from CPU_ALL order by time desc limit 10 tz('Asia/Shanghai')
name: CPU_ALL
time host name value
---- ---- ---- -----
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 Wait% 0.3
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 User% 0.1
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 Sys% 0.1
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 Steal% 0
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 Idle% 99.5
2019-03-04T18:31:44+08:00 qc_predepl_cms-ngx-02 CPUs 4
2019-03-04T18:31:41+08:00 qc_predepl_cms-ngx-02 Wait% 0
2019-03-04T18:31:41+08:00 qc_predepl_cms-ngx-02 User% 0.1
2019-03-04T18:31:41+08:00 qc_predepl_cms-ngx-02 Sys% 0.2
2019-03-04T18:31:41+08:00 qc_predepl_cms-ngx-02 Steal% 0
Distinct去重查询
> SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"
name: h2o_feet
time count
---- -----
1970-01-01T00:00:00Z 4
Max()最大值、Min() 最小值
> select min(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time min_value
---- ---------
2019-03-04T18:21:47+08:00 0
>
> select max(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time max_value
---- ---------
2019-03-04T18:30:47+08:00 100
>
Mean()查询平均值
> select mean(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time mean_value
---- ----------
1970-01-01T08:00:00+08:00 17.336166666666678
>
MEDIAN() 中位数
返回查询结果中间的数值。
> select median(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time median_value
---- ------------
1970-01-01T08:00:00+08:00 0.4
>
SPREAD()最小值与最大值之间的数值差距
> select spread(*) from CPU_ALL tz('Asia/Shanghai')
name: CPU_ALL
time spread_value
---- ------------
1970-01-01T08:00:00+08:00 100
>
本文分享自微信公众号 - DevOps社群（DevOpsCommunity）。
如有侵权，请联系 [email protected] 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。