主页 / dba / 问题 / 186218
Accepted
Randomize
Asked: 2017-09-19 06:24:21 +0800 CST

使用 Postgres 继承一长串缺失值

  • 772

我有一张这样的桌子：

create table foo (foo_label text, foo_price int, foo_date date);

insert into foo (
          values
          ('aaa', 100,  '2017-01-01'),
          ('aaa', NULL, '2017-02-01'),
          ('aaa', NULL, '2017-03-01'),
          ('aaa', NULL, '2017-04-01'),
          ('aaa', 140,  '2017-05-01'),
          ('aaa', NULL, '2017-06-01'),
          ('aaa', 180,  '2017-07-01')
        );

如您所见，该foo_price列上缺少一些值。

我需要的是缺失值以这种方式填充“以前的”可用值：

 foo_label | fixed_foo_price | foo_date
-----------+-----------------+------------
 aaa       | 100             | 2017-01-01
 aaa       | 100             | 2017-02-01
 aaa       | 100             | 2017-03-01
 aaa       | 100             | 2017-04-01
 aaa       | 140             | 2017-05-01
 aaa       | 140             | 2017-06-01
 aaa       | 180             | 2017-07-01

我的尝试：

select 
    foo_label, 
    (case when foo_price is null then previous_foo_price else foo_price end) as fixed_foo_price,
    foo_date
from (
  select 
      foo_label, 
      lag(foo_price) OVER (PARTITION BY foo_label order by foo_date::date) as previous_foo_price, 
      foo_price,
      foo_date
      from foo
) T;

从这里可以看出：

https://www.db-fiddle.com/#&togetherjs=s6giIonUxT

它并没有完全填满“100”系列。

知道如何获得想要的结果吗？

postgresql count

2 个回答

  1. Best Answer

    我将使用窗口函数组成组，count()然后为每个组取第一个值：

    SELECT foo_label
     , first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS fixed_foo_price
     , foo_date
FROM  (
   SELECT foo_label
        , count(foo_price) OVER (PARTITION BY foo_label ORDER BY foo_date) AS grp
        , foo_price
        , foo_date
   FROM   foo
   ) sub;

    这是有效的，因为count()只计算非空值。因此，所有行NULL最终与具有实际值的最后一行位于同一组中。正是你需要的。

    前导 NULL 值（实际上是“0”组）以NULL. COALESCE如果需要，请添加默认值。例如填写0而不是NULL：

         , COALESCE(first_value(foo_price) OVER (...), 0) AS fixed_foo_price
    • 12

  2. 在 postgresql 演练演示中回填缺失的数据

    创建一个my_money用索引、带空值的数字和一个日期调用的表，然后插入一些行。

    drop table if exists my_money; 
CREATE TABLE my_money ( tick     character varying(10), 
                        cci_val  numeric(5,2), 
                        date_val date); 
insert into my_money values('BTC', 35.3, '2021-10-10'); 
insert into my_money values('BTC', null, '2021-10-9'); 
insert into my_money values('BTC',  9.9, '2021-10-8'); 
insert into my_money values('BTC', null, '2021-10-7'); 
insert into my_money values('BTC', null, '2021-10-6'); 
insert into my_money values('BTC',  3.0, '2021-10-5'); 
insert into my_money values('BTC', null, '2021-10-4'); 
select * from my_money;
┌──────┬─────────┬────────────┐ 
│ tick │ cci_val │  date_val  │ 
├──────┼─────────┼────────────┤ 
│ BTC  │   35.30 │ 2021-10-10 │ 
│ BTC  │       ¤ │ 2021-10-09 │   <-- want 9.90 to fill here.
│ BTC  │    9.90 │ 2021-10-08 │ 
│ BTC  │       ¤ │ 2021-10-07 │   <-- want 3.00 to fill here.
│ BTC  │       ¤ │ 2021-10-06 │   <-- want 3.00 to fill here.
│ BTC  │    3.00 │ 2021-10-05 │ 
│ BTC  │       ¤ │ 2021-10-04 │   <-- want a default begin val to fill here.
└──────┴─────────┴────────────┘

    制作一个临时表来构造我们的回填列，命名为： backfilled_cci_val

    drop table if exists my_money2; 
CREATE TABLE my_money2 as ( 
    SELECT tick, cci_val, 
        first_value(cci_val) OVER (
        PARTITION BY tick, grp ORDER BY date_val) AS backfilled_cci_val, 
        date_val
    FROM ( 
        SELECT tick, 
        count(cci_val) OVER (PARTITION BY tick ORDER BY date_val) AS grp,
        cci_val, date_val
        FROM   my_money where tick = 'BTC'
    ) sub order by date_val desc
);
select * from my_money2;
┌──────┬─────────┬────────────────────┬────────────┐ 
│ tick │ cci_val │ backfilled_cci_val │  date_val  │ 
├──────┼─────────┼────────────────────┼────────────┤ 
│ BTC  │   35.30 │              35.30 │ 2021-10-10 │ 
│ BTC  │       ¤ │               9.90 │ 2021-10-09 │ 
│ BTC  │    9.90 │               9.90 │ 2021-10-08 │ 
│ BTC  │       ¤ │               3.00 │ 2021-10-07 │ 
│ BTC  │       ¤ │               3.00 │ 2021-10-06 │ 
│ BTC  │    3.00 │               3.00 │ 2021-10-05 │ 
│ BTC  │       ¤ │                  ¤ │ 2021-10-04 │  <-- set a default value
└──────┴─────────┴────────────────────┴────────────┘

    到目前为止一切都很好，但我们不能回填第一个空值，因为它没有先验，所以你必须通过更新手动决定默认的开始值：

    update my_money2 set backfilled_cci_val = -1 
where tick = 'BTC' 
and backfilled_cci_val is null; 
select * from my_money2; 
┌──────┬─────────┬────────────────────┬────────────┐ 
│ tick │ cci_val │ backfilled_cci_val │  date_val  │ 
├──────┼─────────┼────────────────────┼────────────┤ 
│ BTC  │   35.30 │              35.30 │ 2021-10-10 │ 
│ BTC  │       ¤ │               9.90 │ 2021-10-09 │ 
│ BTC  │    9.90 │               9.90 │ 2021-10-08 │ 
│ BTC  │       ¤ │               3.00 │ 2021-10-07 │ 
│ BTC  │       ¤ │               3.00 │ 2021-10-06 │ 
│ BTC  │    3.00 │               3.00 │ 2021-10-05 │ 
│ BTC  │       ¤ │                 -1 │ 2021-10-04 │ 
└──────┴─────────┴────────────────────┴────────────┘

    最后一步是将新表中的 backfilled_cci_val 替换回原始表中的 cci_val：

    update my_money set cci_val = backfilled_cci_val 
from my_money2 where my_money.date_val = my_money2.date_val; 
select * from my_money;
┌──────┬─────────┬────────────┐ 
│ tick │ cci_val │  date_val  │ 
├──────┼─────────┼────────────┤ 
│ BTC  │   35.30 │ 2021-10-10 │ 
│ BTC  │    9.90 │ 2021-10-09 │ 
│ BTC  │    9.90 │ 2021-10-08 │ 
│ BTC  │    3.00 │ 2021-10-07 │ 
│ BTC  │    3.00 │ 2021-10-06 │ 
│ BTC  │    3.00 │ 2021-10-05 │ 
│ BTC  │   -1.00 │ 2021-10-04 │ 
└──────┴─────────┴────────────┘

    或者，如果您喜欢危险地生活并一次性就地回填：

    此更新语句与上述所有语句相同，只是它位于现有表上。

    update my_money f1 set cci_val = backfilled_cci_val from (
    SELECT tick, 
    cci_val, 
    first_value(cci_val) OVER 
        (PARTITION BY tick, grp ORDER BY date_val) AS backfilled_cci_val,
    date_val
    FROM (
        SELECT tick,
        count(cci_val) OVER (PARTITION BY tick ORDER BY date_val) AS grp,
        cci_val, date_val
        FROM   my_money where tick = 'BTC'
    ) sub order by date_val desc
) f2 where f1.date_val = f2.date_val and f1.tick = 'BTC' and f1.tick = f2.tick;
update my_money set cci_val = -1 where tick = 'BTC' and cci_val is null;

    这会产生与上述相同的最终结果。

    • 2

相关问题