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.

CodePudding user response：

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

CodePudding user response：

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;

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;