从oracle 12c，您可以 UNION ALL 连接两个表，然后使用 MATCH_RECOGNIZE :

SELECT *
FROM   (
  SELECT 'c' AS type, id, time, status FROM communications
UNION ALL
  SELECT 'r' AS type, id, time, status FROM responses
)
MATCH_RECOGNIZE(
  PARTITION BY id
  ORDER     BY time
  MEASURES
    new_comms.time AS new_time,
    realized.time AS realized_time
  ONE ROW PER MATCH
  PATTERN ( new_comms other_comms* realized )
  DEFINE
    new_comms AS (type, status) IN (('c', 'new')),
    other_comms AS (type, status) NOT IN (('c', 'new'),('r', 'realized')),
    realized AS (type, status) IN (('r', 'realized'))
)

注意：这将不允许重叠通信。
或者，在早期版本中，您可以使用 LEAD 用一个 CASE 表达式：

SELECT id,
       time AS new_time,
       realized_time
FROM   (
  SELECT t.*,
         LEAD(
           CASE WHEN type = 'r' AND status = 'realized' THEN time END
         ) IGNORE NULLS OVER (
           PARTITION BY id
           ORDER BY time
         ) AS realized_time
  FROM   (
    SELECT 'c' AS type, id, time, status FROM communications
  UNION ALL
    SELECT 'r' AS type, id, time, status FROM responses
  ) t
)
WHERE  type = 'c'
AND    status = 'new'

注意：这将允许重叠通信（但两个 new 行可能会匹配相同的 realized 行）。
对于样本数据：

CREATE TABLE communications ( ID, TIME, STATUS ) AS
SELECT 636, DATE '2021-03-09' + INTERVAL '08:48:49' HOUR TO SECOND, 'new' FROM DUAL UNION ALL
SELECT 636, DATE '2021-03-10' + INTERVAL '10:47:31' HOUR TO SECOND, 'accepted' FROM DUAL UNION ALL
SELECT 636, DATE '2021-03-12' + INTERVAL '08:41:42' HOUR TO SECOND, 'new' FROM DUAL UNION ALL
SELECT 636, DATE '2021-03-12' + INTERVAL '09:43:49' HOUR TO SECOND, 'accepted' FROM DUAL;

CREATE TABLE responses( ID, TIME, STATUS ) AS
SELECT 636, DATE '2021-03-10' + INTERVAL '09:30:49' HOUR TO SECOND, 'displayed' FROM DUAL UNION ALL
SELECT 636, DATE '2021-03-11' + INTERVAL '07:48:49' HOUR TO SECOND, 'realized' FROM DUAL UNION ALL
SELECT 636, DATE '2021-03-12' + INTERVAL '10:28:49' HOUR TO SECOND, 'displayed' FROM DUAL UNION ALL
SELECT 636, DATE '2021-03-17' + INTERVAL '11:19:49' HOUR TO SECOND, 'realized' FROM DUAL;

输出：
idnew\u time63021-03-09 08:48:492021-03-11 07:48:4963021-03-12 08:41:422021-03-17 11:19:49
db<>在这里摆弄

似乎您只需要每个已实现响应的最大通信时间。单向：

select
  r.communication_id,
  r.response_time,
  max(c.communication_time)
from responses r
join communications c
  on c.communication_id = r.communication_id
  and c.communication_time < r.response_time 
where r.response_status = 'realized'
group by r.communication_id, r.response_time
order by r.communication_id, r.response_time;

另一个：

select
  r.communication_id,
  r.response_time,
  last_c.communication_time
from responses r
cross apply
(
  select communication_time
  from communications c
  where c.communication_id = r.communication_id
  and c.communication_time < r.response_time 
  order by c.communication_time desc
  fetch first row only
) last_c
where r.response_status = 'realized'
order by r.communication_id, r.response_time;