summing data at regular time intervals

Question

I have a table which contains transactions (amount column) with associated timestamps (valuedatetime column) at irregular intervals covering several days. I need to build a query which calculates a cumulated sum at regular time intervals from start until end of every day for which I have transactions (for example every 5 minutes). The idea is to create a dataset which can be used for building a daily linechart. I am pretty new to this and any help would be greatly appreciated.

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( amount, valuedatetime ) AS
  SELECT  1, TIMESTAMP '2017-10-22 23:45:21.234' FROM DUAL UNION ALL
  SELECT  2, TIMESTAMP '2017-10-22 23:47:10.000' FROM DUAL UNION ALL
  SELECT  3, TIMESTAMP '2017-10-22 23:52:41.123' FROM DUAL UNION ALL
  SELECT  4, TIMESTAMP '2017-10-22 23:55:23.456' FROM DUAL UNION ALL
  SELECT  5, TIMESTAMP '2017-10-22 23:58:24.789' FROM DUAL UNION ALL
  SELECT  6, TIMESTAMP '2017-10-22 23:59:59.999' FROM DUAL UNION ALL
  SELECT  7, TIMESTAMP '2017-10-23 00:00:00.000' FROM DUAL UNION ALL
  SELECT  8, TIMESTAMP '2017-10-23 00:01:02.234' FROM DUAL UNION ALL
  SELECT  9, TIMESTAMP '2017-10-23 00:05:00.000' FROM DUAL UNION ALL
  SELECT 10, TIMESTAMP '2017-10-23 00:09:59.999' FROM DUAL;

Query 1:

SELECT SUM( amount ),
       --channel_id,
       TRUNC( valuedatetime )
       +
       TRUNC(
         (
           CAST( valuedatetime AS DATE ) - TRUNC( valuedatetime )
         )
         * 24 -- Hours per day
         * 60 -- Minutes per hour
         / 5  -- group into 5 minute buckets
       ) * 5 / 60 / 24 AS range_start
FROM   table_name
GROUP BY
       --channel_id,
       TRUNC( valuedatetime ),
       TRUNC(
         (
           CAST( valuedatetime AS DATE ) - TRUNC( valuedatetime )
         )
         * 24 -- Hours per day
         * 60 -- Minutes per hour
         / 5  -- group into 5 minute buckets
       )

Results:

| SUM(AMOUNT) |          RANGE_START |
|-------------|----------------------|
|           3 | 2017-10-22T23:45:00Z |
|           3 | 2017-10-22T23:50:00Z |
|          15 | 2017-10-23T00:00:00Z |
|          15 | 2017-10-22T23:55:00Z |
|          19 | 2017-10-23T00:05:00Z |

If you want to include periods where there are no entries then:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( amount, valuedatetime ) AS
  SELECT  1, TIMESTAMP '2017-10-22 23:45:21.234' FROM DUAL UNION ALL
  SELECT  2, TIMESTAMP '2017-10-22 23:47:10.000' FROM DUAL UNION ALL
  SELECT  3, TIMESTAMP '2017-10-22 23:52:41.123' FROM DUAL UNION ALL
  SELECT  4, TIMESTAMP '2017-10-22 23:55:23.456' FROM DUAL UNION ALL
  SELECT  5, TIMESTAMP '2017-10-22 23:58:24.789' FROM DUAL UNION ALL
  SELECT  6, TIMESTAMP '2017-10-22 23:59:59.999' FROM DUAL UNION ALL
  SELECT  7, TIMESTAMP '2017-10-23 00:00:00.000' FROM DUAL UNION ALL
  SELECT  8, TIMESTAMP '2017-10-23 00:01:02.234' FROM DUAL UNION ALL
  SELECT  9, TIMESTAMP '2017-10-23 00:15:00.000' FROM DUAL UNION ALL
  SELECT 10, TIMESTAMP '2017-10-23 00:19:59.999' FROM DUAL;

Query 1:

WITH ranges ( dt ) AS (
  SELECT min_dt + NUMTODSINTERVAL( 5 * ( LEVEL - 1 ), 'MINUTE' )
  FROM   (
    SELECT TRUNC( CAST( MIN( valuedatetime ) AS DATE ) )
           +
           TRUNC(
             ( CAST( MIN( valuedatetime ) AS DATE )
             - TRUNC( MIN( valuedatetime ) ) )
             * 24 * 60 / 5
           ) * 5 / 60 / 24 AS min_dt,
           MAX( valuedatetime ) AS max_dt
    FROM   table_name
  )
  CONNECT BY
         min_dt + INTERVAL '5' MINUTE * ( LEVEL - 1 ) <= max_dt
)
SELECT COALESCE( SUM( amount ), 0 ) AS total_amount,
       --channel_id,
       r.dt AS range_start
FROM   ranges r
       LEFT OUTER JOIN table_name t
       ON ( r.dt               <= t.valuedatetime
           AND t.valuedatetime <  r.dt + INTERVAL '5' MINUTE )
GROUP BY
       --channel_id,
       r.dt
ORDER BY
       --channel_id,
       r.dt

Results:

| TOTAL_AMOUNT |          RANGE_START |
|--------------|----------------------|
|            3 | 2017-10-22T23:45:00Z |
|            3 | 2017-10-22T23:50:00Z |
|           15 | 2017-10-22T23:55:00Z |
|           15 | 2017-10-23T00:00:00Z |
|            0 | 2017-10-23T00:05:00Z |
|            0 | 2017-10-23T00:10:00Z |
|           19 | 2017-10-23T00:15:00Z |