Postgres中的表如下所示:
timestamp | a | b
--------------------+----+---------------------
2016-01-01 00:00:00 | 1 | 10
2016-01-01 00:00:01 | 2 | 11
2016-01-01 00:00:02 | 3 |
2016-01-01 00:00:03 | 4 | 12
2016-01-01 00:00:04 | 5 |
2016-01-01 00:00:05 | 6 |
2016-01-01 00:00:06 | 7 |
2016-01-01 00:00:07 | 8 |
2016-01-01 00:00:08 | 9 |
2016-01-01 00:00:09 | 10 | 13
2016-01-01 00:00:10 | 11 |
2016-01-01 00:00:11 | 12 |
2016-01-01 00:00:12 | 13 | 14
我想在查询中访问几个以前的非空值。假设我有以下伪查询:
SELECT timestamp,
a,
b,
2 * (LAG(b, 1) IGNORE NULLS OVER (ORDER BY timestamp)) +
3 * (LAG(b, 2) IGNORE NULLS OVER (ORDER BY timestamp)) as calc
FROM tbl;
这将获得以下结果的检索:
timestamp | a | b | calc
--------------------+----+-----+---------------
2016-01-01 00:00:00 | 1 | 10 |
2016-01-01 00:00:01 | 2 | 11 |
2016-01-01 00:00:02 | 3 | | 52 (2*11 + 3*10)
2016-01-01 00:00:03 | 4 | 12 | 52 (2*11 + 3*10)
2016-01-01 00:00:04 | 5 | | 57 (2*12 + 3*11)
2016-01-01 00:00:05 | 6 | | 57 (2*12 + 3*11)
2016-01-01 00:00:06 | 7 | | 57 (2*12 + 3*11)
2016-01-01 00:00:07 | 8 | | 57 (2*12 + 3*11)
2016-01-01 00:00:08 | 9 | | 57 (2*12 + 3*11)
2016-01-01 00:00:09 | 10 | 13 | 57 (2*12 + 3*11)
2016-01-01 00:00:10 | 11 | | 62 (2*13 + 3*12)
2016-01-01 00:00:11 | 12 | | 62 (2*13 + 3*12)
2016-01-01 00:00:12 | 13 | 14 | 62 (2*13 + 3*12)
谢谢
横向连接是一种可能性:
select t.*, (t1.b * 2 + t2.b * 3)
from t left join lateral
(select t1.*
from t t1
where t1.b is not null and t1.a < t.a
order by t1.a desc
limit 1
) t1
on true left join lateral
(select t2.*
from t t2
where t2.b is not null and t2.a < t1.a
order by t2.a desc
limit 1
) t2
on true;
这是一个数据库<>小提琴。