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?

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

score 1 · Answer 1 · answered Dec 04 '14 at 15:56

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)

Oracle 10g: howto aggregate by date differences

1 Answers1