首页 > 试题广场 >

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

[编程题]某店铺的各商品毛利率及店铺整体毛利率

热度指数：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%

算法知识视频讲解

咖啡重度依赖

难道没有人发现题目有问题吗，明细表和汇总表的数据对不上 8500*2 + 15000 = 30000？这是神马逻辑？？

发表于 2022-02-13 18:20:56 回复(12)

Mysql

冷凡社长

select '店铺汇总' as product_id, concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_order_overall join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where date_format(event_time,'%Y%m')>='202110' and shop_id=901
union all
(select product_id,concat(round((1-avg(in_price)/(avg(price)))*100,1),'%') profit_rate
from tb_order_overall join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where date_format(event_time,'%Y%m')>='202110' and shop_id=901
group by product_id
having (1-avg(in_price)/(avg(price)))*100 > 24.9
order by product_id)

分为两步来算，分别求得店铺和产品的值再汇总。
主要是搞明白公式计算。

若不想使用union all 可以使用 with rollup 来完成汇总。

select ifnull(product_id,'店铺汇总'),concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_order_overall join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where date_format(event_time,'%Y%m')>='202110' and shop_id=901
group by product_id
with rollup
having case when product_id>0 then (1-avg(in_price)/(avg(price)))*100 > 29.4 else (1-avg(in_price)/(avg(price)))*100 > 0 end 
order by product_id

case when 在sql中真是有妙用。

发表于 2022-08-26 14:50:14 回复(6)

Mysql

苏打烟花无糖粉

注意表之间的关系：

-- order_id 对应的是一个订单多个商品，相同的product_id价格不一定相同
-- tb_order_overall 里的total_amount涉及多个产品，不能用于计算
-- tb_product_info 每一个商品的进货价都是唯一的

同时要注意：最后计算店铺整体毛利率，需要shop_id下全部产品的数据

提交代码使用的数据当中，新增了一个8003产品id，因为不符合商品毛利率大于24.9%，所以输出的结果当中没有8003的数据

由于8003有交易数据，所以计算店铺整体毛利率需要计入

-- 商品毛利率=(1-进价/平均单件售价)*100%
-- 进价in_price，平均单价售价price / cnt，
-- 涉及两表，tb_order_detail，tb_product_info

-- 店铺毛利率=(1-总进价成本/总销售收入)*100%
-- 总进价成本 in_price * cnt，总销售收入 price * cnt
-- 涉及两表，tb_order_detail，tb_product_info

WITH a AS
(
    SELECT pi.product_id,ov.order_id,in_price,price,cnt
    FROM tb_product_info pi INNER JOIN
    tb_order_detail od ON
    pi.product_id = od.product_id
    INNER JOIN tb_order_overall ov ON
    ov.order_id = od.order_id
    WHERE date(event_time) >= "2021-10-01"
    AND shop_id = 901
)


SELECT "店铺汇总" as product_id,
concat ( round( (( 1 - sum(in_price*cnt) / sum(price * cnt) )*100) , 1 ), "%")
FROM a
UNION ALL
SELECT product_id,
concat(round((1 - ( max(in_price) / (sum(price*cnt) / sum(cnt)) ) )*100,1),"%")
FROM a
WHERE product_id in
    (
    SELECT product_id
    FROM a
    GROUP BY product_id
    HAVING 1 - (max(in_price) / (sum(price*cnt) / sum(cnt))) > 0.249
    )
GROUP BY product_id

发表于 2021-12-17 14:45:24 回复(6)

Mysql

只会简单的算法题

# tb_product_info a, tb_order_overall b, tb_order_detail c
# union 合并结果集
# 把 group by 条件去掉，就是店铺汇总
(
select '店铺汇总' as product_id, 
concat(
    round((1 - sum(a.in_price * c.cnt) / (sum(c.price * c.cnt)) ) * 100, 1),
    '%') as profit_rate
from tb_product_info a, tb_order_overall b, tb_order_detail c
where a.shop_id = 901 
    and date_format(b.event_time, '%Y-%m') >= '2021-10'
    and a.product_id = c.product_id and c.order_id = b.order_id
    and b.status = 1
)
union
(
select c.product_id, 
concat(
    round((1 - sum(a.in_price * c.cnt) / (sum(c.price * c.cnt)) ) * 100, 1),
    '%') as profit_rate
from tb_product_info a, tb_order_overall b, tb_order_detail c
where a.shop_id = 901 
    and date_format(b.event_time, '%Y-%m') >= '2021-10'
    and a.product_id = c.product_id and c.order_id = b.order_id
    and b.status = 1
group by c.product_id having replace(profit_rate,'%','') > 24.9 
order by c.product_id
)

发表于 2021-12-16 17:38:50 回复(1)

Mysql

夏朗

综合题解和讨论区各路大佬的答案，思路如下：

1. 首先明确需要的字段：商品id,(1-总进价/总售价)*100%
2. 筛选时间、店铺
3. 计算总进价、总售价
4. 计算毛利率
5. 筛选>24.9

select ifnull(product_id,'店铺汇总') product_id
,concat(profit_rate,'%') profit_rate
from(
	select  product_id
	,round(100*(1-sum(in_price*cnt)/sum(price*cnt)),1) profit_rate
	from tb_order_detail tod
	join tb_order_overall too using(order_id)
	join tb_product_info tpi using(product_id)
	where event_time>='2021-10-01'
	and shop_id='901'
	group by product_id
	with rollup 
	having profit_rate>24.9&nbs***bsp;product_id is null
) t
order by product_id<>'店铺汇总', profit_rate

发表于 2022-01-05 15:50:46 回复(1)

Mysql

啊坡糍嘚

with new_tab as(select a.product_id,a.shop_id,a.in_price
                ,b.order_id,b.price,b.cnt
                from tb_product_info a , tb_order_detail b ,tb_order_overall c 
                where b.order_id=c.order_id and a.product_id=b.product_id
                and substring(c.event_time,1,7)>='2021-10'
                and a.shop_id=901 and c.status in (1,2))
                
(select '店铺汇总',CONCAT(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%')
from new_tab)
union all 
(select product_id,CONCAT(round(ratio*100,1),'%')
from (select distinct product_id ,(1-sum(in_price*cnt)/sum(price*cnt)) as ratio
from new_tab group by product_id order by product_id) t where t.ratio>0.249)

发表于 2022-03-10 21:23:43 回复(0)

Mysql

不想睡觉zzz

select '店铺汇总' product_id,
concat(round((1-sum(p1.in_price*o1.cnt)/(sum(o1.price*o1.cnt)))*100,1),'%') profit_rate
from tb_product_info p1
inner join tb_order_detail o1 on o1.product_id=p1.product_id
inner join tb_order_overall oo1 on oo1.order_id=o1.order_id
where p1.shop_id=901
and date_format(oo1.event_time,'%Y-%m-%d') >= '2021-10-01'
union all
select * from
(select pi.product_id,
concat(round((1-sum(pi.in_price*od.cnt)/(sum(od.price*od.cnt)))*100,1),'%') profit_rate
from tb_product_info pi
inner join tb_order_detail od on od.product_id=pi.product_id
inner join tb_order_overall oo on oo.order_id=od.order_id
where pi.shop_id=901
and date_format(oo.event_time,'%Y-%m-%d') >= '2021-10-01'
and oo.status=1
group by pi.product_id
having 1-(avg(pi.in_price*od.cnt)/(avg(od.price*od.cnt)))>0.249
order by pi.product_id)as a

总感觉是那里怪怪的，这个计算店铺汇总的时候，没有限定product_id，万一有一些product——ID 的毛利率没有24.9%怎么办呢？还是题目我理解错了，只要是这个店铺的商品就都要计算进去？我不理解，有没有大神提点一下，求求了！

发表于 2021-12-03 15:02:10 回复(16)

Mysql

牛客309740715号

求问！为什么第一行的别名如果和字段名product_id一样，coalesce就失效了，返回的汇总行是“none”，不是“店铺汇总”！

select coalesce(product_id,'店铺汇总')  pproduct_id
,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),"%") profit_rate
from tb_order_detail
left join tb_product_info  using(product_id)
where shop_id = 901
group by product_id with rollup
having round((1-sum(in_price*cnt)/sum(price*cnt))*100,1) > 24.9
order by pproduct_id !="店铺汇总",pproduct_id

发表于 2022-09-27 15:50:27 回复(1)

Mysql

lifeitech

with y as
(select p2.product_id, p2.in_price, x2.Q, x2.gr_rev from
(select x.product_id, sum(x.cnt) as Q, sum(x.price * x.cnt) as gr_rev
from
(select p.shop_id, p.product_id, detail.price, detail.cnt, t.order_id, t.event_time, t.status 
from
tb_product_info as p
left join
tb_order_detail as detail
on p.product_id = detail.product_id
right join tb_order_overall as t
on detail.order_id = t.order_id
where p.shop_id = 901 and t.event_time >= '2021-10-01' and t.status = 1) as x
group by x.product_id) as x2
join (select product_id, in_price from tb_product_info) as p2
on x2.product_id = p2.product_id
)
### end of the y table
select '店铺汇总' as product_id, concat(round((1 - sum(y.in_price * y.Q) / sum(y.gr_rev)) * 100, 1), '%') as profit_rate
from y
union all
(select y.product_id, concat(round((1 - y.in_price * y.Q / y.gr_rev) * 100, 1), '%') as profit_rate
from y having cast(profit_rate as float) > 24.9 order by y.product_id)

思路详情见 https://github.com/lifeitech/sql-nowcoder

发表于 2022-05-28 12:57:14 回复(0)

牛客430931612号

SELECT if(grouping(product_id)=1,'店铺汇总',product_id) product_id,
concat(round((1-sum(cnt*in_price*new_status)/sum(cnt*price*new_status))*100,1),'%') profit_rate
FROM 
(SELECT product_id,in_price,cnt,price,if(status=2,-1,status) new_status
FROM tb_order_detail d
JOIN tb_order_overall o USING(order_id)
JOIN tb_product_info i USING(product_id)
WHERE date(event_time)>='2021-10-01' and shop_id = 901)t
GROUP BY product_id WITH ROLLUP
HAVING (1-sum(cnt*in_price*new_status)/sum(cnt*price*new_status))*100>24.9 or product_id is null
ORDER BY product_id

发表于 2022-04-11 18:03:55 回复(0)

Mysql

牛客392104363号

select '店铺汇总' product_id,concat(format((1-sum(in_price*cnt)/sum(total_sale))*100,1),'%') Profit_RATE
from (select *,price*cnt total_sale from tb_order_detail)t left join tb_order_overall using(order_id) left join tb_product_info  using (product_id)
where DATE_FORMAT(event_time,'%Y%m')>=202110 AND shop_id=901
UNION all
(select  product_id AS product_id,concat(format((1-sum(in_price*cnt)/sum(total_sale))*100,1),'%') Profit_RATE
from (select *,price*cnt total_sale from tb_order_detail)t left join tb_order_overall using(order_id) left join tb_product_info  using (product_id)
where DATE_FORMAT(event_time,'%Y%m')>=202110 AND shop_id=901 
GROUP BY product_id
having Profit_RATE>24.9
order by product_id)
加了百分号不会让小数点左移，所以要>24.9
题目说明没讲明白是利润率>24.9的整体店铺利润率还是不用考虑利润率情况的整体店铺利润率

发表于 2021-12-08 10:41:33 回复(0)

爱刷美剧的柯基在许愿

select ifnull(product_id,'店铺汇总') 
,concat(round((1-(sum(in_price*cnt))/(sum(price*cnt)))*100,1),'%') p
from tb_product_info 
join tb_order_detail 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
having p>24.9&nbs***bsp;product_id is null
order by product_id;

发表于 2024-08-02 22:19:07 回复(0)

牛客563312411号

注意商品毛利率中是平均单件售价

商品毛利率=1-in_price/(sum(price*cnt)/sum(cnt))

=1-in_price*sum(cnt)/sum(price*cnt) #一种商品进价一定

=1-sum(in_price*cnt)/sum(pricre*cnt)

#一种商品sum(in_price*cnt)=in_price*cnt，sum(pricre*cnt) 同理，

所以 =1-(in_price*cnt)(/price*cnt)

=1-in_price/price

#一种商品无论sum()或avg()结果相同，

所以 =1-sum(in_price)/sum(price)

=1-avg(in_price)/avg(price)

题目要求是店铺901，而每个订单总金额中包含多个店铺商品的金额，不能用total_amount算

隐含条件：status=1

with a as 
(
select 
tpi.product_id
,in_price
,price
,cnt
from tb_product_info tpi
join tb_order_detail
using(product_id)
join tb_order_overall
using(order_id)
where date_format(event_time,'%Y-%m')>='2021-10'
and shop_id=901
)

select '店铺汇总' as product_id, concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from a
union all
(select product_id,concat(round((1-avg(in_price)/(avg(price)))*100,1),'%') profit_rate
from a
group by product_id
having (1-avg(in_price)/(avg(price)))*100 > 24.9
order by product_id)

2.用with rollup 时，为确保店铺毛利率不被筛选掉（HAVING profit_rate > 24.9 ）需要加上or product_id IS NULL

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;

或者having case when product_id>0 then cast（profit_rate as float) >24.9 else profit_rate is not null end

或者having if(product_id>0 ,cast（profit_rate as float) >24.9,profit_rate)

select 
ifnull(product_id,'店铺汇总') product
,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_order_overall 
join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where date_format(event_time,'%Y%m')>='202110' 
and shop_id=901
group by product_id
with rollup
having case when product_id>0 then (1-avg(in_price)/(avg(price)))*100 > 29.4 else (1-avg(in_price)/(avg(price)))*100 is  not null end 
order by product_id

with a as 
(
select 
product_id
,sum(in_price*cnt) total_in_price
,sum(price*cnt) total_out_price
from tb_order_overall
join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where date(event_time)>='2021-10-1'
and shop_id=901
and status=1
group by 1
)
select 
'店铺汇总' product_id
,concat(round((1-sum(total_in_price)/sum(total_out_price))*100,1),'%')profit_rate
from a 
union 
(
select 
product_id
,concat(round((1-total_in_price/total_out_price)*100,1),'%') profit_rate
from a
where 1-total_in_price/total_out_price>0.249
order by 1
)

转换数据类型为浮点型：

cast(profit_rate as decimal)>24.9

cast(profit_rate as float)>24.9

replace(profit_rate,'%','') 替换

substring_index(profit_rate,'%',1)索引式截取

trim('%' from profit_rate)>24.9 删除

with a as 
(
select 
product_id
,sum(in_price*cnt) total_in_price
,sum(price*cnt) total_out_price
from tb_order_overall
join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where date(event_time)>='2021-10-1'
and shop_id=901
and status=1
group by 1
)
select 
'店铺汇总' product_id
,concat(round((1-sum(total_in_price)/sum(total_out_price))*100,1),'%')profit_rate
from a 
union 
(
select 
product_id
,concat(round((1-total_in_price/total_out_price)*100,1),'%') profit_rate
from a
group by 1
having cast(profit_rate as decimal)>24.9
order by 1
)

编辑于 2024-03-10 14:18:59 回复(0)

Mysql

War张

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)

Mysql

牛客129587388号

with t as

(select

a.order_id,

a.product_id,

sum(price*cnt) as profile,

sum(in_price*cnt) as cost

from tb_order_detail a

left join tb_product_info b

on a.product_id = b.product_id

left join tb_order_overall c

on a.order_id = c.order_id

where shop_id = '901' and date_format(event_time,'%Y-%m') >= '2021-10'

group by a.order_id,a.product_id

)

select

'店铺汇总' product_id,

concat(round((1-sum(cost)/sum(profile))*100,1),'%') as profit_rate

from

union

(select

product_id,

concat(round((1-sum(cost)/sum(profile))*100,1),'%') as profit_rate

from t

group by

product_id

having left(profit_rate,3)>24.9

order by profit_rate)

发表于 2023-04-12 22:56:13 回复(0)

126号选手

with a as
(select 
product_id,
sum(price*cnt) sales_total,#销售总额
sum(in_price*cnt) in_price_total#进价总额
from tb_product_info join tb_order_detail using(product_id)
join tb_order_overall using (order_id)
# 这里where筛选条件说明一下:
# 时间就是题目要求2021-10之后的数据,店铺901
# 对于销售总额,是指已付款和未付款的,因为题目说明对于退款的price记录为负值,所以此处语句没有status<>2
where event_time >='2021-10-01' and shop_id=901 
group by product_id)

# 下面就是按照公式计算,然后拼接
(select
'店铺汇总' as product_id,
concat(round(((1-(sum(in_price_total)/sum(sales_total)))*100),1),'%') profit_rate
from a)
union
(select
product_id,
concat(round((1-in_price_total/sales_total)*100,1),'%') profit_rate
from a
group by product_id
    having profit_rate>24.9
order by product_id)

发表于 2023-03-26 10:28:39 回复(0)

Mysql

4fbd81e

# 商品毛利率=(1-进价/平均单件售价)*100%；
# 店铺毛利率=(1-总进价成本/总销售收入)*100%。

# 注意，这里得是：event_time在2021-10之后，release_time没有要求。
# 其他三种情况不能AC
with total_info as(  
    select tod.*,too.uid,too.event_time,too.total_amount,too.total_cnt,too.status,
    tpi.shop_id,tpi.tag,tpi.in_price,tpi.quantity,tpi.release_time
    from tb_order_detail tod
    join tb_product_info tpi
    on tod.product_id=tpi.product_id
    join tb_order_overall too
    on too.order_id=tod.order_id
    where shop_id=901
    and date_format(event_time,'%Y%m')>='202110'
    # and date_format(release_time,'%Y%m')>='202110'
)

(
select "店铺汇总" as product_id,
CONCAT(ROUND((1-sum(in_price*cnt)/sum(price*cnt))*100,1),"%") as  profit_rate
from total_info
)
union all
(
select product_id,CONCAT(ROUND((1-avg(in_price)/avg(price))*100,1),"%") as  profit_rate
from total_info
group by product_id
having (1-avg(in_price)/avg(price))>=0.249
order by product_id asc
)

发表于 2023-03-18 13:07:00 回复(0)

Mysql

ColaShaker

select 
kk.product_id
,concat(round(100 * kk.profit_rate,1),'%') profit_rate
from
(
    select
    '店铺汇总' as product_id
    ,(1 -sum(in_price * cnt)/sum(price * cnt)) profit_rate
    from tb_order_detail tod
    join tb_order_overall too
    on tod.order_id = too.order_id
    join tb_product_info tpi
    on tod.product_id = tpi.product_id
    where tpi.shop_id = 901
    and date_format(too.event_time,'%Y-%m') > '2021-09'
    union all
    select
    tod.product_id 
    ,(1 -sum(in_price * cnt)/sum(price * cnt)) profit_rate
    from tb_order_detail tod
    join tb_order_overall too
    on tod.order_id = too.order_id
    join tb_product_info tpi
    on tod.product_id = tpi.product_id
    where tpi.shop_id = 901
    and date_format(too.event_time,'%Y-%m') > '2021-09'
    group by 1
    having profit_rate > 0.249
) kk

发表于 2022-12-28 23:39:47 回复(0)

Mysql

酸菜鱼土豆大侠

【场景】：店铺分析、行合并

【分类】：行合并

分析思路

难点：

1.结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率

2.吊牌总金额：产品数量*吊牌单价，即 sum(sales_num * tag_price)

(
select 查询结果 ['店铺汇总';店铺毛利率]
from 从哪张表中查询数据[多表连接]
)
union
(
select 查询结果 [商品ID;商品毛利率]
from 从哪张表中查询数据[多表连接]
where 查询条件 [商品毛利率大于24.9%]
order by 对查询结果排序 [先输出店铺毛利率，再按商品ID升序];
)

求解代码

方法一

with子句

with
    main as(
        #计算店铺毛利率
        select
            '店铺汇总' as product_id,
            concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),"%") as profit_rate
        from tb_product_info
        join tb_order_detail using(product_id)
        join tb_order_overall using(order_id)
        where status <> 2
        and shop_id = 901
        and date(event_time) >= '20211001'
        group by shop_id
    )
    ,attr as(
        #计算商品毛利率
        select
            product_id,
            concat(round((1-sum(in_price)/sum(price))*100,1),"%") as profit_rate
        from tb_product_info
        join tb_order_detail using(product_id)
        join tb_order_overall using(order_id)
        where status <> 2
        and shop_id = 901
        and date(event_time) >= '20211001'
        group by product_id
    )
#难点：结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率
(
    select
        product_id,
        profit_rate
    from main
)
union
(
    select
        product_id,
        profit_rate
    from attr
    where replace(profit_rate,'%','') > 24.9
    order by substring(1,2) desc,product_id
)

方法二

多表连接

#计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率
(        
    #计算店铺毛利率
    select
        '店铺汇总' as product_id,
        concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),"%") as profit_rate
    from tb_product_info
    join tb_order_detail using(product_id)
    join tb_order_overall using(order_id)
    where status <> 2
    and shop_id = 901
    and date(event_time) >= '20211001'
    group by shop_id
)
union
(
    #计算商品毛利率;商品毛利率大于24.9%
    select
        product_id,
        concat(round((1-sum(in_price)/sum(price))*100,1),"%") as profit_rate
    from tb_product_info
    join tb_order_detail using(product_id)
    join tb_order_overall using(order_id)
    where status <> 2
    and shop_id = 901
    and date(event_time) >= '20211001'
    group by product_id having round((1-sum(in_price)/sum(price))*100,1) > 24.9
    order by substring(1,2) desc,product_id
)

发表于 2022-11-06 23:23:54 回复(0)

Mysql

牛客555336690号

select '店铺汇总' as product_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_product_info
left join tb_order_detail
using(product_id)
left join tb_order_overall
using(order_id)
where shop_id=901 and date(event_time)>='2021-10-01' and status=1
union 
select product_id,concat(round((1-avg(in_price)/avg(price))*100,1),'%') as profit_rate
from tb_product_info
left join tb_order_detail c
using(product_id)
left join tb_order_overall
using(order_id)
where shop_id=901 and date(event_time)>='2021-10-01' and status=1
group by product_id
having profit_rate>24.9

提交通过了，但是没有排序，后面直接加order by 就会把店铺汇总放在最后了，有没有什么好办法

发表于 2022-06-30 14:44:15 回复(3)