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

• 感觉这六道题做了很久，每道题都很折磨。。。。。。
• 这道题我本来思路是想直接用窗口函数统计10-01到10-03每天最近7日有销量的商品数目，然后发现窗口函数不能用DISTINCT，只能用子查询搜索和每天相近七天有销量的商品数目。
• 第一个子查询对应查询每条record相距最近七天的产品，去重后输出
• 第二个子查询对应查询整体有多少个独特的商品（DISTINCT product_id）
• 欢迎大家指正！

SELECT dt, ROUND(cnt / total_cnt, 3) AS sale_rate, ROUND(1 - cnt / total_cnt, 3) AS unsale_rate
FROM
(
	SELECT DISTINCT
		DATE(event_time) AS dt,
		(
			SELECT COUNT(DISTINCT (IF(shop_id != 901, null, product_id)))
			FROM tb_order_overall
			JOIN tb_order_detail USING (order_id)
			JOIN tb_product_info USING (product_id)
			WHERE TIMESTAMPDIFF(DAY, event_time, to1.event_time) BETWEEN 0 AND 6
		) AS cnt,
		(
			SELECT COUNT(DISTINCT product_id)
			FROM tb_product_info
			WHERE shop_id = 901
		) AS total_cnt
	FROM tb_order_overall to1
	WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
) AS t0
ORDER BY dt;