缺少几天的Hive滚动平均数

8xiog9wr  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(328)

我正在用hive处理巨大的数据集,并尝试过去一周的滚动平均值。如果缺少一天的数据,我们要考虑6天的滚动平均值。自连接需要很长时间,因此尝试了窗口功能。
例如

Select date,avg(volume) over (order by date ROWS between 6 preceding AND current row) as Moving_AVG
From job_history;

无论如何,这可以通过配置单元窗口功能来完成吗?

z6psavjg

z6psavjg1#

Range between 6 preceding and current row ```
select date
,volume
,avg (volume) over
(
order by date
range between 6 preceding and current row
) as moving_avg

from job_history
;


#### 演示

create table job_history (date date,volume int);

insert into job_history values
('2017-01-01', 1),('2017-01-02', 2),('2017-01-05', 3),('2017-01-06', 4),('2017-01-08', 5)
,('2017-01-09', 6),('2017-01-10', 7),('2017-01-10', 8),('2017-01-10', 9),('2017-01-11',10)
,('2017-01-11',11),('2017-01-12',12),('2017-01-13',13),('2017-01-14',14),('2017-01-17',15)
;

select * from job_history
;

+------------------+--------------------+
| job_history.date | job_history.volume |
+------------------+--------------------+
| 2017-01-01 | 1 |
+------------------+--------------------+
| 2017-01-02 | 2 |
+------------------+--------------------+
| 2017-01-05 | 3 |
+------------------+--------------------+
| 2017-01-06 | 4 |
+------------------+--------------------+
| 2017-01-08 | 5 |
+------------------+--------------------+
| 2017-01-09 | 6 |
+------------------+--------------------+
| 2017-01-10 | 7 |
+------------------+--------------------+
| 2017-01-10 | 8 |
+------------------+--------------------+
| 2017-01-10 | 9 |
+------------------+--------------------+
| 2017-01-11 | 10 |
+------------------+--------------------+
| 2017-01-11 | 11 |
+------------------+--------------------+
| 2017-01-12 | 12 |
+------------------+--------------------+
| 2017-01-13 | 13 |
+------------------+--------------------+
| 2017-01-14 | 14 |
+------------------+--------------------+
| 2017-01-17 | 15 |
+------------------+--------------------+

select date
,volume
,avg (volume) over
(
order by date
range between 6 preceding and current row
) as moving_avg

from job_history
;

+------------+--------+------------+
| date | volume | moving_avg |
+------------+--------+------------+
| 2017-01-01 | 1 | 1.0 |
+------------+--------+------------+
| 2017-01-02 | 2 | 1.5 |
+------------+--------+------------+
| 2017-01-05 | 3 | 2.0 |
+------------+--------+------------+
| 2017-01-06 | 4 | 2.5 |
+------------+--------+------------+
| 2017-01-08 | 5 | 3.5 |
+------------+--------+------------+
| 2017-01-09 | 6 | 4.5 |
+------------+--------+------------+
| 2017-01-10 | 8 | 6.0 |
+------------+--------+------------+
| 2017-01-10 | 9 | 6.0 |
+------------+--------+------------+
| 2017-01-10 | 7 | 6.0 |
+------------+--------+------------+
| 2017-01-11 | 10 | 7.0 |
+------------+--------+------------+
| 2017-01-11 | 11 | 7.0 |
+------------+--------+------------+
| 2017-01-12 | 12 | 8.0 |
+------------+--------+------------+
| 2017-01-13 | 13 | 9.0 |
+------------+--------+------------+
| 2017-01-14 | 14 | 9.5 |
+------------+--------+------------+
| 2017-01-17 | 15 | 12.5 |
+------------+--------+------------+

相关问题