SQL 平均到达间隔时间、日期之间的时间

发布于 2024-10-22 05:01:19 字数 329 浏览 1 评论 0原文

我有一个带有连续时间戳的表：

2011-03-17 10:31:19

2011-03-17 10:45:49

2011-03-17 10:47:49 ...

我需要找到每个（可能有几十个）之间的平均时间差（以秒为单位）或任何最简单的时间差，我可以从那里开始使用它。例如，上述仅前两次的到达间隔时间将为 870 (14m 30s)。对于所有三个时间，其结果为：(870 + 120)/2 = 445 (7m 25s)。

请注意，我使用的是 postgreSQL 8.1.22 。

编辑：我上面提到的表来自另一个查询，它实际上只是时间戳的一列列表

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

这个俗人 2024-10-29 05:01:19

不确定我完全理解你的问题，但这可能就是你正在寻找的：

SELECT avg(difference)
FROM ( 
  SELECT timestamp_col - lag(timestamp_col) over (order by timestamp_col) as difference
  FROM your_table
) t

内部查询计算每行与前一行之间的距离。结果是表中每行的间隔。

外部查询只是对所有差异进行平均。

Not sure I understood your question completely, but this might be what you are looking for:

SELECT avg(difference)
FROM ( 
  SELECT timestamp_col - lag(timestamp_col) over (order by timestamp_col) as difference
  FROM your_table
) t

The inner query calculates the distance between each row and the preceding row. The result is an interval for each row in the table.

The outer query simply does an average over all differences.

回复收藏 0 原文

烟若柳尘 2024-10-29 05:01:19

我想你想找到 avg(timestamptz)。

我的解决方案是 avg（当前 - 最小值）。但由于结果是区间，所以再次将其添加到最小值。

SELECT  avg(target_col - (select min(target_col) from your_table))
        + (select min(target_col) from your_table)
FROM    your_table

i think u want to find avg(timestamptz).

my solution is avg(current - min value). but since result is interval, so add it to min value again.

SELECT  avg(target_col - (select min(target_col) from your_table))
        + (select min(target_col) from your_table)
FROM    your_table

回复收藏 0 原文

乖乖哒 2024-10-29 05:01:19

如果您无法升级到支持窗口函数的 PG 版本，您
可能会“以缓慢的方式”计算表的连续步骤。

假设您的表是“tbl”并且时间戳列是“ts”：

SELECT AVG(t1 - t0)
  FROM (
        -- All this silliness would be moot if we could use
        -- `` lead(ts) over (order by ts) ''
        SELECT tbl.ts  AS t0,
               next.ts AS t1
          FROM tbl
         CROSS JOIN
               tbl next
         WHERE next.ts = (
                          SELECT MIN(ts)
                            FROM tbl subquery
                           WHERE subquery.ts > tbl.ts
                         )
       ) derived;

但不要这样做。它的表现将会很糟糕。请做什么
a_horse_with_no_name 建议，并且使用窗口函数。

If you cannot upgrade to a version of PG that supports window functions, you
may compute your table's sequential steps "the slow way."

Assuming your table is "tbl" and your timestamp column is "ts":

SELECT AVG(t1 - t0)
  FROM (
        -- All this silliness would be moot if we could use
        -- `` lead(ts) over (order by ts) ''
        SELECT tbl.ts  AS t0,
               next.ts AS t1
          FROM tbl
         CROSS JOIN
               tbl next
         WHERE next.ts = (
                          SELECT MIN(ts)
                            FROM tbl subquery
                           WHERE subquery.ts > tbl.ts
                         )
       ) derived;

But don't do that. Its performance will be terrible. Please do what
a_horse_with_no_name suggests, and use window functions.

回复收藏 0 原文

~没有更多了~