牛客网SQL大厂面试真题-SQL170 某店铺的各商品毛利率及店铺整体毛利率

描述

商品信息表tb_product_info

id product_id shop_id tag in_price quantity release_time
1 8001 901 家电 6000 100 2020-01-01 10:00:00
2 8002 902 家电 12000 50 2020-01-01 10:00:00
3 8003 901 3C数码 12000 50 2020-01-01 10:00:00

（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

订单总表tb_order_overall

id order_id uid event_time total_amount total_cnt status
1 301001 101 2021-10-01 10:00:00 30000 3 1
2 301002 102 2021-10-01 11:00:00 23900 2 1
3 301003 103 2021-10-02 10:00:00 31000 2 1

（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

订单明细表tb_order_detail

id order_id product_id price cnt
1 301001 8001 8500 2
2 301001 8002 15000 1
3 301002 8001 8500 1
4 301002 8002 16000 1
5 301003 8002 14000 1
6 301003 8003 18000 1

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）

场景逻辑说明：

  • 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；
  • 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1表示已付款；
  • 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

问题：请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。

注：商品毛利率=(1-进价/平均单件售价)*100%；

店铺毛利率=(1-总进价成本/总销售收入)*100%。

结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。

输出示例：

示例数据的输出结果如下：

product_id profit_rate
店铺汇总 31.0%
8001 29.4%
8003 33.3%

解释：

店铺901有两件商品8001和8003；8001售出了3件，销售总额为25500，进价总额为18000，毛利率为1-18000/25500=29.4%，8003售出了1件，售价为18000，进价为12000，毛利率为33.3%；

店铺卖出的这4件商品总销售额为43500，总进价为30000，毛利率为1-30000/43500=31.0%

答案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
select
    product_id,
    concat(profit_rate, '%') as profit_rate
from
    (
        select
            ifnull(product_id, '店铺汇总') as product_id,
            round((1 - (sum(in_price * cnt) / sum(price * cnt))) * 100, 1) as profit_rate
        from
            (
                select
                    c.product_id,
                    in_price,
                    price,
                    cnt
                from
                    tb_order_overall a
                    join tb_order_detail b on a.order_id = b.order_id
                    join tb_product_info c on b.product_id = c.product_id
                where
                    shop_id = 901
                    and date(event_time) >= '2021-10-01'
            ) d
        group by
            product_id with rollup
        having
            profit_rate > 24.9
            OR product_id IS NULL
        order by
            product_id
    ) e

知识点

WITH ROULLUP：在group by分组之后，再次对聚合函数进行求和