langchao_fayuan/views/v_bi_alm_all_alarm_hour.sql

-- lawe.v_bi_alm_all_alarm_hour source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `v_bi_alm_all_alarm_hour` AS
select
    `m`.`alarm_object_ip` AS `alarm_object_ip`,
    `m`.`alarm_object_id` AS `alarm_object_id`,
    `m`.`alarm_object_type` AS `alarm_object_type`,
    `m`.`alarm_object_name` AS `alarm_object_name`,
    `m`.`severity_id` AS `severity_id`,
    `m`.`title` AS `title`,
    `m`.`alarm_text` AS `alarm_text`,
    `m`.`clr_status` AS `clr_status`,
    `m`.`alarm_count` AS `alarm_count`,
    `m`.`bg_time` AS `bg_time`,
    `m`.`ed_time` AS `ed_time`,
    `m`.`hours` AS `hours`,
    `m`.`dtype` AS `dtype`,
    `m`.`thedvalue` AS `thedvalue`,
    `m`.`is_active_alarm` AS `is_active_alarm`,
    floor((`m`.`alarm_count` / `m`.`hours`)) AS `avg_alarm_count`,
    if((`m`.`alarm_source` = '性能告警'),
    (60 / 5),
    (60 * 2)) AS `collection_frequency`,
    CASE 
	    WHEN occur_time<=thedvalue_b and update_time>=thedvalue_e THEN 60
	    WHEN occur_time>thedvalue_b and update_time>=thedvalue_e THEN 60-MOD(occur_time,100)
	    WHEN occur_time<=thedvalue_b and update_time<thedvalue_e THEN MOD(update_time,100)
	    WHEN occur_time>thedvalue_b and update_time<thedvalue_e THEN MOD(update_time,100)-MOD(occur_time,100)
    END 'alarm_duration'
from
    (
    select
        `aa`.`alarm_object_ip` AS `alarm_object_ip`,
        `aa`.`alarm_object_id` AS `alarm_object_id`,
        `aa`.`alarm_object_type` AS `alarm_object_type`,
        `aa`.`alarm_object_name` AS `alarm_object_name`,
        `aa`.`severity_id` AS `severity_id`,
        `aa`.`title` AS `title`,
        `aa`.`alarm_text` AS `alarm_text`,
        `aa`.`clr_status` AS `clr_status`,
        `aa`.`alarm_count` AS `alarm_count`,
        cast(date_format(`aa`.`occur_time`, '%Y%m%d%H') as unsigned) AS `bg_time`,
        cast(date_format(`aa`.`update_time`, '%Y%m%d%H') as unsigned) AS `ed_time`,
        (abs(timestampdiff(HOUR, `aa`.`update_time`, `aa`.`occur_time`)) + 1) AS `hours`,
        'HOUR' AS `dtype`,
        `dates`.`thevalue` AS `thedvalue`,
        1 AS `is_active_alarm`,
        `aa`.`alarm_source` AS `alarm_source`,
        date_format(STR_TO_DATE(`dates`.`thevalue`, '%Y%m%d%H%m'),'%Y%m%d%H00') 'thedvalue_b',
        date_format(DATE_ADD(STR_TO_DATE(`dates`.`thevalue`, '%Y%m%d%H%m'), INTERVAL 1 HOUR),'%Y%m%d%H00') 'thedvalue_e',
        date_format(`aa`.`occur_time`, '%Y%m%d%H%m') occur_time, date_format(`aa`.`update_time`, '%Y%m%d%H%m') update_time
    from
        (`t_alm_active_alarm` `aa`
    join (
        select
            concat(`dh`.`yyyyMMdd`, lpad(`dh`.`hour`, 2, 0)) AS `thevalue`
        from
            `dim_hour` `dh`
        where
            (`dh`.`yyyyMMdd` between 20230101 and 20241231)) `dates`)
union all
    select
        `ha`.`alarm_object_ip` AS `alarm_object_ip`,
        `ha`.`alarm_object_id` AS `alarm_object_id`,
        `ha`.`alarm_object_type` AS `alarm_object_type`,
        `ha`.`alarm_object_name` AS `alarm_object_name`,
        `ha`.`severity_id` AS `severity_id`,
        `ha`.`title` AS `title`,
        `ha`.`alarm_text` AS `alarm_text`,
        `ha`.`clr_status` AS `clr_status`,
        `ha`.`alarm_count` AS `alarm_count`,
        cast(date_format(`ha`.`occur_time`, '%Y%m%d%H') as unsigned) AS `bg_time`,
        cast(date_format(`ha`.`update_time`, '%Y%m%d%H') as unsigned) AS `ed_time`,
        (abs(timestampdiff(HOUR, `ha`.`update_time`, `ha`.`occur_time`)) + 1) AS `hours`,
        'HOUR' AS `dtype`,
        `dates`.`thevalue` AS `thedvalue`,
        0 AS `is_active_alarm`,
        `ha`.`alarm_source` AS `alarm_source`,
        date_format(STR_TO_DATE(`dates`.`thevalue`, '%Y%m%d%H%m'),'%Y%m%d%H00') 'thedvalue_b',
        date_format(DATE_ADD(STR_TO_DATE(`dates`.`thevalue`, '%Y%m%d%H%m'), INTERVAL 1 HOUR),'%Y%m%d%H00') 'thedvalue_e',
        date_format(`ha`.`occur_time`, '%Y%m%d%H%m') occur_time, date_format(`ha`.`update_time`, '%Y%m%d%H%m') update_time
    from
        (`t_alm_history_alarm` `ha`
    join (
        select
            concat(`dh`.`yyyyMMdd`, lpad(`dh`.`hour`, 2, 0)) AS `thevalue`
        from
            `dim_hour` `dh`
        where
            (`dh`.`yyyyMMdd` between 20230101 and 20241231)) `dates`)) `m`
where
    (`m`.`thedvalue` between `m`.`bg_time` and `m`.`ed_time`);