Get query result 15 second interval in Oracle

I have following query to get 15 seconds interval. However I want to show also ‘0’ values even if the result doesn’t have value.

Here is the Query;

select to_char(trunc(sample_time,'hh24') + (trunc(to_char(sample_time,'mi')/1)*1)/1440 + (trunc(to_char(sample_time,'ss')/15)*15)/86400,'hh24:mi:ss') as SAMPLE_TIME,nvl(wait_class,'CPU') as waits,round(count(*)/15,2)
from gv$active_session_history
where sample_time >= sysdate-60/1440
group by wait_class,to_char(trunc(sample_time,'hh24') + (trunc(to_char(sample_time,'mi')/1)*1)/1440  + (trunc(to_char(sample_time,'ss')/15)*15)/86400,'hh24:mi:ss')
order by 1 desc,3 desc;

The result is;

SAMPLE_TIME    WAITS   COUNT
-----------------------------
14:59:30        CPU     3
14:59:00        CPU     2    
14:58:45        CPU     2
14:58:30        CPU     1
14:58:15        CPU     2
14:57:45        CPU     2

What i want is this;

    SAMPLE_TIME    WAITS   COUNT
    -----------------------------
    14:59:30        CPU     3
    14:59:15        CPU     0  // Added 14:59:15    
    14:59:00        CPU     2    
    14:58:45        CPU     2
    14:58:30        CPU     1
    14:58:15        CPU     2
    14:58:00        CPU     0  // Added 14:59:15
    14:57:45        CPU     2

What should i change from above query?

Answer

You can use a subquery as follows to generate the list of all dates within the last hour with a 15 seconds interval :

select trunc(sysdate, 'mi') - 15*level/(60*60*24) 
from dual
connect by level < 60*60/15;

Inserted that in your query :

with a as (
    select trunc(sysdate, 'mi') - 15*level/(60*60*24) as sample_time 
    from dual 
    connect by level < 60*60/15
)
select 
    a.sample_time,
    nvl(wait_class,'cpu') as waits,
    round(sum(decode(ash.wait_class, null, 0, 1))/15,2)
from a
left join gv$active_session_history ash 
    on ash.sample_time between a.sample_time and a.sample_time + 15/(60*60*24)
group by a.sample_time, wait_class
order by 1 desc,3 desc;