国庆期间近7日日均取消订单量

select *
from
(select date(order_time) as dt,
       round(avg(count(start_time)) over(order by date(order_time) rows between 6 preceding and current row),2) as finish_num_7d,
       round(avg(sum(if(start_time is null,1,0))) over(order by date(order_time) rows between 6 preceding and current row),2) as cancel_num_7d
from tb_get_car_order
group by date(order_time)) as t
where dt between '2021-10-01' and '2021-10-03'

avg(字段名) over(
        partition by 用于分组的列名 order by 用于排序的列名 asc/desc rows between A and B)

rows between 2 preceding and current row #取当前行和前面两行

rows between unbounded preceding and current row #包括本行和之前所有的行

rows between current row and unbounded following #包括本行和之前所有的行

rows between 3 preceding and current row #包括本行和前面三行

rows between 3 preceing and 1 following #从前面三行和下面一行，总共五行

SELECT 
  dt,
  ROUND(SUM(finish_cnt) OVER w / 7, 2) finish_sum_7d,
  ROUND(SUM(cancel_cnt) OVER w / 7, 2) cancel_sum_7d
FROM (
SELECT
  DATE(finish_time) dt,
  SUM(start_time IS NOT NULL) finish_cnt,
  SUM(start_time IS NULL) cancel_cnt
FROM tb_get_car_order
WHERE DATE(order_time) BETWEEN '2021-09-25' AND '2021-10-03'
GROUP BY 1
) t 
WINDOW w AS (ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING)
ORDER BY 1
LIMIT 6, 3

with day as(
    SELECT
        DATE_FORMAT(order_time,'%Y-%m-%d') order_day,
        DATE_FORMAT(start_time,'%Y-%m-%d') start_day
    FROM tb_get_car_order
)
select 
    t.order_day,
    round(count(t1.start_day)/7,2) finish_num,
    round(sum(isnull(t1.start_day))/7,2) concel_num
from (select distinct order_day from day where order_day in ('2021-10-01','2021-10-02','2021-10-03')) t
left join day t1
on DATEDIFF(t.order_day,t1.order_day)<7 and DATEDIFF(t.order_day,t1.order_day)>=0
group by t.order_day
order by t.order_day asc
;

WITH t1 as (
    SELECT DISTINCT DATE(order_time) dt FROM tb_get_car_order
    WHERE DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-03'),
t2 as (
    SELECT DATE(order_time) order1_dt,order_id
    FROM tb_get_car_order
    WHERE fare IS NOT NULL
),
t3 as (
    SELECT DATE(order_time) order2_dt,order_id
    FROM tb_get_car_order
    WHERE fare IS NULL
)
SELECT dt,ROUND(COUNT(distinct t2.order_id)/7,2) finish_num_7d,
ROUND(count(distinct t3.order_id)/7,2) cancel_num_7d
FROM t1 LEFT JOIN t2 ON datediff(dt,order1_dt) BETWEEN 0 AND 6
LEFT JOIN t3 ON datediff(dt,order2_dt) BETWEEN 0 AND 6
GROUP BY dt
ORDER BY dt;

想到了两种方法，都提交成功了。
不过个人更偏好方法2。

方法1：sum() over(range)

with a as(
    select date(order_time) as dt,
        count(start_time) as finish_per_d,
        count(*)-count(start_time) as cancel_per_d,
        datediff(max(date(order_time)),'2021-09-25') as dt_int
    from tb_get_car_order
    where date(order_time) between '2021-09-25' and '2021-10-03'
    group by date(order_time)
)
select dt
    ,round(sum(finish_per_d) over(order by dt_int range between 6 PRECEDING and CURRENT ROW)/7,2) as finish_num_7d 
    ,round(sum(cancel_per_d) over(order by dt_int range between 6 PRECEDING and CURRENT ROW)/7,2) as cancel_num_7d
from a
limit 3 offset 6

方法2：构造日期列，求笛卡尔积，然后聚合

with a as(
    select date(order_time) as dt,
        count(start_time) as finish_per_d,
        count(*)-count(start_time) as cancel_per_d
    from tb_get_car_order
    where date(order_time) between '2021-09-25' and '2021-10-03'
    group by date(order_time)
)
, b as (
    select '2021-10-01' as dt union select '2021-10-02' union select '2021-10-03'
)
select b.dt,
    round(sum(finish_per_d)/7,2) as finish_num_7d,
    round(sum(cancel_per_d)/7,2) as cancel_num_7d
from a join b on datediff(b.dt,a.dt) between 0 and 6
group by b.dt
order by b.dt

求解

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

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

分析思路

难点：

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

select a,round(finish_num/7,2) finish_num_7d,round(finish_a/7,2) cancel_num_7d  from
(select a,sum(b) over(order by a rows 6 preceding) finish_num,sum(c) over(order by a rows 6 preceding) as finish_a
from (select date(finish_time) a,count(fare) b,sum(case when start_time is null then 1 else 0 end) as c from tb_get_car_order group by a)t)m
where a between '2021-10-01' and '2021-10-03'
order by a
切记日期筛选要在滑动窗口的上一层，不能在同一个查询语句里，别再忘了！！

select*
from (select date(order_time) as dt,
round(avg(sum(if(start_time is null,0,1))) over(order by date(order_time) rows 6 preceding),2) finish_num_7d,
round(avg(sum(if(start_time is null,1,0))) over (order by date(order_time) rows 6 preceding),2) cancel_num_7d
from tb_get_car_order
group by dt) t0
where dt between '2021-10-01' and '2021-10-03'
order by dt

# 提取国庆前三天日期
with t1 as (
select distinct date(order_time) as dt
from tb_get_car_order
where date(order_time) between '2021-10-01' and '2021-10-03')

# 统计国庆前三天日期的7日内数据
select t1.dt,
       round(sum(if(start_time is not null, 1, 0))/7, 2) as finish_num_7d,
       round(sum(if(start_time is null, 1, 0))/7, 2) as cancel_num_7d
from tb_get_car_order o join t1 on datediff(t1.dt, o.order_time) between 0 and 6
group by 1
order by 1

with dim_date as (
select '2021-10-01' as dt union all
select '2021-10-02' as dt union all 
select '2021-10-03' as dt 
) 

select 
t1.dt,
round(count(start_time)/7,2) as finish_num_7d,
round(sum(if(start_time is null,1,0))/7,2) as cancel_num_7d
from dim_date  t1 
left join tb_get_car_order t2 
 on datediff(t1.dt, date(t2.order_time)) between  0 and 6 
group by t1.dt

with t as (
    select
        date(finish_time) dt,
        round(avg(count(start_time)) over(order by date(finish_time) rows 6 preceding),2) finish_num_7d,
        round(avg(sum(isnull(start_time))) over(order by date(finish_time) rows 6 preceding),2) cancel_num_7d
    from tb_get_car_order
    group by dt
)
select * from t where dt between '2021-10-01' and '2021-10-03' order by dt;

select *
from 
(
    select date(order_time) dt,
round(avg(count(start_time)) over(order by date(order_time) range interval 6 day preceding),2) as finish_num_7d,
round(avg(sum(case when start_time is null then 1 else 0 end)) over(order by date(order_time) range interval 6 day preceding),2) as cancel_num_7d
from tb_get_car_order 
group by dt
) t
where dt between '2021-10-01' and '2021-10-03'

select *
from (select date(order_time) dt,
             round(sum(count(start_time)) over(order by date(order_time) rows 6 preceding) / 7,2),
             round(sum(count(if(start_time is null,1,null))) over(order by date(order_time) rows 6 preceding) / 7,2)
      from tb_get_car_order 
      group by date(order_time)) t
having dt in ("2021-10-01","2021-10-02","2021-10-03")
order by dt

select * from (
    select dt,
    round(avg(finish_num) over(order by dt rows 6 preceding),2) as finish_num_7d,
    round(avg(cancel_num) over(order by dt rows 6 preceding),2) as cancel_num_7d
    from (
        select date(order_time) as dt,
        count(start_time) as finish_num,
        sum(start_time is null) as cancel_num
        from tb_get_car_order
        group by dt
    )t1
)as t2
where dt between '2021-10-01' and '2021-10-03'
order by dt;