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

# 题目：计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。
# 要求：结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。
# 1、计算901店铺整体毛利率
# 店铺毛利率=(1-总进价成本/总销售收入)*100%
with t as (
        select 
            a.product_id,
            shop_id,
            tag,
            in_price,
            quantity,
            b.order_id,
            uid,
            event_time,
            total_amount,
            total_cnt,
            status,
            price,
            cnt
        from 
            tb_product_info a
        left join 
            tb_order_detail c
        on a.product_id = c.product_id
        left join
            tb_order_overall b 
        on c.order_id = b.order_id
        where 
            shop_id = 901 
            and date(event_time) >= '2021-10-01'
            and status in(1)
)

# select
#     '店铺汇总' AS product_id,
#     concat(format((1-(sum(in_price*cnt)/sum(price*cnt)))*100, 1), '%') as profit_rate
# from 
#     t


# 2、计算901店铺中商品毛利率大于24.9%的商品信息
# 商品毛利率=(1-进价/平均单件售价)*100%

# select
#     product_id,
#     concat(format((1-(sum(in_price*cnt)/sum(price*cnt)))*100, 1), '%') as profit_rate
# from 
#     t
# group by 
#     product_id

# 合并代码
select
    '店铺汇总' AS product_id,
    concat(format((1-(sum(in_price*cnt)/sum(price*cnt)))*100, 1), '%') as profit_rate
from 
    t
union 
select
    product_id,
    concat(format((1-(sum(in_price*cnt)/sum(price*cnt)))*100, 1), '%') as profit_rate
from 
    t
group by 
    product_id
having
    (1-(sum(in_price*cnt)/sum(price*cnt))) > 0.249