snaps-AAS

select TRUNC(CAST(end_interval_time_N AS DATE),'HH24') as end_interval_time, SNAP_ID, ROUND(DB_TIME/60,2) "db_time (min)", ROUND(DB_TIME /((CAST(end_interval_time_N AS DATE) - CAST (begin_interval_time_N AS DATE))*86400),2) as aas
from
(select A.SNAP_ID ,A.INSTANCE_NUMBER ,min(A.begin_interval_time) OVER (partition by A.dbid, A.snap_id) AS begin_interval_time_N ,min(A.end_interval_time) OVER (partition by A.dbid, A.snap_id) AS end_interval_time_N ,round((B.VALUE - LAG(B.VALUE,1,0) OVER (ORDER BY A.SNAP_ID))/1000000,2) as DB_TIME ,row_number() over(partition by a.INSTANCE_NUMBER order by a.SNAP_ID desc ) as orden
from dba_hist_snapshot A , DBA_HIST_SYS_TIME_MODEL B,dba_hist_database_instance C
where A.SNAP_ID=B.SNAP_ID and A.DBID=B.DBID And A.INSTANCE_NUMBER=B.INSTANCE_NUMBER and B.STAT_NAME = 'DB time' and A.DBID=C.DBID and A.INSTANCE_NUMBER=C.INSTANCE_NUMBER and A.startup_time = C.startup_time and A.begin_interval_time >= trunc(sysdate) - 7 and A.begin_interval_time <= trunc(sysdate) + 1 and A.INSTANCE_NUMBER = 1 ) order by orden desc;