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

#需求：计算店铺901在2021年国庆头3天的7日动销率和滞销率
#输出：dt、sale_rate、unsale_rate
#要求：输出时间为1号-3号，取值区间为9月25号-10月3号；shop_id=901；输出指标保留三位小数，按日期升序
#动销率=有销量的商品/已上架总商品数，滞销率=没有销量的商品/已上架总商品数，涉及三个指标
#需要用到的数据：shop_id、product_id、event_time
#拆分：动销率与滞销率：假设动销率=x/y，则滞销率=1-x/y，即只需要求得有销量的商品和已上架总商品数两个参数即可
#时间：有两次对时间的筛选，一次是计算指标时对参数时间区间的过滤，二是输出时对输出dt的区间限制
select dt,round(e1/e2,3) sale_rate,round(1-e1/e2,3) unsale_rate
from(
    select distinct date(event_time) dt,
    (
        select count(distinct product_id)
        from tb_order_overall
        join tb_order_detail using(order_id)
        join tb_product_info using(product_id)
        where timestampdiff(day,event_time,w1.event_time) between 0 and 6 and shop_id=901
        #这里timestampdiff的第一个time参数是表链接后的新表内的时间，第二个time参数是tb_order_overall表内的时间
        #这一步结合外层，是做关联子查询；对于外部查询返回的每一行数据，内部查询都要执行一次
        #逻辑为先选出10-01，执行一次子查询，筛选出09-25——10-01的日期，执行完毕回到外层；再选出10-02，筛选出09-26——10-02的日期，以此类推
    ) e1,
    #e1得到901店铺内，与dt日期关联的7日内，有销量的商品ID数，只要数，所以去重
    (
        select count(distinct product_id)
        from tb_product_info
        where shop_id=901
    ) e2
    #e2得到订单总表中901店内有销量的商品ID数
    from tb_order_overall w1
    where date(event_time) between '2021-10-01' and '2021-10-03'
    #限定输出dt的时间范围
) w2
order by 1