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

SELECT dt, ROUND(SUM(if(nn, 1,0))/summ,3)sale_rate,1- ROUND(SUM(if(nn, 1,0))/summ,3) AS unsale_rate
FROM (
SELECT DISTINCT DATE(b.event_time)dt, if(d.shop_id=901, c.product_id, NULL)nn, (
SELECT COUNT(DISTINCT product_id)
FROM tb_order_detail
INNER JOIN tb_product_info USING(product_id)
WHERE shop_id =901)summ
FROM tb_order_overall a
INNER JOIN tb_order_overall b
LEFT JOIN tb_order_detail c ON a.order_id = c.order_id
LEFT JOIN tb_product_info d ON c.product_id = d.product_id AND d.shop_id =901
WHERE DATE(a.event_time) BETWEEN DATE_SUB(DATE(b.event_time), INTERVAL 6 DAY) AND DATE(b.event_time)
)tt
WHERE dt BETWEEN "2021-10-01" AND "2021-10-03"
GROUP BY dt
ORDER BY dt