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

我这个句子跑出来，动销率和滞销率都对得上，只是没有保留三位小数，但一旦用round保留3位小数，就报错显示10/2的动销率和滞销率数据缺失，问了豆包、ds都解答不了原因，各种办法调试、也想了很久都没有搞懂是为啥，遂放弃，就这样吧。有遇到过相同问题或知道原因的大佬，期待解惑~

with

t1 as(

    select count(distinct product_id) as released_product

    from tb_product_info

    where shop_id=901 and date_format(release_time,'%Y%m%d')<='20211001'),

t2 as(

    select count(distinct product_id) as released_product

    from tb_product_info

    where shop_id=901 and date_format(release_time,'%Y%m%d')<='20211002'),

t3 as(

    select count(distinct product_id) as released_product

    from tb_product_info

    where shop_id=901 and date_format(release_time,'%Y%m%d')<='20211003'),

t4 as(

    select count(distinct a.product_id) as sale_cnt_1001

    from tb_order_detail a

    left join tb_order_overall b on a.order_id=b.order_id

    left join tb_product_info c on a.product_id=c.product_id

    where b.status=1

        and (date_format(b.event_time,'%Y%m%d') between '20210925' and '20211001')

        and c.shop_id=901),

t5 as(

    select count(distinct a.product_id) as sale_cnt_1002

    from tb_order_detail a

    left join tb_order_overall b on a.order_id=b.order_id

    left join tb_product_info c on a.product_id=c.product_id

    where b.status=1

        and (date_format(b.event_time,'%Y%m%d') between '20210926' and '20211002')

        and c.shop_id=901),

t6 as(

    select

        count(distinct a.product_id) as sale_cnt_1003

    from tb_order_detail a

    left join tb_order_overall b on a.order_id=b.order_id

    left join tb_product_info c on a.product_id=c.product_id

    where b.status=1

        and (date_format(b.event_time,'%Y%m%d') between '20210927' and '20211003')

        and c.shop_id=901),

t7 as(

    select

        '2021-10-01' as dt,

        t4.sale_cnt_1001/t1.released_product as sale_rate,

        1-t4.sale_cnt_1001/t1.released_product as unsale_rate

    from t4 join t1 on 1=1),

t8 as(

    select

        '2021-10-02' as dt,

        t5.sale_cnt_1002/t2.released_product as sale_rate,

        1-t5.sale_cnt_1002/t2.released_product as unsale_rate

    from t5 join t2 on 1=1),

t9 as(

    select

        '2021-10-03' as dt,

        t6.sale_cnt_1003/t3.released_product as sale_rate,

        1-t6.sale_cnt_1003/t3.released_product as unsale_rate

    from t6 join t3 on 1=1)

select dt,sale_rate,unsale_rate from t7

union

select dt,sale_rate,unsale_rate from t8

union

select dt,sale_rate,unsale_rate from t9