查找距给定日期/时间戳最近的记录（之前或之后）

user1229681 发表于 Dev

用户名

我想知道如何利用postgresql的时间戳数据类型来计算一些统计信息。例如，假设我要计算每次交易后的盈亏，因此我有一个三列的表tmp：

create table tmp
(date     timestamp,
 customer text,
 price    numeric)

样本视图：

date                 | customer | price 
2014-03-17 18:23:51  | buyer    | 100
2014-03-17 18:14:24  | buyer    | 101   
2014-03-17 18:09:14  | seller   | 102
2014-03-17 18:03:52  | buyer    | 103
2014-03-17 17:57:51  | seller   | 104
2014-03-17 17:52:43  | seller   | 105
2014-03-17 17:52:36  | buyer    | 106
2014-03-17 17:52:35  | seller   | 107
2014-03-17 17:52:35  | buyer    | 108

现在的目标是，根据相关记录之前或之后的相反类型的最接近交易（买或卖），找到买方支付的价格与卖方支付的价格之间的价差。

例如，从顶部看事务5：

2014-03-17 17:57:51  | seller   | 104

客户='买方'紧随其后的记录是00:06:01之后，而客户='买方'紧随其前的记录是00:05:15之前的记录，因此在这里我们要差价104-106 = 2我搜索了周围，却找不到任何提示。谢谢。

编辑：在上面的示例中，它使用之前的价格，但是我试图找到一种通用的解决方案，该解决方案将确定在计算中使用的最接近的价格（之前或之后）。

克洛多尔多·内托

SQL小提琴

select distinct on (t1.date, t1.customer)
    t1.date, t1.customer, t1.price,
    t1.price - t2.price as price_diff,
    abs(extract(epoch from (t1.date - t2.date))) as seconds_diff 
from
    tmp t1
    inner join
    tmp t2 on t1.customer != t2.customer
order by t1.date desc, t1.customer, seconds_diff

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。