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

with t1 as(
select p.product_id,
        p.shop_id,
        p.in_price,
        o.event_time,
        o.total_cnt,
        o.total_amount,
        d.order_id,
        d.price,
        d.cnt
from tb_order_detail d
join tb_order_overall o
using(order_id)
join tb_product_info p
using(product_id)
where shop_id = 901
and status = 1
and event_time >= '2021-10-01'),
t2 as(
select distinct product_id,
       in_price * sum(cnt) over(partition by product_id) as in_total_amount,
       sum(price * cnt) over(partition by product_id) as sale_total_amount
from t1)


(select '店铺汇总' as product_id,
        concat(round(round((1 - sum(in_total_amount) / 
                            sum(sale_total_amount)) * 100, 3), 1), '%') as profit_rate
from t2)
union
(select product_id,
        concat(round(round((1 - in_total_amount / sale_total_amount) * 100, 3), 1), '%') as profit_rate
from t2
where round(round((1 - in_total_amount / sale_total_amount) * 100, 3), 1) > 24.9
order by product_id)