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

 #取订单日期10-01到10-03
with
    t as (
        select
            date(event_time) as dt
        from
            tb_order_overall
        where
            date(event_time) >= '2021-10-01'
            and date(event_time) <= '2021-10-03'
    ),
    # 901商店已成交订单中，每个订单日期里面的product_id
    t1 as (
        select
            date(b.event_time) dt,a.product_id
        from
            tb_order_detail a
            join tb_order_overall b on a.order_id = b.order_id
            and b.status = 1
            join tb_product_info c on a.product_id = c.product_id
            and c.shop_id = '901'
    ),
    #计算每个日期下901商店的在售商品总数
    t2 as (
        SELECT
            calendar.dt,
            COUNT(DISTINCT t1.product_id) sum_product
        FROM
            (
                SELECT DISTINCT
                    date(event_time) dt
                FROM
                    tb_order_overall
            ) calendar
            CROSS JOIN tb_product_info t1
        WHERE
            t1.shop_id = '901'
            AND date(t1.release_time) <= calendar.dt
        GROUP BY
            calendar.dt
    )
select
    t.dt,
    round(count(distinct t1.product_id) / sum_product, 3) as sale_rate,
    ROUND(
        1 - (count(distinct t1.product_id) / sum_product),
        3
    ) as unsale_rate
from
    t
    left join t1 ON datediff(t.dt, t1.dt) between 0 and 6
    JOIN t2 on t.dt = t2.dt
group by
    t.dt
ORDER by
    t.dt