CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.5
Taras Danylchenko
Taras Danylchenko

Reputation: 347

How to get average time per status

I have a history table(without primary_key) like this

id status     change_time (timestamp as bigint)
1  FINISH      165895464
1  PENDING     165695464
1  NEW         165495464
2  PENDING     166695464
2  NEW         166495464
3  NEW         165695464

My main goal is to get the average time per status change divided by the distinct number of ids.

Upvotes: 0

Views: 301

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21115

Well, write SQL as you are formulating.

The first CTE is only the data setup.

The second subquery calulates the time in an event by subtracting the time of the next state for the same ID. Note that the last event cant be calculated.

Finaly make your formula.

with dt as (
select * from ( 
values
(1,  'FINISH',      165895464),
(1,  'PENDING',     165695464),
(1,  'NEW',         165495464),
(2,  'PENDING',     166695464),
(2,  'NEW',         166495464),
(3,  'NEW',         165695464)
) as tab (id, status, change_time)),
dt2 as (
select 
  id, status, 
  lead(change_time) over (partition by id order by change_time) - change_time time_diff
from dt)
select avg(time_diff) / count(distinct id) as "average time per status change divided by the distinct number of ids"
from dt2

The first query with your sample data returns

id          status     time_diff   
----------- ---------- ----------- 
1           NEW        200000      
1           PENDING    200000      
1           FINISH                 
2           NEW        200000      
2           PENDING                
3           NEW

So the AVG time in status is 200000.

Divided through 3 (distinct id count) you get 66666.66

Upvotes: 1

Stefanov.sm
Stefanov.sm

Reputation: 13059

Should be something like ths. avg_weighted_time is an "interval" in the same units as change_time.

select id, 
       nullif(max(change_time) - min(change_time), 0) / (count(*) - 1)
       / (select count(distinct id) from the_table) as avg_weighted_time
from the_table
group by id order by id;
id avg_weighted_time
1 66666
2 66666
3 [null]

If you need a single average for all id-s then

select sum(tp) / count(distinct id) 
from (
 select id,
   change_time - lag(change_time) over (partition by id order by change_time) tp 
 from the_table
) t;

Upvotes: 0

Related Questions