sql - Use last value when current row is null , for PostgreSQL timeseries table

Question

I come across a problem that I could not find an optimal solution. So the idea is to get the price at each given time for a list of products from a list of shops but because the price are registered at different time I get some nulls when grouping by time and also an array o values. Therefore it requires to couple of steps in order to obtain what I need. I am wondering if someone know a better, faster way to achieve this. Bellow is my initial PostgreSQL table of course this is just a snippet of it to get the idea:

Initial Table

Desired results (intermediate table and final one)

And bellow is the PostgreSQL sql code that give the result I want but it seems very costly:

SELECT times,
    first_value(price_yami_egg)  OVER (PARTITION BY partition_price_yami_egg order by time) as price_yami_egg
    first_value(price_yami_salt)  OVER (PARTITION BY partition_price_yami_salt order by time) as price_yami_salt
    first_value(price_dobl_egg)  OVER (PARTITION BY partition_price_dobl_egg order by time) as price_dobl_egg
    first_value(price_dobl_salt)  OVER (PARTITION BY partition_price_dobl_salt order by time) as price_dobl_salt

    FROM(
    SELECT time,
        min(price_yami_egg) as price_yami_egg,
        sum(case when min(price_yami_egg) is not null then 1 end) over (order by times) as partition_price_yami_egg
        min(price_yami_salt) as price_yami_salt,
        sum(case when min(price_yami_salt) is not null then 1 end) over (order by times) as partition_price_yami_salt
        min(price_dobl_egg) as price_dobl_egg,
        sum(case when min(price_dobl_egg) is not null then 1 end) over (order by times) as partition_price_dobl_egg
        min(price_dobl_salt) as price_dobl_salt,
        sum(case when min(price_dobl_salt) is not null then 1 end) over (order by times) as partition_price_dobl_salt
        FROM ( 
            SELECT "time" AS times,
                CASE WHEN  shop_name::text = 'yami'::text AND product_name::text = 'egg'::text THEN price END AS price_yami_egg
                CASE WHEN  shop_name::text = 'yami'::text AND product_name::text = 'salt'::text THEN price END AS price_yami_salt
                CASE WHEN  shop_name::text = 'dobl'::text AND product_name::text = 'egg'::text THEN price END AS price_dobl_egg
                CASE WHEN  shop_name::text = 'dobl'::text AND product_name::text = 'salt'::text THEN price END AS price_dobl_salt

                FROM shop sh
                 ) S

          GROUP BY time
          ORDER BY time) SS

标签： sqlpostgresqlaggregateprice

Answer 1

你只是想要聚合吗？

select time,
       min(price) filter (where shop_name = 'Yami' and product_name = 'EGG'),
       min(price) filter (where shop_name = 'Yami' and product_name = 'SALT'),
       min(price) filter (where shop_name = 'Dobl' and product_name = 'EGG'),
       min(price) filter (where shop_name = 'Dobl' and product_name = 'SALT')
from shop s
group by time;

如果。您关心的是NULL结果中的值，然后您可以填写它们。这有点棘手，但想法是：

with t as (
      select time,
             min(price) filter (where shop_name = 'Yami' and product_name = 'EGG') as yami_egg,
             min(price) filter (where shop_name = 'Yami' and product_name = 'SALT') as yami_salt,
             min(price) filter (where shop_name = 'Dobl' and product_name = 'EGG') as dobl_egg,
             min(price) filter (where shop_name = 'Dobl' and product_name = 'SALT') as dobl_salt
      from shop s
      group by time
     )
select s.*,
       max(yaml_egg) over (yaml_egg_grp) as imputed_yaml_egg,
       max(yaml_salt) over (yaml_egg_grp) as imputed_yaml_salt,
       max(dobl_egg) over (yaml_egg_grp) as imputed_dobl_egg,
       max(dobl_salt) over (yaml_egg_grp) as imputed_dobl_salt
from (select s.*,
             count(yaml_egg) over (order by time) as yaml_egg_grp,

             count(yaml_salt) over (order by time) as yaml_egg_grp,

             count(dobl_egg) over (order by time) as dobl_egg_grp,

             count(dobl_salt) over (order by time) as dobl_salt_grp
      from s
     ) s