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

平台有个案例通不过，但是sql server 通过了，我不理解

SELECT T1.dt, ROUND(sold_num_7_days/onsell_num,3) sale_rate, 
1-ROUND(sold_num_7_days/onsell_num,3) unsale_rate
FROM
(SELECT dt, COUNT(DISTINCT product_id) sold_num_7_days FROM 
 (SELECT DISTINCT DATE(event_time) dt FROM tb_order_overall
  WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03') t1
 LEFT JOIN
 (SELECT DATE(event_time) every_dt, product_id FROM tb_order_detail 
  JOIN tb_order_overall ON tb_order_detail.order_id=tb_order_overall.order_id
  WHERE status=1 AND product_id IN 
  (SELECT product_id FROM tb_product_info WHERE shop_id= 901)) t2
 ON DATEDIFF(dt,every_dt) BETWEEN 0 AND 6
 GROUP BY dt) T1
JOIN
(SELECT dt, COUNT(product_id) onsell_num FROM 
 (SELECT DISTINCT DATE(event_time) dt FROM tb_order_overall
  WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03') t1
 LEFT JOIN
 (SELECT DATE(release_time) rl_dt, product_id FROM tb_product_info
 WHERE shop_id=901) t2
 ON dt>=rl_dt
 GROUP BY dt) T2
ON T1.dt=T2.dt
ORDER BY T1.dt

（1）参考题解区webary的类似思路。T1表记录的是10-1到10-3的近七天有销量的商品种数，T2表记录的是10-1到10-3的截止当天在售商品数。T1和T2表连接后，用公式计算即可。

（2）在T1表的构建中，可以用DATEDIFF(dt,every_dt) BETWEEN 0 AND 6左连接，然后按照dt（即10-01，10-02，10-03）分组计数的方式获得近七天有销量的商品种数。在T2表的构建中，可以用dt>=rl_dt左连接然后按照dt（即10-01，10-02，10-03）分组计数的方式获得截止当天在售商品数。

（3）离谱！我debug半天，推测可能是上面代码的product_id IN (SELECT product_id FROM tb_product_info WHERE shop_id= 901)这个条件没有起到作用。果真。我改成 product_id IN (8001,8002,8003)测试，代码就可以通过了。。。简直无语。

最后代码写成了和tb_product_info内连接后加条件的方式。-_-

上下两种方式的写法难道不是等价的吗！（若有同学看出来了上面写法错在哪里麻烦告诉我，心累）

SELECT T1.dt, ROUND(sold_num_7_days/onsell_num,3) sale_rate, 
1-ROUND(sold_num_7_days/onsell_num,3) unsale_rate
FROM
(SELECT dt, COUNT(DISTINCT product_id) sold_num_7_days FROM 
(SELECT DISTINCT DATE(event_time) dt FROM tb_order_overall
WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03') t1
LEFT JOIN
(SELECT DATE(event_time) every_dt, tb_order_detail.product_id FROM tb_order_detail 
JOIN tb_order_overall ON tb_order_detail.order_id=tb_order_overall.order_id
JOIN tb_product_info
ON tb_product_info.product_id=tb_order_detail.product_id
WHERE status=1 AND shop_id= 901) t2
ON DATEDIFF(dt,every_dt) BETWEEN 0 AND 6
GROUP BY dt) T1
JOIN
(SELECT dt, COUNT(product_id) onsell_num FROM 
(SELECT DISTINCT DATE(event_time) dt FROM tb_order_overall
WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03') t1
LEFT JOIN
(SELECT DATE(release_time) rl_dt, product_id FROM tb_product_info
WHERE shop_id=901) t2
ON dt>=rl_dt
GROUP BY dt) T2
ON T1.dt=T2.dt
ORDER BY T1.dt