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

# 7日动销率 = 有销量的商品/已上架总商品数  
# 7日动销率 = 没有销量的商品/已上架总商品数 

with t1 as (
# 去重 当日有订单的产品就算卖出
select   
distinct  date(event_time) dt , product_id  
from tb_order_overall  
join tb_order_detail using(order_id)    
),

t2 as (
# 取国庆前三日期
select DISTINCT dt  from t1 where dt >= '2021-10-01'  and dt <= '2021-10-03'  

),

t3 as (
# 核心步骤 join 三天的日期 制作分组标签列
select product_id,b.dt  
    from t1 a right join t2 b on  DATEDIFF(b.dt,a.dt) between 0 and 6  
),

t4 as (
# 商品总数量 辅助表
select 
    product_id,   count(product_id) over(partition by shop_id) product_id_count 
    from tb_product_info 
    where shop_id = 901    

),
t5 as (
# 计算滞销比 注意过滤null值
select dt, 
     round( count(DISTINCT if(product_id_count is null,null,product_id) )/max(product_id_count),3) sale_rate ,
    round(1-count(DISTINCT if(product_id_count is null,null,product_id) )/max(product_id_count),3) unsale_rate


    from t3 left join t4 using(product_id) 
    # where product_id_count is not null  
    group by dt order by dt

    )
# 最后 格式化报表输出
select dt,
if( sale_rate is null,0.000,sale_rate), 
round(if( unsale_rate is null,1.000,unsale_rate),3) unsale_rate
from t5 order by dt