Natknąłem się na problem, którego nie mogłem znaleźć optymalnego rozwiązania. Chodzi o to, aby uzyskać cenę w każdym czasie na liście produktów z listy sklepów, ale ponieważ cena jest zarejestrowana w innym czasie, otrzymuję kilka null podczas grupowania według czasu, a także wartości o tablicy. Dlatego wymaga kilku kroków w celu uzyskania tego, czego potrzebuję. Zastanawiam się, czy ktoś zna lepszy, szybszy sposób, aby to osiągnąć. Oczywiście jest mój początkową tabelę PostgreSQL, jest to tylko fragment tego, aby uzyskać pomysł:

Początkowy tabelę

Initial table

Pożądane wyniki (tabela pośrednia i ostatnia) Ważne wyniki

A poniżej jest kodem PostgreSQL SQL, który podaje wynik, ale wydaje się bardzo kosztowne:

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
0
Darko D.r 28 październik 2020, 17:40

1 odpowiedź

Najlepsza odpowiedź

Czy chcesz tylko agregacji?

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;

Gdyby. Twoim zainteresowaniem jest wartości NULL w wyniku, a następnie można je wypełnić. To jest trochę trudne, ale pomysł jest:

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

           
1
Gordon Linoff 28 październik 2020, 14:51