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

# 窗口函数不能用DISTINCT，只能用子查询搜索和每天相近七天有销量的商品数目!!!!!!!!!!
select
    dt,
    round(sales_amount/total_amount, 3) as sale_rate,
    round(1-sales_amount/total_amount, 3) as unsale_rate
from (
    select distinct
        date(event_time) as dt,
        (
            select
                count(distinct(t1.product_id))
            from tb_order_detail t1 
            left join tb_order_overall t2 on t1.order_id=t2.order_id
            left join tb_product_info t3 on t1.product_id=t3.product_id
            where shop_id = 901 and release_time <= event_time and timestampdiff(day, event_time, too.event_time) between 0 and 6
        )as sales_amount,
        (
            select
                count(product_id)
            from tb_product_info
            where shop_id = 901
        ) as total_amount
    from tb_order_overall too
    where date(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
)as total
order by dt asc