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

with date_list as(
  select
    distinct date(event_time) dt,
    DATE_ADD(date(event_time), INTERVAL -6 DAY) dt_7d_before
  from
    tb_order_overall
  where
    event_time between '2021-10-01 00:00:00' and '2021-10-03 23:59:59'
),huopin_info as(
    select shop_id,count(distinct product_id) shangpin_num
    from
    tb_product_info
    where shop_id=901
    group by shop_id
),
info as(
  select
    a.order_id,
    a.product_id,
    date(event_time) event_dt,
    shop_id
  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
    shop_id = 901
)
select a.dt,case when xs is null then 0.000 else xs end,case when xs is null then 1.000 else 1-xs end
from date_list a
left join
(select dt,round(count(distinct product_id)/min(shangpin_num),3) xs
from(
select
  *,
  CASE
    WHEN event_dt between dt_7d_before and dt then 1
  end if_count
from
  date_list join info)t1
  left join huopin_info on huopin_info.shop_id = t1.shop_id
  where if_count=1
  group by 1)t2
  on a.dt=t2.dt
  order by 1