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

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

http://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6

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

明确题意:

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

此处毛利率定义如下:商品毛利率=(1-进价/平均单件售价)*100%;店铺毛利率=(1-总进价成本/总销售收入)*100%。

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


问题分解:

  • 统计每个被售出的商品的售价进价(生成子表t_product_in_each_order):
    • 订单明细表内连接商品信息表: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"
  • 按商品分组:GROUP BY product_id
  • 加上汇总结果:WITH ROLLUP
  • 商品ID列重整:IFNULL(product_id, '店铺汇总') as product_id
  • 计算商品利润率:100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)) as profit_rate
  • 保留1位小数:ROUND(x, 1)
  • 筛选满足条件的分组(商品):HAVING profit_rate > 24.9 OR product_id IS NULL
  • 格式化毛利率格式:CONCAT(profit_rate, "%") as profit_rate

细节问题:

  • 表头重命名:as
  • 按商品ID排序:ORDER BY product_id

完整代码:

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;
SQL大厂真题 文章被收录于专栏

大厂真题手把手教你怎么解~

全部评论
这里为什么都不用考虑status啊?
6 回复 分享
发布于 2022-03-01 09:58
分子分母同时乘以总销售数量cnt,结果两个公式变成一样的了,大佬啊大佬
3 回复 分享
发布于 2022-05-10 21:57
您好,请问一下为啥where里面的product_id = '店铺汇总'结果就不显示,一定要写product_id IS NULL呢?
3 回复 分享
发布于 2022-04-22 12:09
商品毛利率=(1-进价/平均单件售价)*100%;注意是平均单件售价,这样只用一个公式算是不对的,能通过是样例数据刚好能满足条件。把24.9条件改一下可能就错了
2 回复 分享
发布于 2022-01-20 22:43
请问为什么这样就可以把答案汇总放在第一行了呀
1 回复 分享
发布于 2023-07-28 12:16 北京
为什么with rollup后对商品毛利率汇总,就是店铺整体毛利率啊?
1 回复 分享
发布于 2023-05-23 01:15 河南
我在Navicat跑了这个码,店铺汇总还是在最后一行,应该把IFNULL(product_id, '店铺汇总') as product_id写在最外层吧,子查询里就写select product_id,这样子查询里排序时就会把null排在第一行,最外层再用IFNULL(product_id, '店铺汇总') as product_id将null命名为'店铺汇总',就可以把店铺汇总放在第一行了
1 回复 分享
发布于 2023-03-14 11:47 湖北
select ifnull(inf.product_id,"店铺汇总") productid , concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),"%") profit_rate from tb_product_info inf inner join tb_order_detail det on inf.product_id = det.product_id inner join tb_order_overall ove on det.order_id = ove.order_id where shop_id = 901 and date(event_time)>="2021-10-01" group by inf.product_id with rollup having profit_rate>24.9 or productid is null order by productid 求问感觉逻辑一样的 为什么店铺汇总还是在最后一行呀
1 回复 分享
发布于 2023-02-02 22:59 浙江
having profit rate > 24.9这样真的可以吗?
1 回复 分享
发布于 2023-02-01 12:19 山西
请教一下:line12group by和line15 order by后面为什么不可以简写1呢?
1 回复 分享
发布于 2022-11-08 14:50 广东
这就是京东吗,你不要过来呀!
1 回复 分享
发布于 2022-08-27 16:46 广东
(1 - SUM(in_price*cnt) / SUM(price*cnt)) 我始终不能理解这个的含义哪位大佬能详细解释下
1 回复 分享
发布于 2022-07-15 21:35
请问一下如何能够保证先输出的是店铺毛利率
1 回复 分享
发布于 2022-04-24 16:22
这个地方第一个子查询中 select product_id 没有明确是哪个表为什么也能通过
1 回复 分享
发布于 2022-04-19 21:10
sum(if(status = 0,0,in_price*cnt)),是不是应该加个status的条件,没付款的不能算销售收入吧感觉
1 回复 分享
发布于 2022-03-17 09:43
1、应该加上status=1的限定条件;题目隐藏条件应该就是计算status=1的订单; 2、仔细看会发现,店铺总销售收入这样算有问题,不知道为什么答案能通过,不应该是计算total_amount的和么?
1 回复 分享
发布于 2022-03-17 08:46
万一with rollup汇总行的毛利率低于0.24,那汇总行也被去掉了
点赞 回复 分享
发布于 2024-09-06 01:59 北京
想问问,为什么已经有IFNULL(product_id, '店铺汇总') as product_id的情况下,后续筛选的时候HAVING profit_rate > 24.9 OR product_id IS NULL这里为什么还有is null呢?
点赞 回复 分享
发布于 2024-06-01 16:10 广东
这题确实还是不严谨 整体毛利率不是考虑所有商品 另外也不考虑订单状态
点赞 回复 分享
发布于 2024-03-05 11:42 香港
为什么我date_format(event_time,'%y%m') >="202110"有示例不能通过,求教
点赞 回复 分享
发布于 2023-07-13 12:30 广东

相关推荐

评论
71
10
分享

创作者周榜

更多
牛客网
牛客企业服务