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

/* 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
 */
WITH
    tb AS (
        SELECT
            tpi.product_id product_id,
            tpi.shop_id shop_id,
            DATE(tpi.release_time) release_time,
            DATE(too.event_time) sale_time
        FROM
            tb_order_overall too
            JOIN tb_order_detail tod USING (order_id)
            JOIN tb_product_info tpi USING (product_id)
        WHERE
            release_time <= '2021-10-03'
            AND DATE(event_time) BETWEEN '2021-09-25' AND '2021-10-03'
            AND status = 1
    ),
    dt_tb AS (
        SELECT -- 准备时间表
            DATE(event_time) dt
        FROM
            tb_order_overall
        WHERE
            DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
        GROUP BY
            1
    )
SELECT dt,
    ROUND(SUM(is_sale)/SUM(is_shelf),3) sale_rate,
    ROUND(1-SUM(is_sale)/SUM(is_shelf),3) unsale_rate
FROM
    (
        SELECT
            dt,
            product_id,
            MAX(IF(DATEDIFF(dt, sale_time) BETWEEN 0 AND 6, 1, 0)) is_sale,
            MAX(IF(release_time <= dt, 1, 0)) is_shelf
        FROM
            tb t1
            JOIN dt_tb t2
        WHERE
            shop_id = 901
        GROUP BY
            1,
            2
    ) CO
    GROUP BY 1
    ORDER BY 1