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

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

第一个返回差值，第二个返回标准偏差和平均值:

--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 ;