게시물 사이의 시간을 결정하는 효과적인 방법?

모든 창 함수 가 FILTER()절을 지원할 수 있다면 (집계 기반 변형처럼) 작업이 훨씬 더 간단해질 수 있습니다 . 즉 모든 것 필요는 다음과 같습니다

-- won't work, unfortunately
first_value(post_time) filter (where thread_type = 's2c')
                         over (partition by ticket_id
                               order by post_time
                               rows between current row and unbounded following)

그때까지는 셀프 조인을 사용할 수 있습니다.

select  t.*, row_number() over (partition by t.ticket_id order by t.c2s_time) rank
from    (select    distinct on (coalesce(s2c.thread_id, c2s.thread_id))
                   c2s.ticket_id,
                   c2s.post_time c2s_time,
                   c2s.message c2s_message,
                   s2c.post_time s2c_time,
                   s2c.message s2c_message,
                   s2c.post_time - c2s.post_time time_taken
         from      ticket_thread c2s
         left join ticket_thread s2c on  c2s.ticket_id = s2c.ticket_id
                                     and s2c.thread_type = 's2c'
                                     and c2s.post_time < s2c.post_time
                                     and not exists(select 1
                                                    from   ticket_thread
                                                    where  post_time > c2s.post_time
                                                    and    post_time < s2c.post_time
                                                    and    ticket_id = c2s.ticket_id
                                                    and    thread_type = 's2c')
         where     c2s.thread_type = 'c2s'
         order by  coalesce(s2c.thread_id, c2s.thread_id), c2s.post_time) t
order by t.ticket_id, t.c2s_time;

또는 array_agg()창 기능으로 놀 수 있습니다 .

select  t.*, row_number() over (partition by t.ticket_id order by t.c2s_time) rank
from    (select    distinct on (coalesce((m).thread_id, (t).thread_id))
                   (t).ticket_id,
                   (t).post_time c2s_time,
                   (t).message c2s_message,
                   (m).post_time s2c_time,
                   (m).message s2c_message,
                   (m).post_time - (t).post_time time_taken
         from      (select t, array_agg(t) filter (where thread_type = 's2c')
                                             over (partition by ticket_id
                                                   order by     post_time
                                                   rows between current row and unbounded following) a
                    from   ticket_thread t) t
         left join lateral  (select   m
                             from     unnest(a) m
                             order by (m).post_time
                             limit    1) m on true
         where     (t).thread_type = 'c2s'
         order by  coalesce((m).thread_id, (t).thread_id), (t).post_time) t
order by t.ticket_id, t.c2s_time;

내 내부 테스트에서 자체 조인 변형이 조금 더 빠르며 .NET에서 인덱스를 사용할 수도 있습니다 (ticket_id, post_time). (그러나 성능이 정말로 중요하다면 둘 다 테스트해야합니다).

또는 누락 된 기능을 추가 할 수도 있습니다 (예 : first_agg집계 생성 및 창 함수로 사용).

create or replace function first_agg_val(anyelement, anyelement)
  returns anyelement
  language sql
  immutable
  strict
  as 'select $1';

create aggregate first_agg(
  sfunc    = first_agg_val,
  basetype = anyelement,
  stype    = anyelement
);

select  t.*, row_number() over (partition by t.ticket_id order by t.c2s_time) rank
from    (select    distinct on (coalesce((s2c).thread_id, (c2s).thread_id))
                   (c2s).ticket_id,
                   (c2s).post_time c2s_time,
                   (c2s).message c2s_message,
                   (s2c).post_time s2c_time,
                   (s2c).message s2c_message,
                   (s2c).post_time - (c2s).post_time time_taken
         from      (select t c2s, first_agg(t) filter (where thread_type = 's2c')
                                                 over (partition by ticket_id
                                                       order by     post_time
                                                       rows between current row and unbounded following) s2c
                    from   ticket_thread t) t
         where     (c2s).thread_type = 'c2s'
         order by  coalesce((s2c).thread_id, (c2s).thread_id), (c2s).post_time) t
order by t.ticket_id, t.c2s_time;

필요하지 않은 경우 rank외부 쿼리를 제거 할 수 있습니다 (순전히를 위해 존재 함 rank). (대신 클라이언트 측에서 일반적으로 계산하기 쉽습니다.)

http://rextester.com/BUY9309

추신 : 내 검색어의 time_taken열은 interval. 해당 열이 마음에 들지 않거나 파싱 할 수없는 경우 다음 수식을 대신 사용하여 초 단위 의 시간 차이를 얻을 수 있습니다 .

extract(epoch from <interval expresssion>)