题解 | #国庆期间近7日日均取消订单量#

-- 思路：

• -- 1.筛选出'2021-09-25' and '2021-10-03' 之间的订单记录
• -- 2.按日期分组统计每日的订单完成量和订单取消量
• -- 3.然后使用窗口函数sum()统计每天的近7日日均订单完成量和日均订单取消量,核心用法：
• -- rows between 6 preceding and current row表示窗口大小为7，
• -- 范围从当前行往前6行，包括当前行
• -- 4.最后取国庆前三天记录按照dt排序即可

-- 思路：
--      1.筛选出'2021-09-25' and '2021-10-03' 之间的订单记录
--      2.按日期分组统计每日的订单完成量和订单取消量
--      3.然后使用窗口函数sum()统计每天的近7日日均订单完成量和日均订单取消量,核心用法：
--          rows between 6 preceding  and current row表示窗口大小为7，
--          范围从当前行往前6行，包括当前行
--      4.最后取国庆前三天记录按照dt排序即可
with
    t as (
        select
            date_format (r.event_time, '%Y-%m-%d') as dt,
            o.order_id,
            order_time,
            start_time,
            finish_time,
            fare
        from
            tb_get_car_order o
            join tb_get_car_record r on o.order_id = r.order_id
        where
            date_format (r.event_time, '%Y-%m-%d') between '2021-09-25' and '2021-10-03'
    ),
    t1 as (
        select
            dt,
            count(if (start_time is not null, order_id, null)) as day_finish_num,
            count(if (start_time is null, order_id, null)) as day_cancel_num
        from
            t
        group by
            dt
    ),
    t2 as (
        select
            dt,
            round(
                sum(day_finish_num) over (
                    rows between 6 preceding
                    and current row
                ) / 7,
                2
            ) as finish_num_7d,
            round(
                sum(day_cancel_num) over (
                    rows between 6 preceding
                    and current row
                ) / 7,
                2
            ) as cancel_num_7d
        from
            t1
    )
select
    dt,
    finish_num_7d,
    cancel_num_7d
from
    t2
where
    dt >= '2021-10-01'
order by
    dt