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

with 
    t1 as(
        #筛选出有销售记录的日期
        select 
            distinct
            date(event_time) `dt`
        from tb_order_overall 
        where date_format(event_time,'%Y-%m-%d') between '2021-10-01' and '2021-10-03'
    ),
    t2 as(
        #901商店已经成交的订单，每个下单日期中的product_id
        select
            date(event_time) `dt`,
            tod.product_id
        from tb_order_detail tod
        join tb_order_overall too 
        on tod.order_id=too.order_id and status=1
        join tb_product_info tpi
        on tod.product_id =tpi.product_id and shop_id='901'   
    ),
    t3 as(
        #计算每个日期下901商店的在售商品总数
        select 
            date(event_time) `dt`,
            count(distinct case when datediff(date(event_time),date(release_time))>=0 then product_id end) `sum_product`
        from tb_product_info,tb_order_overall
        where shop_id='901'
        group by dt
    )

    select 
        t1.dt,
        round(count(distinct t2.product_id)/t3.sum_product,3) as sale_rate,
        round(1-count(distinct t2.product_id)/t3.sum_product,3) as unsale_rate
    from t1 
    #关键一步 如果当天存在销量就要输出有结果
    left join t2 
    #也很关键 就是让每个日期都和记录join一下（符合条件）
    on datediff(t1.dt,t2.dt) between 0 and 6
    join t3
    on t1.dt=t3.dt
    group by t1.dt
    order by t1.dt