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

with inshop as(
    select product_id
    from tb_product_info 
    where shop_id = 901
)
, tb as(
    select order_id, product_id, date(event_time) dt
    from tb_order_overall too
    right join tb_order_detail tod 
    using(order_id)
    where product_id in (select product_id from inshop)
)
select * from (
    select '2021-10-01', round(count(distinct product_id)/(select count(*) from inshop), 3) as v, round(1 - count(distinct product_id)/(select count(*) from inshop), 3)from tb where dt between '2021-09-25' and '2021-10-01' having EXISTS (SELECT * FROM tb_order_overall WHERE DATE(event_time) = '2021-10-01')
    union
    select '2021-10-02', round(count(distinct product_id)/(select count(*) from inshop), 3) v, round(1 - count(distinct product_id)/(select count(*) from inshop), 3)from tb where dt between '2021-09-26' and '2021-10-02' having EXISTS (SELECT * FROM tb_order_overall WHERE DATE(event_time) = '2021-10-02')
    union
    select '2021-10-03', round(count(distinct product_id)/(select count(*) from inshop), 3) v, round(1 - count(distinct product_id)/(select count(*) from inshop), 3)from tb where dt between '2021-09-27' and '2021-10-03' having EXISTS (SELECT * FROM tb_order_overall WHERE DATE(event_time) = '2021-10-03')
) a 

不讲武德暴力破解！遇到这种讨厌的滑动窗口，天数少的话都可以这样干，这里是三天，写了一个的逻辑复制三个就行。

这道题我学到了having (select count(*) from tb_order_overall where date(event_time) = '2021-10-02') a > 0是不行的，不能直接跟子查询和判断。

having exists (select * from tb_order_overall WHERE DATE(event_time) = '2021-10-03')才可以