Início / dba / Perguntas / 186218
Accepted
Randomize
Asked: 2017-09-19 06:24:21 +0800 CST

Carregue uma longa sequência de valores ausentes com o Postgres

  • 772

Tenho uma tabela assim:

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')
        );

Como você pode ver, alguns valores na foo_pricecoluna estão faltando.

O que eu preciso é que os valores ausentes sejam preenchidos com o valor disponível "anterior" desta maneira:

 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

Minha tentativa:

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;

Como você pode ver daqui:

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

Não preenche completamente a série '100'.

Alguma ideia de como posso obter o resultado desejado?

postgresql count

2 respostas

  1. Best Answer

    Eu formaria grupos com a função window count()e depois pegaria o primeiro valor para cada grupo:

    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;

    Isso funciona porque count()conta apenas valores não nulos. Portanto, todas as linhas com NULLterminam no mesmo grupo que a última linha com um valor real. Exatamente o que você precisa.

    Os valores NULL iniciais (efetivamente o grupo "0") terminam com NULL. Adicione um padrão com COALESCEse desejar. Por exemplo, para preencher em 0vez de NULL:

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

  2. Preencher dados ausentes na demonstração passo a passo do postgresql

    Crie uma tabela chamada my_moneycom um índice, numérico com nulos e uma data e insira algumas linhas.

    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.
└──────┴─────────┴────────────┘

    Faça uma tabela temporária para construir nossa coluna preenchida chamada: 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
└──────┴─────────┴────────────────────┴────────────┘

    Até aí tudo bem, mas não podemos preencher o primeiro nulo, porque ele não tem anterior, então você terá que decidir um valor inicial padrão manualmente com uma atualização:

    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 │ 
└──────┴─────────┴────────────────────┴────────────┘

    O passo final é substituir o backfilled_cci_val da nova tabela de volta para cci_val da tabela original:

    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 │ 
└──────┴─────────┴────────────┘

    Alternativamente, se você gosta de viver perigosamente e fazer backfill de uma só vez e no local:

    Essa instrução de atualização faz o mesmo que todas as anteriores, exceto que ela é implantada na tabela existente.

    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;

    O que produz o mesmo resultado final explicado acima.

    • 2

relate perguntas