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

select *
from(
select '2021-10-01' as "dt", round(count(1)/(
                select count(1)  # 截止到10-01，在售商品数
                from tb_product_info
                where date(release_time) <= '2021-10-01'
                and shop_id = 901
                ),3) as "sale_rate", (1-round(count(1)/( 
                select count(1)  # 截止到10-01，在售商品数
                from tb_product_info
                where date(release_time) <= '2021-10-01'
                and shop_id = 901
                ),3)) as "unsale_rate"
from(
select count(1),tod.product_id
from tb_order_overall too join tb_order_detail tod
on too.order_id = tod.order_id
join tb_product_info tpi
on tod.product_id = tpi.product_id
where event_time between '2021-09-25' and '2021-10-02'
and status = 1
and total_cnt > 0
and tpi.shop_id = 901
group by tod.product_id
)t1
union all
select '2021-10-02' as "dt", round(count(1)/(
                select count(1)  # 截止到10-01，在售商品数
                from tb_product_info
                where date(release_time) <= '2021-10-02'
                and shop_id = 901
                ),3) as "sale_rate", (1-round(count(1)/( 
                select count(1)  # 截止到10-01，在售商品数
                from tb_product_info
                where date(release_time) <= '2021-10-02'
                and shop_id = 901
                ),3)) as "unsale_rate"
from(
select count(1),tod.product_id
from tb_order_overall too join tb_order_detail tod
on too.order_id = tod.order_id
join tb_product_info tpi
on tod.product_id = tpi.product_id
where event_time between '2021-09-26' and '2021-10-03'
and status = 1
and total_cnt > 0
and tpi.shop_id = 901
group by tod.product_id
)t2
union all
select '2021-10-03' as "dt", round(count(1)/(
                select count(1)  # 截止到10-01，在售商品数
                from tb_product_info
                where date(release_time) <= '2021-10-03'
                and shop_id = 901
                ),3) as "sale_rate", (1-round(count(1)/( 
                select count(1)  # 截止到10-01，在售商品数
                from tb_product_info
                where date(release_time) <= '2021-10-03'
                and shop_id = 901
                ),3)) as "unsale_rate"
from(
select count(1),tod.product_id
from tb_order_overall too join tb_order_detail tod
on too.order_id = tod.order_id
join tb_product_info tpi
on tod.product_id = tpi.product_id
where event_time between '2021-09-27' and '2021-10-04'
and status = 1
and total_cnt > 0
and tpi.shop_id = 901
group by tod.product_id
)t3
)final_t
where dt in (
    select date(event_time)
    from tb_order_overall
)
order by dt 


# select count(1), tod.product_id
# from tb_order_overall too join tb_order_detail tod
# on too.order_id = tod.order_id
# join tb_product_info tpi
# on tod.product_id = tpi.product_id
# where event_time between '2021-09-26' and '2021-10-04'
# and status = 1
# and total_cnt > 0
# and tpi.shop_id = 901
# group by tod.product_id