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

with t as(
    # 1. 查询国庆前三天日期
    select date(event_time) as dt from tb_order_overall
    where date(event_time) between '2021-10-01' and '2021-10-03'
), t1 as (
    # 2. 店铺901每天上架总商品数 => 笛卡尔积
    select date(event_time) as dt, count(distinct product_id) as sum_cnt
    from tb_product_info, tb_order_overall
    where release_time <= event_time and shop_id=901 and status=1
    group by dt
), t2 as (
    # 3. 店铺901每天有销量的商品 => 内连接
    select distinct date(event_time) as dt, tb_order_detail.product_id
    from tb_order_detail
    inner join tb_order_overall
    on tb_order_overall.order_id = tb_order_detail.order_id and status=1
    inner join tb_product_info
    on tb_order_detail.product_id = tb_product_info.product_id and shop_id=901
)

# 计算近7天的动销率滞销率: 店铺中一段时间(7天)内有销量的商品占当前已上架总商品数的比例(今天)
# 2. 计算店铺国庆头三天的近7天的动销率和滞销率
select t3.dt, round(sale_cnt/sum_cnt,3) as sale_rate,
1-round(sale_cnt/sum_cnt,3) as unsale_rate from(
    # 1. 计算店铺国庆头三天每天的近7天有销量的商品数 => 左连接
    select t.dt, count(distinct t2.product_id) as sale_cnt from t
    left join t2
    on datediff(t.dt,t2.dt) between 0 and 6
    group by t.dt
)t3
left join t1
on t1.dt = t3.dt
order by t3.dt

难！连接晕眩！