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

首先，利用表连接，将店铺商品信息与用户的订单购买信息结合起来，合并成宽表，提取出我们所需要计算的成本总额，收入总额等，找出所需要的信息作为临时表：

1.找出901号店铺的产品信息，作为左表

2.使用left join 连接产品与订单表中相关的金额与单量数据，就过滤掉了其他店铺的货物信息

with c as 
(
    select product_id
        ,sum(price*cnt) as sum_sold
        ,sum(in_price*cnt) as sum_in
    from
        (
        select a.product_id
              ,a.in_price
              ,a.quantity
              ,t.order_id
              ,t.price
              ,t.cnt
              ,o.total_amount
              ,o.total_cnt
        from
        （select* from tb_product_info where shop_id='901')a #注意限定店铺号
        left join tb_order_detail t 
        on a.product_id = t.product_id
        left join tb_order_overall o on t.order_id = o.order_id
        where date_format(event_time,'%Y-%m') >='2021-10' and o.status = 1
        )b
    group by product_id
    )

接着，将需要计算的所有参数进行计算

1.注意保留小数使用round

2.最终表现用%链接，所以需要计算得数*100后，使用concat与其拼接

select '店铺汇总'as product_id
      ,concat(round((1-sum(sum_in)/sum(sum_sold))*100,1),'%') as profit_rate
from c
    union
select product_id
      ,concat(round((1-sum_in/sum_sold)*100,1),'%') as profit_rate
from c  
where round((1-sum_in/sum_sold)*100,1) > 24.9

最后的脚本表示为：

with c as 
(
    select product_id
        ,sum(price*cnt) as sum_sold
        ,sum(in_price*cnt) as sum_in
    from
        (
        select a.product_id
              ,a.in_price
              ,a.quantity
              ,t.order_id
              ,t.price
              ,t.cnt
              ,o.total_amount
              ,o.total_cnt
        from
        （select* from tb_product_info where shop_id='901')a #注意限定店铺号
        left join tb_order_detail t 
        on a.product_id = t.product_id
        left join tb_order_overall o on t.order_id = o.order_id
        where date_format(event_time,'%Y-%m') >='2021-10' and o.status = 1
        )b
    group by product_id
    )
    select '店铺汇总'as product_id
      ,concat(round((1-sum(sum_in)/sum(sum_sold))*100,1),'%') as profit_rate
from c
    union
select product_id
      ,concat(round((1-sum_in/sum_sold)*100,1),'%') as profit_rate
from c  
where round((1-sum_in/sum_sold)*100,1) > 24.9