ASH Average Active Session(AAS)

with t as (
SELECT sample_id, snap_id,count(*) aas,
       sum(decode(session_state,'ON CPU',1,0)) aas_cpu,
       sum(decode(session_state,'ON CPU',0,1)) aas_noncpu
FROM dba_hist_active_sess_history t
group by sample_id,snap_id
order by sample_id desc
)
SELECT trunc(begin_interval_time,'dd') "day",
       max(aas) max_aas,median(aas) avg_aas,
       max(aas_cpu) max_aasc,median(aas_cpu) avg_aasc,
       max(aas_noncpu) max_aasnc,median(aas_noncpu) avg_aasnc
FROM dba_hist_snapshot h,t  where t.snap_id = h.snap_id
group by trunc(begin_interval_time,'dd')
order by 1;