题解 | #店铺901国庆期间的7日动销率和滞销率#

select  t3.dt,round(min(pd)/count(distinct product_id),3) sale_rate ,
1-round(min(pd)/count(distinct product_id),3)  unsale_rate
from(
# 901有销售记录的7天内销售商品数
select t1.dt,count(distinct if(shop_id=901,t2.product_id,null)) pd
from
    (select distinct date(event_time) dt
    from tb_order_overall
    where date(event_time) in ('2021-10-01','2021-10-02','2021-10-03')) t1
left join(
    select distinct date(event_time) dt,product_id
    from tb_order_detail
    left join tb_order_overall
    on tb_order_overall.order_id = tb_order_detail.order_id) t2 
ON datediff(t1.dt,t2.dt) between 0 and 6
left join tb_product_info on tb_product_info.product_id = t2.product_id
group by t1.dt)  t3
left join (
select product_id,date(release_time)  dt
from tb_product_info
where shop_id = 901) t4
on t3.dt >=t4.dt
group by t3.dt
order by dt

1.计算有销售记录的时间（最后结果输出的日期）

2.计算每个日期销售的所有产品和有销售记录的表连接得到国庆3天内每天销售的产品以及每个产品所属的shop_id

3.链接表可以得到901店铺近国庆前3天7日内销售产品的类别数量

4.通过上架时间早于销售时间连接得到901店铺每天上架的总数量