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

with t as (
    select product_id, price, in_price,
    case
        when status=1 then cnt
        when status=2 then cnt*-1
        when status=0 then 0
    end as cnt
    from (
    select t1.product_id, price, cnt, status, in_price
    from tb_order_detail t1
    join tb_order_overall t2
    join tb_product_info t3
    on t1.order_id=t2.order_id and t1.product_id=t3.product_id
    where date(event_time)>='2021-10-01' and shop_id=901 
    ) t4
)

(select '店铺汇总' as product_id,
    concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100, 1), '%') as profit_rate
from t)
union
(select product_id,
    concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100, 1), '%') as profit_rate
from t
group by product_id
having (1-sum(in_price*cnt)/sum(price*cnt))>0.249
order by product_id);