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

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

输入:

DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301001, 101, '2021-10-01 10:00:00', 30000, 3, 1),
  (301002, 102, '2021-10-01 11:00:00', 23900, 2, 1),
  (301003, 103, '2021-10-02 10:00:00', 31000, 2, 1);

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '家电', 6000, 100, '2020-01-01 10:00:00'),
  (8002, 902, '家电', 12000, 50, '2020-01-01 10:00:00'),
  (8003, 901, '3C数码', 12000, 50, '2020-01-01 10:00:00');

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301001, 8001, 8500, 2),
  (301001, 8002, 15000, 1),
  (301002, 8001, 8500, 1),
  (301002, 8002, 16000, 1),
  (301003, 8002, 14000, 1),
  (301003, 8003, 18000, 1);

输出:

店铺汇总|31.0%
8001|29.4%
8003|33.3%

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 37ms, 内存消耗: 6396KB, 提交时间: 2022-01-03

select ifnull(product_id,'店铺汇总') product_id,concat(profit_rate,'%') profit_rate from
(
    select a.product_id,round((1-sum(in_price*cnt)/sum(price*cnt))*100,1) profit_rate
    from tb_order_detail as a left join tb_order_overall as b on a.order_id=b.order_id
    left join tb_product_info as c on c.product_id=a.product_id
    where c.shop_id=901 and status=1
    and date_format(event_time,'%Y%m')>='202110'
    group by a.product_id with rollup
    having profit_rate>24.9 or a.product_id is null) as d
order by profit_rate

Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2022-02-10

select '店铺汇总' AS product_id
,concat(round((1-(SUM(in_price*cnt) / SUM(price*cnt)))*100,1),"%")
FROM tb_order_detail AS o
LEFT JOIN tb_order_overall AS oo
ON o.order_id = oo.order_id
LEFT JOIN tb_product_info AS p
ON o.product_id = p.product_id 
WHERE TIMESTAMPDIFF(day,"2021-09-30",event_time)>0
and p.shop_id = 901
GROUP BY p.shop_id


union all

SELECT product_id, concat(round(rate*100,1),"%")

FROM
(SELECT o.product_id AS product_id
, (1-(sum(in_price)*sum(cnt))/(sum(price)*sum(cnt))) AS rate
FROM tb_order_detail AS o
LEFT JOIN tb_order_overall AS oo
ON o.order_id = oo.order_id
LEFT JOIN tb_product_info AS p
ON o.product_id = p.product_id 
WHERE TIMESTAMPDIFF(day,"2021-09-30",event_time)>0
and p.shop_id = 901
GROUP BY o.product_id
ORDER BY product_id) AS t

WHERE rate>0.249

Mysql 解法, 执行用时: 37ms, 内存消耗: 6404KB, 提交时间: 2022-01-01

select
'店铺汇总' product_id
,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') profit_rate
from
tb_order_detail tod
left join tb_product_info tpi
on tod.product_id = tpi.product_id
left join tb_order_overall too
on tod.order_id = too.order_id
where too.status = 1
and date(event_time) >= '2021-10-01'
and shop_id = 901
union all
select
tod.product_id
,concat(round((sum((price-in_price)*cnt)/sum(price*cnt))*100,1),'%') profit_rate
from
tb_order_detail tod
left join tb_product_info tpi
on tod.product_id = tpi.product_id
left join tb_order_overall too
on tod.order_id = too.order_id
where too.status = 1
and date(event_time) >= '2021-10-01'
and shop_id = 901
group by product_id
having left(profit_rate,3) > 24.9
#order by product_id

#注意最后group by和order by的时候product_id前不用加表名

Mysql 解法, 执行用时: 37ms, 内存消耗: 6408KB, 提交时间: 2022-01-22

SELECT '店铺汇总' as product_id,
CONCAT(ROUND((1-SUM(in_price*cnt)/SUM(price*cnt))*100,1),'%')
FROM tb_product_info tpi
JOIN tb_order_detail tod
ON tpi.product_id=tod.product_id
JOIN tb_order_overall too
ON too.order_id=tod.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
JOIN tb_order_detail tod
ON tpi.product_id=tod.product_id
JOIN tb_order_overall too
ON too.order_id=tod.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

Mysql 解法, 执行用时: 37ms, 内存消耗: 6408KB, 提交时间: 2021-12-04

select '店铺汇总' as product_id
,concat(round((1-sum(cnt*in_price)/ sum(cnt*price))*100,1),'%') as profit_rate
from (
        select c.shop_id
        ,a.product_id
        ,c.in_price
        ,a.price
        ,sum(a.cnt) as cnt
        from tb_order_detail a
        join tb_order_overall b on a.order_id=b.order_id
        join tb_product_info c on a.product_id=c.product_id
        where b.status='1' and c.shop_id='901' and b.event_time>='2021-10-01'
        group by a.product_id
    ,c.shop_id
    ,c.in_price
    ,a.price
) z
group by shop_id

union all

select product_id
,concat(round((1-sum(cnt*in_price)/ sum(cnt*price))*100,1),'%') as profit_rate
from (
        select c.shop_id
        ,a.product_id
        ,c.in_price
        ,a.price
        ,sum(a.cnt) as cnt
        from tb_order_detail a
        join tb_order_overall b on a.order_id=b.order_id
        join tb_product_info c on a.product_id=c.product_id
        where b.status='1' and c.shop_id='901' and b.event_time>='2021-10-01'
        group by a.product_id
        ,c.shop_id
        ,c.in_price
        ,a.price
) g
group by product_id
having round((1-sum(cnt*in_price)/ sum(cnt*price))*100,1)>24.9

上一题

下一题

详情