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

标签：10 create Hive 订单 2021 date total 刷题

需求描述

查询截止每天的最近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,
       round(sum(total_amount) over (order by datediff(create_date, '2000-01-01') range between 2 preceding and current row ),    2) as total_3d  ,
       round(avg(total_amount) over (order by datediff(create_date, '2000-01-01') range between 2 preceding and current row ),    2) as avg_3d
from (select create_date , sum(total_amount) as total_amount
         from order_info
         group by create_date ) t;

标签：10,create,Hive,订单,2021,date,total,刷题
From： https://www.cnblogs.com/wdh01/p/17041047.html