with t as (
select
       btime, event_name,
       trunc((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),6) avg_ms,
       (count_end-count_beg) ct
from (
  select
         e.event_name,
         cast(s.BEGIN_INTERVAL_TIME as date)  btime,
         total_waits count_end,
         time_waited_micro/1000 time_ms_end,
         Lag (e.time_waited_micro/1000)
                OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,
         Lag (e.total_waits)
                OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg
  from
         DBA_HIST_SYSTEM_EVENT e,
         DBA_HIST_SNAPSHOT s
  where  s.snap_id=e.snap_id
  and    e.event_name in ( 'db file sequential read','db file scattered read')
  --and    e.dbid=&DBID
  and    e.dbid=s.dbid
  order by e.event_name, begin_interval_time)
where (count_end-count_beg) > 0
and trunc(btime) >= to_date('01.06.2021','dd.mm.yyyy')
)
select * from t pivot( max(avg_ms) as ms,max(ct) as cnt  for event_name in ('db file scattered read' as SCAT_RD, 'db file sequential read' as SEQ_RD))
order by btime desc