>source

최적의 솔루션을 찾을 수없는 문제를 발견했습니다. 따라서 아이디어는 상점 목록에서 제품 목록에 대해 주어진 시간에 가격을 가져 오는 것이지만 가격이 다른 시간에 등록되기 때문에 시간별로 그룹화 할 때 일부 null과 배열 o 값을 얻습니다. 따라서 필요한 것을 얻으려면 몇 단계가 필요합니다. 누군가가 이것을 달성하는 더 빠르고 더 나은 방법을 알고 있는지 궁금합니다. Bellow는 내 초기 PostgreSQL 테이블입니다. 물론 이것은 아이디어를 얻기위한 스니 펫일뿐입니다.

초기 테이블

원하는 결과 (중간 표 및 최종 표)

그리고 다음은 내가 원하는 결과를 제공하는 PostgreSQL SQL 코드이지만 비용이 많이 드는 것 같습니다.

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


  • 답변 # 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
              
    
    

관련 자료

  • 이전 android - robolectric - 패키지 targetsdkversion = 30> maxsdkversion = 29
  • 다음 숫자 길이 정규식 만 계산하는 방법