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

-- 为每个商品的订单记录join 2021-10-01、2021-10-02、2021-10-03三天生成笛卡尔积
-- 根据是否七天内有订单记录生成is_sale字段，根据发布日期和dt生成is_shelf字段
-- 聚合求值
with v1 as (
    select
            tpi.product_id ,
            too.order_id,
            date(release_time) as release_dt,
            date(event_time) as sale_dt,
            shop_id,
            tmp.dt
    from tb_product_info tpi 
    left join tb_order_detail tod on tpi.product_id = tod.product_id 
    left join tb_order_overall too on too.order_id = tod.order_id 
    join (select distinct date(event_time) as dt 
            from tb_order_overall 
            where date(event_time) between '2021-10-01' and '2021-10-03'
            ) tmp -- 笛卡尔积
    where release_time <= '2023-10-03' 
    and date(event_time) between '2021-09-25' and '2021-10-03' 
    and status = 1
)
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 v1 
    where shop_id = 901
    group by dt, product_id 
) t 
group by dt
order by dt
;