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

# 字段：product_id , profit_rate
# 时间：2021-10至今
# 店铺：901
# 商品毛利率 = (1-进价/平均单件售价)*100%
# 店铺毛利率 = (1-总进价成本/总销售收入)*100%、
# ?? 需要添加status = 1?
# tb1：连接
with tb1 as(
    select order_id,product_id,shop_id,in_price,price,cnt
    from tb_order_detail left join tb_product_info using(product_id)
    left join tb_order_overall using(order_id)
    where date_format(event_time,'%Y%m') >= 202110
    AND shop_id = 901
),
# tb2：计算平均单件售价
tb2 as(
    select product_id,
    # avg(in_price),sum(price*cnt)/sum(cnt) as avg_price,
    # 商品毛利率
    concat(round((1-avg(in_price)/(sum(price*cnt)/sum(cnt)))*100,1),'%') as profit_rate
    from tb1 
    # where product_id in (select product_id from tb_product_info where shop_id = 901)
    group by product_id
    having (1-avg(in_price)/(sum(price*cnt)/sum(cnt)))>0.249
    order by product_id
),
# tb3：店铺汇总毛利率
# 店铺毛利率=(1-总进价成本/总销售收入)*100%
tb3 as(
    select shop_id,
    concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
    # concat(round((1-avg(in_price)/(sum(price*cnt)/sum(cnt)))*100,1),'%') as profit_rate
    from tb1
    group by shop_id
)

select '店铺汇总' as product_id,profit_rate from tb3
union all
select product_id,profit_rate from tb2

# 注意点：
# 时间格式小心别出错了，前后格式需要一致对齐 date_format(event_time,'%Y%m') 与 '202110'而不是'2021-10'