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

select dt,round(count(distinct product_id)/onsale_cnt,3) as sale_rate,
1 - round(count(distinct product_id)/onsale_cnt,3) as unsale_rate
from
(SELECT DATE(event_time) dt,COUNT( DISTINCT CASE WHEN event_time>=release_time THEN product_id END) onsale_cnt
	FROM tb_order_overall,tb_product_info WHERE shop_id='901' AND DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03' GROUP BY dt) as t1
LEFT JOIN (
	SELECT DATE(event_time)ddt,product_id FROM tb_order_overall
	LEFT JOIN tb_order_detail USING(order_id)
	LEFT JOIN tb_product_info USING(product_id) WHERE shop_id='901'
	AND DATEDIFF('2021-10-03',DATE(event_time)) BETWEEN 0 AND 8
) t2 ON DATEDIFF(dt,ddt) BETWEEN 0 AND 6
group by dt
order by dt

先统计在货架上的商品数量，然后统计国庆前三天的七天内每天的售出商品，

连接两个表的条件是日期差在7天之内，然后按照日期分组排序即可，

比较难的地方就在于两张表连接的条件