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

# 由于窗口函数不能用distinct，所以在子查询中，通过timestampdiff去记录近七天有销售的distinct 商品种数

select
    dt,
    round(cnt/total_product, 3) 'sale_rate',
    round(1 - cnt/total_product,3) 'unsale_rate'

from 
(
select 
    distinct date (event_time) 'dt',
    (
        select count( distinct if(shop_id != '901', null, d.product_id) )
        from tb_order_detail d 
        join tb_order_overall o on d.order_id = o.order_id
        join tb_product_info info on info.product_id = d.product_id
        where timestampdiff(day, o.event_time, tbo.event_time) between 0 and 6
    ) as cnt,
    (select count(distinct product_id) from tb_product_info where shop_id ='901') 'total_product'
from tb_order_overall tbo
where date(event_time) between '2021-10-01' and '2021-10-03'
) as a
order by dt

1. 通过子查询+COUNT(DISTINCT)计算7天内动销商品数双重过滤确保准确性：
2. 用TIMESTAMPDIFF限定7天时间窗口用IF(shop_id!='901',NULL,...)确保只统计901店铺商品最终计算比率：
3. 用7天动销商品数/总商品数得出动销率和滞销率

参考[小摆锤](https://blog.nowcoder.net/n/745e45ff03234f4b83661569dc3c5965）方案