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

# 3.4 9:05-    826
# 字段：dt,sale_rate,unsale_rate
# 时间：2021.10.01-2021.10.03 7日的动销率/滞销率(9.25-10.3)

# 动销率 = 有销量商品 / 上架总商品数 
# 滞销率 = 没有销量商品 / 上架总商品数

# tb1：链接 
with tb1 as(
select date(event_time) as dt,product_id
from tb_order_detail left join tb_order_overall using(order_id)
left join tb_product_info using(product_id)
where shop_id = 901 AND status =1 
order by dt,product_id
),

tb2 as(
select '2021-10-01' as dt,
# 动销率
round(count(distinct product_id)/(
select count(distinct product_id)
from tb_product_info 
where date(release_time)<=date_sub(date_format('2021-10-01','%Y-%m-%d'),interval 6 day) AND shop_id = 901
) ,3) as sale_rate,
# 滞销率
1-round(count(distinct product_id)/(
select count(distinct product_id)
from tb_product_info 
where date(release_time)<=date_sub(date_format('2021-10-01','%Y-%m-%d'),interval 6 day) AND shop_id = 901
) ,3) as unsale_rate
from tb1
where dt>=date_sub(date_format('2021-10-01','%Y-%m-%d'),interval 6 day) 
AND dt<= date_format('2021-10-01','%Y-%m-%d')
union all 
select '2021-10-02' as dt,
# 动销率
round(count(distinct product_id)/(
select count(distinct product_id)
from tb_product_info 
where date(release_time)<=date_sub(date_format('2021-10-02','%Y-%m-%d'),interval 6 day) AND shop_id = 901
) ,3) as sale_rate,
# 滞销率
1-round(count(distinct product_id)/(
select count(product_id)
from tb_product_info 
where date(release_time)<=date_sub(date_format('2021-10-02','%Y-%m-%d'),interval 6 day) AND shop_id = 901
) ,3) as unsale_rate
from tb1
where dt>=date_sub(date_format('2021-10-02','%Y-%m-%d'),interval 6 day) 
AND dt<= date_format('2021-10-02','%Y-%m-%d')

union all 
# 分日统计：10.03
select '2021-10-03' as dt,
# 动销率
round(count(distinct product_id)/(
select count(product_id)
from tb_product_info 
where date(release_time)<=date_sub(date_format('2021-10-03','%Y-%m-%d'),interval 6 day) AND shop_id = 901
) ,3) as sale_rate,
# 滞销率
1-round(count(distinct product_id)/(
select count(product_id)
from tb_product_info 
where date(release_time)<=date_sub(date_format('2021-10-03','%Y-%m-%d'),interval 6 day)  AND shop_id = 901
) ,3) as unsale_rate
from tb1
where dt>=date_sub(date_format('2021-10-03','%Y-%m-%d'),interval 6 day) 
AND dt<= date_format('2021-10-03','%Y-%m-%d')
)
select * 
from tb2 
# 筛选任意一间店铺有出单记录
where dt in (select date(event_time) from tb_order_overall)
# 动销率为0 如果当天有销售记录则输出，没有记录则不输出