WITH t1 AS ( -- 公共宽表
    SELECT
        a.order_id,
        a.product_id,
        a.cnt,
        b.uid,
        a.price * a.cnt AS less_amount,
        b.event_time,
        b.total_amount,
        b.status,
        c.shop_id,
        c.release_time
    FROM tb_order_detail a
    LEFT JOIN tb_order_overall b ON a.order_id = b.order_id
    LEFT JOIN tb_product_info c ON a.product_id = c.product_id
    WHERE status = 1
),
t2 AS ( -- 2021-09-25 到 2021-10-01 901上架产品
    SELECT
           count(distinct product_id) AS total_one
    FROM tb_product_info
    where DATE(release_time) <= '2021-10-01'
    AND shop_id = 901
),
t3 AS ( -- 2021-09-26 到 2021-10-02 901上架产品
    SELECT
           count(distinct product_id) AS total_two
    FROM tb_product_info
    where DATE(release_time) <= '2021-10-02'
    AND shop_id = 901
),
t4 AS ( -- 2021-09-27 到 2021-10-03 901上架产品
    SELECT
           count(distinct product_id) AS total_three
    FROM tb_product_info
    where DATE(release_time) < '2021-10-03'
    AND shop_id = 901
),
t5 AS (-- 901 第一阶段销售
    SELECT count(distinct product_id) AS buy_one,
           MAX(total_one) AS total_one
    FROM t1
    JOIN t2
    WHERE DATE(event_time) BETWEEN '2021-09-25' AND '2021-10-01'
    AND shop_id = 901
    AND cnt > 0
),
t6 AS (
    -- 901 第二阶段销售
    SELECT count(distinct product_id) AS buy_two,
           MAX(total_two) AS total_two
    FROM t1
    JOIN t3
    WHERE DATE(event_time) BETWEEN '2021-09-26' AND '2021-10-02'
    AND shop_id = 901
    AND cnt > 0
),
t7 AS (
    -- 901 第三阶段销售
    SELECT count(distinct product_id) AS buy_three,
           MAX(total_three) AS total_three
    FROM t1
    JOIN t4
    WHERE DATE(event_time) BETWEEN '2021-09-27' AND '2021-10-03'
    AND shop_id = 901
    AND cnt > 0
),
t8 AS (
    -- 所有商店第一阶段
    SELECT count(distinct product_id) AS buy_all_one
    FROM t1
    WHERE DATE(event_time) = '2021-10-01'
    AND cnt >0
),
t9 AS (
    -- 所有商店第二阶段
    SELECT count(distinct product_id) AS buy_all_two
    FROM t1
    WHERE DATE(event_time) = '2021-10-02'
    AND cnt > 0
),
t10 AS (
    -- 所有商店第三阶段
    SELECT count(distinct product_id) AS buy_all_three
    FROM t1
    WHERE DATE(event_time) = '2021-10-03'
    AND cnt > 0
),
t11 AS (
    SELECT * FROM t5
    JOIN t8
),
t12 AS (
    SELECT * FROM t6
    JOIN t9
),
t13 AS (
    SELECT * FROM t7
    JOIN t10
),
t14 AS (
    SELECT IF(buy_all_one = 0, 'Miss' ,'2021-10-01')    AS dt,
           ROUND(IFNULL(buy_one / total_one, 0), 3)     AS sale_rate,
           1 - ROUND(IFNULL(buy_one / total_one, 0), 3) AS unsale_rate
    FROM t11
    UNION ALL
    SELECT IF(buy_all_two = 0, 'Miss' ,'2021-10-02')    AS dt,
           ROUND(IFNULL(buy_two / total_two, 0), 3)     AS sale_rate,
           1 - ROUND(IFNULL(buy_two / total_two, 0), 3) AS unsale_rate
    FROM t12
    UNION ALL
    SELECT IF(buy_all_three = 0, 'Miss' ,'2021-10-03')      AS dt,
           ROUND(IFNULL(buy_three / total_three, 0), 3)     AS sale_rate,
           1 - ROUND(IFNULL(buy_three / total_three, 0), 3) AS unsale_rate
    FROM t13
)
SELECT * FROM t14
WHERE dt != 'Miss';

# 这道题的关键在于 “只要当天任一店铺有任何商品的销量就输出该天的结果” ，这里的当天是指1-2-3三天的时间！