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

• 方法思路是利用DENSE_RANK()代替COUNT（DISTINCT ），问题在于如果当天没有数据，那么结果将不显示，如在结果测试中，10-2号附近的销售量为0，结果不输出。

WITH t1 AS
(
SELECT product_id
        , '2021-10-01' AS dt
        , DENSE_RANK() OVER(ORDER BY product_id )+DENSE_RANK() OVER(ORDER BY product_id DESC)-1 AS sale
        , (SELECT COUNT(DISTINCT product_id)
            FROM tb_product_info
            WHERE shop_id = 901) AS total_sales
FROM tb_product_info
JOIN tb_order_detail USING(product_id)
JOIN tb_order_overall USING(order_id)
WHERE DATEDIFF('2021-10-01', event_time) BETWEEN 0 AND 6 AND shop_id = 901

UNION ALL
SELECT product_id
        , '2021-10-02' AS dt
        , DENSE_RANK() OVER(ORDER BY product_id )+DENSE_RANK() OVER(ORDER BY product_id DESC)-1 AS sale
        , (SELECT COUNT(DISTINCT product_id)
            FROM tb_product_info
            WHERE shop_id = 901) AS total_sales
FROM tb_product_info
JOIN tb_order_detail USING(product_id)
JOIN tb_order_overall USING(order_id)
WHERE DATEDIFF('2021-10-02', event_time) BETWEEN 0 AND 6 AND shop_id = 901

UNION ALL
SELECT product_id
        , '2021-10-03' AS dt
        , DENSE_RANK() OVER(ORDER BY product_id )+DENSE_RANK() OVER(ORDER BY product_id DESC)-1 AS sale
        , (SELECT COUNT(DISTINCT product_id)
            FROM tb_product_info
            WHERE shop_id = 901) AS total_sales
FROM tb_product_info
JOIN tb_order_detail USING(product_id)
JOIN tb_order_overall USING(order_id)
WHERE DATEDIFF('2021-10-03', event_time) BETWEEN 0 AND 6 AND shop_id = 901
)

SELECT dt
        , ROUND(AVG(sale/total_sales), 3) AS sale_rate
        , ROUND(1 - AVG(sale/total_sales), 3) AS unsale_rate
FROM t1
GROUP BY dt