用分组函数就可以实现;
如下,就是按照groupname,hostname,ip分组,where筛选后,取hi.value最小时候信息;
如果要取hi.value最大的时候的信息,要将 row_number() OVER (PARTITION BY g.name,h.name,t.ip ORDER BY hi.value) as N
中order by 倒叙
row_number() OVER (PARTITION BY g.name,h.name,t.ip ORDER BY hi.value desc ) as N
代码如下:
select groupname,hostname,ip,hivalue,[time] from
(
select
g.name as 'groupname',
h.name as 'hostname',
t.ip as 'ip',
100-hi.value as hivalue,
hi.clock as [time],
row_number() OVER (PARTITION BY g.name,h.name,t.ip ORDER BY hi.value) as N
from
zabbix_server.hosts_groups as hg
join zabbix_server.hstgrp g on g.groupid = hg.groupid
join zabbix_server.items i on hg.hostid = i.hostid
join zabbix_server.history hi on i.itemid = hi.itemid
join zabbix_server.hosts h on h.hostid = i.hostid
join zabbix_server.interface t on h.hostid = t.hostid
where g.groupid in (20)
and i.key_ = 'system.cpu.util[,idle]'
and hi.clock >= (select unix_timestamp(cast(sysdate()as date) - interval 1 day))
and hi.clock <= (select unix_timestamp(cast(sysdate()as date)))
group by g.name , h.name ,t.ip
) as a
where a.n=1
补充:Mysql实现
SET @row_number:=0,@groupname:=null,@hostname:=null,@ip:=null; WITH TOTAL AS ( SELECT @row_number:=CASE WHEN @groupname = groupname and @hostname=hostname and @ip=ip THEN @row_number + 1 else 1 END AS num, @groupname:=groupname, @hostname:=hostname, @ip:= ip, B.* FROM ( select g.name as 'groupname', h.name as 'hostname', t.ip as 'ip', 100-min(hi.value) AS HIVALUE, hi.clock from zabbix_server.hosts_groups as hg join zabbix_server.hstgrp g on g.groupid = hg.groupid join zabbix_server.items i on hg.hostid = i.hostid join zabbix_server.history hi on i.itemid = hi.itemid join zabbix_server.hosts h on h.hostid = i.hostid join zabbix_server.interface t on h.hostid = t.hostid where g.groupid in (20) and i.key_ = 'system.cpu.util[,idle]' and hi.clock >= (select unix_timestamp(cast(sysdate()as date) - interval 1 day)) and hi.clock <= (select unix_timestamp(cast(sysdate()as date)))) AS B ORDER BY groupname , hostname ,ip,hi.clock desc ) SELECT * FROM TOTAL WHERE num=1