mysql8的with查询和自查询对比

-- string转时间戳
select unix_timestamp('2021-10-13 15:15:00');
-- 时间转时间
select from_unixtime(1634109300);
-- date转时间戳
select unix_timestamp('2021-10-13 15:15:00');
-- string转时区（最后得到的还是string类型）
select convert_tz('2021-10-13 15:15:00', '+00:00', '-08:00');
-- date转时区（最后得到的还是date）
select convert_tz('2021-10-13 15:15:00', '+00:00', '-08:00');
-- 计算时间 (参与计算的两个值可以是 时间类型 时间格式的string类型)
select TIMESTAMPDIFF(MINUTE, '2021-10-13 15:15:00', now());
-- 计算时间 (参与计算的两个值可以是 时间类型 时间格式的string类型)
select datediff('2021-10-11 15:15:00', now());
-- date转string
select date_format(now(), '%Y-%m-%d %H:%i:%s');
-- string转date
select str_to_date('2021-10-11 15:15:00', '%Y-%m-%d %H:%i:%s');


-- 延迟with查询sql（每个G库-每台机器-每个指标 最后一条数据的日志时间和入库时差）
WITH log_create_last_time_tab as (
    SELECT max(time) as time,
           glib_id,
           hostname,
           metric_item,
           metric_origin
    FROM t_dhc_metric_20211015
    WHERE metric_origin = 11
      and metric_item in ('sdk_heatbeat', 'online')
    GROUP BY glib_id,
             hostname,
             metric_item,
             metric_origin
),
     log_create_link_tab as (
         SELECT time,
                glib_id,
                hostname,
                metric_item,
                metric_origin,
                metric_value
         FROM t_dhc_metric_20211015
         WHERE metric_origin = 11
           and metric_item in ('sdk_heatbeat', 'online')
     ),
     log_create_tab as (
         select t1.time,
                t1.glib_id,
                t1.hostname,
                t1.metric_item,
                t1.metric_origin,
                t2.metric_value
         from log_create_last_time_tab t1
                  left join
              log_create_link_tab t2
              ON t1.time = t2.time
                  AND t1.glib_id = t2.glib_id
                  AND t1.hostname = t2.hostname
                  AND t1.metric_item = t2.metric_item
                  AND t1.metric_origin = t2.metric_origin
     ),
     log_db_last_time_tab as (
         SELECT max(time) as time,
                glib_id,
                hostname,
                metric_item,
                metric_origin
         FROM t_dhc_metric_20211015
         WHERE metric_origin = 12
           and metric_item in ('sdk_heatbeat', 'online')
         GROUP BY glib_id,
                  hostname,
                  metric_item,
                  metric_origin
     ),
     log_db_link_tab as (
         SELECT time,
                glib_id,
                hostname,
                metric_item,
                metric_origin,
                metric_value
         FROM t_dhc_metric_20211015
         WHERE metric_origin = 12
           and metric_item in ('sdk_heatbeat', 'online')
     ),
     log_db_tab as (
         select t1.time,
                t1.glib_id,
                t1.hostname,
                t1.metric_item,
                t1.metric_origin,
                t2.metric_value
         from log_db_last_time_tab t1
                  left join
              log_db_link_tab t2
              ON t1.time = t2.time
                  AND t1.glib_id = t2.glib_id
                  AND t1.hostname = t2.hostname
                  AND t1.metric_item = t2.metric_item
                  AND t1.metric_origin = t2.metric_origin
     ),
     delay_log_tab as (
         SELECT t1.time,
                t1.glib_id,
                t1.hostname,
                t1.metric_item,
                COALESCE(t1.metric_value, 0)                                as metric_value1,
                COALESCE(t2.metric_value, 0)                                as metric_value2,
                COALESCE(t1.metric_value, 0) - COALESCE(t2.metric_value, 0) AS diff
         FROM log_db_tab t1
                  left join
              log_create_tab t2
              ON t1.time = t2.time
                  AND t1.glib_id = t2.glib_id
                  AND t1.hostname = t2.hostname
                  AND t1.metric_item = t2.metric_item
         where t1.metric_value - t2.metric_value > 0
     )

select *
from delay_log_tab;

-- 延迟with查询sql（每个G库-每台机器-每个指标 最后一条数据的日志时间和采集时差）
WITH log_create_last_time_tab as (
    SELECT max(time) as time,
           glib_id,
           hostname,
           metric_item,
           metric_origin
    FROM t_dhc_metric_20211015
    WHERE metric_origin = 11
      and metric_item in ('sdk_heatbeat', 'online')
    GROUP BY glib_id,
             hostname,
             metric_item,
             metric_origin
),
     log_create_link_tab as (
         SELECT time,
                glib_id,
                hostname,
                metric_item,
                metric_origin,
                metric_value
         FROM t_dhc_metric_20211015
         WHERE metric_origin = 11
           and metric_item in ('sdk_heatbeat', 'online')
     ),
     log_create_tab as (
         select t1.time,
                t1.glib_id,
                t1.hostname,
                t1.metric_item,
                t1.metric_origin,
                t2.metric_value
         from log_create_last_time_tab t1
                  left join
              log_create_link_tab t2
              ON t1.time = t2.time
                  AND t1.glib_id = t2.glib_id
                  AND t1.hostname = t2.hostname
                  AND t1.metric_item = t2.metric_item
                  AND t1.metric_origin = t2.metric_origin
     ),
     heart_beat_tab as (
         SELECT lc.time,
                lc.glib_id,
                lc.hostname,
                lc.metric_item,
                COALESCE(unix_timestamp(time), 0)                                as metric_value1,
                COALESCE(lc.metric_value, 0)                                     as metric_value2,
                COALESCE(unix_timestamp(time), 0) - COALESCE(lc.metric_value, 0) as diff
         FROM log_create_tab lc
     )

select * from heart_beat_tab where diff > 0;

-- 延迟报警with查询的sql
WITH log_time_tab as (
    SELECT substr(time, 1, 16) AS time,
           glib_id,
           hostname,
           metric_item,
           metric_origin,
           max(metric_value)   as metric_value
    FROM t_dhc_metric_20211014
    WHERE metric_origin = 11
      and metric_item in ('sdk_heatbeat', 'online')
    GROUP BY substr(time, 1, 16),
             glib_id,
             hostname,
             metric_item,
             metric_origin
),
     db_time_tab as (
         SELECT substr(time, 1, 16) AS time,
                glib_id,
                hostname,
                metric_item,
                metric_origin,
                max(metric_value)   as metric_value
         FROM t_dhc_metric_20211014
         WHERE metric_origin = 12
           and TIMESTAMPDIFF(MINUTE, time, now()) < 50
           and metric_item in ('sdk_heatbeat', 'online')
         GROUP BY substr(time, 1, 16),
                  glib_id,
                  hostname,
                  metric_item,
                  metric_origin
     ),
     delay_log_tab as (
         SELECT t1.time,
                t1.glib_id,
                t1.hostname,
                t1.metric_item,
                COALESCE(t1.metric_value, 0)                                as metric_value1,
                COALESCE(t2.metric_value, 0)                                as metric_value2,
                COALESCE(t1.metric_value, 0) - COALESCE(t2.metric_value, 0) AS diff
         FROM db_time_tab t1
                  left join
              log_time_tab t2
              ON t1.time = t2.time
                  AND t1.glib_id = t2.glib_id
                  AND t1.hostname = t2.hostname
                  AND t1.metric_item = t2.metric_item
         where t1.metric_value - t2.metric_value > 0
     )

select *
from delay_log_tab;

-- 心跳with查询sql
with max_item_value_tab as (
    SELECT glib_id,
           hostname,
           metric_item,
           metric_origin,
           max(metric_value) as max_metric_value
    FROM t_dhc_metric_20211014
    WHERE metric_origin = 11
      and TIMESTAMPDIFF(MINUTE, time, now()) < 50
      and metric_item in ('sdk_heatbeat', 'online')
    GROUP BY glib_id,
             hostname,
             metric_item,
             metric_origin
),
     max_time_tab as (
         select *
         from t_dhc_metric_20211014
         where metric_origin = 11
           and TIMESTAMPDIFF(MINUTE, time, now()) < 50
           and metric_item in ('sdk_heatbeat', 'online')
     ),
     max_data_tab as (
         select a.glib_id,
                a.hostname,
                a.metric_item,
                a.metric_origin,
                a.max_metric_value,
                b.metric_value,
                b.time
         from max_item_value_tab as a
                  left join
              max_time_tab as b
              on a.glib_id = b.glib_id and
                 a.hostname = b.hostname and
                 a.metric_item = b.metric_item and
                 a.metric_origin = b.metric_origin and
                 a.max_metric_value = b.metric_value
     ),
     distinct_max_time_tab as (
         select glib_id,
                hostname,
                metric_item,
                metric_origin,
                max_metric_value,
                metric_value,
                max(time) as time
         from max_data_tab
         group by glib_id,
                  hostname,
                  metric_item,
                  metric_origin,
                  max_metric_value,
                  metric_value
     ),

     heartbeat_tab as (
         select time,
                glib_id,
                hostname,
                metric_item,
                metric_origin,
                unix_timestamp(time)                                              as metric_value1,
                metric_value                                                      as metric_value2,
                COALESCE(unix_timestamp(time), 0) - COALESCE(max_metric_value, 0) AS diff
         from distinct_max_time_tab
         where unix_timestamp(time) - max_metric_value > 1
     )
select *
from heartbeat_tab;





-- 延迟报警子查询查询的sql
SELECT t1.time,
       t1.glib_id,
       t1.hostname,
       t1.metric_item,
       COALESCE(t1.metric_value, 0)                                as metric_value1,
       COALESCE(t2.metric_value, 0)                                as metric_value2,
       COALESCE(t1.metric_value, 0) - COALESCE(t2.metric_value, 0) AS diff
FROM (
         SELECT substr(time, 1, 16) AS time,
                glib_id,
                hostname,
                metric_item,
                metric_origin,
                max(metric_value)   as metric_value
         FROM t_dhc_metric_20211014
         WHERE metric_origin = 12
           and TIMESTAMPDIFF(MINUTE, time, now()) < 60
           and metric_item in ('sdk_heatbeat', 'online')
         GROUP BY substr(time, 1, 16),
                  glib_id,
                  hostname,
                  metric_item,
                  metric_origin
     ) t1
         LEFT JOIN (
    SELECT substr(time, 1, 16) AS time,
           glib_id,
           hostname,
           metric_item,
           metric_origin,
           max(metric_value)   as metric_value
    FROM t_dhc_metric_20211014
    WHERE metric_origin = 11
      and TIMESTAMPDIFF(MINUTE, time, now()) < 60
      and metric_item in ('sdk_heatbeat', 'online')
    GROUP BY substr(time, 1, 16),
             glib_id,
             hostname,
             metric_item,
             metric_origin
) t2
                   ON t1.time = t2.time
                       AND t1.glib_id = t2.glib_id
                       AND t1.hostname = t2.hostname
                       AND t1.metric_item = t2.metric_item
where t1.metric_value - t2.metric_value > 2;

-- 心跳子查询sql
select glib_id,
       hostname,
       metric_item,
       metric_origin,
       max_metric_value,
       metric_value,
       time
from (
         select glib_id,
                hostname,
                metric_item,
                metric_origin,
                max_metric_value,
                metric_value,
                max(time) as time
         from (
                  select a.glib_id,
                         a.hostname,
                         a.metric_item,
                         a.metric_origin,
                         a.max_metric_value,
                         b.metric_value,
                         b.time
                  from (
                           SELECT glib_id,
                                  hostname,
                                  metric_item,
                                  metric_origin,
                                  max(metric_value) as max_metric_value
                           FROM t_dhc_metric_20211013
                           WHERE metric_origin = 11
                             and TIMESTAMPDIFF(MINUTE, time, now()) < 60
                             and metric_item not in ('sdk_heatbeat', 'online')
                           GROUP BY glib_id,
                                    hostname,
                                    metric_item,
                                    metric_origin
                       ) a
                           left join
                       (select *
                        from t_dhc_metric_20211013
                        where metric_origin = 11
                          and TIMESTAMPDIFF(MINUTE, time, now()) < 60
                          and metric_item not in ('sdk_heatbeat', 'online')) b
                       on a.glib_id = b.glib_id and
                          a.hostname = b.hostname and
                          a.metric_item = b.metric_item and
                          a.metric_origin = b.metric_origin and
                          a.max_metric_value = b.metric_value
              ) as b2
         group by glib_id,
                  hostname,
                  metric_item,
                  metric_origin,
                  max_metric_value,
                  metric_value) as d
where unix_timestamp(time) - max_metric_value > 2;