需要为整周db_name选择最大LAG_MIN，并计算整周的发生次数

Question

下面是选择过去 24 小时内最大lag_min的 SQL 查询。

WITH temp1 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,
b.PROPERTY_VALUE DataGuardStatus,
c.PROPERTY_VALUE OpenMode
FROM sysman.mgmt_metrics_raw a,
SYSMAN.MGMT$TARGET_PROPERTIES b,
SYSMAN.MGMT$TARGET_PROPERTIES c
WHERE a.metric_guid = '52D41523BFF32FD2E50DB55B9E730E68'
AND round((a.value / 60),0)> 5
AND a.target_guid = b.target_guid AND b.target_guid=c.target_guid
AND (b.target_name NOT LIKE '%opd%' AND b.target_name NOT LIKE '%ope%' AND b.target_name NOT LIKE '%opt%' AND b.target_name NOT LIKE '%opl%')
AND b.PROPERTY_NAME='DataGuardStatus'
AND c.PROPERTY_NAME='OpenMode'
AND a.collection_timestamp > SYSDATE - 1
)
select collection_timestamp,
lag_min,
db_name,
OpenMode
from (
select collection_timestamp,
lag_min,
db_name,
OpenMode,
max(collection_timestamp) over (partition by db_name) max_col_time
from
(
select collection_timestamp,
lag_min,
db_name,
OpenMode,
max(lag_min) over (partition by db_name) max_lag_min
from temp1 where db_name is not NULL
)
where lag_min = max_lag_min
)
where collection_timestamp = max_col_time 
order by lag_min desc;

输出 -->

COLLECTION_TIMESTAM    LAG_MIN DB_NAME    OPENMODE
------------------- ---------- ---------- ---------------------
2019-07-21 06:14:42        179 opp314a    MOUNTED
2019-07-21 03:01:08        178 ewp2c      READ ONLY WITH APPLY
2019-07-21 04:52:28         53 opp426a    MOUNTED
2019-07-20 10:44:05         40 opp219a    MOUNTED
2019-07-21 01:52:00         39 opp231a    MOUNTED
2019-07-21 00:40:17         27 opp302a    MOUNTED
2019-07-21 04:19:50         11 opp351a    READ ONLY WITH APPLY
2019-07-21 04:13:13         10 opp395a    MOUNTED
2019-07-21 04:14:58         10 opp422a    MOUNTED

已选择 9 行。

示例 如果下面是数据：

COLLECTION_TIMESTAM    LAG_MIN DB_NAME    OPENMODE
------------------- ---------- ---------- ---------------------
2019-07-21 06:14:42        179 opp314a    MOUNTED
2019-07-20 03:01:08        228 opp314a    MOUNTED
2019-07-19 04:52:28        167 opp314a    MOUNTED
2019-07-18 10:44:05         41 opp314a    MOUNTED
2019-07-21 01:52:00         39 opp231a    MOUNTED
2019-07-20 00:40:17         27 opp231a    MOUNTED
2019-07-19 04:19:50         11 opp231a    MOUNTED

我希望输出为

COLLECTION_TIMESTAM    LAG_MIN DB_NAME    OPENMODE        COUNT
------------------- ---------- ---------- --------------- ------
2019-07-20 03:01:08        228 opp314a    MOUNTED         4 
2019-07-21 01:52:00         39 opp231a    MOUNTED         3

Answer 1

您可以将

max() over (partition by ...)和count() over (partition by ...)分析函数应用于<your query>

select collection_timestamp,lag_min,db_name,OpenMode,"COUNT"
  from 
  (
   select collection_timestamp,lag_min,db_name,OpenMode,
         max(lag_min) over 
            ( partition by db_name ) as lag_max,
         count(db_name)  over 
            ( partition by db_name ) as "COUNT"  
     from
     (
       <your_query>    
     )
   )
 where lag_max =  lag_min
 order by collection_timestamp;

演示