Aggregating multiple rows more than once

Question

I've got a set of data which has an type column, and a created_at time column. I've already got a query which is pulling the relevant data from the database, and this is the data that is returned.

type            | created_at                   | row_num
-----------------------------------------------------
"ordersPage"    | "2015-07-21 11:32:40.568+12" | 1
"getQuote"      | "2015-07-21 15:49:47.072+12" | 2
"completeBrief" | "2015-07-23 01:00:15.341+12" | 3
"sendBrief"     | "2015-07-24 08:59:42.41+12"  | 4
"sendQuote"     | "2015-07-24 18:43:15.967+12" | 5
"acceptQuote"   | "2015-08-03 04:40:20.573+12" | 6

The row number is returned from the standard row number function in postgres

ROW_NUMBER() OVER (ORDER BY created_at ASC) AS row_num

What I want to do is somehow aggregate this data so get a time distance between every event, so the output data might look something like this

type_1          | type_2          | time_distance
--------------------------------------------------------
"ordersPage"    | "getQuote"      | 123423.3423
"getQuote"      | "completeBrief" | 123423.3423
"completeBrief" | "sendBrief"     | 123423.3423
"sendBrief"     | "sendQuote"     | 123423.3423
"sendQuote"     | "acceptQuote"   | 123423.3423

The time distance would be a float in milliseconds, in other queries I've been using something like this to get time differences.

EXTRACT(EPOCH FROM (MAX(events.created_at) - MIN(events.created_at)))

But this time i need it for every pair of events in the sequential order of the row_num so I need the aggregate for (1,2), (2,3), (3,4)...

Any ideas if this is possible? Also doesn't have to be exact, I can deal with duplicates, and with type_1 and type_2 columns returning an existing row in a different order. I just need a way to at least get those values above.

`JOIN` the data returned to itself on `t1.row_num=t2.row_num+1` to get every pair of events in the sequential order. — Serg, May 10 '16 at 07:50

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

What about a self join ? It would look like this :

SELECT 
  t1.type
  , t2.type
  , ABS(t1.created_at - t2.created_at) AS time_diff
FROM your_table t1
INNER JOIN your_table t2
ON t1.row_num = t2.row_num + 1

edited May 23 '17 at 12:31

Community

1
1

answered May 10 '16 at 07:45

Pholochtairze

1,836
1
14
18

score 1 · Answer 2 · edited May 10 '16 at 08:54

1

select type_1,
       type_2,
       created_at_2-created_at_1 as time_distance
from
(select 
type type_1,
lead(type,1) over (order by row_num) type_2,
created_at created_at_1,
lead(created_at,1) over (order by row_num) created_at_2
from table_name) temp
where type_2 is not null

edited May 10 '16 at 08:54

Ezequiel Tolnay

4,302
1
19
28

answered May 10 '16 at 08:04

MNC

11
3

score 1 · Answer 3 · answered May 10 '16 at 08:09

You can use the LAG window function to compare the current value with the previous:

with
  t(type,created_at) as (
    values
      ('ordersPage', '2015-07-21 11:32:40.568+12'::timestamptz),
      ('getQuote', '2015-07-21 15:49:47.072+12'),
      ('completeBrief', '2015-07-23 01:00:15.341+12'),
      ('sendBrief', '2015-07-24 08:59:42.41+12'),
      ('sendQuote', '2015-07-24 18:43:15.967+12'),
      ('acceptQuote', '2015-08-03 04:40:20.573+12'))

select *, EXTRACT(EPOCH FROM created_at - lag(created_at) over (order by created_at))
from t
order by created_at

Aggregating multiple rows more than once

3 Answers3