Query to Calculate Average Time Between Successive Events

Getting Average Time between list of successive dates in TSQL

I don't know which answer is the best for your case. But your question raises an issue I think database developers (and programmers in general) should be more aware of.

Taking an average is easy, but the average is often the wrong measure of central tendency.

transactionid  start_time               end_time                 elapsed_days
--
277            2011-11-19 11:00:00      2011-11-19 11:00:00      0
278            2011-11-19 11:00:00      2012-03-20 15:19:46.16   122
279            2012-03-20 15:19:46.16   2012-03-20 19:23:06.507  0
288            2012-03-20 19:23:06.507  2012-03-20 19:43:41.98   0
289            2012-03-20 19:43:41.98   2012-03-20 19:55:17.523  0
291            2012-03-20 19:55:17.523     

Here's what a histogram of that distribution looks like.

The average of elapsed days is 24.4, but the median is 0. And the median is clearly the better measure of central tendency here. If you had to bet whether the next value would be closer to 0, closer to 24, or closer to 122, smart money would bet on 0.

SQL query to calculate average time duration between different events

As already mentioned this can easily be done using a window function:

select id,  
       test_id,
       start_date, 
       end_date,
       state,
       case 
         when state = 'FAIL' then lag(start_date) over (partition by test_id order by start_date desc) - end_date
         else null
       end as time_diff
from logs
order by test_id, start_date desc;

SQLFiddle: http://sqlfiddle.com/#!15/0d40f/2

Average time between two consecutive events in SQL

The basic idea is lead() to get the time from the next row. Then you need to calculate the difference. So for all rows:

select t.*,
       (to_unix_timestamp(lead(time) over (order by time) -
        to_unix_timestamp(time)
       ) as diff_seconds
from t;

Use a subquery and filtering for just A and the average:

select avg(diff_seconds)
from (select t.*,
             (to_unix_timestamp(lead(time) over (order by time) -
              to_unix_timestamp(time)
             ) as diff_seconds
      from t
     ) t
where event = 'A';

Calculate average days between events?

Here's a solution without using a temporary table:

select daybetweenevents as days_diff,
   count(daybetweenevents) as count
from (select t1.user_id,
    t1.event_time,
    datediff(day, t1.event_time, min(t2.event_time)) as daybetweenevents
    from yourtable t1
    inner join yourtable t2
       on t1.user_id = t2.user_id
    and t1.event_time < t2.event_time
    group by t1.user_id, t1.event_time) temp
group by daybetweenevents

How to calculate the time between two alterning events in SQL

It is difficult to suggest something specific without http://sqlfiddle.com/ example. so I can just give you a strategy.

1. create an INTEGER column with 1/0 to represent Open/Close events.
2. Use SUM(action_int) OVER (see example here Calculate a running total in MySQL) to calculate running total of opens and closes which will give you unique identifier for each full combination.
3. Self join this results based on this identifier filtering right side to Open, left side to Close
4. Sum up the difference between close_date and open_date from previous step to calculate total time door was open.

This should take care of your orphan Open events. You probably will have to fiddle a bit with orphan Close events to exclude them from the dataset.

Here is sql for Postgres, I believe with minor adjustments you can make it work for MySql:

WITH door_events AS (
  SELECT 
    id, 
    door, 
    action, 
    SUM(action) OVER(PARTITION BY door ORDER BY date) AS cycle_id,
    date
  FROM 
      events 
)
SELECT 
  door, 
  SUM(date_close - date_open) door_open 
FROM
(
  SELECT 
   id as open_id,
   door as door,
   date as date_open,
   cycle_id
  FROM 
    door_events
  WHERE action = 1
) AS o LEFT JOIN
(
  SELECT 
    id as close_id,
    door as door_close,
    date as date_close,
    cycle_id
   FROM 
    door_events
   WHERE action = 0
) AS c ON o.cycle_id = c.cycle_id
GROUP BY door

http://sqlfiddle.com/#!17/bafb1/19/0

Calculating the average time between orders for each customer

the key here is the lag analytic function

select cust_id , avg(orderdate - lag_orderdate) as avg_time_between_orders
from (
    select cust_id , orderDate , lag(orderdate) over (partition by cust_id) as lag_orderdate
    from  orders )

---

Related Topics