Here is an example on how to do a an aggregation over a timeline.

timeline_aggregation.sql

CREATE TABLE
  t_intervals (
    begin_time DATE, 
    end_time DATE, 
    category VARCHAR2(5), 
    code VARCHAR2(5), 
    value NUMBER
  );

INSERT INTO t_intervals(begin_time, end_time, category, code, value) VALUES (to_date('08:00', 'HH24:MI'), to_date('08:30', 'HH24:MI'), 'A', '#1', 7);
INSERT INTO t_intervals(begin_time, end_time, category, code, value) VALUES (to_date('08:10', 'HH24:MI'), to_date('08:40', 'HH24:MI'), 'A', '#2', 2);
INSERT INTO t_intervals(begin_time, end_time, category, code, value) VALUES (to_date('08:20', 'HH24:MI'), to_date('08:40', 'HH24:MI'), 'A', '#3', 4);
INSERT INTO t_intervals(begin_time, end_time, category, code, value) VALUES (to_date('08:10', 'HH24:MI'), to_date('08:40', 'HH24:MI'), 'B', '#1', -7);
INSERT INTO t_intervals(begin_time, end_time, category, code, value) VALUES (to_date('08:20', 'HH24:MI'), to_date('08:50', 'HH24:MI'), 'B', '#2', -2);
INSERT INTO t_intervals(begin_time, end_time, category, code, value) VALUES (to_date('08:30', 'HH24:MI'), to_date('08:50', 'HH24:MI'), 'B', '#3', -4);
COMMIT;

-- Here is what we do for the A category:
--
-- 1. T_INTERVALS
--     [------------- 7 -------------[ #1
--   08:00                         08:30
--               [------------- 2 -------------[ #2
--             08:10                         08:40
--                         [-------- 4 --------[ #3
--                       08:20               08:40
--
-- 2. MOMENTS
--   08:00     08:10     08:20     08:30     08:40
--
-- 3. SPLIT_INTERVALS
--     [--- 7 ---[ #1
--   08:00     08:10
--               [--- 7 ---[ #1
--             08:10     08:20
--                         [--- 7 ---[ #1
--                       08:20     08:30
--               [--- 2 ---[ #2
--             08:10     08:20
--                         [--- 2 ---[ #2
--                       08:20     08:30
--                                   [--- 2 ---[ #2
--                                 08:30     08:40
--                         [--- 4 ---[ #3
--                       08:20     08:30
--                                   [--- 4 ---[ #3
--                                    08:30     08:40
--
-- 4. GROUP BY
--     [--- 7 ---[-- 4.5 --[- 4.334 -[--- 3 ---[
--   08:00     08:10     08:20     08:30     08:40
WITH
  moments
AS (
  SELECT
    m.time, 
    LEAD(m.time, +1) OVER (PARTITION BY m.category ORDER BY m.time) next_time, 
    m.category, 
    m.code
  FROM
    (
      (
        SELECT
          i.begin_time time, 
          i.category, 
          i.code
        FROM
          t_intervals i
      ) UNION (
        SELECT
          i.end_time time, 
          i.category, 
          i.code
        FROM
          t_intervals i
      )
    ) m
), 
  split_intervals
AS (
  SELECT
    m.time begin_time, 
    m.next_time end_time, 
    i.category, 
    i.code, 
    i.value
  FROM
    moments m
  JOIN
    t_intervals i
  ON
      m.category = i.category
  AND i.begin_time <= m.time AND m.time < i.end_time
)
SELECT
  s.begin_time, 
  s.end_time, 
  s.category, 
  AVG(s.value) value
FROM
  split_intervals s
GROUP BY
  s.begin_time, 
  s.end_time, 
  s.category
ORDER BY
  s.category,
  s.begin_time, 
  s.end_time;

DROP TABLE t_intervals;