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

with product_rate as (

    select product_id, concat(round(profit_rate * 100,1), '%') as profit_rate
    from
    (select t1.product_id, round((1-in_price/average_price), 3) as profit_rate
    from
    (select product_id, sum(price*cnt)/sum(cnt) as average_price
    from tb_order_detail left join tb_order_overall on tb_order_detail.order_id = tb_order_overall.order_id
    where event_time >='2021-10-01'
    group by product_id) as t1 join tb_product_info on t1.product_id = tb_product_info.product_id
    where shop_id = 901
    having profit_rate > 0.249
    order by t1.product_id) as t5
    
), 
store as(
    select product_id, concat(round(profit_rate*100,1), '%') as profit_rate
    from
    (select '店铺汇总' as product_id, round((1-sum(cost)/sum(revenue)),3) as profit_rate
    from
    (select t2.product_id, in_price*cnt as cost, price*cnt as revenue
    from
    (select product_id, price, cnt
    from tb_order_overall right join tb_order_detail on tb_order_overall.order_id = tb_order_detail.order_id
    where event_time >='2021-10-01 ' and status != 2) as t2 left join tb_product_info on t2.product_id = tb_product_info.product_id
    where shop_id = 901
) as t3) as t6
)

select product_id, profit_rate
from store
union
select product_id, profit_rate
from product_rate