编写Vertica SQL查询统计指定用户组CPU使用率阈值违规记录
设备CPU利用率违规统计问题说明
收到评论反馈后,我重新整理问题并补充所需数据如下:
表结构及样例数据
表1:etl_group_membership(组-成员关联表)
create table etl_group_membership ( group_item_id int not null, member_item_id int not null ); INSERT INTO etl_group_membership (group_item_id, member_item_id) VALUES (335640, 117722); INSERT INTO etl_group_membership (group_item_id, member_item_id) VALUES (335640, 104151); INSERT INTO etl_group_membership (group_item_id, member_item_id) VALUES (335640, 5316); INSERT INTO etl_group_membership (group_item_id, member_item_id) VALUES (335641, 117723); INSERT INTO etl_group_membership (group_item_id, member_item_id) VALUES (335641, 104152); INSERT INTO etl_group_membership (group_item_id, member_item_id) VALUES (335641, 5317); INSERT INTO etl_group_membership (group_item_id, member_item_id) VALUES (335642, 117724); INSERT INTO etl_group_membership (group_item_id, member_item_id) VALUES (335642, 104153); INSERT INTO etl_group_membership (group_item_id, member_item_id) VALUES (335642, 5318);
表2:v_poll_item(设备-监控项关联表)
create table v_poll_item ( device_item_id int not null, item_id int not null ); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (117722, 273215); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (117722, 117936); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (117722, 117873); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (117722, 123305); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (104151, 240006); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (104151, 240005); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (104151, 239415); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (104151, 239414); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (5316, 118310); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (5316, 130627); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (5316, 298564); INSERT INTO v_poll_item (device_item_id, item_id) VALUES (5316, 118311);
表3:nrm_cpustats_rate(CPU利用率监控数据表)
注意:im_utilization字段允许为NULL
create table nrm_cpustats_rate ( item_id int not null, tstamp datetime not null, im_utilization float, ); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (273215, '2021-06-28 19:55:00.000000', 2); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (273215, '2021-06-27 23:35:00.000000', 24); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (273215, '2021-06-26 14:05:00.000000', 27); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (273215, '2021-06-25 09:05:00.000000', 29); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (117936, '2021-06-28 19:30:00.000000', 17); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (117936, '2021-06-28 19:15:00.000000', 35); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (117936, '2021-06-28 19:05:00.000000', 50); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (117936, '2021-06-27 05:45:00.000000', 89); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (117936, '2021-06-25 09:20:00.000000', 37); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (117936, '2021-06-25 09:10:00.000000', 51); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (117936, '2021-06-25 08:50:00.000000', 90); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (118310, '2021-06-23 04:10:00.000000', 51); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (118310, '2021-06-23 03:15:00.000000', 48); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (118310, '2021-06-22 22:20:00.000000', 19); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (239414, '2021-06-22 17:10:00.000000', 11); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (239414, '2021-06-22 16:30:00.000000', 37); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (239414, '2021-06-22 16:35:00.000000', 38); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (239414, '2021-06-28 18:45:00.000000', 74); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (239414, '2021-06-28 18:48:00.000000', 76); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (239414, '2021-06-28 18:50:00.000000', 77); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (118311, '2021-06-28 00:40:00.000000', 29); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (118311, '2021-06-23 22:30:00.000000', 37); INSERT INTO nrm_cpustats_rate (item_id, tstamp, im_utilization) VALUES (118311, '2021-06-23 22:25:00.000000', 92);
基础查询语句
- 查询组内设备ID:
SELECT member_item_id FROM etl_group_membership WHERE group_item_id = 335640; - 根据设备ID查询对应监控item_id:
SELECT item_id FROM v_poll_item WHERE device_item_id IN (<上一步查询到的设备ID列表>);
查询输入参数
- 时间范围:过去7天到昨日,条件为
AND tstamp > '2021-06-22 00:00:00.000000' AND tstamp <= '2021-06-28 23:59:59.000000' - 指定组ID:335640
- 违规阈值(breach_threshold):25,即im_utilization >= 25判定为违规
- 单日最低违规次数:2,即单日违规次数达到2次及以上才纳入统计
预期输出
仅保留单日违规次数>=2的记录,例如item_id为273215的记录需排除,因为其虽然总共有2次违规,但分散在2天,单日违规次数均不足2次。
device_item_id | item_id | count_of_breach | date_when_breached | max_utilization | max_utilization_tstamp ===================================================================================================================== 117722 | 117936 | 2 | 2021-06-28 | 90 | 2021-06-25 08:50:00.000000 117722 | 117936 | 3 | 2021-06-25 | 90 | 2021-06-25 08:50:00.000000 5316 | 118310 | 2 | 2021-06-23 | 51 | 2021-06-23 04:10:00.000000 5316 | 118311 | 2 | 2021-06-23 | 92 | 2021-06-23 22:25:00.000000 104151 | 239414 | 2 | 2021-06-22 | 77 | 2021-06-28 18:50:00.000000 104151 | 239414 | 3 | 2021-06-28 | 77 | 2021-06-28 18:50:00.000000
待解答问题
- 如果无法用单条SQL实现上述输出,能否提供2条优化后的查询语句?
- 现有查询每日违规记录的语句可以进一步优化,请给出优化方案。
- 我自己写了如下查询语句来获取每个item_id的最大im_utilization及对应时间戳,但不清楚为什么需要在两处都加上时间范围过滤条件,请帮忙解释:
SELECT t.item_id, t.tstamp, t.im_utilization FROM ( SELECT item_id, MAX(im_utilization) AS max_cpu FROM nrm_cpustats_rate WHERE item_id IN (SELECT item_id from v_poll_item WHERE device_item_id IN (SELECT item_id FROM v_poll_item WHERE device_item_id IN (117722, 104151, 5316))) AND tstamp > '2021-06-22 00:00:00.000000' AND tstamp <= '2021-06-28 23:59:59.000000' GROUP BY item_id ) AS m INNER JOIN nrm_cpustats_rate AS t ON t.item_id = m.item_id AND t.im_utilization = m.max_cpu AND tstamp > '2021-06-22 00:00:00.000000' AND tstamp <= '2021-06-28 23:59:59.000000' ORDER BY 1, 2 DESC, 3 DESC
内容的提问来源于stack exchange,提问作者curious_brain
相关产品推荐
相关产品推荐

