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

# 指标：商品毛利率=(1-进价总额/销售总额)*100%
#      店铺毛利率=(1-总进价成本/总销售收入)*100%。
# 方法1：union
# 1.商品毛利率
# select tpi.product_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%')
# from tb_product_info tpi
# inner join tb_order_detail tod
# on tpi.product_id=tod.product_id
# inner join tb_order_overall too
# on tod.order_id=too.order_id
# where shop_id='901'
# and date(event_time) >= "2021-10-01"
# group by product_id
# # 2.店铺毛利率
# select '商铺汇总' as product_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%')
# from tb_product_info tpi
# inner join tb_order_detail tod
# on tpi.product_id=tod.product_id
# inner join tb_order_overall too
# on tod.order_id=too.order_id
# where shop_id='901'
# and date(event_time) >= "2021-10-01"
# 3.整合
# select '店铺汇总' as product_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%')
# from tb_product_info tpi
# inner join tb_order_detail tod
# on tpi.product_id=tod.product_id
# inner join tb_order_overall too
# on tod.order_id=too.order_id
# where shop_id='901'
# and date(event_time) >= "2021-10-01"
# union
# select tpi.product_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%')
# from tb_product_info tpi
# inner join tb_order_detail tod
# on tpi.product_id=tod.product_id
# inner join tb_order_overall too
# on tod.order_id=too.order_id
# where shop_id='901'
# and date(event_time) >= "2021-10-01"
# group by product_id
# having 1-sum(in_price*cnt)/sum(price*cnt)>0.249
# 方法2：ifnull+ with rollup
SELECT product_id, CONCAT(profit_rate, "%") as profit_rate
FROM (
    SELECT IFNULL(product_id, '店铺汇总') as product_id,
        ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) as profit_rate
    FROM (
        SELECT product_id, price, cnt, in_price
        FROM tb_order_detail
        JOIN tb_product_info USING(product_id)
        JOIN tb_order_overall USING(order_id)
        WHERE shop_id = 901 and DATE(event_time) >= "2021-10-01"
    ) as t_product_in_each_order
    GROUP BY product_id
    WITH ROLLUP
    HAVING profit_rate > 24.9 OR product_id IS NULL
    ORDER BY product_id
) as t1;