Блог

Мне нужен запрос, который суммирует все значения за каждый день данной недели и группирует по неделям, включая пустые недели.

Этот запрос группируется по неделям и включает пустые недели, но он не суммирует все дни недели, как ожидалось:

Ожидаемый результат:

 [ 
  ...
  {"week"=>"2019-02-28", "amount_net"=>"0"},
  {"week"=>"2019-03-07", "amount_net"=>"300"}
]  
  

Фактический результат:

 [
  ...
  {"week"=>"2019-02-28", "amount_net"=>"0"},
  {"week"=>"2019-03-07", "amount_net"=>"0"}
]
  

Вот запрос, который я придумал:

 SELECT 
  week, 
  COALESCE (amount_net, 0) as amount_net 
FROM 
  (
    SELECT 
      to_char(
        generate_series(
          timestamp '2018-12-13 22:34:31 UTC', 
          timestamp '2019-03-14', interval '1 week'
        ):: date, 
        'YYYY-MM-DD'
      ) as week
  ) d 
  LEFT JOIN (
    SELECT 
      to_char(
        date_trunc('week', created_at), 
        'YYYY-MM-DD'
      ) AS week, 
      SUM(
        ROUND(
          (
            coalesce(cost_items.base_price, 0) - coalesce(cost_items.base_discount, 0)   coalesce(cost_items.base_fee, 0)   coalesce(cost_items.base_taxes_total, 0)   coalesce(
              cost_items.base_commission_included, 
              0
            ) - coalesce(cost_items.base_voided_price, 0)   coalesce(
              cost_items.base_voided_discount, 
              0
            ) - coalesce(cost_items.base_voided_fee, 0) - coalesce(
              cost_items.base_voided_taxes_total, 
              0
            ) - coalesce(
              cost_items.base_voided_commission_included, 
              0
            )
          ):: numeric, 
          2
        )
      ) as amount_net 
    FROM 
      cost_items 
    WHERE 
      id IN ('0', '1', '2') 
    GROUP BY 
      1
  ) t USING (week) 
ORDER BY 
  week;
  

Как мне настроить этот запрос, чтобы правильно суммировать все значения для каждого дня недели?

Разобрался:

 with host_weeks as (
SELECT 
generate_series(
  timestamp '2018-12-01', 
  timestamp '2019-04-01', interval '1 day'
)::date as host_week )


select date_trunc('week', day)::date as week, sum(amount_net) from
(

select hw.host_week as day,
SUM(
  ROUND(
    (
      coalesce(ci.base_price, 0) - coalesce(ci.base_discount, 0)   coalesce(ci.base_fee, 0)   coalesce(ci.base_taxes_total, 0)   coalesce(
        ci.base_commission_included, 
        0
      ) - coalesce(ci.base_voided_price, 0)   coalesce(
        ci.base_voided_discount, 
        0
      ) - coalesce(ci.base_voided_fee, 0) - coalesce(
        ci.base_voided_taxes_total, 
        0
      ) - coalesce(
        ci.base_voided_commission_included, 
        0
      )
    ):: numeric, 
    2
  )
) as amount_net
from host_weeks hw left join cost_items ci on hw.host_week = ci.created_at::date and ci.id in (....)
group by 1 order by 1) t  group by 1 order by 1;