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

-- 思路：分别统计对应指标：商品毛利率和店铺毛利率，然后union

-- 商品毛利率=(1-进价/平均单件售价)*100%

-- 店铺毛利率=(1-总进价成本/总销售收入)*100%

with
    t as (
        select
            p.product_id,
            p.in_price,
            d.price,
            d.cnt
        from
            tb_order_overall o
            left join tb_order_detail d on o.order_id = d.order_id
            left join tb_product_info p on d.product_id = p.product_id
        where
            o.status = 1
            and date (o.event_time) >= '2021-10-01'
            and p.shop_id = 901
    )
select
    '店铺汇总' as product_id,
    concat (
        round(
            (1 - sum(in_price * cnt) / sum(price * cnt)) * 100,
            1
        ),
        '%'
    ) as profit_rate
from
    t
union
select *
from
    (
        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
            round(
                (1 - sum(in_price * cnt) / sum(price * cnt)) * 100,
                1
            ) > 24.9
        order by
            product_id
    ) t1