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

# 可以分类讨论，对于9-25--10-01
with t1 as (
    select count(product_id) as release_cnt
    from tb_product_info
    where shop_id = '901'
    and date_format(release_time, '%Y-%m-%d') <= '2021-10-01'
),t2 as (
    select count(DISTINCT product_id) as sale_cnt
    from (
        select a.product_id, a.order_id, b.shop_id
        from tb_order_detail as a
        left join tb_product_info as b
        on a.product_id = b.product_id
        where b.shop_id = '901'
    ) as c
    where order_id in (select order_id from tb_order_overall where date_format(event_time, '%Y-%m-%d') between '2021-09-25' and '2021-10-01')
),# 9-26--10-02
t3 as (
    select count(product_id) as release_cnt
    from tb_product_info
    where shop_id = '901'
    and date_format(release_time, '%Y-%m-%d') <= '2021-10-02'
),t4 as (
    select count(DISTINCT product_id) as sale_cnt
    from (
        select a.product_id, a.order_id, b.shop_id
        from tb_order_detail as a
        left join tb_product_info as b
        on a.product_id = b.product_id
        where b.shop_id = '901'
    ) as c
    where order_id in (select order_id from tb_order_overall where date_format(event_time, '%Y-%m-%d') between '2021-09-26' and '2021-10-02')
),# 9-27--10-03
t5 as (
    select count(product_id) as release_cnt
    from tb_product_info
    where shop_id = '901'
    and date_format(release_time, '%Y-%m-%d') <= '2021-10-03'
),t6 as (
    select count(DISTINCT product_id) as sale_cnt
    from (
        select a.product_id, a.order_id, b.shop_id
        from tb_order_detail as a
        left join tb_product_info as b
        on a.product_id = b.product_id
        where b.shop_id = '901'
    ) as c
    where order_id in (select order_id from tb_order_overall where date_format(event_time, '%Y-%m-%d') between '2021-09-27' and '2021-10-03')
),# 将三天的union起来
t7 as (
    select dt, round(sale_rate, 3) as sale_rate, round(1 - sale_rate, 3) as unsale_rate
    from (
        select '2021-10-01' as dt, sale_cnt / (select release_cnt from t1) as sale_rate
        from t2
        union all
        select '2021-10-02' as dt, sale_cnt / (select release_cnt from t3) as sale_rate
        from t4
        union all
        select '2021-10-03' as dt, sale_cnt / (select release_cnt from t5) as sale_rate
        from t6
    ) as g
)
select t7.*
from t7
right join (
    # 要判断那一天有没有店铺有销量，有销量的才输出
    select DISTINCT date_format(event_time, '%Y-%m-%d') as dt
    from tb_order_overall
    where date_format(event_time, '%Y-%m-%d') between '2021-10-01' and '2021-10-03'
) as e
on e.dt = t7.dt;