sql - 计算按其他字段分组的时间戳之间的平均差异？

Question

我有一个“track_events”表，它以下列格式跟踪到信标(区域)的 ping:

id  region  triggered_at
1234  abc  2016-03-04 21:07:18.817+00
1235  def  2016-03-04 22:04:11.817+00
1236  abc  2016-03-05 01:21:43.817+00
1237  def  2016-03-05 07:44:43.817+00
1238  abc  2016-03-05 11:34:45.817+00
1238  ghi  2016-03-05 14:09:55.817+00
1238  abc  2016-03-06 02:12:10.817+00

我正在尝试构建逻辑以通过确定 [avg.触发器之间的时间差]，然后生成 [avg.时差 btw 触发] < [当前时间 - 上次触发]。我的逻辑是这样的:

1. 使用 LAG 类型 f(x) 生成“prev_triggered_at”字段；
2. 找到“triggered_at”和“prev_triggered_at”字段之间的 AVG 时间差；
3. 生成此平均值 < [当前时间 - 上次 ping] 的区域列表

关于如何编写这个查询有什么建议吗？

Answer 1

我建议在 temptable 上工作，让您的生活更轻松，并按部就类地做任何您需要做的事情。对于每个步骤添加一列以投入计算或从查询中提取。例如prev_triggered_at , AVG,Diff 等

第一步:使用以下脚本:

      select a.id, a.region, a.triggered_at
           , max(b.triggered_at) as  prev_triggered_at
      from track_events a 
      inner join track_events b 
      on a.region=b.region
      and a.triggered_at>b.triggered_at
      group by  a.id,a.region,a.triggered_at

第 2 步:只需将一个 AVG 列添加到 temptable 并使用 avg() 函数计算您想要的平均值。我会提供查询，但我不确定我是否从您提供的信息中得到正确的答案。

第 3 步:按照您的描述选择带有 where 子句的语句，因为您会将所有信息放在同一行。