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

这题和SQL5哪一题思路完全一样

1. 先找9月25日至10月3日期间产生的所有订单

select * 
	from tb_get_car_record
    inner join tb_get_car_order
    using(order_id)
    where date(event_time) between '2021-09-25' AND '2021-10-03'

2. 按照日期找到每一天的完成订单及取消订单

 select date(event_time) as dt, 
        sum(case when isnull(fare) = FALSE then 1 ELSE 0 end) daily_complete,
        sum(case when isnull(fare)= TRUE THEN 1 ELSE 0 end) daily_cancel
        from tb_get_car_record
        inner join tb_get_car_order
        using(order_id)
        where date(event_time) between '2021-09-25' AND '2021-10-03'
        GROUP BY date(event_time)

3. 使用窗口函数技巧 sum() over(order by dt rows 6 preceding) 获取当前日期及其前6天的的信息

 select dt, 
        round(sum(daily_complete) over(order by dt rows 6 preceding)/7,2) finish_num_7d,
        round(sum(daily_cancel) over(order by dt rows 6 preceding)/7,2) cancel_num_7d
        from (
            select date(event_time) as dt, 
            sum(case when isnull(fare) = FALSE then 1 ELSE 0 end) daily_complete,
            sum(case when isnull(fare)= TRUE THEN 1 ELSE 0 end) daily_cancel
            from tb_get_car_record
            inner join tb_get_car_order
            using(order_id)
            where date(event_time) between '2021-09-25' AND '2021-10-03'
            GROUP BY date(event_time)
        ) temp 

4. 最后将10月1号及10月3号之间的统计信息选择出来

select * 
    from (
        select dt, 
        round(sum(daily_complete) over(order by dt rows 6 preceding)/7,2) finish_num_7d,
        round(sum(daily_cancel) over(order by dt rows 6 preceding)/7,2) cancel_num_7d
        from (
            select date(event_time) as dt, 
            sum(case when isnull(fare) = FALSE then 1 ELSE 0 end) daily_complete,
            sum(case when isnull(fare)= TRUE THEN 1 ELSE 0 end) daily_cancel
            from tb_get_car_record
            inner join tb_get_car_order
            using(order_id)
            where date(event_time) between '2021-09-25' AND '2021-10-03'
            GROUP BY date(event_time)
        ) temp 
    ) temp2
    where dt between '2021-10-01' AND '2021-10-03'