Oracle Summarize data by 15 second interval

oracle group by hour interval
oracle group by 30 minute intervals
oracle query to get data for every hour
oracle format interval
oracle interval day to second to number
oracle interval minute
oracle sql query to get hourly data
oracle group by date from timestamp

I want to summarize below query by 15 second time intervals.

select to_char(sample_time,'hh24:mi:ss') as SAMPLE_TIME,nvl(wait_class,'CPU'),count(*) 
from gv$active_session_history 
group by wait_class,sample_time;

Result;

SAMPLE_TIME    WAITS   COUNT
-----------------------------
14:59:00        CPU     3
14:59:02        CPU     1
14:59:08        CPU     2
14:58:11        CPU     2
14:59:18        CPU     1
14:59:24        CPU     2
14:58:29        CPU     2

What i want is summing values by 15 second intervals. How can i make it?

SAMPLE_TIME    WAITS   COUNT
-----------------------------
14:59:15        CPU     8
14:59:30        CPU     5   

Use this function:

CREATE OR REPLACE FUNCTION MakeInterval(ts IN TIMESTAMP, roundInterval IN INTERVAL DAY TO SECOND) RETURN TIMESTAMP DETERMINISTIC IS
    denom INTEGER;
BEGIN
    IF roundInterval >= INTERVAL '1' HOUR THEN
        denom := EXTRACT(HOUR FROM roundInterval);
        IF MOD(24, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts) + TRUNC(EXTRACT(HOUR FROM ts) / denom) * denom * INTERVAL '1' HOUR;
    ELSIF roundInterval >= INTERVAL '1' MINUTE THEN
        denom := EXTRACT(MINUTE FROM roundInterval);
        IF MOD(60, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'hh') + TRUNC(EXTRACT(MINUTE FROM ts) / denom) * denom * INTERVAL '1' MINUTE;
    ELSE
        denom := EXTRACT(SECOND FROM roundInterval);                
        IF MOD(60, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'mi') + TRUNC(EXTRACT(SECOND FROM ts) / denom) * denom * INTERVAL '1' SECOND;
    END IF;
END MakeInterval;

Then you can use

...
GROUP BY wait_class, MakeInterval(SAMPLE_TIME, INTERVAL '15' SECOND)

Ask TOM "Summarizing data over time - by time interval", Developers and DBAs get help from Oracle experts on: Summarizing data over query to average data over standard periods, such as 5 seconds, 15 seconds, . Developers and DBAs get help from Oracle experts on: Summarizing data over time - by time interval. Skip to Main Content. 15 seconds, 5 minutes, 10 minutes

-- can't add comments because my rep is very low

@Wernfried Domscheit gave you all the information required, I took his comments and simply "slapped" them in the group by part of your query (and obviously needed to add the bucket definition in the select statement)

The code below will allow you to check Wernfried's reply:

select TO_CHAR(TRUNC(SAMPLE_TIME, 'mi') + TRUNC(EXTRACT(SECOND FROM SAMPLE_TIME) /
EXTRACT(SECOND FROM INTERVAL '15' SECOND)) * EXTRACT(SECOND FROM INTERVAL '15' 
SECOND) *     INTERVAL '1' SECOND,'MM/DD/YYYY HH24:MI:SS') AS TimeBucket
,  nvl(wait_class,'CPU')
,count(*) as NbrRecords
from gv$active_session_history 
GROUP BY TO_CHAR(TRUNC(SAMPLE_TIME, 'mi') + TRUNC(EXTRACT(SECOND FROM SAMPLE_TIME) /
 EXTRACT(SECOND FROM INTERVAL '15' SECOND)) * EXTRACT(SECOND FROM INTERVAL '15' 
SECOND) *     INTERVAL '1' SECOND,'MM/DD/YYYY HH24:MI:SS'), nvl(wait_class,'CPU')
ORDER BY TimeBucket DESC
;

HTH,

B

grouping query result in 15 minutes interval from - Ask Tom, Category: Database - Version: 11.2 I have a question about grouping query result in 15 minutes interval But if I have 32 results from first INNER JOIN and 36 results from second INNER JOIN the overall result is 32 rows INTERVAL '15' MINUTE. 15 minutes. INTERVAL '250' HOUR (3) 250 hours. INTERVAL '15.6789' SECOND (2,3) Rounded to 15.679 seconds. Because the precision is 3, the fractional second ‘6789’ is rounded to ‘679’. In this tutorial, you have learned how to use the Oracle INTERVAL data type to store periods of time in the tables.

This looks a bit hack-ish but it should be what you are looking for. The lpad is just for a nicer looking result.

Edit: Forgot to group by the actual statement and not the sample_time. It should now group in 15 seconds intervals.

select 
    to_char(sample_time, 'hh24:mi:') || lpad(trunc(to_number(to_char(sample_time, 'ss')) / 15) * 15, 2, '0') as SAMPLE_TIME,
    nvl(wait_class, 'CPU') as wait_class,
    count(*) 
from gv$active_session_history 
group by nvl(wait_class, 'CPU'), to_char(sample_time, 'hh24:mi:') || lpad(trunc(to_number(to_char(sample_time, 'ss')) / 15) * 15, 2, '0')

group records by interval of 3 seconds - Ask Tom, What I need to do is sum the amounts where the time of the records is within 3 dbms_random.value( 0, 100 ) ); 14 l_date := l_date + 1/24/60/60; 15 end loop; to how you grouped data by a time interval as you demonstrated in this thread? Summarize by calendar week. Summarizing time-series data by calendar week is useful, and it works very much like the other interval reports. To make this work, we need a formula that can yield the first day of the calendar week given any DATE value. Oracle’s TRUNC() function makes this easy. TRUNC(sales_time,'DAY')

Oracle INTERVAL: A Beginner's Guide, Summary: in this tutorial, we'll introduce you to the Oracle INTERVAL data types and show INTERVAL DAY TO SECOND – stores intervals using days, hours, minutes, and INTERVAL '15:30' MINUTE TO SECOND, 15 minutes 30 seconds. Every finite set of days can be written in a unique way as the (finite) union of non-empty sets of CONSECUTIVE days, called "closed intervals", so that - in addition - no two intervals are adjacent (one interval starts the day after another interval ends; obviously, two such intervals should be combined into a single one, equal to their union).

Troubleshooting Oracle Performance, Second, when using automatic degree of parallelism which is enabled by setting the an increasing number of concurrent parallel operations are executed at short intervals. <<<$sql & sleep 5 done Figure 15-9 summarizes the results measured on version 11.2. Comparison of loading data with and without direct-​path. I have a question about grouping query result in 15 minutes interval from two tables. I already have the query which gives me quite normal result, but I have two issues SCHNAME.TABLE1 as T1: NAME, ID2, ID SCHNAME.TABLE2 as T2: IDNAME, TIME1 as INTERVAL1, NUM as QUANTITY1 SCHNAME.TABLE3 as T3: AID, TIME2 as INTERVAL2, QUANTITY2 T1.ID = T2.IDNAME

Mastering Oracle SQL, 105 functions, 135–143 interval data, 130 INTERVAL DAY TO SECOND datatype, 123 summarizing by, 123 time zones database, 125 overview, 125 session, WHERE clause, 15 CONNECT BY clause, hierarchical queries, 84 constants,  Interval Literals. An interval literal specifies a period of time. You can specify these differences in terms of years and months, or in terms of days, hours, minutes, and seconds. Oracle Database supports two types of interval literals, YEAR TO MONTH and DAY TO SECOND. Each type contains a leading field and may contain a trailing field.

Comments
  • I dont want to use function. Just need to write in sql.
  • @johntrue Are you not able to pick the desired part from the function?
  • Would be GROUP BY TRUNC(SAMPLE_TIME, 'mi') + TRUNC(EXTRACT(SECOND FROM SAMPLE_TIME) / EXTRACT(SECOND FROM INTERVAL '15' SECOND)) * EXTRACT(SECOND FROM INTERVAL '15' SECOND) * INTERVAL '1' SECOND or a bit shorter GROUP BY TRUNC(SAMPLE_TIME, 'mi') + TRUNC(EXTRACT(SECOND FROM SAMPLE_TIME) / 15) * INTERVAL '15' SECOND
  • could you please rewrite in a single sql ? Then i can verify this is correct answer. Thank you.
  • @johntrue Sorry, SO is not a coding service.
  • Pretty sure it's 15 seconds interval - I have a resultset in front of my eyes :) - but I'm not sure how to add a screenshot!
  • @johntrue most likely you don't have data for 1.5 Minutes because of idle sessions
  • It does not group by 15 second interval
  • It groups data of several days into one value. I don't think this is what john is looking for.