SQL for grouping/compressing by time span for a report dynamically

How can I compress / aggregate / group a table with events dynamically over time. I have a table with values and time of occurrence.

Something like this:

value_col   time_col
3         | 2011-02-16 22:21:05.250
2         | 2011-02-16 21:21:06.170
15        | 2011-02-16 21:21:05.250

I need to aggregate the val开发者_运维百科ues by a given time span (e.g. hourly) starting from the first row (latest event). So in this example I want to end up with two rows for hourly aggregation.

5
15

So if a new value comes in:

value_col   time_col
6         | 2011-02-16 23:21:05.247
3         | 2011-02-16 22:21:05.250
2         | 2011-02-16 21:21:06.170
15        | 2011-02-16 21:21:05.250

If I would run that query again I want to end up with:

9
17

It should be easy to change the time span in the query. For example compress over the last 30 seconds, past 6 hours, past 24 hours , etc.. How can I do that in oracle and MS SQL?

Thanks to the previous answers I got the idea on how to fulfill all the requirements.

For each record I calculate the time difference to the latest record in milliseconds (or seconds, depending on resolution). I then modulo the difference with the time span that I am currently interested in (e.g. 3600 sec = 1 h). Then I add that value to the time_col of the same record and group over that.

Create table:

CREATE TABLE [dbo].[test_table](
    [value_col] [int] NOT NULL,
    [time_col] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test_table] ([value_col], [time_col]) VALUES (3, CAST(0x00009E8C01705737 AS DateTime))
INSERT [dbo].[test_table] ([value_col], [time_col]) VALUES (2, CAST(0x00009E8C015FDD8B AS DateTime))
INSERT [dbo].[test_table] ([value_col], [time_col]) VALUES (15, CAST(0x00009E8C015FDC77 AS DateTime))
INSERT [dbo].[test_table] ([value_col], [time_col]) VALUES (6, CAST(0x00009E8C0180D1F6 AS DateTime))

Solution for SQL:

SELECT SUM(value_col) AS s_val, aggregation_time FROM 
 (SELECT value_col, time_col, 
  DATEADD(millisecond,DATEDIFF(millisecond,time_col,(SELECT MAX(time_col) 
  FROM test_table)) % (3600 * 1000), time_col) AS aggregation_time 
 FROM test_table)
GROUP BY aggregation_time
ORDER BY aggregation_time DESC

Solution for Oracle:

SELECT SUM(value_col) as s_val, aggregation_time FROM
 (SELECT value_col, time_col + 
  (MOD(ROUND(((CAST((SELECT MAX(time_col) FROM test_table) AS DATE ) - 
  CAST(time_col AS DATE ))*86400),0),3600))/86400 as aggregation_time
  FROM test_table l)     
GROUP BY aggregation_time
ORDER BY aggregation_time DESC

If I want to aggregate over the last 2 h I just change 3600 to 7200 seconds.

The result is:

9   2011-02-16 23:21:05.247
17  2011-02-16 22:21:05.247

 a              b
3  | 2011-02-16 23:21:05.250
2  | 2011-02-16 22:21:05.267
15 | 2011-02-16 22:21:05.155

with tmp as (
  select a, to_char(b, 'YYYYMMDDHH24') h from tab
)
select sum(a), h from tmp group by h
/

Here's an example how to aggregate hourly:

SELECT TO_CHAR(TRUNC(a.created, 'HH24'), 'DD.MM.YYYY HH24:MI'), COUNT(*)
FROM all_objects a
GROUP BY TRUNC(a.created, 'HH24');

This gives you the number of objects from all_objects aggregated hourly by their creation time. The key is TRUNC(column, 'HH24') which aggregates your data hourly.

In your case, something like this:

create table t (i int, d date);
insert into t values (3, to_date('2011-02-16 22:21:05', 'YYYY-MM-DD HH24:MI:SS'));
insert into t values (2, to_date('2011-02-16 21:21:05', 'YYYY-MM-DD HH24:MI:SS'));
insert into t values (15, to_date('2011-02-16 21:21:05', 'YYYY-MM-DD HH24:MI:SS'));
commit;
select sum(i), TO_CHAR(TRUNC(t.d, 'HH24'), 'DD.MM.YYYY HH24:MI') from t group by TRUNC(t.d, 'HH24');

Here is an Oracle variant, using only one table access.

SQL> create table t (value,mydate)
  2  as
  3  select 3, to_timestamp('2011-02-16 22:21:05.250','yyyy-mm-dd hh24:mi:ss.ff3') from dual union all
  4  select 2, to_timestamp('2011-02-16 21:21:05.267','yyyy-mm-dd hh24:mi:ss.ff3') from dual union all
  5  select 15, to_timestamp('2011-02-16 21:21:05.155','yyyy-mm-dd hh24:mi:ss.ff3') from dual
  6  /

Table created.

The next query groups by difference in hours, counted from the most recent timestamp, which seems to be what you want:

SQL> select sum(value)
  2    from ( select extract(hour from (max(mydate) over () - mydate)) difference_in_hours
  3                , value
  4             from t
  5         )
  6   group by difference_in_hours
  7   order by difference_in_hours
  8  /

SUM(VALUE)
----------
         5
        15

2 rows selected.

But apparently your example is not accurate, because when I add the fourth row from your example, the 15 value is more than two hours away from the most recent timestamp, which leads to an extra group:

SQL> insert into t values (6,to_timestamp('2011-02-16 23:21:05.249','yyyy-mm-dd hh24:mi:ss.ff3'))
  2  /

1 row created.

SQL> select sum(value)
  2    from ( select extract(hour from (max(mydate) over () - mydate)) difference_in_hours
  3                , value
  4             from t
  5         )
  6   group by difference_in_hours
  7   order by difference_in_hours
  8  /

SUM(VALUE)
----------
         9
         2
        15

3 rows selected.

So did I misinterpret your requirement or do you have a mistake in your example?

Regards,
Rob.

For SQLServer you will have something like

SELECT DATEDIFF(hour,b.date_time_col,a.dt), SUM(b.id)
FROM (SELECT MAX(date_time_col) as dt FROM table1)a,  
table1 b
GROUP BY DATEDIFF(hour,b.date_time_col,a.dt)

Oracle doesn't have DATE_DIFF, equivalent will be TRUNC(24*(a.dt-b.date_time_col))