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

注意点

• 时间限定：国庆节头3天
• 计算指标：近7日动销率（1-滞销率）
  • 因此，实际关注的时间范围是：国庆前6天+国庆头3天
• 核心字段：product_id, order_id, event_time, shop_id, shop_id
• 难点：近七日在售商品识别，已上架商品识别
• 坑点：当天任一店铺有任何商品的销量就输出该天的结果，意味着先有所有商店结果再筛选901店铺

思路

• 合并数据，记录国庆前7天到国庆头3天时段中每件商品的店铺名、上架时间、下单时间
  • 这里以商品信息表为主表，采用左连接另外两张表，保证上架商品数计算不漏
• 合并后的信息表记为tb，自联结生成字段dt存储"2021-10-01"到"2021-10-03"三段时间
  • 这里联结后得到商品id、商铺名、上架时间、下单时间与目标日期（头三天）
• 筛选出901店铺，计算每件商品在目标日期的七日内是否销售，目标日期是否上架。
  • 如果tb中任意店铺该天有商品销售，那么dt则会记录当天
  • 七日内动销，目标日期与下单日期之差在0到6之间则返回1，若存在1则说明近7日动销
  • 上架识别，上架日期比目标日期小即可
• 最后计算国庆头三天的动销商品数除以上架商品数，滞销率为1-动销率
• 保留3位小数，按日期升序

实现代码

with tb as (
    select tpi.product_id,
           date(tpi.release_time) as release_dt,
           date(too.event_time)   as sale_dt,
           shop_id
    from tb_product_info as tpi
             left join tb_order_detail as tod on tod.product_id = tpi.product_id
             left join tb_order_overall too on tod.order_id = too.order_id
    where too.status = 1
      and tpi.release_time < '2021-10-04'
      and too.event_time between '2021-09-25' and '2021-10-04'
)

select dt,
       round(sum(is_sale) / sum(is_shelf), 3)     as sale_rate,
       round(1 - sum(is_sale) / sum(is_shelf), 3) as unsale_rate
from (
         select dt,
                product_id,
                max(if(datediff(dt, sale_dt) between 0 and 6, 1, 0)) as is_sale,
                max(if(release_dt < dt, 1, 0))                       as is_shelf
         from tb as t1
                  join (select sale_dt as dt
                        from tb
                        where sale_dt between '2021-10-01' and '2021-10-04'
                        group by sale_dt) as t2
         where shop_id = 901
         group by t2.dt, product_id
     ) as t
group by dt
order by dt;