sql - 删除空值直到第一个值不为空

Question

我的数据集中有公司的每日时间序列，并使用 PostgreSQL。
对于每家公司，应删除第 3 列中具有 NULL 的所有行，直到该公司的该列中的第一个 NOT NULL 条目。然后，所有连续的缺失值都用该公司的最后一个非 NULL 可观察值的值填充。

您可以想象以下示例数据:

        date           company        column3
1 2004-01-01             A               5  
2 2004-01-01             B               NULL
3 2004-01-01             C               NULL  
4 2004-01-02             A               NULL
5 2004-01-02             B               7
6 2004-01-02             C               NULL
7 2004-01-03             A               6
8 2004-01-03             B               7
9 2004-01-03             C               9    
10 2004-01-04            A               NULL
11 2004-01-04            B               NULL
12 2004-01-04            C               NULL

如果我设法编写一个交付的查询，那就太好了

        date           company        column3
1 2004-01-01             A               5  
2 2004-01-02             A               5
3 2004-01-02             B               7
4 2004-01-03             A               6
5 2004-01-03             B               7
6 2004-01-03             C               9
7 2004-01-04             A               6
8 2004-01-04             B               7
9 2004-01-04             C               9

我试过:

SELECT a.date, a.company, COALESCE(a.column3, (SELECT b.column3 FROM mytable b 
WHERE b.company=a.company AND b.colmun3 IS NOT NULL ORDER BY b.company=a.company 
DESC LIMIT 1)) FROM mytable a;

代码有两个问题:

1. 它不会删除所有具有 NULL 值的记录，直到第一个 NOT NULL 值，但是
   填写所有缺失值。
2. ...使用列中的第一个观察值，而不是之前的最后一个观察值
   缺失值。

Answer 1

我建议使用 window functions 的两个子查询级别而不是相关子查询:

SELECT *
FROM  (
   SELECT the_date, company, max(col3) OVER (PARTITION BY company, grp) AS col3
   FROM (
      SELECT *, count(col3) OVER (PARTITION BY company ORDER BY the_date) AS grp
      FROM   tbl
      ) sub1
   ) sub2
WHERE  col3 IS NOT NULL
ORDER  BY the_date, company;

产生请求的结果。

db<> fiddle here
<子>旧sqlfiddle

这假设每个 (company, the_date) 的条目都是唯一的。对于不止几行的表格，应该快得多。一个(unique 强制唯一性？!)索引将对性能有很大帮助:

CREATE INDEX tbl_company_date_idx ON tbl (company, the_date);

如何？

聚合函数 count() 在计数时忽略 NULL 值。用作聚合窗口函数，它根据默认窗口定义计算列的运行计数，即 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这会导致具有 NULL 值的行的计数被“卡住”，从而形成一个共享相同(非空)值的对等组。

在第二个窗口函数中，使用 max() 可以轻松提取每组唯一的非空值。第一个非空值之前的组保留 NULL，这很容易在最后的 SELECT 中消除。

参见:

• Retrieve last known value for each column of a row