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

with
t0 as(
    select
        a.order_id,
        a.product_id,
        c.in_price,
        a.price,
        a.cnt
    from tb_order_detail a
    left join tb_order_overall b on a.order_id=b.order_id
    left join tb_product_info c on a.product_id=c.product_id
    where date_format(b.event_time,'%Y%m')>='202110' and b.status=1 and c.shop_id=901),
t1 as(
    select
        product_id,
        concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
    from t0
    group by product_id
    having round((1-sum(in_price*cnt)/sum(price*cnt))*100,1)>24.9
    order by product_id),
t2 as(
    select
        '店铺汇总' as product_id,
        concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
    from t0)

select t2.* from t2
union
select t1.* from t1