根据条件 PostgreSQL 查找以前的值

Question

id    account   amount  
123     abc      10        
1234    abc      12        
4568    abc      16        
456     def      20        
458     def      30         

我想过滤差异(金额(小于 5 的唯一 ID(按帐户分组(。这里的答案是这样的：

id:
123
1234
4568 

Answer 1

您可以将LEAD和LAG结合起来以获得所需的结果。

SELECT id
FROM (
SELECT t.*
,amount - LAG(amount, 1, 0) OVER (
PARTITION BY account ORDER BY amount
) AS diff1
,amount - LEAD(amount, 1, 0) OVER (
PARTITION BY account ORDER BY amount
) AS diff2
FROM t
) s
WHERE abs(diff1) < 5
OR abs(diff2) < 5;

演示