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

明确题意：

计算店铺901在2021年国庆头3天的7日动销率和滞销率。结果保留3位小数，按日期升序排序。只要当天有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例，同理滞销率就是没有销量的商品比例。

问题分解：

• 计算每天的动销率（有销量产品的比例）：
  • 计算国庆期间店铺901截止每天的近7天有销量的商品数（生成子表t_dt_901_pid_cnt）：
    • 统计每个有销量的日期（生成子表t_dates）：
      • 筛选目标时间窗：WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
      • 取去重后日期：DISTINCT DATE(event_time) as dt
    • 统计每天有销量的商品（生成子表t_dt_pid）：
      • 用订单号关联订单总表和明细表：tb_order_overall JOIN tb_order_detail USING(order_id)
      • 统计每天售出的商品：DISTINCT DATE(event_time) as event_dt, product_id
    • 左连接两张表，得到每个有销售的日期近7天售出的商品：t_dates LEFT JOIN t_dt_pid ON DATEDIFF(dt,event_dt) BETWEEN 0 AND 6
    • 继续左连接商品信息，得到店铺id：LEFT JOIN tb_product_info USING(product_id)
    • 按日期分组：GROUP BY dt
    • 统计每天店铺901近7天有销量的商品数：COUNT(DISTINCT IF(shop_id!=901, NULL, product_id)) as sale_pid_cnt
  • 统计店铺901每个商品上架日期（生成子表t_release_dt）：
    • 筛选店铺：WHERE shop_id=901
    • 生成日期：SELECT DATE(release_time) as release_dt, product_id as all_pid
  • 左连接两个表，得到每天已上架在售的商品：t_dt_901_pid_cnt LEFT JOIN t_release_dt ON dt >= release_dt
  • 按日期分组：GROUP BY dt
  • 计算动销率（有销量商品数已计算好，非聚合列，加MIN避免语法错误）：MIN(sale_pid_cnt) / COUNT(all_pid) as sale_rate
  • 保留3位小数：ROUND(x, 3)
• 基于上述结果计算滞销率：1 - sale_rate as unsale_rate

细节问题：

• 表头重命名：as

完整代码：

SELECT dt, sale_rate, 1 - sale_rate as unsale_rate
FROM (
    SELECT dt, ROUND(MIN(sale_pid_cnt) / COUNT(all_pid), 3) as sale_rate
    FROM (
        -- 国庆期间店铺901截止每天的近7天有销量的商品数
        SELECT dt, COUNT(DISTINCT IF(shop_id!=901, NULL, product_id)) as sale_pid_cnt
        FROM (
            SELECT DISTINCT DATE(event_time) as dt
            FROM tb_order_overall
            WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
        ) as t_dates
        LEFT JOIN (
            SELECT DISTINCT DATE(event_time) as event_dt, product_id
            FROM tb_order_overall
            JOIN tb_order_detail USING(order_id)
        ) as t_dt_pid ON DATEDIFF(dt,event_dt) BETWEEN 0 AND 6
        LEFT JOIN tb_product_info USING(product_id)
        GROUP BY dt
    ) as t_dt_901_pid_cnt
    LEFT JOIN (
        -- 店铺901每个商品上架日期
        SELECT DATE(release_time) as release_dt, product_id as all_pid
        FROM tb_product_info
        WHERE shop_id=901
    ) as t_release_dt ON dt >= release_dt # 当天店铺901已上架在售的商品
    GROUP BY dt
) as t_dt_sr;