题解 | #常规解法 通俗易懂#

select -- 最后根据日期进行汇总
     t1.dt
    ,round(ifnull(count(distinct t2.product_id)/t3.release_cnt, 0), 3) as sale_rate
    ,round(1 - ifnull(count(distinct t2.product_id)/t3.release_cnt, 0), 3) as unsale_rate
from ( -- 选出2021-10-01至2021-10-03三天中有销售数据的日期
    select distinct
        date_format(event_time, '%Y-%m-%d') as dt
    from tb_order_overall
    where date_format(event_time, '%Y-%m-%d') between '2021-10-01' and '2021-10-03'
) as t1
left join ( -- 找出901店铺在2021-09-25到2021-10-03之间售卖的产品，根据日期差，划分到10-01到10-03
    select distinct
         t1.product_id
        ,date_format(t2.event_time, '%Y-%m-%d') as dt
    from tb_order_detail as t1
    left join tb_order_overall as t2
        on t1.order_id = t2.order_id
    left join tb_product_info as t3
        on t1.product_id = t3.product_id
    where t2.status != 2 
        and date_format(t2.event_time, '%Y-%m-%d') between '2021-09-25' and '2021-10-03'
        and t3.shop_id = 901
) as t2 on datediff(t1.dt, t2.dt) between 0 and 6
left join ( -- 使用拉链表计算不同日期区间的商品发布数，不同的日期区间对应不同的商品发布数
    select
         dt as start_dt
        ,ifnull(lead(dt, 1) over(order by dt), '9999-12-31') as end_dt
        ,sum(release_cnt) over(order by dt) as release_cnt 
    from (
        select
            shop_id
            ,date_format(release_time, '%Y-%m-%d') as dt
            ,count(1) as release_cnt
        from tb_product_info
        where shop_id = 901
        group by
            shop_id
            ,dt
    ) as t1
) as t3 on t1.dt >= t3.start_dt
    and t1.dt < t3.end_dt
group by
     t1.dt
    ,t3.release_cnt
order by
    t1.dt