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

WITH t0 AS ( SELECT *, price * cnt AS t_price FROM tb_order_detail ),
t1 as (
SELECT
	p.product_id,
	in_price * sum( cnt ) as cb,
	sum( t_price ) as lr,
	concat( round(( 1-in_price * sum( cnt )/ sum( t_price ))* 100, 1 ), '%' ) AS profit_rate 
FROM
	tb_product_info p
	LEFT JOIN t0 ON p.product_id = t0.product_id
	LEFT JOIN tb_order_overall o ON o.order_id = t0.order_id 
WHERE
	shop_id = 901 
	AND event_time >= '2021-10-01' 
	AND STATUS = 1 
GROUP BY
	1,cnt,in_price,t_price
order by 2)
	select '店铺汇总' as product_id,concat(round((1-(sum(cb)/sum(lr)))*100,1),'%') as profit_rate from t1
union  
(SELECT product_id, profit_rate
FROM t1
WHERE CAST(REPLACE(profit_rate, '%', '') AS DECIMAL(5,2)) > 25.0
order by 2)