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

# 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
)

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中真是有妙用。

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)

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

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

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

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的整体店铺利润率还是不用考虑利润率情况的整体店铺利润率

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)

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;

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
)

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
)

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;

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)

# 商品毛利率=(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
)

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

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

【分类】：行合并

分析思路

难点：

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
)

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 就会把店铺汇总放在最后了，有没有什么好办法

WITH temp as(
select 
ifnull(t1.product_id,'店铺汇总') id, CONCAT( round((1- sum(t1.in_price*t2.cnt )/sum(t2.price*t2.cnt))*100 ,1) ,'%')  rate
from tb_product_info t1 , tb_order_detail t2   ,tb_order_overall t3
where t1.shop_id =901 and t1.product_id=t2.product_id and t2.order_id=t3.order_id and DATE(t3.event_time)>='2021-10-01' 
group by t1.product_id WITH ROLLUP       having TRIM(TRAILING '%' FROM rate) >24.9&nbs***bsp;product_id IS NULL   ) 
select * from temp where id='店铺汇总'
union
select * from temp where id<>'店铺汇总';
#为什么我用rollup  汇总行在最后一行  无法得出正确的排序。