目前想统计出当前CPU峰值的入表时间（history表中的clock字段），目前如果把clock字段带入，groupby时，就会出现多个，不知道如何编写

Question

以下是不加入clock字段的sql：

select  
    g.name as 'groupname',
    h.name as 'hostname',
    t.ip as 'ip',
    100-min(hi.value)
    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;

Answer 1

用分组函数就可以实现；
如下，就是按照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