HiveSQL 面试题 - 查询每天最近三天的订单总额和平均订单额

最新推荐文章于 2024-04-19 16:24:40 发布

孙看看

最新推荐文章于 2024-04-19 16:24:40 发布

阅读量856

点赞数

分类专栏： HiveSQL 文章标签： hive sql 面试

本文链接：https://blog.csdn.net/SUNK2014/article/details/131856868

版权

HiveSQL 专栏收录该内容

12 篇文章 3 订阅

订阅专栏

1 需求

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

订单信息表 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

create table order_info(user_id string,
                        total_amount string,
                        create_date string,
                        order_id string) 
                        ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

101,29000.00,2021-09-27,1
101,70500.00,2021-09-28,2
101,43300.00,2021-09-29,3
101,860.00,2021-09-30,4
102,46180.00,2021-10-01,5
102,50000.00,2021-10-01,6
102,75500.00,2021-10-01,7
102,6170.00,2021-10-02,8
103,18580.00,2021-10-02,9
103,28000.00,2021-10-02,10
103,23400.00,2021-10-02,11
103,5910.00,2021-10-03,12
104,13000.00,2021-10-03,13
104,69500.00,2021-10-03,14
104,2000.00,2021-10-03,15
104,5380.00,2021-10-03,16
105,6210.00,2021-10-04,17
105,68000.00,2021-10-04,18
105,43100.00,2021-10-04,19
105,2790.00,2021-10-04,20
106,9390.00,2021-10-04,21
106,58000.00,2021-10-05,22
106,46600.00,2021-10-05,23
106,5160.00,2021-10-05,24
107,55350.00,2021-10-05,25
107,14500.00,2021-10-05,26
107,47400.00,2021-10-06,27
107,6900.00,2021-10-06,28
108,56570.00,2021-10-06,29
108,44500.00,2021-10-06,30
108,50800.00,2021-10-07,31
108,3900.00,2021-10-07,32
109,41480.00,2021-10-07,33
109,88000.00,2021-10-07,34
109,15000.00,2020-10-08,35
109,9020.00,2020-10-08,36
1010,9260.00,2020-10-08,37
1010,12000.00,2020-10-08,38
1010,23900.00,2020-10-08,39
1010,6790.00,2020-10-08,40

2 实现

思路

使用开窗函数 sum()，avg() 对数据进行聚合即可，注意数据的区间范围，不包含当天。

实现

select create_date,
       cast(sum(sum_amount)
                over (order by create_date rows between 3 preceding and 1 preceding) as decimal(16, 2)) total_3d,
       cast(avg(sum_amount)
                over (order by create_date rows between 3 preceding and 1 preceding) as decimal(16, 2)) avg_3d
from (select create_date, sum(total_amount) sum_amount from tmp_table.tmp_skk group by create_date) t1;

+--------------+------------+------------+
| create_date  |  total_3d  |   avg_3d   |
+--------------+------------+------------+
| 2020-10-08   | NULL       | NULL       |
| 2021-09-27   | 75970.00   | 75970.00   |
| 2021-09-28   | 104970.00  | 52485.00   |
| 2021-09-29   | 175470.00  | 58490.00   |
| 2021-09-30   | 142800.00  | 47600.00   |
| 2021-10-01   | 114660.00  | 38220.00   |
| 2021-10-02   | 215840.00  | 71946.67   |
| 2021-10-03   | 248690.00  | 82896.67   |
| 2021-10-04   | 343620.00  | 114540.00  |
| 2021-10-05   | 301430.00  | 100476.67  |
| 2021-10-06   | 404890.00  | 134963.33  |
| 2021-10-07   | 464470.00  | 154823.33  |
+--------------+------------+------------+