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

with info as
(
SELECT pi.product_id,ov.order_id,in_price,price,cnt
    FROM tb_product_info pi INNER JOIN
    tb_order_detail od ON
    pi.product_id = od.product_id
    INNER JOIN tb_order_overall ov ON
    ov.order_id = od.order_id
    WHERE date(event_time) >= "2021-10-01"
    AND shop_id = 901
)

select '店铺汇总' as product_id,
    concat( round(( 1 - sum(in_price*cnt) / sum(price * cnt) )*100, 1), '%')
from info
union all
select product_id,
    concat( round(( 1 - max(in_price) / (sum(price * cnt)/ sum(cnt)) )*100, 1), '%')
from info
where product_id in
    (
        select product_id
        from info
        group by product_id
        having ( 1 - max(in_price) / (sum(price * cnt)/ sum(cnt)) )*100 > 24.9
    )
GROUP BY product_id