求解

【类型】：聚合窗口函数高级用法

【场景】：近几日的数据情况 【分类】：高级聚合窗口函数—窗口函数的移动平均

分析思路

难点：

1.当有2个变量时如何处理：使用高级窗口函数或者笛卡尔积

（1）统计订单完成记录和取消记录

加时间限制条件是为了减少计算量

  • [条件] 国庆头3天里近7日的时间范围

  • [使用] group by dt_7

（2）统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数

  • [使用] avg(finish_cut) over(order by dt_7 rows 6 preceding)
  • 或者 sum(finish_cut) over(order by dt_7 rows 6 preceding)/7

求解代码

方法一

with子句

with
    main as(
        #统计订单完成记录和取消记录（加时间限制是为了减少计算量）
        select
            date(finish_time) as dt_7,
            count(if(start_time is not null,order_id,null)) as finish_cut,
            count(if(start_time is null,order_id,null)) as cancel_cut
        from tb_get_car_order
        where date(finish_time) between '2021-09-25' and '2021-10-03'
        group by dt_7
        order by dt_7 desc
    )
#统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数
select distinct
    dt_7 as dt,
    round(avg(finish_cut) over(order by dt_7 rows 6 preceding),2) as finish_num_7d,
    round(avg(cancel_cut) over(order by dt_7 rows 6 preceding),2) as cancel_num_7d
from main
order by dt_7
limit 6,3

统计订单完成记录和取消记录输出

1	2021-10-03|1|1	
2	2021-10-02|3|1	
3	2021-10-01|2|0	
4	2021-09-30|2|0		
5	2021-09-29|1|0		
6	2021-09-28|1|0		
7	2021-09-27|1|0		
8	2021-09-26|1|1		
9	2021-09-25|2|0

每天的近7日日均订单完成量和日均订单取消量输出

1	2021-09-25|0.29|0.00		
2	2021-09-26|0.43|0.14		
3	2021-09-27|0.57|0.14		
4	2021-09-28|0.71|0.14		
5	2021-09-29|0.86|0.14		
6	2021-09-30|1.14|0.14		
7	2021-10-01|1.43|0.14	
8	2021-10-02|1.57|0.29	
9	2021-10-03|1.57|0.29

最后利用 limit 6,3 取国庆头3天。

方法二

from子查询

#统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数
select distinct
    dt_7 as dt,
    round(sum(finish_cut) over(order by dt_7 rows 6 preceding)/7,2) as finish_num_7d,
    round(sum(cancel_cut) over(order by dt_7 rows 6 preceding)/7,2) as cancel_num_7d
from(
    #统计订单完成记录和取消记录（加时间限制是为了减少计算量）
    select
        date(finish_time) as dt_7,
        count(if(start_time is not null,order_id,null)) as finish_cut,
        count(if(start_time is null,order_id,null)) as cancel_cut
    from tb_get_car_order
    where date(finish_time) between '2021-09-25' and '2021-10-03'
    group by dt_7
    order by dt_7 desc
    ) main
order by dt_7
limit 6,3

方法三

不使用窗口函数 使用union连接所有行

优点：思路清晰、暴力破解

缺点：当求的天数变多或者有些天数不需要计算近7日的信息时，会出现计算代码繁杂和不能计算的情况

with
    main as(
        #统计订单完成记录和取消记录（加时间限制是为了减少计算量）
        select
            date(finish_time) as dt_7,
            count(if(start_time is not null,order_id,null)) as finish_cut,
            count(if(start_time is null,order_id,null)) as cancel_cut
        from tb_get_car_order
        where date(finish_time) between '2021-09-25' and '2021-10-03'
        group by dt_7
        order by dt_7 desc
    )
    ,attr as(
        #统计国庆第一天近7日日均订单完成量和日均订单取消量
        select
            '2021-10-01' as dt,
            round(sum(if(datediff('2021-10-01',dt_7) between 0 and 6,finish_cut,0))/7,2) as finish_num_7d,
            round(sum(if(datediff('2021-10-01',dt_7) between 0 and 6,cancel_cut,0))/7,2) as cancel_num_7d
        from main
    )
    ,attr1 as(
        #统计国庆第二天近7日日均订单完成量和日均订单取消量
        select
            '2021-10-02' as dt,
            round(sum(if(datediff('2021-10-02',dt_7) between 0 and 6,finish_cut,0))/7,2) as finish_num_7d,
            round(sum(if(datediff('2021-10-02',dt_7) between 0 and 6,cancel_cut,0))/7,2) as cancel_num_7d
        from main
    )
    ,attr2 as(
        #统计国庆第三天近7日日均订单完成量和日均订单取消量
        select
            '2021-10-03' as dt,
            round(sum(if(datediff('2021-10-03',dt_7) between 0 and 6,finish_cut,0))/7,2) as finish_num_7d,
            round(sum(if(datediff('2021-10-03',dt_7) between 0 and 6,cancel_cut,0))/7,2) as cancel_num_7d
        from main
    )

#统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数
select *
from attr
union
select *
from attr1
union
select *
from attr2

方法四

不使用窗口函数 做笛卡尔积后查找近7日数据做处理

优点：当求的天数变多或者有些天数不需要计算近7日的信息时，也可以计算

with
    main as(
        #统计订单完成记录和取消记录（加时间限制是为了减少计算量）
        select
            date(finish_time) as dt_7,
            count(if(start_time is not null,order_id,null)) as finish_cut,
            count(if(start_time is null,order_id,null)) as cancel_cut
        from tb_get_car_order
        where date(finish_time) between '2021-09-25' and '2021-10-03'
        group by dt_7
        order by dt_7 desc
    )
    ,attr as(
        #得到国庆头三天日期（为了分别计算每个日期的近7日数据，先要得到头三天的日期）
        select distinct
            date(finish_time) as dt
        from tb_get_car_order
        where date(finish_time) between '2021-10-01' and '2021-10-03'
    )

#统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数
select
    dt,
    round(sum(if(datediff(dt,dt_7) between 0 and 6,finish_cut,0))/7,2) as finish_num_7d,
    round(sum(if(datediff(dt,dt_7) between 0 and 6,cancel_cut,0))/7,2) as cancel_num_7d
from main,attr
group by dt
order by dt