Запрос для вычисления среднего времени между последовательными событиями

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)
);

Параметры могут быть изменены по мере изменения количества ступеней. В настоящее время это установлено, чтобы найти среднее значение между этапами 1 и 2 из трехэтапного процесса.

ИЗМЕНИТЬ пару опечаток

Дизайн вашей таблицы ошибочен. Как вы можете сказать, какой этап 1 соответствует этапу 2? Без возможности сделать это, я не думаю, что ваш запрос возможен.

Самый простой способ - упорядочить по времени и использовать курсор (tsql) для перебора данных. Поскольку курсоры - это зло, рекомендуется извлекать данные, упорядоченные по времени, в код вашего приложения и выполнять итерацию там. Возможно, есть и другие способы сделать это в SQL, но они будут очень сложными и будут основываться на нестандартных расширениях языка.

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'
        )

Это основано на том факте, что у вас всегда есть полные группы этапов, и они всегда идут в одном и том же порядке (то есть, этап 1 , затем этап 2 затем этап 3 )

попробуйте это

   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)

Для каждой записи, представляющей начало стадии, этот sql присоединяет ее к записи, которая представляет конец, берет разницу между временем окончания и временем начала и усредняет эти различия. Not Exists гарантирует, что промежуточный набор результатов начальных записей, соединенных с конечными записями, включает только начальные записи как s ... и первое условие соединения гарантирует, что только одна конечная запись (с тем же именем и следующим значением времени после время начала) присоединяется к нему ...

Чтобы увидеть промежуточный набор результатов после объединения, но до того, как будет взято среднее значение, выполните следующее:

   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)

Аааа и с посыпанием черной магией:

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

Это даст вам строки типа:

stage3  stage1  2
stage1  stage2  2
stage2  stage3  5

Первый столбец - стартовое событие, второй столбец - конечное событие. Если есть событие 3 сразу после события 1, то оно также будет указано в списке. В противном случае вы должны указать некоторые критерии того, на каком этапе следует какой этап, поэтому время рассчитывается только между ними.

Added: Это должно работать нормально как на Transact-SQL (MSSQL, Sybase), так и на PL/SQL (Oracle, PostgreSQL). Однако я не протестировал его, и все еще могут быть синтаксические ошибки. Это НЕ будет работать ни на одной из версий MySQL.

Вы не говорите, за какой вкус SQL вы хотите получить ответ. Скорее всего, это означает, что Вам нужен код в SQL-сервере (как [sql] обычно = [sql-сервер] в использовании SO-тегов).

Но на тот случай, если вы (или какой-нибудь будущий искатель) используете Oracle, такой запрос достаточно прост в использовании с аналитическими функциями, в данном случае LAG(). Зацените:

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>

Изменение формата внутреннего запроса необходимо, так как я сохранил столбец TIME как тип данных DATE, поэтому я преобразовываю его в секунды, чтобы сделать математику более понятной. Альтернативным решением будет работа с типом данных Day to Second Interval. Но на самом деле это решение связано с LAG().

edit

В своем дублете по этому вопросу я явно не вычислил разницу между предыдущим этапом 3 и последующим этапом 1. Это вопрос требований.

Я не могу комментировать, но должен согласиться с HLGEM. Хотя вы можете сказать с помощью предоставленного набора данных, операционная система должна знать, что полагаться только на один набор стадий, существующих в одно время, может быть слишком оптимистичной.

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

Мы не знаем ни окружения, ни того, что создаёт данные. Решать, правильно ли построена таблица, зависит от операционной системы.

Оракул справился бы с этим с помощью Analytics. как ответ Вилкса.