Aggregate function over a given time interval

aggregate, oracle, oracle10g, sql

Solution

CREATE TABLE tt (time TIMESTAMP, value NUMBER);

INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.40.00.000000000 PM', 2);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.41.35.000000000 PM', 3);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.43.22.000000000 PM', 4);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.47.55.000000000 PM', 5);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.52.00.000000000 PM', 2);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.54.59.000000000 PM', 3);
INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.56.01.000000000 PM', 4);


WITH tmin AS (
    SELECT MIN(time) t FROM tt
),   tmax AS (
    SELECT MAX(time) t FROM tt
)
SELECT ranges.inf, ranges.sup, AVG(tt.value)
FROM
     (
        SELECT 
            5*(level-1)*(1/24/60) + tmin.t as inf,
            5*(level)*(1/24/60) + tmin.t as sup
        FROM tmin, tmax
        CONNECT BY (5*(level-1)*(1/24/60) + tmin.t) < tmax.t
    ) ranges JOIN tt ON tt.time BETWEEN ranges.inf AND ranges.sup
GROUP BY ranges.inf, ranges.sup
ORDER BY ranges.inf

fiddle: http://sqlfiddle.com/#!4/9e314/11

edit: beated by Justin, as usual... :-)

Problem

My SQL is a bit rusty and I'm having quite a bit of difficulty with this problem. Suppose I have a table with a Timestamp column and a Number column. The goal is to return a result set containing the average value for some arbitrarily chosen regular interval. So, for example, if I had the following initial data, the resulting output with a 5 minute interval would be as follows: ``` time value ------------------------------- ----- 06-JUN-12 12.40.00.000000000 PM 2 06-JUN-12 12.41.35.000000000 PM 3 06-JUN-12 12.43.22.000000000 PM 4 06-JUN-12 12.47.55.000000000 PM 5 06-JUN-12 12.52.00.000000000 PM 2 06-JUN-12 12.54.59.000000000 PM 3 06-JUN-12 12.56.01.000000000 PM 4 OUTPUT: start_time avg_value ------------------------------- --------- 06-JUN-12 12.40.00.000000000 PM 3 06-JUN-12 12.45.00.000000000 PM 5 06-JUN-12 12.50.00.000000000 PM 2.5 06-JUN-12 12.55.00.000000000 PM 4 ``` Note that this is an Oracle database, so Oracle-specific solutions would work fine. This could, of course, be done with a stored procedure but I was hoping to accomplish the task in a single query.

Original source