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

根据题意，我们需要筛选三个变量——

• 日期dt
• 每个dt对应的7日之内已售出的商品out_cnt
• 每个dt对应的7日之内已上架的商品on_cnt

  1.dt

  日期dt的选取较为简单粗暴——q1
• 取出日期date(event_time) as dt
• where卡在国庆前三天

  with q1 as 
  (select distinct date(event_time) as dt
  from tb_order_overall
  where date(event_time) between '2021-10-01' and '2021-10-03')

  2.out_cnt

• 所有日期售出商品——q2
  • 三表得到售出商品的售出时间event_dt及商品信息product_id
  • 限定shop_id=901

    select distinct date(event_time) as event_dt,tod.product_id
    from tb_order_detail as tod
    join tb_order_overall as too
    on tod.order_id=too.order_id
    join tb_product_info as tpi
    on tod.product_id=tpi.product_id
    where shop_id=901

• 指定日期的7日内售出商品信息——q1 left join q2
  • q1,q2两表左连接，连接条件为“7日内” datediff(dt,event_dt) between 0 and 6
    易错点：此处不可写为datediff<7,因为会包含负值，即会包含dt后售出的商品信息
• 指定日期7日内售出商品数目——q4
  • 由于商品会多次售卖，应对其去重计数count(distinct product_id)

    select dt,count(distinct product_id) as out_cnt
    from q1
    left join q2
    on datediff(q1.dt,q2.event_dt) between 0 and 6
    group by dt

    3.on_cnt

• 所有日期上架的商品——q3
  • 从第一个表中得上架商品信息product_id及时间release_dt

    select date(release_time) as release_dt,product_id
    from tb_product_info
    where shop_id=901

• 指定日期的7日内售出商品信息——q1 left join q4
  • q1,q4左连接，连接条件为dt>=release_dt
• 指定日期7日内售出商品数目——q5
  • 由于商品会多次售卖，应对其去重计数count(distinct product_id)

    select dt,count(distinct product_id) as on_cnt
    from q1
    left join q3
    on dt>=release_dt
    group by dt

    4.整合——q4 join q5

    完整代码如下：

    with q1 as 
    (select distinct date(event_time) as dt
    from tb_order_overall
    where date(event_time) between '2021-10-01' and '2021-10-03')
    ##↑↑↑↑↑↑↑↑↑↑第一部分q1↑↑↑↑↑↑↑↑↑↑
    select q4.dt,round(out_cnt/on_cnt,3) as sale_rate,
    round(1-out_cnt/on_cnt,3) as unsale_rate
    from
    ##↓↓↓↓↓↓↓↓↓↓第二部分q1+q2——>q4↓↓↓↓↓↓↓↓↓↓
        (select dt,count(distinct product_id) as out_cnt
         from q1
         left join
            (select distinct date(event_time) as event_dt,tod.product_id
             from tb_order_detail as tod
             join tb_order_overall as too
             on tod.order_id=too.order_id
             join tb_product_info as tpi
             on tod.product_id=tpi.product_id
             where shop_id=901) as q2
        on datediff(q1.dt,q2.event_dt) between 0 and 6
        group by dt) as q4
    ##↑↑↑↑↑↑↑↑↑↑第二部分q1+q2——>q4↑↑↑↑↑↑↑↑↑↑
    ##↓↓↓↓↓↓↓↓↓↓第三部分q1+q3——>q5↓↓↓↓↓↓↓↓↓↓
    join
         (select dt,count(distinct product_id) as on_cnt
          from q1
          left join 
              (select date(release_time) as release_dt,product_id
               from tb_product_info
               where shop_id=901) as q3
    on dt>=release_dt
    group by dt) as q5
    ##↑↑↑↑↑↑↑↑↑↑第三部分q1+q3——>q5↑↑↑↑↑↑↑↑↑↑
    on q4.dt=q5.dt