查询以计算连续事件之间的平均时间 [英] Query to calculate average time between successive events

问题描述

我的问题是关于如何编写SQL查询来计算连续事件之间的平均时间.

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

我有一张小桌子:

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)和阶段(i + 1)之间平均时间的答案.

I want to build a query that get as an answer the average of the times between stage(i) and stage(i+1).

例如， 第2阶段到第3阶段的平均时间为5:

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

(3+6+6)/3 =  5

推荐答案

Aaaa并撒上一些黑魔法:

Aaaaand with a sprinkle 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

这将为您提供以下行:

stage3  stage1  2
stage1  stage2  2
stage2  stage3  5

第一列是开始事件，第二列是结束事件.如果事件1之后紧接事件3，则也会列出该事件.否则，您应该提供有关哪个阶段跟随哪个阶段的标准，因此时间仅在两个阶段之间计算.

The first column is the starting event, the second column is the ending event. If there is Event 3 right after Event 1, that will be listed as well. Otherwise you should provide some criteria as to which stage follows which stage, so the times are calculated only between those.

已添加:这在Transact-SQL(MSSQL，Sybase)和PL/SQL(Oracle，PostgreSQL)上都可以正常运行.但是我还没有测试过，仍然可能存在语法错误.这不适用于任何版本的MySQL.

Added: This should work OK on both Transact-SQL (MSSQL, Sybase) and PL/SQL (Oracle, PostgreSQL). However I haven't tested it and there could still be syntax errors. This will NOT work on any edition of MySQL.

这篇关于查询以计算连续事件之间的平均时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！