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

这个题的逻辑还是比较简单，应该都能明白，不再打一堆废话。评论区可能有更简练的写法。

注意一点就可以了：1、注意退单的订单数据，在计算销售的总价格和总数量的时候，要把退单的那个明细表里对应的量要剪掉。

with e as
(
    select
        d.product_id,
        format((1-d.in_price/(d.totalUnitPrice/totalCnts))*100,1) as profit_rate,
        format((1 - sum(d.in_price*d.totalCnts) over() /sum(d.totalUnitPrice) over())*100,1) as shopRate
    from 
    (
        select
            a.product_id,
            max(a.in_price) as in_price,
            sum(case when c.status = 1 then b.price*b.cnt when c.status = 2 then 0-b.price*b.cnt end) as totalUnitPrice,
            sum(case when c.status = 1 then b.cnt when c.status = 2 then 0-b.cnt end) as totalCnts
        from tb_product_info a join tb_order_detail b on a.shop_id='901' and a.product_id = b.product_id 
            join tb_order_overall c on b.order_id = c.order_id and date_format(c.event_time,'%Y-%m-%d') >= '2021-10-01'

        group by a.product_id
    ) d 
)
select
    f.product_id,concat(f.profit_rate,'%') as profit_rate
from 
(
    select
        '店铺汇总' as product_id,max(shopRate) as profit_rate,1 as rk
    from e
    union all
    select
        product_id,profit_rate,2 as rk
    from e where e.profit_rate > 24.9
) f

order by rk,product_id