Oracle 10g: howto aggregate by date differences

Question

hi have te following data:

KEY VALUE TIMESTAMP
-------------- ---------- -----------------------
0F8CE962 900 20141124054503
0F8CE962 900 20141124082431
0F8CE962 0 20141124083808
0F8CE962 0 20141124104408
0F8CE962 0 20141124105009
0F8CE962 0 20141124110213
0F8CE962 900 20141124110720
0F8CE962 900 20141125051641
0F8CE962 0 20141125054112

every row is "distant" from the next about 15 minutes, except for the last two. if I execute:

select KEY, sum(VALUE), min(TIMESTAMP), max(TIMESTAMP)
from myTable
group by KEY

I get (of course)

KEY sum(VALUE) min(TIMESTAMP) max(TIMESTAMP)
-------------- ---------- ----------------------- -----------------------
0F8CE962 3600 20141124054503 20141125054112

What I need is to aggregate only that rows that differs, once ordered, max 15 minutes. This is what I'd like:

select KEY, sum(VALUE), min(TIMESTAMP), max(TIMESTAMP)
from myTable
group by KEY
some_magic_function(max(15 minutes))
KEY sum(VALUE) min(TIMESTAMP) max(TIMESTAMP)
-------------- ---------- ----------------------- -----------------------
0F8CE962 2700 20141124054503 20141124110720
0F8CE962 900 20141125051641 20141125054112

Is it possible?

• Multisync: What's the format of TIMESTAMP? If it's YYYYMMDDHH24MISS then 05:45:03 < 11:07:20 by more than 15 minutes
• morbidflame: Yes it's YYYYMMDDHH24miss. Please note that last two rows are 11/25, the previous are 11/24 :-)

Answer 1

You can use the "start of group" method as demonstrated elsewhere. As applied to your example and assuming timestamp is, or can be converted to, a numeric value:

with mytable1 as
 (select mytable.*
 , case
 when lag(timestamp, 1, timestamp-150001) over
 (partition by key order by timestamp) < timestamp-150000
 then 1
 else 0
 end start_of_group
 from mytable)
, mytable2 as
 (select mytable1.*
 , sum(start_of_group) over (partition by key order by timestamp) grp
 from mytable1)
select key
 , sum(value)
 , min(timestamp)
 , max(timestamp)
from mytable2
group by key
 , grp
order by key
 , min(timestamp)