某店铺的各商品毛利率及店铺整体毛利率
建表语句
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);
问题描述
请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。
注:
商品毛利率=(1-进价/平均单件售价)*100%;
店铺毛利率=(1-总进价成本/总销售收入)*100%。
结果先输出店铺毛利率,再按商品ID升序输出各商品毛利率,均保留1位小数。
解释:
店铺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%
解法一:(WITH ROLLUP)
问题分解:
统计每个被售出的商品的售价进价(生成子表t_product_in_each_order):
订单明细表内连接商品信息表和内连接订单总表:
tb_product_info AS t1
INNER JOIN tb_order_detail AS t2 ON t1.product_id = t2.product_id
INNER JOIN tb_order_overall AS t3 ON t2.order_id = t3.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
WITH ROLLUP 用法
1、在使用group by 语句后,可能会对数据再次进行汇总,这个时候就需要with rollup。
2、with rollup 就是作用在聚合函数上的。如果聚合函数是COUNT()则会在统计的记录中再次求COUNT(),如果是AVG(),则会再次求平均
3、with rollup 作用在group by 的第一个字段
解法二:(分解合并)
先求店铺汇总的(SQL1)
SELECT
'店铺汇总' AS 'product_id',
CONCAT(ROUND(100 * (1 - (SUM(t1.in_price * t2.cnt) / SUM(t2.price * t2.cnt))), 1), '%') AS 'profit_rate'
FROM
tb_product_info AS t1
INNER JOIN tb_order_detail AS t2 ON t1.product_id = t2.product_id
INNER JOIN tb_order_overall AS t3 ON t2.order_id = t3.order_id
WHERE t1.shop_id = 901
AND DATE(t3.event_time) >= '2021-10-01'
单个商品了,我们采用类似的方法即可,注意这里的毛利率需要基于单价计算(SQL2)
SELECT
t1.product_id,
CONCAT(ROUND(100 * (1 - t1.in_price / AVG(t2.price)), 1), '%') AS 'profit_rate'
FROM
tb_product_info AS t1
INNER JOIN tb_order_detail AS t2 ON t1.product_id = t2.product_id
INNER JOIN tb_order_overall AS t3 ON t2.order_id = t3.order_id
WHERE t1.shop_id = 901
AND DATE(t3.event_time) >= '2021-10-01'
GROUP BY t1.product_id
连接
SQL1
UNION ALL
SQL2
HAVING CAST(SUBSTRING_INDEX(profit_rate, '%', 1) AS DECIMAL(3, 1)) > 24.9