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

with
    t1 as (
        select
            ti.product_id,
            in_price,
            price,
            cnt
        from
            tb_product_info ti
            left join tb_order_detail td on ti.product_id = td.product_id
            left join tb_order_overall tl on td.order_id = tl.order_id
        where
            shop_id = '901'
            and date (event_time) >= '2021-10-01'
    ),
    t2 as (
        select
            product_id,
            sum(cnt) as t_cnt,
            sum(t1.price * t1.cnt) as t_m
        from
            t1
        group by
            product_id
    ),
    t3 as (
        select
            concat (
                round(
                    (1 - sum(in_price * cnt) / sum(price * cnt)) * 100,
                    1
                ),
                '%'
            ) as profit_rate
        from
            t1
    ),
    t4 as (
        select distinct
            t1.product_id,
            concat (
                round((1 - t1.in_price * t_cnt / t_m) * 100, 1),
                '%'
            ) as profit_rate,
            2 as num
        from
            t1
            left join t2 on t1.product_id = t2.product_id
        where
            round((1 - t1.in_price * t_cnt / t_m) * 100, 1) > 24.9
        union all
        select
            '店铺汇总' as product_id,
            profit_rate,
            1 as num
        from
            t3
    )
select
    product_id,
    profit_rate
from
    t4
order by
    num,
    product_id