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

select 
        distinct 
        '店铺汇总'  as 'product_id',
        concat(round( (sum(t2.profit_all)  over() / sum(t2.price_all)  over())*100,1),'%') `profit_rate` 
    from(
        select
            t1.product_id,
            sum(t1.profit) `profit_all`,
            sum(t1.sum_price)  `price_all`,
            sum(t1.profit)/sum(t1.sum_price) `profit_rate`
        from(
            select 
                tod.product_id,
                tod.price*tod.cnt `sum_price`,
                (tod.price-tpi.in_price)*tod.cnt `profit`
            from tb_order_detail tod
            join tb_product_info tpi
            #过滤出901号店铺数据
            on tod.product_id=tpi.product_id and tpi.shop_id='901'
            join tb_order_overall too
            #过滤出2021年10月以来店铺的数据
            on tod.order_id=too.order_id and date_format(too.event_time,'%Y-%m') >= '2021-10'
        ) t1 
        group by product_id
    )t2
union 
    select 
        t2.product_id 'product',
        concat(round(100*t2.profit_rate,1),'%') `profit_rate`
    from(
        select
            t1.product_id,
            sum(t1.profit) `profit_all`,
            sum(t1.sum_price)  `price_all`,
            sum(t1.profit)/sum(t1.sum_price) `profit_rate`
        from(
            select 
                tod.product_id,
                tod.price*tod.cnt `sum_price`,
                (tod.price-tpi.in_price)*tod.cnt `profit`
            from tb_order_detail tod
            join tb_product_info tpi
            #过滤出901号店铺数据
            on tod.product_id=tpi.product_id and tpi.shop_id='901'
            join tb_order_overall too
            #过滤出2021年10月以来店铺的数据
            on tod.order_id=too.order_id and date_format(too.event_time,'%Y-%m') >= '2021-10'
        ) t1 
        group by product_id
    )t2
   where t2.profit_rate>0.249