题解 | #某店铺的各商品毛利率及店铺整体毛利率#

select
  "店铺汇总" product_id,
  concat(
    round(
      100 * round(
        1 - (sum(tpi.in_price * tod.cnt) / sum(tod.price * tod.cnt)),
        3
      ),
      1
    ),
    "%"
  ) profit_rate
from
  tb_product_info tpi
  inner join tb_order_detail tod on tpi.product_id = tod.product_id
  inner join tb_order_overall too on too.order_id = tod.order_id
where
  date_format(too.event_time, "%Y-%m") >= "2021-10"
  and tpi.shop_id = 901
group by
  tpi.shop_id
union all
(
  select
    tpi.product_id,
    concat(
      round(
        100 * round(
          1 - (
            sum(tpi.in_price) * sum(tod.cnt) / (sum(tod.price) * sum(tod.cnt))
          ),
          3
        ),
        1
      ),
      "%"
    ) profit_rate
  from
    tb_product_info tpi
    inner join tb_order_detail tod on tpi.product_id = tod.product_id
    inner join tb_order_overall too on too.order_id = tod.order_id
  where
    date_format(too.event_time, "%Y-%m") >= "2021-10"
    and tpi.shop_id = 901
  group by
    tpi.product_id
  having
    round(1 - (avg(tpi.in_price) / avg(tod.price)), 3) > 0.249
  order by
    product_id
)