select host,hostid from hosts where host= "Zabbix server" ;
mysql> select host,hostid from hosts where host= "Zabbix server" ;
+---------------+--------+
| host | hostid |
+---------------+--------+
| Zabbix server | 10084 |
+---------------+--------+
1 row in set (0.00 sec)
select itemid, name ,key_ from items where hostid=10084 and key_= "line.utilization[192.168.100.20,200000000,ifHCInOctets.369099151,ifHCOutOctets.369099151]" ;
mysql> select itemid, name ,key_ from items where hostid=10084 and key_= "line.utilization[192.168.100.20,200000000,ifHCInOctets.369099151,ifHCOutOctets.369099151]" ;
+--------+-------------------------+-------------------------------------------------------------------------------------------+
| itemid | name | key_ |
+--------+-------------------------+-------------------------------------------------------------------------------------------+
| 39621 | BJ_AliCloud_utilization | line.utilization[192.168.100.20,200000000,ifHCInOctets.369099151,ifHCOutOctets.369099151] |
+--------+-------------------------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
select from_unixtime(clock) as DateTime,round(value/1024/1024,2) as BJ_AliCloud_utilization from history_uint where itemid= "39621" and from_unixtime(clock)>= '2022-01-29 15:00' and from_unixtime(clock)< '2022-01-29 16:00' limit 20;
mysql> select from_unixtime(clock) as DateTime,round(value/1024/1024,2) as BJ_AliCloud_utilization from history_uint where itemid= "39621" and from_unixtime(clock)>= '2022-01-29 15:00' and from_unixtime(clock)< '2022-01-29 16:00' limit 20;
+---------------------+-------------------------+
| DateTime | BJ_AliCloud_utilization |
+---------------------+-------------------------+
| 2022-01-29 15:00:21 | 0.00 |
| 2022-01-29 15:01:21 | 0.00 |
| 2022-01-29 15:02:21 | 0.00 |
| 2022-01-29 15:03:21 | 0.00 |
| 2022-01-29 15:04:21 | 0.00 |
| 2022-01-29 15:05:21 | 0.00 |
| 2022-01-29 15:06:21 | 0.00 |
| 2022-01-29 15:07:21 | 0.00 |
| 2022-01-29 15:08:21 | 0.00 |
| 2022-01-29 15:09:21 | 0.00 |
| 2022-01-29 15:10:21 | 0.00 |
| 2022-01-29 15:11:21 | 0.00 |
| 2022-01-29 15:12:22 | 0.00 |
| 2022-01-29 15:13:21 | 0.00 |
| 2022-01-29 15:14:21 | 0.00 |
| 2022-01-29 15:15:21 | 0.00 |
| 2022-01-29 15:16:21 | 0.00 |
| 2022-01-29 15:17:21 | 0.00 |
| 2022-01-29 15:18:21 | 0.00 |
| 2022-01-29 15:19:21 | 0.00 |
+---------------------+-------------------------+
20 rows in set (0.49 sec)
（4）查询一天中主机流量的最大值，最小值和平均值
select date as DateTime,round(min(utilization)/1024/1024,2) as TotalMinIN,round(avg(utilization)/1024/1024,2) as TotalAvgIN,round(max(utilization)/1024/1024,2) as TotalMaxIN from (select from_unixtime(clock, "%Y-%m-%d") as date , sum(value) as utilization from history_uint where itemid= "39621" and from_unixtime(clock)>= '2022-01-29 15:00' and from_unixtime(clock)< '2022-01-29 16:00' group by from_unixtime(clock, "%Y-%m-%d %H:%i")) tmp;
mysql> select date as DateTime,round(min(utilization)/1024/1024,2) as TotalMinIN,round(avg(utilization)/1024/1024,2) as TotalAvgIN,round(max(utilization)/1024/1024,2) as TotalMaxIN from (select from_unixtime(clock, "%Y-%m-%d") as date , sum(value) as utilization from history_uint where itemid= "39621" and from_unixtime(clock)>= '2022-01-29 15:00' and from_unixtime(clock)< '2022-01-29 16:00' group by from_unixtime(clock, "%Y-%m-%d %H:%i")) tmp;
+------------+------------+------------+------------+
| DateTime | TotalMinIN | TotalAvgIN | TotalMaxIN |
+------------+------------+------------+------------+
| 2022-01-29 | 0.00 | 0.00 | 0.00 |
+------------+------------+------------+------------+
1 row in set (0.03 sec)
（1） 如果mysql数据库中@@GLOBAL.sql_mode、@@SESSION.sql_mode设置为ONLY_FULL_GROUP_BY模式就会和之前 group by语法不兼容，需要去掉。
查看这两个模式：
去除 ONLY_FULL_GROUP_BY
（2）(value/1024/1024,2)，这个地方要注意，是根据采集的数据进行运算，并保留小数位数
