SQL170 某店铺的各商品毛利率及店铺整体毛利率

SELECT product_id,
CONCAT(ROUND(profit_rate*100,1),'%') profit_rate
FROM(
    SELECT tb_product_info.product_id,
    (1-SUM(in_price)/SUM(price)) profit_rate
    FROM tb_order_overall
    LEFT JOIN tb_order_detail ON tb_order_overall.order_id=tb_order_detail.order_id
    LEFT JOIN tb_product_info ON tb_order_detail.product_id=tb_product_info.product_id
    WHERE LEFT(event_time,7)>='2021-10'
    AND shop_id='901' AND status !='2'
    GROUP BY 1
    HAVING (1-SUM(in_price)/SUM(price))>0.249

    UNION ALL

    SELECT "店铺汇总" product_id,
    (1-SUM(in_price*cnt)/SUM(price*cnt)) profit_rate
    FROM tb_order_overall
    LEFT JOIN tb_order_detail ON tb_order_overall.order_id=tb_order_detail.order_id
    LEFT JOIN tb_product_info ON tb_order_detail.product_id=tb_product_info.product_id
    WHERE LEFT(event_time,7)>='2021-10'
    AND shop_id='901' AND status !='2'
    GROUP BY 1
    ) T
ORDER BY product_id IN ('8001','8002','8003')

#1.确定筛选条件：2021年10月以来店铺901中商品毛利率大于24.9%的商品
#2.输出：product_id、profit_rate、店铺汇总
#3.浏览表结构，确定连接方式：LEFT JOIN
#4.WHERE写1.，FROM写3.，SELECT写product_id、profit_rate
#5.按照商品聚合，GROUP BY product_id，加上HAVING 限制profit_rate>0.249
#6.测试运行，不报错则下一步，写店铺汇总
#7.UNION ALL，翻版，没多少变化，product_id按‘店铺汇总’，聚合(区别就是前一段是按商品聚合，这里按汇总聚合，不要HAVING，对汇总没有毛利指标要求)
#8.外层SELECT，加上排序，over