logo标签列表

计算连续事件之间平均时间的查询

sqlaverage
7
我的问题是如何编写一个SQL查询来计算相继事件之间的平均时间。
我有一个小表格：
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)之间时间的平均值作为答案。

例如，阶段2和阶段3之间的平均时间为5：

(3+6+6)/3 =  5
在LINQ中做起来容易多了... - Hamish Grubijan
1
你正在使用哪种SQL方言？ - ThinkJet
8个回答
15

再加上一点黑魔法：

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，则也会列出。否则，您应提供一些标准来确定哪个阶段跟随哪个阶段，因此时间仅在这些阶段之间计算。

添加：这在Transact-SQL（MSSQL、Sybase）和PL/SQL（Oracle、PostgreSQL）上都可以正常工作。然而，我还没有测试过，可能仍然存在语法错误。这将无法在任何版本的MySQL上运行。

实际上，这个查询也会给出 stage 3 stage 1 150。从需求中并不清楚是否需要这个结果。我假设不需要。 - APC
感谢您对我的解决方案进行评论，然后将其窃取并声称为自己的，甚至没有给我点赞... - David Oneill
@David：Vilx的代码与你的不同（例如，你没有使用group by），呈现更好，解释更清晰。 - Manu
@APC：作者并未指定如何区分哪个事件在哪个事件之后发生，以及什么会重新开始“序列”。我们所知道的是，这可能是期望的情况。 - Vilx-
@David - 请看我在你的解决方案中的第二条评论。 - Vilx-
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)
);

参数可以随着阶段数量的变化而更改。目前设置为从3个阶段过程中找到第1和第2阶段之间的平均值。

编辑了几个拼写错误。

1
注意 - 这是针对PL / SQL方言的。 - Vilx-
1
在我写代码的时候，我没有看到你的解决方案。但是如果你想要点赞 - 给你！ :) - Vilx-
谢谢。我觉得说这话有点小气，但是我已经卡在500以下一段时间了，而且还有几个标签需要修改。 - David Oneill
1

您没有说明需要哪种SQL的答案。这可能意味着您想要在SQL Server中编写代码（因为[sql]通常等同于[sql-server]）。

但是，以防万一您（或某个未来的搜索者）正在使用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()有关。

编辑

在我的查询中，我明确没有计算先前阶段3和后续阶段1之间的差异。这是一个要求问题。

1

你的表设计有问题。怎样才能知道哪个stage1对应哪个stage2呢？如果没有办法做到这一点，我不认为你的查询是可行的。

它是一个按时间顺序排序的序列。 - Manu
2
HLGEM 有一点是正确的。我们必须假定这是一个串行化的过程 - 也就是说，在第三阶段运行时，第一阶段永远不会开始。但在现实生活中，大多数过程都是多线程/多用户的，因此我们需要一个额外的标识符来隔离流。 - APC
1

最简单的方法是按时间排序并使用游标（tsql）迭代数据。由于游标很危险，建议将按时间排序的数据提取到应用程序代码中，并在那里进行迭代。可能有其他在SQL中完成此操作的方法，但它们会非常复杂，并依赖于非标准语言扩展。

0
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）。
你能不能用avg()来简化这个问题，而不是使用sum()？这样你就不需要再除以计数了。 - meriton
0

我无法评论，但我必须同意HLGEM的观点。虽然您可以使用提供的数据集进行判断，但应提醒OP仅依赖于一组阶段存在于同一时间可能过于乐观。


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

我们不知道环境或者是什么在创建数据。这取决于 OP 决定表是否构建正确。

Oracle 会使用类似 Vilx 回答中的分析功能来处理此问题。

0

试一下这个

   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)
我不明白：“and not exists”条件有什么用？它似乎排除了除第一个事件之外的所有事件，并导致代码发出单个值（第一个转换时间）的平均值... - meriton
Not Exists 的作用是确保 SQL 只为每个事件阶段的开始记录输出一行。它过滤掉左连接左侧的 Ending 记录 - 表别名为 "s" - 因为对于这些记录，没有其他记录具有相同的 EventName 和更早的时间。对于 Ending 记录，只有另一个记录（开始记录），因此 Not Exists 过滤掉它。 - Charles Bretana
我不确定这个可行...你怎么得到stage + 1？这似乎是获取阶段之间的时间间隔...例如// 阶段1（B）- 阶段1（A）。我认为问题是如何获得阶段之间的差异（例如// 阶段2 - 阶段1）。 - James
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接