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

select dt, coalesce(sum(sale_rate),0.000) as sale_rate, coalesce(sum(unsale_rate),1.000) as unsale_rate
from (
select  dt_group as dt,
    shop_id,
    round(count(distinct if(shop_id='901', t1.product_id, null)) / if(shop_id='901',t1.ccnntt,null),3) as sale_rate,
    round(1-count(distinct if(shop_id='901', t1.product_id, null)) / if(shop_id='901',t1.ccnntt,null),3) as unsale_rate
from
(
select a.product_id,c.cnt,a.shop_id
    ,date_format(b.event_time,'%Y-%m-%d') as dt_0
    ,ccnntt
from tb_order_overall b 
 join tb_order_detail c 
on b.order_id = c.order_id
and date_format(b.event_time,'%Y-%m-%d') between '2021-09-25' and '2021-10-03'
 join (select product_id,shop_id,count(1) over(partition by shop_id) as ccnntt from tb_product_info
) a
on a.product_id = c.product_id
) t1
left join (
    select distinct date_format(event_time,'%Y-%m-%d') as dt_group
    from tb_order_overall
    where date_format(event_time,'%Y-%m-%d') between '2021-10-01' and '2021-10-03'
) t2
on t1.dt_0 >= date_sub(t2.dt_group,Interval 6 day) and t1.dt_0 <= t2.dt_group
group by dt_group,t1.ccnntt,shop_id
)d  group by dt
order by dt

题目本身没啥，关键就是各种小需求给人使绊子，做的让人窝火；

值得注意的是，当你想用count(distinct) 开窗无果的时候，应该考虑用笛卡尔放大，多出来的一列作为group