There is the raw data to calculate moving average number:
2020-04-01 210
2020-04-02 125
2020-04-03 150
2020-04-04 230
2020-04-05 200
2020-04-06 25
2020-04-07 215
2020-04-08 300
2020-04-09 250
2020-04-10 220
To calculate the moving average with 5 days in excel:
Frome c1 till c5 there is no data.(In c5 ,=AVERAGE(B1:B5) is the formula,same logic for c6 till c10.)
Now calculate it with postgres.
postgres=# create table sales(order_date date,sale int);
postgres=# insert into sales values('2020-04-01',210),
('2020-04-02',125),('2020-04-03',150),('2020-04-04',230),
('2020-04-05',200),('2020-04-06',25),
('2020-04-07',215),('2020-04-08',300),('2020-04-09',250),('2020-04-10',220);
To calculate:
SELECT a.order_date,a.sale,
AVG(a.sale)
OVER(ORDER BY a.order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
AS avg_sales
FROM sales a ;
order_date | sale | avg_sales
------------+------+----------------------
2020-04-01 | 210 | 210.00
2020-04-02 | 125 | 167.50
2020-04-03 | 150 | 161.66
2020-04-04 | 230 | 178.75
2020-04-05 | 200 | 183.00
2020-04-06 | 25 | 146.00
2020-04-07 | 215 | 164.00
2020-04-08 | 300 | 194.00
2020-04-09 | 250 | 198.00
2020-04-10 | 220 | 202.00
How can make the result as below with psql command?
order_date | sale | avg_sales
------------+------+----------------------
2020-04-01 | 210 |
2020-04-02 | 125 |
2020-04-03 | 150 |
2020-04-04 | 230 |
2020-04-05 | 200 | 183.00
2020-04-06 | 25 | 146.00
2020-04-07 | 215 | 164.00
2020-04-08 | 300 | 194.00
2020-04-09 | 250 | 198.00
2020-04-10 | 220 | 202.00
