获取行值之间差值的平均值和标准差

Question

给定下表：

CREATE TABLE datapoints
(
  id serial NOT NULL,
  datasource text,
  "timestamp" integer,
  value text,
  CONSTRAINT datapoints_pkey PRIMARY KEY (id)
)

如何计算timestamp1中差从一行到下一行的平均值和标准差？

我的意思是，如果数据看起来像这样：

timestamp
---------
1385565639
1385565641
1385565643

我想计算以下数据的平均值和标准偏差：

timestamp difference
--------------------
0
2
2

这在单个查询中是否可能？

Answer 1

第一个返回差值，第二个返回stddev和avg:

--difference
WITH rn as(
    SELECT timestamp , row_number()over() rown
    FROM datapoints order by timestamp
)
SELECT  ta.rown, tb.rown,tb.timestamp - ta.timestamp 
FROM rn as ta,rn as tb
WHERE ta.rown=tb.rown+1 ;
--avg, stddev
WITH rn as(
    SELECT timestamp , row_number()over() rown
    FROM datapoints 
    ORDER BY timestamp
)
SELECT  stddev(tb.timestamp - ta.timestamp), avg(tb.timestamp - ta.timestamp) 
FROM rn as ta,rn as tb
WHERE ta.rown=tb.rown+1 ; 

Answer 2

除非我误解或过于简化了你的问题这样的东西可能会有所帮助。

select t2.timestamp - t1.timestamp
from
TableName t1
join TableName t2 on 
(
    t1.timestamp < t2.timestamp
    and
    (
    not exists select null from TableName tMid
    where
    tMid.timestamp > t1.timestamp and tMid.timestamp < t2.timestamp
    )
)

我怀疑这是否是最有效的方法，但您提到希望通过一个查询完成。

只是给你一个想法。

如果你的ID是连续的，你可以更简单地进行连接（在t1.ID=t2.ID-1或类似的情况下）。

然后，您还需要了解如何还包括最后/第一个差异（也许你尝试外部联接）。我想我的问题漏掉了那个。

---

没关系，我好像误解了你的问题。

这似乎对你的案子有用。

SQL：显示标准偏差内的平均值和最小值/最大值