mysql group by 按组返回 top 10 信息
来源：互联网 发布：淘宝的营销词是什么 编辑：程序博客网 时间：2024/06/03 21:54
需求:
对 mysql 输出进行 groug by 排序并执行 top 10 返回
前提:
当前需对 zabbix 执行自定义监控数据返回.
涉及到的表比较多, 下面作出简单的说明
hosts_groups 用于记录主机与组的 id 关系
mysql> desc hosts_groups;+-------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------+---------------------+------+-----+---------+-------+| hostgroupid | bigint(20) unsigned | NO | PRI | NULL | || hostid | bigint(20) unsigned | NO | MUL | NULL | || groupid | bigint(20) unsigned | NO | MUL | NULL | |+-------------+---------------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> select * from hosts_groups limit 1, 2;+-------------+--------+---------+| hostgroupid | hostid | groupid |+-------------+--------+---------+| 47 | 10047 | 1 || 98 | 10048 | 1 |+-------------+--------+---------+2 rows in set (0.00 sec)
groups 表用于记录主机组信息
mysql> desc groups;+----------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------------+------+-----+---------+-------+| groupid | bigint(20) unsigned | NO | PRI | NULL | || name | varchar(64) | NO | MUL | | || internal | int(11) | NO | | 0 | || flags | int(11) | NO | | 0 | |+----------+---------------------+------+-----+---------+-------+4 rows in set (0.01 sec)mysql> select * from groups where name in ( 'QA','DEV','MOBILE','OPS') ;+---------+--------+----------+-------+| groupid | name | internal | flags |+---------+--------+----------+-------+| 13 | QA | 0 | 0 || 14 | DEV | 0 | 0 || 15 | MOBILE | 0 | 0 || 20 | OPS | 0 | 0 |+---------+--------+----------+-------+4 rows in set (0.00 sec)
hosts 表用于记录主机信息
mysql> desc hosts;+--------------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------------------+---------------------+------+-----+---------+-------+| hostid | bigint(20) unsigned | NO | PRI | NULL | || proxy_hostid | bigint(20) unsigned | YES | MUL | NULL | || host | varchar(128) | NO | MUL | | || status | int(11) | NO | MUL | 0 | || disable_until | int(11) | NO | | 0 | || error | varchar(128) | NO | | | || available | int(11) | NO | | 0 | || errors_from | int(11) | NO | | 0 | || lastaccess | int(11) | NO | | 0 | || ipmi_authtype | int(11) | NO | | 0 | || ipmi_privilege | int(11) | NO | | 2 | || ipmi_username | varchar(16) | NO | | | || ipmi_password | varchar(20) | NO | | | || ipmi_disable_until | int(11) | NO | | 0 | || ipmi_available | int(11) | NO | | 0 | || snmp_disable_until | int(11) | NO | | 0 | || snmp_available | int(11) | NO | | 0 | || maintenanceid | bigint(20) unsigned | YES | MUL | NULL | || maintenance_status | int(11) | NO | | 0 | || maintenance_type | int(11) | NO | | 0 | || maintenance_from | int(11) | NO | | 0 | || ipmi_errors_from | int(11) | NO | | 0 | || snmp_errors_from | int(11) | NO | | 0 | || ipmi_error | varchar(128) | NO | | | || snmp_error | varchar(128) | NO | | | || jmx_disable_until | int(11) | NO | | 0 | || jmx_available | int(11) | NO | | 0 | || jmx_errors_from | int(11) | NO | | 0 | || jmx_error | varchar(128) | NO | | | || name | varchar(128) | NO | MUL | | || flags | int(11) | NO | | 0 | || templateid | bigint(20) unsigned | YES | MUL | NULL | || description | text | NO | | NULL | |+--------------------+---------------------+------+-----+---------+-------+33 rows in set (0.00 sec)mysql> select name, hostid from hosts where name = 'tps_mq-awdpn.vclound.com';+--------------------------+--------+| name | hostid |+--------------------------+--------+| tps_mq-awdpn.vclound.com | 10685 |+--------------------------+--------+1 row in set (0.00 sec)
item 用于记录监控项
mysql> desc items;+-----------------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------------+---------------------+------+-----+---------+-------+| itemid | bigint(20) unsigned | NO | PRI | NULL | || type | int(11) | NO | | 0 | || snmp_community | varchar(64) | NO | | | || snmp_oid | varchar(255) | NO | | | || hostid | bigint(20) unsigned | NO | MUL | NULL | || name | varchar(255) | NO | | | || key_ | varchar(255) | NO | | | || delay | int(11) | NO | | 0 | || history | int(11) | NO | | 90 | || trends | int(11) | NO | | 365 | || status | int(11) | NO | MUL | 0 | || value_type | int(11) | NO | | 0 | || trapper_hosts | varchar(255) | NO | | | || units | varchar(255) | NO | | | || multiplier | int(11) | NO | | 0 | || delta | int(11) | NO | | 0 | || snmpv3_securityname | varchar(64) | NO | | | || snmpv3_securitylevel | int(11) | NO | | 0 | || snmpv3_authpassphrase | varchar(64) | NO | | | || snmpv3_privpassphrase | varchar(64) | NO | | | || formula | varchar(255) | NO | | | || error | varchar(2048) | NO | | | || lastlogsize | bigint(20) unsigned | NO | | 0 | || logtimefmt | varchar(64) | NO | | | || templateid | bigint(20) unsigned | YES | MUL | NULL | || valuemapid | bigint(20) unsigned | YES | MUL | NULL | || delay_flex | varchar(255) | NO | | | || params | text | NO | | NULL | || ipmi_sensor | varchar(128) | NO | | | || data_type | int(11) | NO | | 0 | || authtype | int(11) | NO | | 0 | || username | varchar(64) | NO | | | || password | varchar(64) | NO | | | || publickey | varchar(64) | NO | | | || privatekey | varchar(64) | NO | | | || mtime | int(11) | NO | | 0 | || flags | int(11) | NO | | 0 | || interfaceid | bigint(20) unsigned | YES | MUL | NULL | || port | varchar(64) | NO | | | || description | text | NO | | NULL | || inventory_link | int(11) | NO | | 0 | || lifetime | varchar(64) | NO | | 30 | || snmpv3_authprotocol | int(11) | NO | | 0 | || snmpv3_privprotocol | int(11) | NO | | 0 | || state | int(11) | NO | | 0 | || snmpv3_contextname | varchar(255) | NO | | | || evaltype | int(11) | NO | | 0 | |+-----------------------+---------------------+------+-----+---------+-------+47 rows in set (0.00 sec)mysql> select a.key_, a.itemid, a.hostid from items a, hosts b where a.hostid = b.hostid and b.name='tps_mq-awdpn.vclound.com' and a.key_ in ( 'net.if.in[eth0]', 'net.if.out[eth0]', 'vm.memory.size[total]', 'vm.memory.size[available]' );+---------------------------+--------+--------+| key_ | itemid | hostid |+---------------------------+--------+--------+| net.if.in[eth0] | 90380 | 10685 || net.if.out[eth0] | 90382 | 10685 || vm.memory.size[available] | 90340 | 10685 || vm.memory.size[total] | 90341 | 10685 |+---------------------------+--------+--------+4 rows in set (0.00 sec)
history 与 history_uint 表结构一样, 都是用于记录 items 的记录, 但 history_unit 记录的是通过 discovery 方式发现的 items. (具体信息参考www.zabbix.com 官方资料)
mysql> desc history;+--------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+-------+| itemid | bigint(20) unsigned | NO | MUL | NULL | || clock | int(11) | NO | | 0 | || value | double(16,4) | NO | | 0.0000 | || ns | int(11) | NO | | 0 | |+--------+---------------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> select * from history_uint where itemid in ( 90380,90340 ) and clock > 1416278920;+--------+------------+------------+-----------+| itemid | clock | value | ns |+--------+------------+------------+-----------+| 90340 | 1416278980 | 3972816896 | 508372588 || 90380 | 1416278960 | 8096 | 188420279 |+--------+------------+------------+-----------+2 rows in set (0.00 sec)
如果觉得复杂, 可以直接创建下面的表进行测试.
select b.name project, d.name hostname, c.key_, e.value value from hosts_groups a, groups b, items c, hosts d, history e where b.groupid = a. groupid and b.name in ( 'QA','DEV','MOBILE','OPS') and c.hostid = d.hostid and c.hostid = a.hostid and e.itemid = c.itemid and c.key_ = 'net.if.out[eth0]' and e.clock >= ( select unix_timestamp() - (60 * 60 )) order by value desc limit 1;+---------+------------------------------------------------+------------------+----------+| project | hosts | mkey | value |+---------+------------------------------------------------+------------------+----------+| DEV | test_project-wezqp.vclound.com | net.if.out[eth0] | 3848928 || DEV | lamcloud-xmcds.vclound.com | net.if.out[eth0] | 2846344 || DEV | seven-test-server-9srvb.vclound.com | net.if.out[eth0] | 1031008 || DEV | test_1-eyhxk.vclound.com | net.if.out[eth0] | 906168 || DEV | query-uk6hq.vclound.com | net.if.out[eth0] | 564352 || DEV | bmqiu-pcmhw.vclound.com | net.if.out[eth0] | 447984 || DEV | chao-data01.vclound.com | net.if.out[eth0] | 353504 || DEV | linqu-qu4pt.vclound.com | net.if.out[eth0] | 300120 || DEV | jenkins_server-vduzh.vclound.com | net.if.out[eth0] | 293976 || DEV | bmqiu-4p59i.vclound.com | net.if.out[eth0] | 248040 || MOBILE | bencui-hh-dev-web-xunqy.vclound.com | net.if.out[eth0] | 283720 || MOBILE | mobile_configcenter_dev-vvcuf.vclound.com | net.if.out[eth0] | 146368 || MOBILE | stonexie_test-nf7qi.vclound.com | net.if.out[eth0] | 141656 || MOBILE | bendevota-xjraf.vclound.com | net.if.out[eth0] | 137200 || MOBILE | mobile_configcenter_data_dev-tsgw9.vclound.com | net.if.out[eth0] | 121096 || MOBILE | test_01-czths.vclound.com | net.if.out[eth0] | 108960 || MOBILE | mobile_test_automation-xztwz.vclound.com | net.if.out[eth0] | 107872 || MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 103304 || MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 103304 || MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 103304 || OPS | db_li-wkq54.vclound.com | net.if.out[eth0] | 180656 || OPS | pp-pe5vs.vclound.com | net.if.out[eth0] | 153704 || OPS | pp-aytgj.vclound.com | net.if.out[eth0] | 140104 || OPS | pp-vqjky.vclound.com | net.if.out[eth0] | 139544 || OPS | db_li-nrziy.vclound.com | net.if.out[eth0] | 125160 || OPS | chima-hh-dev-web-tivix.vclound.com | net.if.out[eth0] | 125152 || OPS | fei-usv76.vclound.com | net.if.out[eth0] | 106448 || OPS | data_mining_web-kfjhn.vclound.com | net.if.out[eth0] | 104776 || OPS | data_mining_file-532ev.vclound.com | net.if.out[eth0] | 96656 || OPS | johnny_liu-vtceh.vclound.com | net.if.out[eth0] | 90816 || QA | voptest3-dbkfr.vclound.com | net.if.out[eth0] | 37872920 || QA | b2c-zhongbo-xdmpm.vclound.com | net.if.out[eth0] | 2131664 || QA | voptest6-spupv.vclound.com | net.if.out[eth0] | 756000 || QA | backendtest-ihuri.vclound.com | net.if.out[eth0] | 640128 || QA | backend-sd66e.vclound.com | net.if.out[eth0] | 637856 || QA | voptest-3mspf.vclound.com | net.if.out[eth0] | 604704 || QA | dev_pay_app-xg3gb.vclound.com | net.if.out[eth0] | 266360 || QA | voptest4-rwikf.vclound.com | net.if.out[eth0] | 252808 || QA | vop_test_02-k7jum.vclound.com | net.if.out[eth0] | 243696 || QA | dev_vis_app02-rkbux.vclound.com | net.if.out[eth0] | 208536 |+---------+------------------------------------------------+------------------+----------+真实数据超过 10 万条, 上述数据只为显示而已. 如有兴趣, 执行创建数据自行进行测试
看下面的方法, (以真实数据进行测试咯) 所以返回值与上述表不一样
1. 获取一行的 top 数据
select b.name project, substring_index( group_concat( d.name order by e.value desc),',',1) as hostnamefrom zabbix.hosts_groups a, zabbix.groups b, zabbix.items c, zabbix.hosts d, zabbix.history_uint e where b.groupid = a. groupid and b.name in ( 'QA','DEV','MOBILE','OPS') and c.hostid = d.hostid and c.hostid = a.hostid and e.itemid = c.itemid and c.key_ = 'net.if.out[eth0]' and e.clock >= ( select unix_timestamp() - 60) group by project;+---------+------------------------------------------+| project | hostname |+---------+------------------------------------------+| DEV | iclub-8y9hy.vclound.com || MOBILE | mobile_test_automation-xztwz.vclound.com || OPS | db_li-nrziy.vclound.com || QA | b2c-zhongbo-xdmpm.vclound.com |+---------+------------------------------------------+4 rows in set, 2 warnings (0.11 sec)
2. 把其他列的数据也添加上去
select b.name project, substring_index( group_concat( d.name order by e.value desc),',',1) as hostname, substring_index( group_concat( c.key_ order by e.value desc),',',1) as newkey, substring_index( group_concat( e.value order by e.value desc),',',1) as valuefrom zabbix.hosts_groups a, zabbix.groups b, zabbix.items c, zabbix.hosts d, zabbix.history_uint e where b.groupid = a. groupid and b.name in ( 'QA','DEV','MOBILE','OPS') and c.hostid = d.hostid and c.hostid = a.hostid and e.itemid = c.itemid and c.key_ = 'net.if.out[eth0]' and e.clock >= ( select unix_timestamp() - 60) group by project;+---------+------------------------------------------------+------------------+--------+| project | hostname | newkey | value |+---------+------------------------------------------------+------------------+--------+| DEV | simanhu-hh-dev-web-puzys.vclound.com | net.if.out[eth0] | 94528 || MOBILE | mobile_configcenter_data_dev-tsgw9.vclound.com | net.if.out[eth0] | 4672 || OPS | db_li-nrziy.vclound.com | net.if.out[eth0] | 4552 || QA | dev_ebs_app_db-uya9f.vclound.com | net.if.out[eth0] | 671960 |+---------+------------------------------------------------+------------------+--------+4 rows in set, 3 warnings (0.12 sec)
3. 要创建 group by 方式的 top 10 , 需要创建一个临时表.
CREATE TABLE tinyint_asc ( zhi tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (zhi)) ;INSERT INTO tinyint_asc VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205),(206),(207),(208),(209),(210),(211),(212),(213),(214),(215),(216),(217),(218),(219),(220),(221),(222),(223),(224),(225),(226),(227),(228),(229),(230),(231),(232),(233),(234),(235),(236),(237),(238),(239),(240),(241),(242),(243),(244),(245),(246),(247),(248),(249),(250),(251),(252),(253),(254),(255);
4. 最终结果, 按照 group by 输出 top 10 信息
select b.name project, substring_index(substring_index( group_concat( d.name order by e.value desc),',',f.zhi),',',-1) as hostname, substring_index(substring_index( group_concat( c.key_ order by e.value desc),',',f.zhi),',',-1) as newkey, substring_index(substring_index( group_concat( e.value order by e.value desc),',',f.zhi),',',-1) as valuefrom zabbix.hosts_groups a, zabbix.groups b, zabbix.items c, zabbix.hosts d, zabbix.history_uint e, tinyint_asc f where b.groupid = a. groupid and b.name in ( 'QA','DEV','MOBILE','OPS') and c.hostid = d.hostid and c.hostid = a.hostid and e.itemid = c.itemid and c.key_ = 'net.if.out[eth0]' and e.clock >= ( select unix_timestamp() - 60) and f.zhi >= 1 and f.zhi <= 10 group by project, zhi;+---------+------------------------------------------------+------------------+--------+| project | hostname | newkey | value |+---------+------------------------------------------------+------------------+--------+| DEV | chao-data01.vclound.com | net.if.out[eth0] | 9152 || DEV | garnettwang-hh-qa-web-xrw6d.vclound.com | net.if.out[eth0] | 6656 || DEV | lamcloud-xmcds.vclound.com | net.if.out[eth0] | 6160 || DEV | ckjiang-hh-qa-web-ry4jw.vclound.com | net.if.out[eth0] | 5824 || DEV | waf-1-satp3.vclound.com | net.if.out[eth0] | 5504 || DEV | waf-1-satp3.vclound.com | net.if.out[eth0] | 5504 || DEV | test-pq9ms.vclound.com | net.if.out[eth0] | 5072 || DEV | ashen-yjnrc.vclound.com | net.if.out[eth0] | 5000 || DEV | ftp_server-rtnhd.vclound.com | net.if.out[eth0] | 4992 || DEV | jessenli-4535z.vclound.com | net.if.out[eth0] | 4680 || MOBILE | mobile_configcenter_data_dev-tsgw9.vclound.com | net.if.out[eth0] | 4864 || MOBILE | bendevota-xjraf.vclound.com | net.if.out[eth0] | 4568 || MOBILE | bencui-hh-dev-web-xunqy.vclound.com | net.if.out[eth0] | 4296 || MOBILE | test_01-czths.vclound.com | net.if.out[eth0] | 4144 || MOBILE | stonexie_test-nf7qi.vclound.com | net.if.out[eth0] | 4008 || MOBILE | mobile_configcenter_dev-vvcuf.vclound.com | net.if.out[eth0] | 3600 || MOBILE | mobile_test_automation-xztwz.vclound.com | net.if.out[eth0] | 3480 || MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 3472 || MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 3472 || MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 3472 || OPS | data_mining_file-532ev.vclound.com | net.if.out[eth0] | 74104 || OPS | db_li-wkq54.vclound.com | net.if.out[eth0] | 4568 || OPS | db_li-nrziy.vclound.com | net.if.out[eth0] | 4560 || OPS | data_mining_file-qfctf.vclound.com | net.if.out[eth0] | 4088 || OPS | kingtest-k56ne.vclound.com | net.if.out[eth0] | 3864 || OPS | chima-hh-dev-web-tivix.vclound.com | net.if.out[eth0] | 3816 || OPS | pp-aytgj.vclound.com | net.if.out[eth0] | 3736 || OPS | pp-pe5vs.vclound.com | net.if.out[eth0] | 3624 || OPS | pp-vqjky.vclound.com | net.if.out[eth0] | 3584 || OPS | fei-usv76.vclound.com | net.if.out[eth0] | 3512 || QA | dev_ebs_app_db-uya9f.vclound.com | net.if.out[eth0] | 549448 || QA | b2c-zhongbo-xdmpm.vclound.com | net.if.out[eth0] | 529344 || QA | zhaoantan-hh-qa-web-ar5tx.vclound.com | net.if.out[eth0] | 67784 || QA | dev_vis_app02-rkbux.vclound.com | net.if.out[eth0] | 63560 || QA | voptest-3mspf.vclound.com | net.if.out[eth0] | 40232 || QA | vop_test_02-k7jum.vclound.com | net.if.out[eth0] | 35632 || QA | vop_test_03-gdqb2.vclound.com | net.if.out[eth0] | 30120 || QA | voptest8-jnusa.vclound.com | net.if.out[eth0] | 29648 || QA | voptest7-zbvxy.vclound.com | net.if.out[eth0] | 27152 || QA | dev_fcs_app02-mpa9a.vclound.com | net.if.out[eth0] | 27072 |+---------+------------------------------------------------+------------------+--------+40 rows in set, 30 warnings (1.17 sec)
0 0
- mysql group by 按组返回 top 10 信息
- Mysql group by top N的问题
- Mysql group by top N的问题
- Mysql group by top N的问题
- mysql按区间group by
- Group by的Top用法
- SQL Group By Top 实现
- mysql 使用group by with rollup分组聚合信息
- MySQL 组内排序(Group By)
- mysql group by 组内排序
- mysql group by组内排序
- mysql group by 组内排序
- mysql group by 组内排序
- mysql group by 组内排序方法
- mysql group by 组内排序
- mysql group by组内排序
- mysql中的group by
- mysql group by
- 为iPhone 6设计自适应布局
- java编程思想第四版第9章
- bind
- linux文件目录详解
- overload和override的区别
- mysql group by 按组返回 top 10 信息
- hibernate的注解为何要在属性的get方法上声明
- 黑马程序员C语言学习-关键字、标示符、注释、数据类型、常量变量
- Ubuntu下彻底卸载重装mysql
- Centos6.4下tar包安装最新版Mysql5.6
- 15款免费WiFi（入侵破解）安全测试工具
- 第一阶段：环境搭建
- 关于事务和内存泄漏
- Scopes-----Learning Python
