Query to calculate the average time between consecutive events

Question

My question is how to write an SQL query to calculate the average time between consecutive events.

I have a small table:

event Name | Time stage 1 | 10:01 stage 2 | 10:03 stage 3 | 10:06 stage 1 | 10:10 stage 2 | 10:15 stage 3 | 10:21 stage 1 | 10:22 stage 2 | 10:23 stage 3 | 10:29 

I want to build a query that receives the average time between stages (i) and stage (i + 1) as a response.

For example, the average time between step 2 and step 3 is 5:

 (3+6+6)/3 = 5 

Answer 1

Aaaaand with a cover of black magic:

 select a.eventName, b.eventName, AVG(DATEDIFF(MINUTE, a.[Time], b.[Time])) as Average from (select *, row_number() over (order by [time]) rn from events) a join (select *, row_number() over (order by [time]) rn from events) b on (a.rn=b.rn-1) group by a.eventName, b.eventName 

This will give you lines like:

 stage3 stage1 2 stage1 stage2 2 stage2 stage3 5 

The first column is the start event, the second column is the end event. If there is Event 3 immediately after Event 1, this will also be indicated. Otherwise, you should indicate some criteria, by which stage it should be, at what stage, therefore the time is calculated only between them.

Added: This should work both on Transact-SQL (MSSQL, Sybase), and on PL / SQL (Oracle, PostgreSQL). However, I have not tested it, and there may still be syntax errors. This will not work on any version of MySQL.

Answer 2

 Select Avg(differ) from ( Select s1.r, s2.r, s2.time - s1.time as differ from ( Select * From (Select rownum as r, inn.time from table inn order by time) s1 Join (Select rownum as r, inn.time from table inn order by time) s2 On mod(s2.r, 3) = 2 and s2.r = s1.r + 1 Where mod(s1.r, 3) = 1) ); 

Parameters may change as the number of stages changes. It is currently established to find the average between steps 1 and 2 of the three-step process.

EDIT a couple of typos

Answer 3

Your table design is wrong. HOw can you tell at what stage1 goes, at which stage2? Without a way to do this, I do not think your request is possible.

Answer 4

The easiest way is to sort by time and use the cursor (tsql) to iterate over the data. Since cursors are evil, it is advisable to get time-ordered data into your application code and repeat it. There are probably other ways to do this in SQL, but they will be very complex and rely on non-standard language extensions.

Answer 5

You do not say what taste of SQL you want to receive. This probably means that you need code in SQL Server (like [sql] common = [sql-server] in using SO tags).

But just in case, if you (or some future crawler) use Oracle, such a query is quite simple with analytic functions, in this case LAG() . Check it:

 SQL> select stage_range 2 , avg(time_diff)/60 as average_time_diff_in_min 3 from 4 ( 5 select event_name 6 , case when event_name = 'stage 2' then 'stage 1 to 2' 7 when event_name = 'stage 3' then 'stage 2 to 3' 8 else '!!!' end as stage_range 9 , stage_secs - lag(stage_secs) 10 over (order by ts, event_name) as time_diff 11 from 12 ( select event_name 13 , ts 14 , to_number(to_char(ts, 'sssss')) as stage_secs 15 from timings ) 16 ) 17 where event_name in ('stage 2','stage 3') 18 group by stage_range 19 / STAGE_RANGE AVERAGE_TIME_DIFF_IN_MIN ------------ ------------------------ stage 1 to 2 2.66666667 stage 2 to 3 5 SQL> 

Changing the format in an internal query is necessary because I saved the TIME column as a DATE data type, so I convert it to seconds to make the math clearer. An alternative solution would be to work with the Day to Second Interval instead. But this solution really matters LAG() .

change

In my answer to this query, I clearly did not calculate the difference between the previous step 3 and the next step 1. This is a requirement question.

Answer 6

try it

  Select Avg(e.Time - s.Time) From Table s Join Table e On e.Time = (Select Min(Time) From Table Where eventname = s.eventname And time > s.Time) And Not Exists (Select * From Table Where eventname = s.eventname And time < s.Time) 

For each record representing the beginning of a step, this sql appends it to the record that represents the end, accepts the difference between the end time and the start time, and averages these differences. "Does not exist" ensures that the intermediate results of the set of start records combined with the end records include only the start records as s ... and the first join condition ensures that only one end record (the same name and the next time value after the start time) joins him ...

To view the intermediate results after the merge, but before the average is accepted, do the following:

  Select s.EventName, s.Time Startime, e.Time EndTime, (e.Time - s.Time) Elapsed From Table s Join Table e On e.Time = (Select Min(Time) From Table Where eventname = s.eventname And time > s.Time) And Not Exists (Select * From Table Where eventname = s.eventname And time < s.Time) 

Answer 7

 WITH q AS ( SELECT 'stage 1' AS eventname, CAST('2009-01-01 10:01:00' AS DATETIME) AS eventtime UNION ALL SELECT 'stage 2' AS eventname, CAST('2009-01-01 10:03:00' AS DATETIME) AS eventtime UNION ALL SELECT 'stage 3' AS eventname, CAST('2009-01-01 10:06:00' AS DATETIME) AS eventtime UNION ALL SELECT 'stage 1' AS eventname, CAST('2009-01-01 10:10:00' AS DATETIME) AS eventtime UNION ALL SELECT 'stage 2' AS eventname, CAST('2009-01-01 10:15:00' AS DATETIME) AS eventtime UNION ALL SELECT 'stage 3' AS eventname, CAST('2009-01-01 10:21:00' AS DATETIME) AS eventtime UNION ALL SELECT 'stage 1' AS eventname, CAST('2009-01-01 10:22:00' AS DATETIME) AS eventtime UNION ALL SELECT 'stage 2' AS eventname, CAST('2009-01-01 10:23:00' AS DATETIME) AS eventtime UNION ALL SELECT 'stage 3' AS eventname, CAST('2009-01-01 10:29:00' AS DATETIME) AS eventtime ) SELECT ( SELECT AVG(DATEDIFF(minute, '2009-01-01', eventtime)) FROM q WHERE eventname = 'stage 3' ) - ( SELECT AVG(DATEDIFF(minute, '2009-01-01', eventtime)) FROM q WHERE eventname = 'stage 2' ) 

It depends on the fact that you always have complete groups of stages, and they always go in the same order (i.e. stage 1 , then stage 2 , then stage 3 )

Answer 8

I can not comment, but I have to agree with HLGEM. Although you can say that with the dataset provided, the OP should be aware that using only one set of steps that exist at a time may be too optimistic.

 event Name | Time stage 1 | 10:01 stage 2 | 10:03 stage 3 | 10:06 stage 1 | 10:10 stage 2 | 10:15 stage 3 | 10:21 stage 1 | 10:22 stage 2 | 10:23 stage 1 | 10:25 --- new stage 1 stage 2 | 10:28 --- new stage 2 stage 3 | 10:29 stage 3 | 10:34 --- new stage 3 

We do not know the environment or what creates the data. It is up to the OP to decide if the table is built correctly.

Oracle will handle this with Google Analytics. as Vilx answer.