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

思路： 反向推导， 正向执行； 步骤拆解

反向推导

1. 计算店铺901国庆期间（前三天）的7日动销率和滞销率， 根据公式， 滞销率 = 1 - 动销率， 因此只需要计算出动销率， 根据动销率的公式， 可以拆解为两个指标， 一是国庆期间近七天出售的商品数， 二是截止国庆期间上架的商品数
2. 计算国庆期间近七天出售的商品数， 又可以分为两步， 一是获取国庆期间近七天的order， 二是根据order_id计算国庆期间近七天出售的商品数
3. 计算截止国庆期间上架的商品数， 只需要统计上架时间在国庆前三天之前的商品数

正向执行

1. 首先准备一个国庆前三天的日期表， 作为最终结果输出的时间维度和计算过程中的日期参照对象
2. 利用商品表计算国庆前三天为止， 店铺901上架的商品数
3. 根据订单表， 筛选出国庆前三天的近七天order_id， 再结合order详情表和商品表， 统计出国庆前三天店铺901销售的商品数
4. 最后， 用事前准备的日期表， 汇聚上架的商品数和销售的商品数， 并计算动销率和滞销率

两个关键点

1. 筛选截止国庆前三天的上架商品， 有两个节点， 一是每个商品的上架日期， 二是国庆前三天的日期， 限定条件为前者不大于后者即可
2. 筛选国庆前三天的近七天订单， 也有两个节点， 一是每个订单的日期， 二是国庆前三天的日期， 后者与前者的差控制在[0, 6]即可

     select f.dt,
            round(ifnull(product_cnt_sale / product_cnt_in, 0), 3) as sale_rate,
            round(1 - ifnull(product_cnt_sale / product_cnt_in, 0), 3) as unsale_rate
       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 f -- 最终输出的日期
  left join (
            select dt,
                   count(distinct product_id) as product_cnt_in
              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 a 
          left join tb_product_info as b 
                 on a.dt >= b.release_time
                    and b.shop_id = 901
           group by dt 
  ) as g -- 截止国庆前三天的上线产品数量
         on f.dt = g.dt 
  left join (
                select dt,
                       count(distinct product_id) as product_cnt_sale
                  from (
                         select dt, 
                                order_id
                           from tb_order_overall as c 
                      left join (
                                select distinct date(event_time) as dt
                                  from tb_order_overall
                                 where date(event_time) between "2021-10-01" and "2021-10-03"
                      ) as d 
                             on datediff(dt, date(event_time)) between 0 and 6
                  ) as e -- 国庆前三天近7天的order_id
                  join tb_order_detail using(order_id)
                  join tb_product_info using(product_id)
                 where shop_id = 901
              group by dt
  ) as h -- 国庆前三天近七天有销量的产品数量
         on f.dt = h.dt 
   order by dt