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

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

建表语句

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