Hive 刷题——订单金额趋势分析

需求描述

查询截止每天的最近3天内的订单金额总和以及订单金额日平均值，保留两位小数，四舍五入。

期望结果如下：

create_date   (日期)	total_3d <decimal(16,2)> (最近3日订单金额总和)	avg_3d <decimal(16,2)> (最近3日订单金额日平均值)
2020-10-08	75970.00	75970.00
2021-09-27	104970.00	52485.00
2021-09-28	175470.00	58490.00
2021-09-29	142800.00	47600.00
2021-09-30	114660.00	38220.00
2021-10-01	215840.00	71946.67
2021-10-02	248690.00	82896.67
2021-10-03	343620.00	114540.00
2021-10-04	301430.00	100476.67
2021-10-05	404890.00	134963.33
2021-10-06	464470.00	154823.33
2021-10-07	519160.00	173053.33

需要用到的表：

订单信息表：order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

参考SQL

select create_date,
       cast(sum(total_amount) over (order by datediff(create_date,'2019-02-01') range between 2 preceding and current row ) as decimal(10, 2)) total_3d,
       cast(avg(total_amount) over (order by datediff(create_date,'2019-02-01')  range between 2 preceding and current row ) as decimal(10, 2)) avg_3d
from (select create_date, sum(total_amount) total_amount
      from order_info
      group by create_date) t;