时间戳

首页 > 解决方案 > 我只需要找到最大 LAG_MIN 的 DB_NAME

sql - 我只需要找到最大 LAG_MIN 的 DB_NAME

问题描述

SELECT DISTINCT a.collection_timestamp,
                round((a.value / 60),0) lag_min,
                substr(b.target_name,1,instr(b.target_name,'.',1,1)- 1) db_name
FROM sysman.mgmt_metrics_raw a,
     sysman.mgmt$agents_monitoring_targets b
WHERE a.metric_guid = '26372D42E0B05538E505F330D67E8CEA'
      AND round((a.value / 60),0) > 5
      AND a.target_guid = b.target_guid
      AND b.target_name LIKE '%opp%'
      AND a.collection_timestamp > SYSDATE - 1
ORDER BY a.collection_timestamp ASC;

返回低于输出。但我只需要找到具有最大 LAG_MIN 的 DB_NAME。我怎样才能做到这一点？

COLLECTION_TIMESTAM    LAG_MIN DB_NAME
------------------- ---------- ----------
2019-03-31 08:27:29        488 opp449a
2019-03-31 08:29:33        105 opp447a
2019-03-31 08:32:29        493 opp449a
2019-03-31 08:34:32        110 opp447a
2019-03-31 08:37:29        498 opp449a
2019-03-31 08:39:33        115 opp447a

标签： sqloracleoracle11g

解决方案

如果您将当前查询用作 CTE（我将其命名为“temp”），那么这样的事情可能会完成这项工作：

WITH temp AS
  (SELECT DISTINCT a.collection_timestamp,
                   round((a.value / 60),0)lag_min,
                   substr(b.target_name,1,instr(b.target_name,'.',1,1)- 1)db_name
     FROM sysman.mgmt_metrics_raw a,
          sysman.mgmt$agents_monitoring_targets b
     WHERE a.metric_guid = '26372D42E0B05538E505F330D67E8CEA'
       AND round((a.value / 60),0)> 5
       AND a.target_guid = b.target_guid
       AND b.target_name LIKE '%opp%'
       AND a.collection_timestamp > SYSDATE - 1
  )
SELECT *
FROM temp
WHERE lag_min = (SELECT MAX(lag_min)
                 FROM temp
                );

推荐阅读