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

明确题意：

请计算店铺901在2021年国庆头3天的7日动销率和滞销率

问题分解：

关联商品信息表和订单总表：from tb_product_info, tb_order_overall

筛选店铺901在2021年国庆头3天的记录：where (date(event_time) between '2021-10-01' and '2021-10-03') and shop_id = 901

按dt分组：group by dt1

计算已上架总商品数：count(distinct case when event_time >= release_time then product_id end)

关联订单总表和订单明细表：left join tb_order_detail tod on too.order_id = tod.order_id

关联订单明细表和商品信息表：left join tb_product_info tpi on tod.product_id = tpi.product_id

筛选店铺901在9.26-10.3日有销量的商品记录：where (datediff('2021-10-03',date(event_time)) between 0 and 8) and shop_id = 901

关联已上架商品信息和有销量商品信息：t1 left join t2 on datediff(dt1,dt2) between 0 and 6

按dt分组：group by dt

计算动销率：count(distinct product_id) / sale_total

计算滞销率：1 - 动销率

细节问题：

结果保留3位小数：round(x,3)

按日期升序排序：order by dt

select
    dt1 dt,
    round(count(distinct product_id) / sale_total, 3) sale_rate,
    round(1 - count(distinct product_id) / sale_total, 3) unsale_rate	
from(
select
    date(event_time) dt1,
    count(distinct case when event_time >= release_time then product_id end) sale_total
from tb_product_info, tb_order_overall
where (date(event_time) between '2021-10-01' and '2021-10-03') and shop_id = 901
group by dt1
) t1
left join(
select
    date(event_time) dt2,
    tod.product_id
from tb_order_overall too
left join tb_order_detail tod on too.order_id = tod.order_id
left join tb_product_info tpi on tod.product_id = tpi.product_id
where (datediff('2021-10-03',date(event_time)) between 0 and 8) and shop_id = 901
) t2 on datediff(dt1,dt2) between 0 and 6
group by dt
order by dt;