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

#思路：主要从下面三个步骤进行思考
select dt,
    round(if(n2 is null,0,n2)/n1,3) as sale_rate,
    1-round(if(n2 is null,0,n2)/n1,3) as unsale_rate
from(
    #1、根据上架时间确定截止当天在售的商品种数
    select date(event_time) as dt,
        count(distinct product_id) as n1
    from (
        select event_time
        from tb_order_overall
        where date(event_time) between '2021-10-01' and '2021-10-03'
    ) as tab1
    left join(
        select release_time,
            product_id
        from tb_product_info
        where shop_id = 901
    ) as tab2 on timestampdiff(second,release_time,event_time) >= 0
    group by date(event_time)
) as tab5
#3、将得到的上架商品总数表tab5与901店铺的销售情况表tab6用left join连接在一起
#这里用left join主要考虑店铺901当天的动销率为0的情况
left join(
    #2、使用left join 确定店铺901在10-01到10-03各自七天内有销售的商品
    select date(event_time) as dt,
        count(distinct product_id) as n2
    from (
        select event_time
        from tb_order_overall
        where date(event_time) between '2021-10-01' and '2021-10-03'
    ) as tab3
    left join(
        select date(event_time) as dt,
            product_id
        from tb_order_detail
        inner join tb_order_overall using(order_id)
        inner join tb_product_info using(product_id)
        where shop_id = 901
    ) as tab4 on timestampdiff(day,tab4.dt,date(tab3.event_time)) between 0 and 6
    group by date(event_time)
) as tab6 using(dt)