首页 > 试题广场 >

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

[编程题]某店铺的各商品毛利率及店铺整体毛利率
  • 热度指数:118930 时间限制:C/C++ 1秒,其他语言2秒 空间限制:C/C++ 256M,其他语言512M
  • 算法知识视频讲解

商品信息表tb_product_info


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


订单总表tb_order_overall


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


订单明细表tb_order_detail

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


场景逻辑说明

  • 用户将购物车中多件商品一起下单时,订单总表会生成一个订单(但此时未付款,status-订单状态0表示待付款),在订单明细表生成该订单中每个商品的信息;

  • 当用户支付完成时,在订单总表修改对应订单记录的status-订单状态1表示已付款;

  • 若用户退货退款,在订单总表生成一条交易总金额为负值的记录(表示退款金额,订单号为退款单号,status-订单状态为2表示已退款)。


问题:请计算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%
示例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%
select '店铺汇总' product_id,CONCAT(round((1-sum(in_price*t2.cnt)/sum(t2.price*t2.cnt))*100,1),'%') AS profit_rate
FROM tb_product_info AS t1
JOIN tb_order_detail AS t2 ON t1.product_id = t2.product_id
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'
union
(SELECT 
    t1.product_id,
    CONCAT(round((1-sum(in_price*cnt)/sum(t2.price*t2.cnt))*100,1),'%') AS profit_rate
FROM tb_product_info AS t1
JOIN tb_order_detail AS t2 ON t1.product_id = t2.product_id
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
having round((1-sum(in_price*cnt)/sum(t2.price*t2.cnt))*100,1)>24.9
order by t1.product_id)
店铺的数据跟商品的数据分开求,用union连接,商品数据的排序记得要用括号括起来,不然会识别成对联合后的表进行排序。

发表于 2025-06-09 19:03:55 回复(0)
with A as(
    select
        tod.product_id as product_id,
        cnt,in_price,price
    from tb_order_detail tod 
    left join tb_order_overall too using (order_id)
    left join tb_product_info tpi using (product_id)
    where date_format(event_time,'%Y-%m')>='2021-10' and shop_id= 901 and status>0
),
B as(
    select 
    '店铺汇总' as product_id,
    round((1-sum(cnt*in_price)/sum(cnt*price))*100,1) as profit_rate,
    1 as o
    from A
    union all
    select 
        product_id,
        round((1-sum(cnt*in_price)/sum(cnt*price))*100,1) as profit_rate,
        2 as o
    from A
    group by product_id
    having  round((1-sum(cnt*in_price)/sum(cnt*price))*100,1)>24.9
)
select
    product_id,
    concat(profit_rate,'%') as profit_rate
from B 
order by o,product_id

发表于 2025-01-10 17:29:31 回复(0)
WITH product_tmp AS (
    SELECT
        d.product_id AS product_id,
        SUM(d.price * d.cnt) AS product_sale_total,
        SUM(d.cnt) AS product_sale_num,
        SUM(d.price * d.cnt) / SUM(d.cnt) AS product_sale_avg
    FROM tb_order_overall o
    JOIN tb_order_detail d
    ON o.order_id = d.order_id
    WHERE o.status = 1
    AND o.event_time >= '2021-10-01' -- 表示2021年10月之后
    GROUP BY product_id
), 
/*
筛选订单成交且在10月后的所有订单,以商品种类为组计算涉及的所有商品各自的销售总额、销售数量和销售均价
*/

all_tmp AS (
    SELECT
        i.product_id,
        i.shop_id,
        i.in_price,
        (i.in_price * t.product_sale_num) AS in_amount,
        t.product_sale_total,
        t.product_sale_avg
    FROM tb_product_info i
    JOIN product_tmp t
    ON i.product_id = t.product_id
    ORDER BY product_id --注意在这里就把按照商品ID排序做好,外部排序会把“店铺汇总”也排序了
)
/*
获取合并商品信息,综合商品的进价(in_price),总成本(in_amount),销售总收入(product_sale_total),销售均价(product_sale_avg)
*/

SELECT
    '店铺汇总' AS product_id,
    CONCAT(ROUND((1 - (SUM(in_amount) / SUM(product_sale_total))) * 100, 1), '%') AS profit_rate
    -- 把计算结果转化为保留一位小数的百分数
FROM all_tmp
WHERE shop_id = 901

UNION ALL

SELECT
    product_id,
    CONCAT(ROUND((1 - (in_amount / product_sale_total)) * 100, 1), '%') AS profit_rate
FROM all_tmp
WHERE shop_id = 901
AND (1 - (in_price / product_sale_avg)) > 0.249;

发表于 2024-12-13 22:31:52 回复(0)
select
ifnull(tbp.product_id,'店铺汇总') as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from
    tb_order_detail as tb
left join
    tb_order_overall as tbo
on tb.order_id = tbo.order_id
left join
    tb_product_info as tbp
on
    tb.product_id = tbp.product_id
where
 date(event_time)>='2021-10-01' and shop_id = 901
group by tbp.product_id with ROLLUP
having product_id is null or round((1-sum(in_price*cnt)/sum(price*cnt))*100,1)>24.9
order by tbp.product_id ASC
发表于 2024-10-01 17:56:51 回复(0)
SELECT
    COALESCE(product_id, '店铺汇总') AS product_id,
    CONCAT (profit_rate, '%') AS profit_rate
FROM
    (
        SELECT
            a.product_id,
            ROUND(
                (
                    1 - SUM(b.cnt * a.in_price) / SUM(b.cnt * b.price)
                ) * 100,
                1
            ) AS profit_rate,
            ROUND((1 - max(a.in_price) / AVG(b.price)) * 100, 1) AS product_rate
        FROM
            tb_product_info AS a
            LEFT JOIN tb_order_detail AS b ON a.product_id = b.product_id
            LEFT JOIN tb_order_overall AS c ON b.order_id = c.order_id
        WHERE
            DATE_FORMAT (c.event_time, "%Y-%m") >= '2021-10'
            AND a.shop_id = '901'
        GROUP BY
            a.product_id
        WITH
            ROLLUP
    ) AS result_table
WHERE
    product_rate > 24.9
    OR product_id IS NULL
ORDER BY
    product_id != '店铺汇总'
  
发表于 2024-09-28 17:37:42 回复(0)
# 本题注意:2021年10月以来,表示>=2021-10
with dwd_data as (
select 
  t1.order_id,t1.product_id,t1.price,t1.cnt,t3.in_price,t2.total_amount,t2.status
from tb_order_detail t1 
inner join tb_order_overall t2
on t1.order_id = t2.order_id
inner join tb_product_info t3 
on t1.product_id = t3.product_id
where t3.shop_id = 901
and date_format(t2.event_time,'%Y-%m')>='2021-10'
) 


select 
  "店铺汇总" as product_id,
  concat(round(100-sum(in_price*cnt)/sum(price*cnt) *100,1),'%') as total_product_price
from dwd_data
union all 
select 
  product_id,
  concat(round(100-max(in_price)/(sum(price*cnt)/sum(cnt))*100,1),'%') as avg_product_price
from dwd_data
group by product_id
having avg_product_price>24.9

发表于 2024-08-19 15:49:49 回复(0)
with total as (
    select 
        product_id,
        sum(price*cnt)/sum(cnt) as avg_price,
        sum(cnt) as total_cnt
    from tb_order_overall
    left join tb_order_detail
    using(order_id)
    where status = 1 
    and event_time>'2021-10-01 00:00:00' ##2021年10月以来
    group by product_id
),
add_prod as (
    select 
        product_id,
        avg_price,
        total_cnt,
        in_price as avg_cost
    from total
    left join tb_product_info
    using(product_id)
    where shop_id=901 ##限制店铺901
),
shop as (
    select 
        '店铺汇总' product_id,
        concat(
            round(
                100*(1-sum(total_cnt*avg_cost)/sum(total_cnt*avg_price))
            ,1)
        ,'%') profit_rate
    from add_prod
),
products as (
    select
        product_id,
        concat(
            round(
                100*(1-avg_cost/avg_price)
            ,1)
        ,'%') profit_rate
    from add_prod
    where round(100*(1-avg_cost/avg_price),1)>24.9
)
select * from (
    select * from shop
    union
    select * from products
) a

发表于 2024-08-13 17:31:39 回复(0)
# shop901的商品id
with shop as(
select product_id,in_price
from tb_product_info
where shop_id=901
)

# 有效订单(去除退款订单、未支付订单)
,income as(
select order_id
from tb_order_overall
where status !=0 and date(event_time)>='2021-10-01'
group by order_id
having sum(total_amount)>0
)

# 店铺汇总
select '店铺汇总' as product_id,
    concat(round((1-sum(s.in_price*cnt)/sum(t.price*cnt))*100,1),'%') as profit_rate
from tb_order_detail t
join shop s
on t.product_id= s.product_id
where order_id in (select order_id from income)
union ALL
# 分商品汇总
select t.product_id, concat(round((1-s.in_price/AVG(t.price))*100,1),'%') as profit_rate
from tb_order_detail t
join shop s
on t.product_id= s.product_id
where order_id in (select order_id from income)
group by t.product_id,s.in_price
having 1-s.in_price/AVG(t.price)>=0.249


编辑于 2024-04-13 15:29:37 回复(0)
用grouping函数和rollup修饰符实现了一次查询
with o as(
    select
        od.order_id,
        od.product_id,
        od.price,
        od.cnt,
        oo.event_time,
        oo.status
    from
        tb_order_overall oo left join tb_order_detail od 
        on oo.order_id = od.order_id
    where
        date_format (oo.event_time, "%Y%m") >= 202110
    ),
s as (
    select
        product_id,
        shop_id,
        in_price
    from
        tb_product_info
    where
        shop_id = 901
    )

select
    case when grouping(s.product_id) = 1 then '店铺汇总'
        else s.product_id end product_id,
    concat(format(100*(
        1-sum(case when o.status<2 then s.in_price*o.cnt else 0 end)
            /sum(case when o.status<2 then o.price*o.cnt else 0 end)
    ),1),'%') profit_rate
from
    s left join o on s.product_id = o.product_id
group by
    s.product_id with rollup
having
    (grouping(s.product_id) = 0 and substring_index(profit_rate,'%',1) > 24.9)
    or grouping(s.product_id) >= 1
order by
    s.product_id asc


发表于 2024-04-10 11:01:42 回复(0)
# 请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。
# 商品毛利率=(1-进价/平均单件售价)*100%;
# 店铺毛利率=(1-总进价成本/总销售收入)*100%。
# 结果先输出店铺毛利率,再按商品ID升序输出各商品毛利率,均保留1位小数。

# join匹配三个表格,筛选2021年10月以来数据,店铺901数据

# 计算所有店铺总体毛利率
select new.product_id,
    concat(new.profit_rate_c,"%") as profit_rate
from
((select "店铺汇总" as product_id,
    round((1-(sum(in_price*cnt) over (partition by shop_id))/(sum(price*cnt) over (partition by shop_id)))*100,1) as profit_rate_c
from tb_order_detail
join tb_order_overall using (order_id)
join tb_product_info using (product_id)
where year(event_time)>=2021 and month(event_time)>=10 and shop_id = 901 )


# 合并上述数据
union

# 根据产品分组求毛利率,筛选毛利率大于24.9%
(select data2.product_id,
    data2.profit_rate_c
from
(select product_id,
    round((1-in_price/((sum(price*cnt) over (partition by product_id))/(sum(cnt) over (partition by product_id))))*100,1) as profit_rate_c
from tb_order_detail
join tb_order_overall using (order_id)
join tb_product_info using (product_id)
where year(event_time)>=2021 and month(event_time)>=10 and shop_id = 901 
# having profit_rate_c>24.9
order by product_id) as data2
where profit_rate_c >24.9
)) as new

编辑于 2024-03-02 23:50:07 回复(0)
WITH t1 AS (SELECT tbod.*,tboo.status,tbpi.shop_id,tbpi.in_price,event_time,price*cnt AS price_sale,in_price*cnt AS price_in FROM  tb_order_detail tbod JOIN tb_order_overall tboo
ON tbod.`order_id`=tboo.`order_id` 
JOIN tb_product_info tbpi ON tbod.product_id=tbpi.product_id
WHERE STATUS=1 AND shop_id='901' AND DATE(tboo.event_time)>='2021-10-01')
SELECT '店铺汇总' AS product_id,CONCAT(ROUND((1-SUM(in_price*cnt)/SUM(price*cnt))*100,1),'%') profit_rate FROM t1
UNION
(SELECT product_id, CONCAT(ROUND((1-SUM(price_in)/SUM(price_sale))*100,1),'%') profit_rate FROM t1 GROUP BY product_id   having ROUND((1-SUM(price_in)/SUM(price_sale))*100,1) >24.9 ORDER BY product_id
);
限制条件有点多,得看仔细
发表于 2023-12-16 23:44:05 回复(0)
select 
    ifnull(pi.product_id,'店铺汇总') product_id,
    concat(round((1 - sum(in_price * cnt) / sum(price * cnt)) * 100,1),'%') profit_rate
from tb_product_info pi 
join tb_order_detail od 
on pi.product_id = od.product_id
join tb_order_overall oo
on od.order_id = oo.order_id
where date_format(event_time,'%Y-%m') >= '2021-10'
and shop_id = 901
group by pi.product_id with rollup
having if(pi.product_id is null,1,profit_rate > 24.9)
order by pi.product_id;

发表于 2023-11-03 20:52:12 回复(0)
select 
    if(a.shop_id=901,'店铺汇总',null),
    concat(round(100 * round(1-sum(a.in_price*c.cnt)/sum(c.price*c.cnt),3),1),'%')
from 
    tb_product_info a,
    tb_order_overall b,
    tb_order_detail c 
where 
    a.product_id = c.product_id and 
    b.order_id = c.order_id and 
    b.event_time>='2021-10-01 00:00:00' and 
    a.shop_id = 901 
union all 
(select 
    a.product_id,
    concat(round(100 * round(1-sum(a.in_price*c.cnt)/sum(c.price*c.cnt),3),1),'%')
from 
    tb_product_info a,
    tb_order_overall b,
    tb_order_detail c 
where 
    a.product_id = c.product_id and 
    b.order_id = c.order_id and 
    b.event_time>='2021-10-01 00:00:00' and 
    a.shop_id = 901 
group by c.product_id 
having 1-sum(in_price*cnt)/sum(price*cnt)>0.249 
order by c.product_id)  
丑陋的题解
发表于 2023-09-08 21:18:40 回复(0)
select '店铺汇总',concat(round((1-sum(tpi.in_price*tod.cnt)/(sum(tod.price*tod.cnt)))*100,1),'%')
    from tb_product_info tpi
    join tb_order_detail tod on tpi.product_id=tod.product_id and tpi.shop_id=901
    join tb_order_overall too on too.status=1 and too.order_id=tod.order_id
    and too.event_time>='2021-10-01'
union
select product_id,concat(profit_rate_01,'%') as profit_rate from
(
    select tpi.product_id,
    round((1-tpi.in_price/(sum(tod.price*tod.cnt)/sum(tod.cnt)))*100,1) as profit_rate_01
    from tb_product_info tpi
    join tb_order_detail tod on tpi.product_id=tod.product_id and tpi.shop_id=901
    join tb_order_overall too on too.status=1 and too.order_id=tod.order_id
    and too.event_time>='2021-10-01'
    group by tpi.product_id,tpi.in_price
) tmp where profit_rate_01>24.9;

发表于 2023-08-26 21:00:36 回复(0)