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

这题要多做几次

此题共包含三张表：

表1：tb_product_info

表2：tb_order_overall

表3：tb_order_detail

要解决的问题：

问题：请计算店铺901在2021年国庆3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序

解题思路： 本来想用窗口函数取近7天的次数，但是不能去重！ 用union写了一种特别麻烦的解法。。。 看到有大佬这么写，先放在这里，慢慢学习思路

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;