postgresql - Last Observation Carried Forward (LOCF) 是否在 PostgreSQL 中实现？

Question

在PostgreSQL中是否实现了数据插补方法Last Observation Carried Forward (LOCF)？
如果没有，我该如何实现这个方法？

Answer 1

下面的代码假定一个表 tbl 包含列 a、b(键)、t(时间) 和 v(locf 估算值):

create or replace function locf_s(a float, b float)
returns float
language sql
as '
  select coalesce(b, a)
';

drop aggregate if exists locf(float);
CREATE AGGREGATE locf(FLOAT) (
  SFUNC = locf_s,
  STYPE = FLOAT
);

select a,b,t,v,
    locf(v) over (PARTITION by a,b ORDER by t) as v_locf
from tbl
order by a,b,t
;

( SQLFiddle )

---

教程:"LOCF and Linear Imputation with PostgreSQL"