题解 | #店铺901国庆期间的7日动销率和滞销率#
店铺901国庆期间的7日动销率和滞销率
http://www.nowcoder.com/practice/e7837f66e8fb4b45b694d24ea61f0dc9
- 感觉这六道题做了很久，每道题都很折磨。。。。。。
- 这道题我本来思路是想直接用窗口函数统计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;
