sql - 通过填充前移/LOCF 在 SQL 中的一系列连续行上插补一列？

Question

在为时间序列分析格式化数据时，一个常见的需求是通过随时间填充前向值来估算缺失值(也称为 Last-Observation-Carried-Forward/LOCF )。

虽然数据分析环境通常提供该功能(例如 Pandas fillna())，但对于更大的数据集，在 SQL 中计算它会更有效(例如，利用数据并行数据仓库设备)。

例如，考虑:

    | UNIT | TIME | VALUE |
    |------|------|-------|
    | 1    | 1    | NULL  |
    | 1    | 2    | .5    |
    | 1    | 3    | NULL  |
    | 1    | 4    | NULL  |
    | 1    | 5    | .2    |
    | 1    | 6    | NULL  |
    | 2    | 1    | .6    |
    | 2    | 2    | NULL  |

其中，在 VALUE 列上填充 TIME(独立于每个 UNIT)后产生:

    | UNIT | TIME | VALUE |
    |------|------|-------|
    | 1    | 1    | NULL  |
    | 1    | 2    | .5    |
    | 1    | 3    | .5    |
    | 1    | 4    | .5    |
    | 1    | 5    | .2    |
    | 1    | 6    | .2    |
    | 2    | 1    | .6    |
    | 2    | 2    | .6    |

(注意 UNIT 1 的初始 NULL 不能估算，因为没有先验值)

时间也可以是时间戳或日期时间类型的列。

Answer 1

对于某些数据库，例如 Postgres，您可以定义自己的聚合函数。
LOCF 只是一个运行中的 COALESCE。

CREATE OR REPLACE FUNCTION locf_state( FLOAT, FLOAT )
RETURNS FLOAT
LANGUAGE SQL
AS $f$
  SELECT COALESCE($2,$1)
$f$;

CREATE AGGREGATE locf(FLOAT) (
  SFUNC = locf_state,
  STYPE = FLOAT
);

然后查询更具可读性:

SELECT unit, time, 
       locf(value) OVER( PARTITION BY unit ORDER BY time )
FROM   mytable;

SQLFiddle: http://sqlfiddle.com/#!15/2c73b/1/0