I am working with hive on huge dataset and trying rolling average for past one week. If data for one day is missing we want to consider rolling average with 6 days. Self joins are taking a long time so tried window functionality.
e.g.
Select date,avg(volume) over (order by date ROWS between 6 preceding AND current row) as Moving_AVG
From job_history;
Anyway this can be done with hive window functionality?
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 |
+------------+--------+------------+